잔재미코딩

데이터베이스 예제코드 (MySQL+AWS) - 복잡한 조건의 데이터 SQL로 추출하기 예제2

7. 복잡한 조건의 데이터 SQL로 추출하기 예제2

예제2: film테이블에서 rating (등급)으로 그룹을 묶어서, 각 등급별 영화 갯수와 rating (등급), 각 그룹별 평균 rental_rate (렌탈 비용) 출력하되, 영화 갯수와 평균 렌탈 비용은 각각 total_films, avg_rental_rate 으로 출력하고, avg_rental_rate이 높은 순으로 출력하시오

문제 나누기1: film테이블에서 rating (등급)으로 그룹을 묶어서, 각 등급별 영화 갯수 출력하기
In [56]:
SQL_QUERY = """
    SELECT rating, COUNT(*) FROM film GROUP BY rating;
"""
In [57]:
pd.read_sql(SQL_QUERY, db)
Out[57]:
rating COUNT(*)
0 G 178
1 PG 194
2 PG-13 223
3 R 195
4 NC-17 210
문제 나누기2: film테이블에서 rating (등급)으로 그룹을 묶어서, 각 등급별 영화 갯수와 등급 출력하기
In [58]:
SQL_QUERY = """
    SELECT rating, COUNT(*) FROM film GROUP BY rating;
"""
In [59]:
pd.read_sql(SQL_QUERY, db)
Out[59]:
rating COUNT(*)
0 G 178
1 PG 194
2 PG-13 223
3 R 195
4 NC-17 210
문제 나누기3: film테이블에서 rating (등급)으로 그룹을 묶어서, 각 등급별 영화 갯수와 rating (등급), 각 그룹별 평균 rental_rate (렌탈 비용) 출력하기
In [60]:
SQL_QUERY = """
    SELECT COUNT(*), rating, AVG(rental_rate) FROM film GROUP BY rating;
"""
In [61]:
pd.read_sql(SQL_QUERY, db)
Out[61]:
COUNT(*) rating AVG(rental_rate)
0 178 G 2.888876
1 194 PG 3.051856
2 223 PG-13 3.034843
3 195 R 2.938718
4 210 NC-17 2.970952
문제 나누기4: film테이블에서 rating (등급)으로 그룹을 묶어서, 각 등급별 영화 갯수와 rating (등급), 각 그룹별 평균 rental_rate (렌탈 비용) 출력하되, 평균 렌탈비용이 높은 순으로 출력하시오
In [68]:
SQL_QUERY = """
    SELECT COUNT(*), rating, AVG(rental_rate) FROM film GROUP BY rating ORDER BY AVG(rental_rate) DESC;
"""
In [69]:
pd.read_sql(SQL_QUERY, db)
Out[69]:
COUNT(*) rating AVG(rental_rate)
0 194 PG 3.051856
1 223 PG-13 3.034843
2 210 NC-17 2.970952
3 195 R 2.938718
4 178 G 2.888876
문제 나누기5: film테이블에서 rating (등급)으로 그룹을 묶어서, 각 등급별 영화 갯수와 rating (등급), 각 그룹별 평균 rental_rate (렌탈 비용) 출력하되, 영화 갯수와 평균 렌탈 비용은 각각 total_films, avg_rental_rate 으로 출력하고, avg_rental_rate이 높은 순으로 출력하시오
In [70]:
SQL_QUERY = """
    SELECT 
        COUNT(*) AS total_films, 
        rating, 
        AVG(rental_rate) AS avg_rental_rate 
    FROM film 
    GROUP BY rating 
    ORDER BY avg_rental_rate DESC;
"""
In [71]:
pd.read_sql(SQL_QUERY, db)
Out[71]:
total_films rating avg_rental_rate
0 194 PG 3.051856
1 223 PG-13 3.034843
2 210 NC-17 2.970952
3 195 R 2.938718
4 178 G 2.888876

출력할 번호를 사용해서 SQL구문을 작성할 수도 있습니다.

  • SQL 구문이 복잡해지면 INDENTATION 을 할 수 있습니다.
In [158]:
SQL_QUERY = """
    SELECT
        rating,
        COUNT(*) "total_films",
        AVG(rental_rate) "average_rental_rate"
    FROM film
    GROUP BY 1
    ORDER BY 3
    ;
"""
  • 1 은 rating, 3 은 AVG(rental_rate) "average_rental_rate" 임
  • """ """ 사이에는 " " 따옴표가 가능하지만, " " 안에서 쓸 때는 ' ' 로 써야 에러가 나지 않음
  • 따옴표 없이 사용해도 무방함
In [162]:
SQL_QUERY = """
    SELECT
        rating,
        COUNT(*) total_films,
        AVG(rental_rate) average_rental_rate
    FROM film
    GROUP BY 1
    ORDER BY 3
    ;
"""
In [163]:
pd.read_sql(SQL_QUERY, db)
Out[163]:
rating total_films average_rental_rate
0 G 178 2.888876
1 R 195 2.938718
2 NC-17 210 2.970952
3 PG-13 223 3.034843
4 PG 194 3.051856