집계와 GROUP BY

여러 행을 하나로 줄입니다

지금까지는 행을 하나씩 냈습니다. 집계 함수는 여러 행을 탐색해 값 하나로 줄입니다. 몇 개인지, 모두 합하면 얼마인지, 평균이 얼마인지 같은 것입니다.

SELECT COUNT(*)          AS 글수,
       SUM(hit_count)   AS 조회수합,
       AVG(hit_count)   AS 평균,
       MIN(hit_count)   AS 최소,
       MAX(hit_count)   AS 최대
FROM Board.POSTS;

507행이 한 행으로 줄었습니다. 집계 함수를 적으면 GROUP BY 가 없어도 테이블 전체가 한 덩어리가 됩니다.

정수의 평균은 정수로 나옵니다

위 결과에서 평균이 194 였습니다. 98521 ÷ 507 은 194.32… 인데 소수점 아래가 없습니다.

SELECT AVG(hit_count)                    AS 정수평균,
       AVG(CAST(hit_count AS FLOAT))  AS 실수평균
FROM Board.POSTS;

AVG 는 넣은 값의 형식으로 결과를 냅니다. int 를 넣으면 int 가 나오면서 소수점 아래가 버려집니다. 오류가 나지 않으므로 알아채기 어렵습니다.

비율이나 평점처럼 소수가 뜻을 갖는 자리에서는 형 변환을 하십시오. 돈이라면 DECIMAL 로 바꿉니다(1.4) — FLOAT 은 오차가 있습니다.

묶어서 세기 — GROUP BY

GROUP BY 에 적은 열의 값이 같은 행끼리 묶고, 묶음마다 집계합니다.

SELECT board_num, COUNT(*) AS 글수, AVG(hit_count) AS 평균조회
FROM Board.POSTS
GROUP BY board_num
ORDER BY board_num;

번호 대신 이름을 내려면 조인합니다(2.1).

SELECT B.name AS 게시판, COUNT(*) AS 글수
FROM Board.POSTS AS P
    INNER JOIN Board.BOARDS AS B ON B.num = P.board_num
GROUP BY B.name
ORDER BY 글수 DESC;

묶지 않은 열은 낼 수 없습니다

묶음 하나에 여러 행이 들어 있으므로, 그 묶음의 제목이 무엇인지는 답이 없습니다. 그런 열을 SELECT 에 적으면 오류입니다.

SELECT board_num, title, COUNT(*) AS c
FROM Board.POSTS
GROUP BY board_num;

SELECT 에 적을 수 있는 것은 둘뿐입니다.

• GROUP BY 에 적은 열
• 집계 함수로 감싼 것 — MAX(title) 처럼

제목까지 보고 싶다면 묶는 기준을 바꾸거나(GROUP BY board_num, title), 묶지 않고 순위를 매기는 방법을 사용해야 합니다. 윈도 함수(2.7)가 그 일을 합니다.

이 오류는 문장을 해석하는 단계에서 나므로 TRY…CATCH 로 잡히지 않습니다. 4.5 에서 다시 다룹니다.

COUNT 는 세 가지입니다

SELECT COUNT(*)                     AS 전체행,
       COUNT(email)                 AS 값있는행,
       COUNT(DISTINCT user_id)     AS 서로다른아이디
FROM Member.USERS;

SUM · AVG · MIN · MAX 도 NULL 을 빼고 셈합니다. 평균에서 특히 중요합니다. 값이 없는 행을 0 으로 계산하려면 AVG(COALESCE(열, 0)) 으로 채워 넣어야 합니다.

LEFT JOIN 뒤의 COUNT(*) 는 1 을 셉니다

이 단원에서 가장 자주 어긋나는 자리입니다. 글마다 댓글이 몇 개인지 세려고 LEFT JOIN 하고 COUNT(*) 를 적으면, 댓글이 하나도 없는 글이 1 로 나옵니다.

SELECT TOP 5 P.num, P.title,
       COUNT(*)      AS count_star,
       COUNT(C.num)  AS count_col
FROM Board.POSTS AS P
    LEFT JOIN Board.COMMENTS AS C ON C.post_num = P.num
GROUP BY P.num, P.title
ORDER BY P.num;

다섯째 글을 보십시오. 댓글이 없는데 COUNT(*) 가 1 입니다.

LEFT JOIN 이 짝을 못 찾으면 그 자리를 NULL 로 채운 행 하나를 만듭니다(2.1). COUNT(*) 는 행을 세므로 그 행도 1 로 셉니다. COUNT(C.num) 은 값이 있는 것만 세므로 0 이 나옵니다.

LEFT JOIN 뒤에 세는 것은 언제나 COUNT(오른쪽 테이블의 열) 입니다. 댓글 수가 모두 1 씩 부풀어 있는 목록 화면은 대개 이 실수입니다.

여러 열로 묶기

쉼표로 이으면 그 조합마다 묶습니다.

SELECT board_num, depth, COUNT(*) AS 건수
FROM Board.POSTS
GROUP BY board_num, depth
ORDER BY board_num, depth;

공지사항(1번)에는 depth 가 0 인 줄만 있습니다. 답글을 받지 않는 게시판이라 답글이 아예 없기 때문입니다(1.2).

없는 조합은 줄이 생기지 않습니다. 통계표에 빈칸 없이 0 을 채워야 한다면 날짜나 분류 목록을 따로 만들어 이어야 합니다(6.8).

직접 해보기