[Postgresql] WITH statement

SQL을 작성하다보면 계속해서 반복되는 쿼리가 발생한다.

sub query에서 outer query에 접근하지 못하거나, 그 외에도 많은 경우에 동일한 쿼리를 짜야할 때가 있다.

이런 동일 쿼리가 발생할 경우, 작성도 어렵거니와 쿼리를 수정할 때 반복된 모든 구간에서의 수정이 필요하다.

따라서 이런 쿼리를 더 쉽고 간편하게 작성하기 위해서, 변수와 같이 쿼리를 저장해둘 필요가 있다.

그럴 때 사용하는 것이 바로 WITH 문이다.

WITH의 사용 예시

WITH cte AS (
  SELECT column1, column2, column3
  FROM table1
  WHERE column1 = 'some value'
)
SELECT *
FROM cte
WHERE column2 = 'another value';

이런 식으로 WITH 다음에 alias를 작성하고, 그 후에 AS 문으로 어떤 쿼리인지를 보여주면 된다.

이후 미리 잡은 alias로 해당 쿼리를 간편하게 호출할 수 있다.

일반적인 AS 문이, 작성된 쿼리를 호출하기 위해서 명명하는 거였기에, 이 구문이 특이하게 보인다.

이제 이를 활용한 복잡한 예시를 한 번 봐보자.

WITH cte AS (
    SELECT
        sub. "week",
        COUNT(*)
    FROM (
        SELECT
            DATE_TRUNC('week', oi. "createdAt") AS "week", oi. "companyId"
        FROM
            order_info AS oi
        GROUP BY
            DATE_TRUNC('week', oi. "createdAt"), oi. "companyId"
        ORDER BY
            DATE_TRUNC('week', oi. "createdAt") DESC) AS sub
    GROUP BY
        sub. "week"
    ORDER BY
        sub. "week" ASC
)

SELECT MIN(cte."week") AS "first week to archieve", 10 AS "count" FROM cte WHERE cte."count" >= 10

여기서 의도하고 있는 것은 주문서 ( = order_info ) 가 생성된 주간이 같은 날짜들로 그룹화한 다음,

주문이 들어온 날짜 ( = createdAt ) 가 동일한 주문서의 회사 아이디 ( = companyId ) 를 뽑고,

다시 그 주간으로 그룹화하여 회사의 수를 구한 cte ( = common table expression의 약자 ) 를 구한 것이다.

이렇게 만들어진 cte 를 이제, 원할 때 호출해서 더 편리한 쿼리를 작성할 수 있게 되었다.

아래의 SELECT 문은 주문서의 회사 수가 10개가 된 첫 번째 주간을 구하는 쿼리다.

만약 20번째 회사가 우리 서비스를 결제한 시점, 30번째 회사가 우리 서비스를 결제한 시점을 알고 싶다면?

SELECT MIN(cte."week") AS "first week to archieve", 10 AS "count" FROM cte WHERE cte."count" >= 10;
SELECT MIN(cte."week") AS "first week to archieve", 20 AS "count" FROM cte WHERE cte."count" >= 20;
SELECT MIN(cte."week") AS "first week to archieve", 30 AS "count" FROM cte WHERE cte."count" >= 30;

이렇게 호출하는 것만으로도 간단하게 된다.

앞서 배운 것과 같이, UNION ALL문을 WITH문과 함께 사용하면 더 깔끔한 출력 결과를 볼 수 있다.

SELECT MIN(cte."week") AS "first week to archieve", 10 AS "count" FROM cte WHERE cte."count" >= 10;
UNION ALL SELECT MIN(cte."week") AS "first week to archieve", 20 AS "count" FROM cte WHERE cte."count" >= 20;
UNION ALL SELECT MIN(cte."week") AS "first week to archieve", 30 AS "count" FROM cte WHERE cte."count" >= 30;