SQL 조회 문법 총정리 4편 (MySQL/Oracle 정규식, 윈도우 함수 심화, 피벗)

Advanced SQL Query Syntax Part 4 (MySQL/Oracle Regex, Advanced Window Functions, Pivot)

1, 2편에서 사용했던 Users 테이블과 Orders 테이블을 계속 사용합니다.

• MySQL
  CUBE를 직접 지원하지 않습니다. UNION ALL과 여러 GROUP BY 조합을 통해 유사하게 구현해야 합니다.
  (MySQL 8.0.30부터 GROUPING SETS는 지원 예정이라는 언급이 있으나, 일반적으로는 ROLLUP만 고려하는 것이 안전합니다.)
• Oracle
  GROUP BY CUBE(col1, col2, ...)를 지원합니다. GROUPING(col) 함수로 집계 여부를 확인할 수 있습니다.

• Oracle (CUBE 및 GROUPING 함수 사용)

  SELECT
      -- U.name이 CUBE에 의해 집계된 것이면 NULL, 아니면 이름
      CASE WHEN GROUPING(U.name) = 1 THEN '모든 사용자' ELSE U.name END AS user_name,
      -- O.product가 CUBE에 의해 집계된 것이면 NULL, 아니면 상품명
      CASE WHEN GROUPING(O.product) = 1 THEN '모든 상품' ELSE O.product END AS product_name,
      SUM(O.amount) AS total_sales -- 주문 총액
  FROM Orders O
  JOIN Users U ON O.user_id = U.id
  GROUP BY CUBE(U.name, O.product) -- 이름과 상품의 모든 조합으로 그룹화하고 CUBE 적용
  ORDER BY U.name NULLS LAST, O.product NULLS LAST; -- NULLS LAST로 총계/소계 행들을 마지막에 정렬

(결과는 (사용자, 상품), (사용자, 모든 상품), (모든 사용자, 상품), (모든 사용자, 모든 상품)의 조합에 대한 합계를 보여줍니다.)

MySQL에서는 이 기능을 구현하려면 여러 GROUP BY 쿼리를 UNION ALL로 연결해야 하므로 코드가 매우 길어집니다.

• MySQL
  MySQL 8.0.1부터 GROUPING SETS를 지원하기 시작했다는 언급이 있으나, 버전 및 환경에 따라 지원 여부가 다를 수 있습니다. 일반적으로는 UNION ALL을 통한 수동 구현을 고려합니다. (코딩테스트 환경에서는 보수적으로 판단)
• Oracle
  GROUP BY GROUPING SETS((col1, col2), (col1), (col2), ()) 등을 지원합니다. ()는 전체 총계를 의미합니다.

• Oracle (GROUPING SETS 사용)

  SELECT
      U.name AS user_name, -- 사용자 이름
      O.product AS product_name, -- 상품명
      SUM(O.amount) AS total_sales, -- 주문 총액
      GROUPING_ID(U.name, O.product) AS grouping_level -- 어떤 그룹핑 조합인지 식별 (0: name,product / 1: name / 2: product / 3: 전체)
  FROM Orders O
  JOIN Users U ON O.user_id = U.id
  GROUP BY GROUPING SETS (
      (U.name, O.product), -- 사용자별, 상품별
      (U.name),            -- 사용자별
      (O.product),         -- 상품별
      ()                   -- 전체 총계
  )
  ORDER BY U.name NULLS LAST, O.product NULLS LAST;

(결과는 각 GROUPING SETS에 지정된 조합별로 집계된 행들을 보여줍니다.)

MySQL에서는 각 GROUP BY 결과를 UNION ALL로 합쳐야 합니다.

• MySQL
  REGEXP 또는 RLIKE (동의어, 패턴 매칭). MySQL 8.0.4부터 REGEXP_LIKE(), REGEXP_REPLACE(), REGEXP_SUBSTR(), REGEXP_INSTR() 등 다양한 함수 지원. 패턴 문법은 POSIX ERE(Extended Regular Expression)를 따릅니다.
• Oracle
  REGEXP_LIKE(source_char, pattern [, match_parameter]), REGEXP_REPLACE(), REGEXP_SUBSTR(), REGEXP_INSTR(), REGEXP_COUNT(). 패턴 문법은 POSIX ERE 및 Perl 스타일의 확장을 일부 지원합니다. match_parameter로 대소문자 구분('i'), 여러 줄 매칭('m') 등을 설정할 수 있습니다.

• MySQL (8.0.4+)

  SELECT
      name,  -- 사용자 이름
      email  -- 사용자 이메일
  FROM Users
  -- 이메일이 정규표현식 패턴과 일치하는지 확인
  -- ^: 문자열 시작, [A-Za-z0-9._%+-]+: 해당 문자들 1회 이상 반복, @: @ 문자
  -- (example\\.com|another\\.com): 'example.com' 또는 'another.com' (점은 이스케이프, |는 OR)
  -- $: 문자열 끝
  WHERE REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@(example\\.com|another\\.com)$');

• Oracle

  SELECT
      name,  -- 사용자 이름
      email  -- 사용자 이메일
  FROM Users
  -- 이메일이 정규표현식 패턴과 일치하는지 확인 (패턴은 MySQL과 거의 동일, . 이스케이프 불필요 가능성)
  WHERE REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@(example\.com|another\.com)$');

• MySQL (8.0.4+)

  -- Orders 테이블에 product 컬럼 값이 'Apple (Red)', 'Banana (Yellow)' 등이 있다고 가정
  SELECT
      product, -- 원본 상품명
      -- 정규표현식을 사용하여 괄호 안의 내용을 추출
      -- \\(: 여는 괄호 이스케이프, ([^)]+): 닫는 괄호가 아닌 문자 1개 이상 반복 (이것이 그룹 1)
      -- \\): 닫는 괄호 이스케이프
      -- REGEXP_SUBSTR(문자열, 패턴, 시작위치, 발생횟수, 매치옵션, 그룹인덱스)
      REGEXP_SUBSTR(product, '\\(([^)]+)\\)', 1, 1, NULL, 1) AS detail
  FROM Orders
  WHERE product REGEXP '\\(.+\\)'; -- 괄호를 포함하는 상품만 대상

• Oracle

  -- Orders 테이블에 product 컬럼 값이 'Apple (Red)', 'Banana (Yellow)' 등이 있다고 가정
  SELECT
      product, -- 원본 상품명
      -- 정규표현식을 사용하여 괄호 안의 내용을 추출
      -- \(: 여는 괄호, ([^)]+): 닫는 괄호가 아닌 문자 1개 이상 반복 (그룹 1)
      -- \): 닫는 괄호
      -- REGEXP_SUBSTR(문자열, 패턴, 시작위치, 발생횟수, 매치파라미터, 그룹인덱스)
      REGEXP_SUBSTR(product, '\(([^)]+)\)', 1, 1, NULL, 1) AS detail
  FROM Orders
  WHERE REGEXP_LIKE(product, '\(.*\)') ; -- 괄호를 포함하는 상품만 대상

• MySQL
  NTILE(n) OVER (PARTITION BY ... ORDER BY ...) (8.0 이상)
• Oracle
  NTILE(n) OVER (PARTITION BY ... ORDER BY ...)

-- UserTotalAmounts CTE: 사용자별 총 주문 금액 계산
WITH UserTotalAmounts AS (
    SELECT
        user_id,
        SUM(amount) AS total_amount
    FROM Orders
    WHERE user_id IS NOT NULL
    GROUP BY user_id
),
-- UserQuintiles CTE: 각 사용자를 주문 총액 기준 5분위로 나눔
UserQuintiles AS (
    SELECT
        U.name, -- 사용자 이름
        UTA.total_amount, -- 사용자의 총 주문액
        -- 총 주문액을 내림차순으로 정렬하여 5개의 그룹으로 나누고 각 사용자가 속한 그룹 번호 반환
        NTILE(5) OVER (ORDER BY UTA.total_amount DESC) AS amount_quintile
    FROM Users U
    JOIN UserTotalAmounts UTA ON U.id = UTA.user_id -- Users와 UserTotalAmounts 조인
)
-- 메인 쿼리: 각 분위수별 사용자 수 계산
SELECT
    amount_quintile, -- 분위수
    COUNT(*) AS num_users, -- 해당 분위수에 속한 사용자 수
    GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS user_list -- (MySQL 전용, Oracle은 LISTAGG) 해당 분위 사용자 목록
FROM UserQuintiles
GROUP BY amount_quintile
ORDER BY amount_quintile;

(데이터가 적어 각 분위에 1명씩 배정된 예시)

• MySQL
  ROWS BETWEEN ..., RANGE BETWEEN ... 지원 (8.0 이상).
• Oracle
  ROWS BETWEEN ..., RANGE BETWEEN ... 지원.
  • RANGE는 ORDER BY 컬럼 값이 같은 경우 동일한 값으로 취급하여 프레임을 설정합니다. 이 때문에 ORDER BY 컬럼은 유니크하지 않을 경우 주의해야 하며, 주로 날짜나 숫자형 컬럼에 사용됩니다.
  • ROWS는 현재 행을 기준으로 물리적인 행의 위치로 프레임을 설정합니다.

SELECT
    O.id AS order_id, -- 주문 ID
    U.name AS user_name, -- 사용자 이름
    O.order_date, -- 주문 날짜
    O.amount, -- 현재 주문 금액
    -- 사용자별(PARTITION BY U.id)로 주문 날짜 오름차순(ORDER BY O.order_date) 정렬 후
    -- 현재 행을 포함하여 이전 2개 행 (총 3개 행)의 주문 금액 평균을 계산
    -- 만약 이전 행이 2개 미만이면 있는 만큼만으로 평균 계산
    AVG(O.amount) OVER (PARTITION BY U.id ORDER BY O.order_date
                          ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3_orders
FROM Orders O
JOIN Users U ON O.user_id = U.id -- Users와 Orders 조인
ORDER BY U.name, O.order_date; -- 결과 정렬

(moving_avg_3_orders는 (2000+3000+8000)/3)

• MySQL
  CASE WHEN ... THEN ... ELSE ... END와 집계 함수를 조합하여 수동으로 구현합니다. 동적 피벗(열이 가변적일 때)은 프로시저나 프리페어드 스테이트먼트를 사용해야 합니다.
• Oracle
  CASE문 조합 또는 11g부터 지원하는 PIVOT 연산자를 사용합니다. PIVOT XML을 사용하면 열 목록을 동적으로 생성할 수도 있습니다.

• MySQL (CASE문 사용)

  SELECT
      U.name, -- 사용자 이름
      -- 'Apple' 상품 주문액 합계, 없으면 0
      SUM(CASE WHEN O.product = 'Apple' THEN O.amount ELSE 0 END) AS "Apple_Total",
      -- 'Banana' 상품 주문액 합계, 없으면 0
      SUM(CASE WHEN O.product = 'Banana' THEN O.amount ELSE 0 END) AS "Banana_Total",
      -- 'Orange' 상품 주문액 합계, 없으면 0
      SUM(CASE WHEN O.product = 'Orange' THEN O.amount ELSE 0 END) AS "Orange_Total"
  FROM Users U
  LEFT JOIN Orders O ON U.id = O.user_id -- 모든 사용자를 포함하기 위해 LEFT JOIN
  GROUP BY U.name -- 사용자 이름으로 그룹화
  ORDER BY U.name; -- 결과 정렬

• Oracle (PIVOT 연산자 사용)

  SELECT * -- 피벗 결과의 모든 컬럼 선택 (name, "Apple_Total", "Banana_Total", "Orange_Total")
  FROM (
      -- 피벗 대상이 될 원본 데이터 선택 (사용자 이름, 상품명, 주문액)
      SELECT U.name, O.product, O.amount
      FROM Users U
      LEFT JOIN Orders O ON U.id = O.user_id
  )
  PIVOT (
      SUM(amount) -- 집계할 값 (주문액 합계)
      FOR product IN ( -- 열로 변환할 컬럼 (상품명)과 해당 값들, 그리고 새 컬럼명 지정
          'Apple' AS "Apple_Total",
          'Banana' AS "Banana_Total",
          'Orange' AS "Orange_Total"
      )
  )
  ORDER BY name; -- 결과 정렬

• MySQL
  UNION ALL을 사용하여 각 열을 행으로 수동 변환합니다.
• Oracle
  UNPIVOT 연산자를 지원합니다 (11g부터).

• PivotedSales 테이블 가정

  name	Apple_Total	Banana_Total	Orange_Total
  이동훈	0	1500	0
  홍길동	6000	0	2000

• MySQL (UNION ALL 사용)

  -- PivotedSales 테이블이 있다고 가정하고 작성
  SELECT name, 'Apple' AS product, Apple_Total AS amount FROM PivotedSales WHERE Apple_Total > 0
  UNION ALL
  SELECT name, 'Banana' AS product, Banana_Total AS amount FROM PivotedSales WHERE Banana_Total > 0
  UNION ALL
  SELECT name, 'Orange' AS product, Orange_Total AS amount FROM PivotedSales WHERE Orange_Total > 0
  ORDER BY name, product;

• Oracle (UNPIVOT 연산자 사용)

  -- PivotedSales 테이블이 있다고 가정하고 작성
  SELECT name, product, amount
  FROM PivotedSales
  UNPIVOT (
      amount -- 행으로 변환될 값을 담을 새 컬럼명
      FOR product IN ( -- 열에서 행으로 변환될 때, 원래 열 이름을 담을 새 컬럼명과 대상 열들
          Apple_Total AS 'Apple',
          Banana_Total AS 'Banana',
          Orange_Total AS 'Orange'
      )
  )
  WHERE amount > 0 -- 매출이 0인 경우는 제외 (선택 사항)
  ORDER BY name, product;

• SQL Tutorial↗
• Mode Analytics SQL Tutorial↗
• SQLZoo↗