분석가의 데이터 이야기/SQL과 DB

[SQL] MySQL을 쓰다 PostgreSQL을 쓰면서 느낀 차이 (희망편 ②)

니나노래방 2024. 2. 7. 10:35

이 글의 개요

  • 소개 : 지난 편에 이어 PostgreSQL을 겪어보면서 느낀 PostgreSQL의 소소한 장점을 꿀 기능으로 정리했습니다. 
  • 예상 독자 : MySQL(혹은 표준 SQL)에 익숙해졌다가 PostgreSQL을 쓰게 된 상황을 겪으신 분들이 공감하실 수 있습니다.
    DB를 너무 잘 아시는 분들에게는 다소 심심할 수 있습니다 :)
  • 글의 목표 : PostgreSQL도 좋은 점이 많다는 것을 널리 전파하기
더보기

목차
--- 포스팅 ① 지난 글

0. 들어가며 : 처음 PostgreSQL을 만났을 때의 떨림 

1. PostgreSQL이 직관적으로 느껴졌던 순간들 

 

-- 포스팅 ② 이번 글

2. 결과를 쉽게 만드는 PostgreSQL의 꿀 기능들

(1) with cte (+values) : CTE를 INSERT TABLE처럼

(2) generate_series : 쉽게 시리즈 만들기

(3) regexp_split_to_tables : 여러 문자열을 한번에 '행'으로 분리해버리다 (UNNEST) 

(4) cube : 조합을 쉽게 만들어주는 GROUP BY 구(subclause)

 

3. 나가며 : 가까이 보면 사랑스럽다. MySQL도 그럴 것이다

 

2. 결과를 쉽게 만드는 PostgreSQL의 꿀 기능들

같은 결과물을 내더라도 빠른 속도로 처리하면 훨씬 효율적인데요. 

PostgreSQL을 쓸 때 도움이 되었던 꿀 기능을 소개합니다 🍯🍯

 

(1) WITH, VALUES : CTE를 INSERT TABLE처럼

분석가에게는 데이터베이스의 추가, 수정, 삭제 권한이 없을 때가 종종 있습니다. 이때 WITH문을 이용한 CTE를 구성해놓고 분석할 때 활용할 수 있죠. 하지만 테이블과 관련 없이 간단한 목록을 만들 때는 SELECT ~ AS ~ UNION ALL을 열심히 반복하며 허탈함을 느낄 때가 있는데요(이걸 왜 하고 있는가..?).  PostgreSQL에서는 WITH문 CTE를 좀더 간편하게(?) 만들 수 있습니다.  

 

예를 들어 7, 14, 21, 28이 포함된 임시 테이블을 만들어 보겠습니다. 

원래의 작업이라면 내부에 SELECT에 필요한 항목과 컬럼 명칭을 넣고, UNION ALL로 결합해 줍니다. 

WITH retention_periods as (
        SELECT '7day_retention' AS title,  7 AS diffs
        UNION ALL
        SELECT '14day_retention' AS title,  14 AS diffs
        UNION ALL
        SELECT '21day_retention' AS title,  21 AS diffs
        UNION ALL
        SELECT '28day_retention' AS title,  28 AS diffs
)

SELECT *
FROM retention_periods

 

 

PostgreSQL에서는 WITH문에 데이터를 넣을때 INSERT를 하는 것과 비슷한 방법을 사용합니다. WITH문 옆에 괄호로 컬럼 명을 미리 써두는데요. 이렇게 하면 컬럼 명을 일일이 쓰지 않아도 되고, SELECT나 UNION ALL도 반복하지 않습니다. 

WITH retention_periods(title, diffs) as (
    values
        ('7day_retention', 7),
        ('14day_retention', 14),
        ('21day_retention', 21),
        ('28day_retention', 28)
)

SELECT *
FROM retention_periods;

 

CTE가 훨씬 깔끔해졌고 과정도 단순해졌습니다. 별도의 테이블 생성 없이 하지 않고 CTE를 활용할 수 있어 부담도 적었습니다. 



 

(2) generate_series : 쉽게 시리즈 만들기

두 번째도 CTE를 구성할 때 상당히 편했던 기능입니다. 연속된 숫자, 날짜를 만들 때 유용한 함수인데요. 바로 generate_series 입니다. 

파이썬(Python)으로 보면 "range()" 같은 역할을 하는 녀석입니다.

 

이렇게 연속된 숫자, 날짜를 만드는 이유는 이 목록을 인덱스처럼 활용하기 위해서입니다. 특히 날짜 별 지표 계산을 할 때 필요한데요. 집계가 0이더라도  날짜를 보이게 만드는 겁니다. 특정 날짜 행이 없을 때 단순히 GROUP BY 집계를 하면 날짜 혹은 시간이 안 보이는 문제가 생기거든요. 

 

이전에는 시리즈 데이터를 만들기 위해 UNION ALL을 열심히 만들거나 (💪)  혹은 RECURSIVE CTE를 사용하는 방법을 택했습니다. 

-- MySQL : UNION ALL
SELECT 1 AS num
UNION ALL
SELECT 2 AS num
UNION ALL
SELECT 3 AS num
UNION ALL
SELECT 4 AS num
UNION ALL
SELECT 5 AS num;

-- MySQL : RECURSIVE_CTE
WITH RECURSIVE
consecutive_nums (num) as (
    SELECT 1 AS num
    UNION ALL
    SELECT num + 1
    FROM consecutive_nums
    WHERE num < 5
    )
SELECT *
FROM consecutive_nums;

 

반면 PostgreSQL에서는 generate_series라는 함수를 이용해서 마법같이 시리즈를 만들어 줍니다 💞

행이 많을 수록 generate_series의 장점이 더욱 부각됩니다. 

/*PostgreSQL에서 generate_series로 규칙 있는 결과 만들기 */
-- 1씩 커지는 숫자
SELECT generate_series(1, 5);

-- 3씩 커지는 숫자
SELECT generate_series(10, 100, 3);

-- 1씩 줄어들게 하기
SELECT generate_series(10, 1, -1);

-- 날짜 조정
SELECT generate_series('2024-02-01'::date, '2024-02-28'::date, interval '1 day')::date;

 

PostgreSQL에서 연속된 결과가 필요할 땐 꼭 'generate_series'를 써먹읍시다! 

 

 

(3) regexp_split_to_tables : 여러 문자열을 한번에 '행'으로 분리해주다 (UNNEST) 

여러 문자열이 한 컬럼에 있는 경우가 있습니다. 저는 '상품 태그' 데이터에서 이런 형태의 데이터를 본 적이 있어요. 예를 들어 티스토리에서 어떤 태그를 많이 달았는지 세어보고 싶은 상황입니다. 

 

이럴 때 '파이썬 라이브러리의 판다스에서의 melt 같은 기능이 있지 않을까?' 했는데, MySQL은 없더라고요. 

반면 PostgreSQL에도 UNNEST 기능을 제공하는 유사한 함수가 있었어요. 바로 regexp_split_to_tables()입니다. 

 

MySQL은 UNNEST 기능이 없으므로, 별도의 테이블을 만들어 주고 조인해서 해결합니다.

*MySQL의 자세한 해결 방안은 링크를 참고해 주세요.

WITH RECURSIVE
    -- recursive cte로 행을 필요한 만큼 만들어줌
num AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n+1
    FROM num
    WHERE n < 5
)

-- 행을 나눈 후 필요한 단어만 출력
SELECT post_id
     , n AS row_num -- n값(행 번호) 
     , tags AS origin_columns -- 원래 컬럼
     , SUBSTRING_INDEX(REGEXP_REPLACE(tags, ' ', ''), ',', n) AS to_n_th_words -- n번째에 있는 ',' 앞에 있는 단어까지 잘라오기 
     , SUBSTRING_INDEX(SUBSTRING_INDEX(REGEXP_REPLACE(tags, ' ', ''), ',', n), ',', -1) AS final_n_th_words -- 그 중 마지막 단어 가져오기
FROM num
     INNER JOIN post_tags t ON n <= CHAR_LENGTH(REGEXP_REPLACE(tags, ' ', '')) - CHAR_LENGTH(REGEXP_REPLACE(tags, '[, ]', '')) + 1 -- 앞의 recursive cte에서 구한 행 중 컬럼의 단어 수 만큼 행을 가져옴
ORDER BY post_id, tags, n

 

 

반면 PostgreSQL은 regexp_split_to_tables로 한번에 문자열을 행으로 만들어줬습니다. 

/*PostgreSQL에서 unnest가 되는 과정
  -- replace로 빈칸을 모두 없애준다.
  -- array를 만든다. 
  -- unnest를 해서 array를 행으로 풀어준다.
  -- 이걸 한꺼번에 해주는 게 regexp_split_to_table! 
*/

select post_id
     , regexp_split_to_array(regexp_replace(tags, ' ', '', 'g'), ',') as array_only
     , unnest(regexp_split_to_array(regexp_replace(tags, ' ', '', 'g'), ',')) as array_to_unnest
     , regexp_split_to_table(regexp_replace(tags, ' ', '', 'g'), ',') as to_table
from post_tags;

 

 

(4) CUBE : 조합을 쉽게 만들어주는 GROUP BY 구(subclause)

다음으로는 GROUP BY 패턴 중 하나를 보여드리려고 합니다. 집계를 할 때 여러 그룹 간 조합을 가지고 결과를 보고 싶을 때가 있습니다. 예를 들어 3개의 상품 카테고리와 2개의 배송 타입 집계가 있는데요. 

 

CUBE를 사용하면 ① 카테고리, 배송 타입 별  ② 카테고리 별  ③ 배송 타입 별 ④ 전체 집계를 한 쿼리로 확인할 수 있습니다.

 

MySQL에서는 ROLLUP은 제공하지만, CUBE와 같은 조합을 다 쓸 수는 없습니다. 이때 MySQL에서 CUBE를 쓰려면 다소 복잡한 과정을 거쳐야 합니다. 

-- PostgreSQL GROUP BY구 CUBE 서브클로즈
SELECT category
     , delivery_type
     , sum(price) AS total_sales
FROM products
GROUP BY CUBE(category, delivery_type)
ORDER BY category;

-- MySQL은 각각의 합계를 구해서 UNION ALL 해줘야 합니다. 
SELECT category
     , delivery_type
     , SUM(price) As total_sales
FROM products
GROUP BY category, delivery_type

UNION ALL

SELECT category
     , NULL AS delivery_type
     , SUM(price) AS total_sales
FROM products
GROUP BY category

UNION ALL

SELECT NULL
     , delivery_type
     , SUM(price) AS total_sales
FROM products
GROUP BY delivery_type

UNION ALL

SELECT NULL
     , NULL
     , SUM(price) AS total_sales
FROM products

 

3. 나가며 : 가까이 보면 사랑스럽다. MySQL도 그럴 것이다

처음엔 낯설기만 했던 PostgreSQL도 쓰다보니 정이 들고, 좋은 점이 자꾸 보이더라구요. 어느 순간부터 "PostgreSQL은 되는데 MySQL 넌 왜 안 돼?!" 하는 순간이 있었지만, 반대로 생각해보면 PostgreSQL의 멋진 매력이 아니었나 싶습니다.

이렇게 생각해보면 MySQL도 나름의 장점이 있고, 쓰면서 이 녀석만의 매력을 발견할 수 있을 거라 믿으며 글을 마치겠습니다. 

 

 

감사합니다. 

 

 

 

글또를 하면서 글을 한번씩 가다듬어보고 있어요. 

피드백은 언제든 환영입니다! 틀린 부분이 있다면 꼭 말씀해 주세요!!

저작자표시 비영리 변경금지 (새창열림)