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

이해하기 쉽고, 장황하지 않은 자료를 기반으로 강의를 진행합니다.
잔재미코딩 소식 공유
좀더 제약없이, IT 컨텐츠를 공유하고자, 자체 온라인 사이트와, 다음 두 채널도 오픈하였습니다
응원해주시면, 곧 좋은 컨텐츠를 만들어서 공유하겠습니다
●  잔재미코딩 뉴스레터 오픈 [구독해보기]
●  잔재미코딩 유투브 오픈 [구독해보기]

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;
"""
본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다
체계적으로 전문가 레벨까지 익힐 수 있도록 온라인 강의 로드맵을 제공합니다
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 (렌탈 비용) 출력하되, 평균 렌탈비용이 높은 순으로 출력하시오
본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다
체계적으로 전문가 레벨까지 익힐 수 있도록 온라인 강의 로드맵을 제공합니다
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
본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다
체계적으로 전문가 레벨까지 익힐 수 있도록 온라인 강의 로드맵을 제공합니다

출력할 번호를 사용해서 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