카테고리:: 쿼리 구문

WITH¶

WITH 절은 SELECT 문의 본문 앞에 오는 선택적 절이며, 문에서 나중에 사용할 수 있는 하나 이상의 CTE(공통 테이블 식) 를 정의합니다. 예를 들어, CTE는 FROM 절에서 참조될 수 있습니다.

참고

저장 프로시저와 유사한 익명 프로시저를 만들고 호출할 때 WITH 절을 사용할 수 있습니다. 그 절은 SELECT 명령이 아닌 CALL 명령을 수정합니다. 자세한 내용은 CALL(익명 프로시저 사용) 섹션을 참조하십시오.

WITH 절은 머신 러닝 모델 오브젝트와 함께 사용되어 모델의 특정 버전에 대한 별칭을 만든 다음 해당 버전의 메서드를 호출하는 데 사용할 수 있습니다. SQL에서 모델 메서드 호출하기 섹션을 참조하십시오.

참고 항목:: CONNECT BY, 모델 명령

구문¶

하위 쿼리:

[ WITH
       <cte_name1> [ ( <cte_column_list> ) ] AS ( SELECT ...  )
   [ , <cte_name2> [ ( <cte_column_list> ) ] AS ( SELECT ...  ) ]
   [ , <cte_nameN> [ ( <cte_column_list> ) ] AS ( SELECT ...  ) ]
]
SELECT ...

Copy

재귀 CTE:

[ WITH [ RECURSIVE ]
       <cte_name1> ( <cte_column_list> ) AS ( anchorClause UNION ALL recursiveClause )
   [ , <cte_name2> ( <cte_column_list> ) AS ( anchorClause UNION ALL recursiveClause ) ]
   [ , <cte_nameN> ( <cte_column_list> ) AS ( anchorClause UNION ALL recursiveClause ) ]
]
SELECT ...

Copy

여기서:

anchorClause ::=
    SELECT <anchor_column_list> FROM ...

recursiveClause ::=
    SELECT <recursive_column_list> FROM ... [ JOIN ... ]

Copy

cte_name1 , cte_nameN: CTE 이름은 뷰 및 유사한 오브젝트 식별자 에 대한 규칙을 따라야 합니다.
cte_column_list: CTE(공통 테이블 식)의 열 이름입니다.
anchor_column_list: 재귀 CTE의 앵커 절에 사용되는 열입니다. 이 목록의 열은 cte_column_list 에 정의된 열과 일치해야 합니다.
recursive_column_list: 재귀 CTE의 재귀 절에 사용된 열입니다. 이 목록의 열은 cte_column_list 에 정의된 열과 일치해야 합니다.

자세한 내용은 이 항목에 있는 앵커 절 및 재귀 절 을 참조하십시오. 앵커 절과 재귀 절이 함께 작동하는 방식에 대한 자세한 설명은 CTEs(일반 테이블 식) 관련 작업하기 을 참조하십시오.

사용법 노트¶

일반 사용¶

WITH 절은 자신을 재귀적으로 참조할 수 있으며, 동일한 절의 앞부분에 나타나는 다른 CTE를 참조할 수 있습니다. 예를 들어, cte_name2 는 cte_name1 과 자신을 참조할 수 있는 반면, cte_name1 은 자신을 참조할 수 있지만, cte_name2 는 참조할 수 없습니다.
WITH 절에서 재귀 및 비재귀(반복 및 비반복) CTE 절을 혼합할 수 있습니다. CTE 절은 CTE가 다른 CTE를 참조해야 하는 경우, 참조할 CTE가 문 앞부분에 정의되도록 순서를 지정해야 합니다(예: 두 번째 CTE는 첫 번째 CTE를 참조할 수 있지만, 그 반대는 불가능).

CTE는 재귀 여부에 따라 순서대로 나열할 필요가 없습니다. 예를 들어, 비재귀 CTE는 RECURSIVE 키워드 바로 뒤에 나열될 수 있으며 재귀 CTE는 해당 비재귀 CTE 뒤에 올 수 있습니다.

재귀 CTE 내에서 앵커 절 또는 재귀 절(또는 둘 다)은 다른 CTE를 참조할 수 있습니다.
재귀 CTE의 경우, cte_column_list 가 필요합니다.
비재귀 CTE의 경우, cte_column_list 는 선택 사항입니다.
재귀 CTE에서는 UNION 이 아닌 UNION ALL 을 사용해야 합니다.
RECURSIVE 키워드는 선택 사항입니다.
- CTE는 RECURSIVE 가 지정되었는지 여부와 관계없이 재귀적일 수 있습니다.
- 재귀 CTE가 없더라도 RECURSIVE 키워드를 사용할 수 있습니다.
- RECURSIVE 가 사용되는 경우, 둘 이상의 CTE가 재귀적인 경우라고 할지라도 한 번만 사용해야 합니다.
SQL 문이 RECURSIVE 키워드를 사용하거나 사용하지 않으면서 제대로 작동하더라도, 키워드를 적절하게 사용하면 코드를 더 쉽게 이해하고 유지 관리할 수 있습니다. Snowflake는 하나 이상의 CTE가 재귀적이라면 RECURSIVE 키워드를 사용할 것을 권장하고, 재귀적인 CTE가 없다면 이 키워드를 생략할 것을 강력히 권장합니다.

주의

재귀적 CTE를 사용하면 무한 루프로 들어가 쿼리가 성공하거나, 쿼리 시간이 초과되거나(예: STATEMENT_TIMEOUT_IN_SECONDS 매개 변수로 지정된 사간(초) 초과), 쿼리를 취소 할 때까지 크레딧을 소비하는 쿼리를 만들 수 있습니다.

무한 루프가 발생할 수 있는 방법에 대한 정보와 이 문제를 방지하는 방법에 대한 지침은 재귀 CTE 문제 해결 섹션을 참조하십시오.

예를 들어 반복 횟수를 10회 미만으로 제한하는 방법은 다음과 같습니다.

WITH cte AS (
  SELECT ..., 1 as level ...

  UNION ALL

  SELECT ..., cte.level + 1 as level
   FROM cte ...
   WHERE ... level < 10
) ...

Copy

제한 사항¶

Snowflake의 재귀 CTE 구현은 일부 다른 시스템에서 지원하는 다음 키워드를 지원하지 않습니다.
- SEARCH DEPTH FIRST BY ...
- CYCLE ... SET ...

앵커 절¶

재귀 CTE의 앵커 절은 SELECT 문입니다.

앵커 절은 포함된 문을 실행하는 동안 한 번 실행됩니다. 이는 재귀 절 전에 실행되며, 재귀 CTE에서 첫 번째 행 세트를 생성합니다. 이러한 행은 쿼리 출력에 포함될 뿐만 아니라 재귀 절에서도 참조됩니다.

앵커 절은 SELECT 절에서 허용되는 모든 SQL 구문을 포함할 수 있습니다. 그러나 앵커 절은 cte_name1 을 참조할 수 없습니다. 재귀 절만 cte_name1 을 참조할 수 있습니다.

앵커 절은 일반적으로 재귀 절과 동일한 테이블에서 선택하지만, 필수는 아닙니다. 앵커 절은 다른 테이블, 뷰, UDTF 또는 상수 값을 비롯하여 테이블과 유사한 모든 데이터 소스에서 선택할 수 있습니다.

앵커 절은 계층 구조의 단일 “수준”(일반적으로 최상위 수준) 또는 가장 높은 관심 수준을 선택합니다. 예를 들어, 쿼리가 자동차의 “부품 전개”를 표시하려는 경우, 앵커 절은 자동차 자체인 최상위 구성 요소를 반환합니다.

앵커 절의 출력은 계층 구조의 한 계층을 나타내며 이 계층은 재귀 절의 첫 번째 반복에서 액세스되는 “뷰”의 내용으로 저장됩니다.

재귀 절¶

재귀 절은 SELECT 문입니다. 이 SELECT는 프로젝션, 필터, 조인(재귀 참조가 외부 조인의 보존된 쪽에 있는 내부 조인 및 외부 조인)으로 제한됩니다. 재귀 절에는 다음이 포함될 수 없습니다.

집계 또는 윈도우 함수,
GROUP BY, ORDER BY, LIMIT 또는 DISTINCT.

재귀 절은 CTE가 테이블이나 뷰인 것처럼 cte_name1 을 참조할 수 있고 일반적으로 참조합니다.

재귀 절에는 일반적으로 앵커 절에 사용된 테이블을 CTE에 조인하는 JOIN이 포함됩니다. 그러나 JOIN은 둘 이상의 테이블이나 테이블과 유사한 데이터 소스(뷰 등)를 조인할 수 있습니다.

재귀 절의 첫 번째 반복은 앵커 절의 데이터로 시작합니다. 그런 다음 해당 데이터는 재귀 절의 FROM 절에 있는 다른 테이블에 조인됩니다.

각 후속 반복은 이전 반복의 데이터로 시작됩니다.

CTE 절 또는 “뷰”는 이전 반복 내용이 조인 가능하도록 이러한 이전 반복 내용을 보유하는 것으로 볼 수 있습니다. 한 번의 반복 동안 CTE에는 모든 이전 반복에서 누적된 결과가 아니라, 이전 반복의 내용만 포함됩니다. 누적된 결과(앵커 절의 결과 포함)는 별도의 위치에 저장됩니다.

재귀 CTE의 열 목록¶

재귀 CTE에는 다음과 같은 세 개의 열 목록이 있습니다.

cte_column_list
anchor_column_list (앵커 절에서)
recursive_column_list (재귀 절에서)

재귀 CTE는 다른 열 목록(예: 하위 쿼리)을 포함할 수 있지만, 이러한 세 개의 열 목록이 반드시 있어야 합니다.

이 세 개의 열 목록은 모두 서로 일치해야 합니다.

의사 코드에서 이는 다음과 유사합니다.

WITH RECURSIVE cte_name (X, Y) AS
(
  SELECT related_to_X, related_to_Y FROM table1
  UNION ALL
  SELECT also_related_to_X, also_related_to_Y
    FROM table1 JOIN cte_name ON <join_condition>
)
SELECT ... FROM ...

Copy

열 X 및 related_to_X 는 일치해야 합니다. 앵커 절은 CTE가 나타내는 “뷰”의 초기 “내용”을 생성하므로 앵커 절의 각 열(예: 열 related_to_x)은 CTE의 해당 열(예: 열 X)에 속할 출력을 생성해야 합니다.

also_related_to_X 및 X 열은 일치해야 합니다. 재귀 절의 각 반복에서 해당 절의 출력은 다음 반복에 대한 CTE/뷰의 새 내용이 됩니다.

또한, related_to_X 및 also_related_to_X 열은 일치해야 합니다. 이러한 열은 UNION ALL 연산자의 한쪽에 있으며, UNION ALL 연산자의 양쪽에 있는 열이 일치해야 하기 때문입니다.

예¶

비재귀 예¶

이 섹션에서는 샘플 쿼리 및 샘플 출력을 제공합니다. 예를 짧게 유지하기 위해 이 코드에서는 테이블을 만들고 로딩하는 문을 생략합니다.

이 첫 번째 예에서는 데이터의 하위 세트(이 경우 1976년에 출시된 음악 앨범)을 추출하는 뷰로서 간단한 WITH 절을 사용합니다. 이 작은 데이터베이스의 경우, 쿼리 출력은 다음과 같이 1976년에 출시된 두 앨범인 “Amigos” 및 “Look Into The Future”입니다.

with
  albums_1976 as (select * from music_albums where album_year = 1976)
select album_name from albums_1976 order by album_name;
+----------------------+
| ALBUM_NAME           |
|----------------------|
| Amigos               |
| Look Into The Future |
+----------------------+

Copy

다음 예에서는 이전 WITH 절과 함께 WITH 절을 사용합니다. journey_album_info_1976 이라는 CTE는 album_info_1976 이라는 CTE를 사용합니다. 출력은 다음과 같이 “Look Into The Future” 앨범으로, 밴드 이름이 포함됩니다.

with
   album_info_1976 as (select m.album_ID, m.album_name, b.band_name
      from music_albums as m inner join music_bands as b
      where m.band_id = b.band_id and album_year = 1976),
   Journey_album_info_1976 as (select *
      from album_info_1976 
      where band_name = 'Journey')
select album_name, band_name 
   from Journey_album_info_1976;
+----------------------+-----------+
| ALBUM_NAME           | BAND_NAME |
|----------------------+-----------|
| Look Into The Future | Journey   |
+----------------------+-----------+

Copy

이 예는 Santana 앨범 및 Journey 앨범에서 연주한 음악가를 나열합니다. 이 예에서는 WITH 절을 사용하지 않습니다. 이 쿼리 및 다음 몇 가지 쿼리(이러한 모든 쿼리는 같은 쿼리를 실행하는 동일한 방법임)의 경우, 출력은 다음과 같이 Santana 앨범 및 Journey 앨범에서 연주한 음악가의 ID와 이름입니다.

select distinct musicians.musician_id, musician_name
 from musicians inner join musicians_and_albums inner join music_albums inner join music_bands
 where musicians.musician_ID = musicians_and_albums.musician_ID
   and musicians_and_albums.album_ID = music_albums.album_ID
   and music_albums.band_ID = music_bands.band_ID
   and music_bands.band_name = 'Santana'
intersect
select distinct musicians.musician_id, musician_name
 from musicians inner join musicians_and_albums inner join music_albums inner join music_bands
 where musicians.musician_ID = musicians_and_albums.musician_ID
   and musicians_and_albums.album_ID = music_albums.album_ID
   and music_albums.band_ID = music_bands.band_ID
   and music_bands.band_name = 'Journey'
order by musician_ID;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

Copy

보시다시피 이전 쿼리에는 중복 코드가 포함되어 있습니다. 다음 몇 가지 예에서는 하나 이상의 명시적 뷰를 사용하여 이 쿼리를 단순화하는 방법, 그리고 CTE를 사용하여 쿼리를 단순화하는 방법을 보여줍니다.

이 쿼리는 뷰를 사용하여 이전 예의 중복 및 복잡성을 줄이는 방법을 보여줍니다(이전 예에서와 같이 이는 WITH 절을 사용하지 않음).

create or replace view view_musicians_in_bands AS
  select distinct musicians.musician_id, musician_name, band_name
    from musicians inner join musicians_and_albums inner join music_albums inner join music_bands
    where musicians.musician_ID = musicians_and_albums.musician_ID
      and musicians_and_albums.album_ID = music_albums.album_ID
      and music_albums.band_ID = music_bands.band_ID;

Copy

이 뷰를 사용하면 원래 쿼리를 다음과 같이 다시 쓸 수 있습니다.

select musician_id, musician_name from view_musicians_in_bands where band_name = 'Santana'
intersect
select musician_id, musician_name from view_musicians_in_bands where band_name = 'Journey'
order by musician_ID;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

Copy

이 예에서는 WITH 절을 사용하여 이전 쿼리와 동일한 작업을 수행합니다.

with
  musicians_in_bands as (
     select distinct musicians.musician_id, musician_name, band_name
      from musicians inner join musicians_and_albums inner join music_albums inner join music_bands
      where musicians.musician_ID = musicians_and_albums.musician_ID
        and musicians_and_albums.album_ID = music_albums.album_ID
        and music_albums.band_ID = music_bands.band_ID)
select musician_ID, musician_name from musicians_in_bands where band_name = 'Santana'
intersect
select musician_ID, musician_name from musicians_in_bands where band_name = 'Journey'
order by musician_ID
  ;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

Copy

이러한 문은 보다 세분화된 뷰를 만듭니다(이 예에서는 WITH 절을 사용하지 않음).

다음과 같이 특정 밴드의 앨범을 나열합니다.

create or replace view view_album_IDs_by_bands AS
 select album_ID, music_bands.band_id, band_name
  from music_albums inner join music_bands
  where music_albums.band_id = music_bands.band_ID;

Copy

다음과 같이 앨범에서 연주한 음악가를 나열합니다.

create or replace view view_musicians_in_bands AS
 select distinct musicians.musician_id, musician_name, band_name
  from musicians inner join musicians_and_albums inner join view_album_IDs_by_bands
  where musicians.musician_ID = musicians_and_albums.musician_ID
    and musicians_and_albums.album_ID = view_album_IDS_by_bands.album_ID;

Copy

이제 이러한 뷰를 사용하여 Santana 및 Journey 앨범 모두에서 연주한 음악가를 다음과 같이 쿼리합니다.

select musician_id, musician_name from view_musicians_in_bands where band_name = 'Santana'
intersect
select musician_id, musician_name from view_musicians_in_bands where band_name = 'Journey'
order by musician_ID;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

Copy

이러한 문은 보다 세분화된 암시적 뷰를 다음과 같이 만듭니다(이 예에서는 WITH 절을 사용함):

with
  album_IDs_by_bands as (select album_ID, music_bands.band_id, band_name
                          from music_albums inner join music_bands
                          where music_albums.band_id = music_bands.band_ID),
  musicians_in_bands as (select distinct musicians.musician_id, musician_name, band_name
                          from musicians inner join musicians_and_albums inner join album_IDs_by_bands
                          where musicians.musician_ID = musicians_and_albums.musician_ID
                            and musicians_and_albums.album_ID = album_IDS_by_bands.album_ID)
select musician_ID, musician_name from musicians_in_bands where band_name = 'Santana'
intersect
select musician_ID, musician_name from musicians_in_bands where band_name = 'Journey'
order by musician_ID
  ;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

Copy

재귀 예¶

다음은 재귀 CTE를 사용하여 피보나치 수열을 생성하는 기본 예입니다.

WITH RECURSIVE current_f (current_val, previous_val) AS
    (
    SELECT 0, 1
    UNION ALL 
    SELECT current_val + previous_val, current_val FROM current_f
      WHERE current_val + previous_val < 100
    )
  SELECT current_val FROM current_f ORDER BY current_val;
+-------------+
| CURRENT_VAL |
|-------------|
|           0 |
|           1 |
|           1 |
|           2 |
|           3 |
|           5 |
|           8 |
|          13 |
|          21 |
|          34 |
|          55 |
|          89 |
+-------------+

Copy

이 예는 자동차의 “부품 전개”를 표시하는 재귀 CTE가 있는 쿼리입니다.

-- The components of a car.
CREATE TABLE components (
    description VARCHAR,
    component_ID INTEGER,
    quantity INTEGER,
    parent_component_ID INTEGER
    );

INSERT INTO components (description, quantity, component_ID, parent_component_ID) VALUES
    ('car', 1, 1, 0),
       ('wheel', 4, 11, 1),
          ('tire', 1, 111, 11),
          ('#112 bolt', 5, 112, 11),
          ('brake', 1, 113, 11),
             ('brake pad', 1, 1131, 113),
       ('engine', 1, 12, 1),
          ('piston', 4, 121, 12),
          ('cylinder block', 1, 122, 12),
          ('#112 bolt', 16, 112, 12)   -- Can use same type of bolt in multiple places
    ;

Copy

WITH RECURSIVE current_layer (indent, layer_ID, parent_component_ID, component_id, description, sort_key) AS (
  SELECT 
      '...', 
      1, 
      parent_component_ID, 
      component_id, 
      description, 
      '0001'
    FROM components WHERE component_id = 1
  UNION ALL
  SELECT indent || '...',
      layer_ID + 1,
      components.parent_component_ID,
      components.component_id, 
      components.description,
      sort_key || SUBSTRING('000' || components.component_ID, -4)
    FROM current_layer JOIN components 
      ON (components.parent_component_id = current_layer.component_id)
  )
SELECT
  -- The indentation gives us a sort of "side-ways tree" view, with
  -- sub-components indented under their respective components.
  indent || description AS description, 
  component_id,
  parent_component_ID
  -- The layer_ID and sort_key are useful for debugging, but not
  -- needed in the report.
--  , layer_ID, sort_key
  FROM current_layer
  ORDER BY sort_key;
+-------------------------+--------------+---------------------+
| DESCRIPTION             | COMPONENT_ID | PARENT_COMPONENT_ID |
|-------------------------+--------------+---------------------|
| ...car                  |            1 |                   0 |
| ......wheel             |           11 |                   1 |
| .........tire           |          111 |                  11 |
| .........#112 bolt      |          112 |                  11 |
| .........brake          |          113 |                  11 |
| ............brake pad   |         1131 |                 113 |
| ......engine            |           12 |                   1 |
| .........#112 bolt      |          112 |                  12 |
| .........piston         |          121 |                  12 |
| .........cylinder block |          122 |                  12 |
+-------------------------+--------------+---------------------+

Copy

더 많은 예는 CTEs(일반 테이블 식) 관련 작업하기 섹션을 참조하십시오.