[MariaDB] GROUP BY

• GROUP BY
  • 선택된 레코드의 집합을 특정 값으로 그룹화한 결과 집합
  • SELECT 컬럼명 FROM 테이블명 GROUP BY 컬럼명;
  • 사용목적
    • 데이터의 값을 집계하기 위해
    • 주로 집계 함수와 같이 사용(total sum, average 등)
  • 아래 excel의 경우 Region을 group화 시켜 통계값 산출

    

    → Region으로 묶을 경우 Items는 여러개지만 출력할 수 없음. 하나의 row로 출력하기 때문

  • 집계함수
    • COUNT() : 행의 개수를 세어줌 - null 제외
      • COUNT(*) - 전체 행의 갯수
    • AVG() : 행 안에 있는 값의 평균을 내어줌

    SELECT round(avg(price), 0) from post; -- 소숫점 자릿수 0개 표시

    • MIN() : 행 안에 있는 값의 최솟값을 반환해줌
    • MAX() : 행 안에 있는 값의 최댓값을 반환해줌
    • SUM() : 행 안에 있는 값의 합을 내어줌
  • 예시)
    • select author_id, count(*) from post group by author_id; - 보통 group by 한 컬럼을 같이 조회함
      • author_id로 그룹화한 데이터의 갯수구하는 집계 SQL

      

      • select count(author_id) from post group by author_id; - null은 count X

        

    • 만약 post마다 원고료가 있었다면, select author_id, sum(price), avg(price) from post group by author_id;

      

  • HAVING 절
    • HAVING 절은 GROUP BY를 사용하여 그룹화된 후의 데이터에 대한 조건을 설정

      → Region 별로 sales 평균 값을 구하되, 평균값이 300달러 이상인 건만 출력하시오. (GROUP BY 후 데이터 거르기 - HAVING)

      select author_id, avg(price) from post group by author_id having avg(price) > 5000;

    • WHERE 절은 데이터를 그룹화하기 전의 개별 레코드에 대한 조건을 설정

      → author_id 별로 price 평균 값을 구하되, 건별로 300달러 이상인 데이터만 평균을 내서 출력하시오. (WHERE 조건으로 데이터를 거른 후 GROUP BY)

      select author_id, avg(price) from post where price > 5000 group by author_id;

    • HAVING 절은 주로 집계 함수(COUNT(), SUM(), AVG() 등)와 함께 특정 조건을 만족하는 그룹만을 필터링하고 싶을 때 사용
    • select author_id, count(*) as count from post group by author_id having count > 3;
    • *프로그래머스 SQL문제(group by, count, date_Format - 입양 시각 구하기(1))

      https://school.programmers.co.kr/learn/courses/30/lessons/59412

      --%H랑 %h의 차이는..? 오전오후 차이인듯 (09로 나오는데 정답처리됨)
      SELECT DATE_FORMAT(DATETIME, '%H') AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE DATE_FORMAT(DATETIME, '%H') >= 9 AND DATE_FORMAT(DATETIME, '%H') < 20 GROUP BY HOUR ORDER BY HOUR;
      -- cast로 정수 변환해서 09를 9로 나오게
      SELECT cast(DATE_FORMAT(DATETIME, '%H') as unsigned) AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE DATE_FORMAT(DATETIME, '%H') >= 9 AND DATE_FORMAT(DATETIME, '%H') < 20 GROUP BY HOUR ORDER BY HOUR;
      --HOUR()로 사용해도 됨
      SELECT HOUR(DATETIME) AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) >= 9 AND HOUR(DATETIME) < 20 GROUP BY HOUR ORDER BY HOUR;

    • *프로그래머스 SQL문제(join, group by - 성분으로 구분한 아이스크림 총 주문량)

      https://school.programmers.co.kr/learn/courses/30/lessons/133026

      SELECT INGREDIENT_TYPE, SUM(TOTAL_ORDER) AS TOTAL_ORDER FROM FIRST_HALF A LEFT JOIN ICECREAM_INFO B ON A.FLAVOR = B.FLAVOR GROUP BY INGREDIENT_TYPE;

    • *프로그래머스 SQL문제(join, group by, count, like - 자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기)

      https://school.programmers.co.kr/learn/courses/30/lessons/151137

      SELECT CAR_TYPE, COUNT(*) AS CARS FROM CAR_RENTAL_COMPANY_CAR WHERE OPTIONS LIKE '%통풍시트%' OR OPTIONS LIKE '%열선시트%' OR OPTIONS LIKE '%가죽시트%' GROUP BY CAR_TYPE ORDER BY CAR_TYPE;

    • *프로그래머스 SQL문제(group by 2개 이상 - 재구매가 일어난 상품과 회원 리스트 구하기)

      https://school.programmers.co.kr/learn/courses/30/lessons/131536

      SELECT USER_ID, PRODUCT_ID FROM ONLINE_SALE GROUP BY USER_ID, PRODUCT_ID HAVING COUNT(PRODUCT_ID) >= 2 ORDER BY USER_ID, PRODUCT_ID DESC;

    • UNION - WITH RECURSIVE
      • WITH RECURSIVE 키워드는 sql에서 재귀문으로서 자기 자신을 참조하여 반복적으로 데이터를 생성하거나 변형하면서 하나의 테이블을 만드는 용도로 사용
      • UNION과 함께 사용되며 데이터행을 더해나가는 방식
      • WITH RECURSIVE 재귀문에서 WHERE 절은 재귀적으로 생성되는 각각의 행에 대해 평가되며, 조건이 거짓이 되는 순간 더 이상 새로운 행을 생성하지 않고, 전체 재귀문이 STOP

        WITH RECURSIVE number_sequence(HOUR) AS  --테이블명, 컬럼명 지정
        ( 
        	SELECT 0        -- HOUR에 0 부터 입력
        	UNION ALL
        	SELECT HOUR + 1 FROM number_sequence WHERE HOUR < 23  -- HOUR +1이 23이 될 때 까지 반복
        )SELECT HOUR, 0 AS COUNT FROM number_sequence;

        • *프로그래머스 SQL문제(재귀, union, count, not in - 입양 시각 구하기(2))

          https://school.programmers.co.kr/learn/courses/30/lessons/59413

          WITH RECURSIVE number_sequence(HOUR) AS
          ( 
          	SELECT 0      
          	UNION ALL
          	SELECT HOUR + 1 FROM number_sequence WHERE HOUR < 23 
          )SELECT HOUR, 0 AS COUNT FROM number_sequence
          WHERE HOUR NOT IN (SELECT HOUR(DATETIME) AS HOUR FROM ANIMAL_OUTS)
          UNION SELECT HOUR(DATETIME) AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS GROUP BY HOUR ORDER BY HOUR;