Zenn
❄️

[Snowflake] 指定した日付間の日付一覧を作成するクエリを書いてみた

2023/07/10に公開
SQL
データ分析
Snowflake
DataEngineering
データ活用
tech

2つの日付を与え、その期間の日付一覧を作成したいことが時々あるのでSnowflakeでも作成してみました。

指定した期間の日付一覧を得るクエリ

WITH
arr AS (
  SELECT
    ARRAY_GENERATE_RANGE(
      0, 
      DATEDIFF('DAY', '2023-07-01', '2023-07-10') + 1
    )
),

dates AS (
  SELECT
    DATEADD('DAY', value, DATE('2023-07-01')) AS date
  FROM
    TABLE(FLATTEN(INPUT => SELECT * FROM arr))
)

SELECT * FROM dates

このクエリを実行すると、以下の様な出力が得られます。

arr AS (
  SELECT
    ARRAY_GENERATE_RANGE(
      0, 
      DATEDIFF('DAY', '2023-07-01', '2023-07-10') + 1
    )
)

この部分で [0, 1, 2, 3, … 9] という、10個の要素を持つ ARRAY を作成しています。10個なのは 2023-07-01 ~ 2023-07-10 までの日数が10日間だからで
す。DATE_DIFFで計算しています。

dates AS (
  SELECT
    DATEADD('DAY', value, DATE('2023-07-01')) AS date
  FROM
    TABLE(FLATTEN(INPUT => SELECT * FROM arr))
)

ここでは、先程の部分で作成される ARRAY を FLATTEN を使って行に展開し TABLE関数 で読み込んでいます。
最後に ARRAY の各要素 (0 … 9) を 2023-07-01 に DATE_ADD してやると完成です。
※ FLATTENでARRAYを展開すると、6列 × ARRAYの要素数 のデータが出力され、VALUEという列にARRAYの各要素が格納されます [参考]

日付一覧に加え、曜日などの情報を付加してviewにしておくと便利だと思います。

CREATE OR REPLACE VIEW calendar AS 

WITH
arr AS (
  SELECT
    ARRAY_GENERATE_RANGE(
      0, 
      DATEDIFF('DAY', '2023-07-01', '2023-07-10') + 1
    )
),

dates AS (
  SELECT
    DATEADD('DAY', value, DATE('2023-07-01')) AS date
  FROM
    TABLE(FLATTEN(INPUT => SELECT * FROM arr))
)

SELECT 
  date,
  EXTRACT(YEAR FROM date) AS year,
  EXTRACT(MONTH FROM date) as month,
  EXTRACT(DAY FROM date) AS day,
  EXTRACT(DOW FROM date) AS dow,
  DAYNAME(date) AS day_name
FROM
  dates

指定した日付からN日間の日付一覧を得るクエリ

指定した2つの日付間ではなく、指定した日からN日間の日付一覧であれば、以下の様にもっとシンプルに書くこともできます。

-- 2023-07-01から10日間の日付一覧を得るクエリ
SELECT
  DATEADD('DAY', seq1(), DATE('2023-07-01')) AS date
FROM
  TABLE(GENERATOR(rowcount => 10))

Discussion