집계 함수 다루기

실전 문제에 SQL 적용하기

Dmitriy (Dima) Gorenshteyn

Lead Data Scientist, Memorial Sloan Kettering Cancer Center

GROUP BY

SELECT rating,
  AVG(replacement_cost)
FROM film
GROUP BY rating

GROUP BY

SELECT rating,
  AVG(replacement_cost)
FROM film
GROUP BY rating

GROUP BY

SELECT rating,
  AVG(replacement_cost)
FROM film
GROUP BY rating

GROUP BY

SELECT rating,
  AVG(replacement_cost)
FROM film
GROUP BY rating

GROUP BY

SELECT rating,
  AVG(replacement_cost)
FROM film
GROUP BY rating

수치 집계 함수

SELECT rating,
       AVG(replacement_cost) AS avg_cost,
       COUNT(rating) AS number_elements,
       SUM(replacement_cost) AS total_cost
FROM film
GROUP BY rating;

rating    avg_cost    number_elements    total_cost
PG-13     20.40256    223                4549.77    
R         20.23103    195                3945.05    
G         20.12483    178                3582.22    
PG        18.95907    194                3678.06

문자열 집계 함수

STRING_AGG(<column>, '<separator>')

SELECT rating,
       STRING_AGG(title, ',') as films
FROM film
GROUP BY rating;

rating       films
PG-13        AIRPLANE SIERRA,ALABAMA DEVIL,...
R            AIRPORT POLLOCK,DATE SPEED,...
G            ACE GOLDFINGER,AFFAIR PREJUDICE,...

이제 집계해 봅시다!

실전 문제에 SQL 적용하기