[컴] PostgreSQL 에서 with recursive

WITH RECURSIVE

현재 내용은 ref. 2 에서 설명을 잘해 주고 있다. ref. 2 의 설명을 바탕으로 재구성했다.

WITH RECURSIVE t(n) AS (
    -- initial query
    SELECT 1
  UNION ALL
    -- recursive query
    SELECT n+1 FROM t
)
-- parent query(or outer query)
SELECT n FROM t LIMIT 100;

WITH RECURSIVE query 는 2부분으로 되어 있다. 위의 예제를 보면 알겠지만, 처음에 한번 실행하는 initial query 와 그 후에 계속 반복적으로 도는 recursive query 가 있다. 그래서 이렇게 나온 결과를 합쳐서 그 다음에 오는 parent query 에 넘겨주게 된다.

• WITH RECURSIVE 결과 = initial query 결과 UNION ALL recursive query 1 UNION ALL recursive query 2 UNION ALL recursive query 3 ...

Exit 조건

recursive 에는 언제나 빠져나오는 조건이 필요하다. 이 조건은 다음과 같다.

• recursive query 의 결과 set 이 empty

"recursive query 의 결과 set 이 empty" 이면 WITH RECURSIVE 는 끝이 난다.

예제

WITH RECURSIVE t(n) AS (
    -- initial query
    SELECT 1
  UNION ALL
    -- recursive query
    SELECT n+1 FROM t
)
-- parent query(or outer query)
SELECT n FROM t LIMIT 100;

위의 query 로 한번 보여주면,
처음 result 는

1  // SELECT 1

2번째 result 는

t = 
|n |
+--+
|1 |
+--+

1+1  // SELECT n+1 FROM t

3번째 result 는

t = 
|n |
+--+
|2 |
+--+

2+1  // SELECT n+1 FROM t

Test tip

ref.1 에 있는 이야기인데, Test 할 때는 LIMIT 을 사용하면 LOOP 이 도는 query 여도 결과를 확인할 수 있다. 이것은 PostgreSQL 의 구현은 실제 parent query 에 의해 쓰여지는 row 수만큼 WITH query 의 row 들을 산정 하기 때문에 LIMIT 을 걸면 loop 을 돌지않게 된다.
그런데 이것은 production(실사용) 에서는 사용하지 말라고 한다. 다른 시스템들은 다르게 돌 수 있고 parent query 에서 sort 나 join 등을 하게 되면 LIMIT 이 의미가 없어진다.

개인적인 TIP

WITH RECURSIVE func_name(param,...) AS ( ... )
위같은 WITH RECURSIVE 에서 func_name 은 f_n-1 이라고 생각하면 이해하기 쉬워진다.

아래같은 식은 아래의 식을 WITH RECURSIVE 로 나타냈다고 보면 된다.

• f_n = f_n-1 + 1

WITH RECURSIVE t(n) AS (
    -- initial query
    SELECT 1
  UNION ALL
    -- recursive query
    SELECT n+1 FROM t
)
-- parent query(or outer query)
SELECT n FROM t LIMIT 100;

EXAMPLES

WITH RECURSIVE r(no, date, code, count, cash, ratio) AS (
    SELECT 0::bigint, '2015-10-01'::date, 'A005380', 66::float, 9800000, 0.1
  UNION ALL
    SELECT t.no AS no, t.date, r.code,
    ( ((r.count*t.close_price + r.cash)*r.ratio)/t.close_price )::float as count,
    r.cash, r.ratio
     from (
 SELECT row_number() OVER (PARTITION BY code ORDER BY date) AS no, * 
 FROM mfactors_stockprice 
 WHERE date >= '2015-10-01' AND date <= '2015-11-01'
 AND code='A005380'
 
     ) AS t, r
     WHERE t.no = r.no + 1
)
SELECT * FROM r ;

Reference

1. PostgreSQL: Documentation: 9.4: WITH Queries (Common Table Expressions)
2. How to select using WITH RECURSIVE clause, Stack Overflow