1.왜 이게 필요했을까?

• 여러 언어를 오고가면서 분석작업을 진행할 때가 많은데,
• 가끔은 SQL로만 하고 싶을 때가 있고, 그 때마다 문제가 되었던 부분 중 하나가 For Loop였다. 그래서 찾아보다가 알게 되었다.

2.구현하기

• presto(0.241)에서 진행하였다.

A.기본 Sequence Table 생성

--Sequence Table 생성
CREATE TABLE temp.sequence AS (
    SELECT 0 AS num
    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
    UNION ALL SELECT 6  AS num
    UNION ALL SELECT 7  AS num
    UNION ALL SELECT 8  AS num
    UNION ALL SELECT 9  AS num
)

Copy

B.필요한 반복수만큼의 Sequence 테이블 생성

-- 시퀀스 테이블의 조합을 통해서 For Loop을 하기 위한 Sequence 생성합니다.
--100번 돌리기 위한 방법

-- CREATE TABLE temp.for_loop AS (
--     SELECT 1 + s1.num + s2.num * 10 AS i
--     FROM temp.sequence s1
--     CROSS JOIN temp.sequence s2
--     ORDER BY i
-- )

-- 1000 번 돌리기 위한 방법
CREATE TABLE temp.for_loop AS (
    SELECT 1 + s1.num + s2.num * 10 + s3.num * 100 AS i
    FROM temp.sequence s1
    CROSS JOIN temp.sequence s2
    CROSS JOIN temp.sequence s3    
    ORDER BY i
)

Copy

C.사용하기

• 이제 위 for_loop와 반복실행하려는 작업을 cross join으로 묶어 진행하면 된다.

3.예시( 동전 던지기 시뮬레이션)

A.확률 테이블 생성

CREATE TABLE temp.coin
(
      coin_name      varchar(10),
      probability    double
)

Copy

INSERT INTO temp.coin
VALUES ('FairCoin', 0.5),
       ('BiasCoin', 0.9)

Copy

B.For Loop 돌리기

CREATE TABLE temp.experiments AS (       
    SELECT c.coin_name, 
           CASE WHEN random() > c.probability THEN 'head' ELSE 'tail' END AS experiment
FROM temp.coin AS c
CROSS JOIN temp.for_loop AS f
)

Copy

C.결과 요약

SELECT coin_name, 
       COUNT(CASE WHEN experiment = 'head' then 1 end) as n_heads, 
       COUNT(*) as n_flips,  
       cast(COUNT(CASE WHEN experiment = 'head' then 1 end) as double) / COUNT(*) as head_prob
FROM temp.experiments
GROUP BY coin_name

Copy

4.결론

• Cross Join을 사용하기 때문에 리소스를 엄청 먹기 때문에 주의를 요하지만, 가끔 쓰기에는 아주 좋은 방법이다. ?

5.Footnotes

• How to implement a For Loop in SQL