如何在雪花中的两个日期之间动态生成日期？

Question

我一直在搜索雪花中的一个很好的generate_series类似物，但是到目前为止，我发现的范围有些限制。我见过的大多数示例都使用 rowcount ，但是我需要比这更动态的东西。

我有这些列： Location_ID，subscription_id，start_date，end_date

日期列的日期通常是一年，但是在很多情况下，我需要考虑这一点。

如何在开始日期和结束日期之间产生无间隙的日期范围？

谢谢你！

Answer 1

有几种方法可以解决此问题，但这是我使用 sql Generator function datespine_groups 。

我之所以喜欢这样做，是因为它的灵活性足够灵活，我可以在日期之间添加每周，每小时或每月的间隔并重复使用代码。

参数组界限更改连接以微妙的方式发生的方式，使您可以控制日期如何被过滤出来：

global

local - 每个location_id，subscription_id subscription_id都有自己的启动/结束日期，该日期基于日期列中的第一个和最后一个值

混合混合 - 每个位置_id，subscription_id都有自己开始/结束日期，但是它们都共享相同的结束日期，

而不是尝试使其在1查询中完美，我认为使用混合进行生成可能更容易，然后过滤> group_start_date 发生在原始数据的 end_date 之后。

这是SQL。一开始您可以（1）找到一种动态生成3个参数的方法，或（2）硬码一个荒谬的范围，可以持续您的职业生涯，并让其余的查询过滤掉:)

您可以更改月到另一个 datePart ，我只认为您正在寻找每月。

WITH GLOBAL_SPINE AS (
  SELECT 
    ROW_NUMBER() OVER (
      ORDER BY 
        NULL
    ) as INTERVAL_ID, 
    DATEADD(
      'month', 
      (INTERVAL_ID - 1), 
      '2018-01-01T00:00' :: timestamp_ntz
    ) as SPINE_START, 
    DATEADD(
      'month', INTERVAL_ID, '2018-01-01T00:00' :: timestamp_ntz
    ) as SPINE_END 
  FROM 
    TABLE (
      GENERATOR(ROWCOUNT => 2192)
    )
), 
GROUPS AS (
  SELECT 
    location_id, 
    subscription_id, 
    MIN(start_date) AS LOCAL_START, 
    MAX(start_date) AS LOCAL_END 
  FROM 
    My_First_Table 
  GROUP BY 
    location_id, 
    subscription_id
), 
GROUP_SPINE AS (
  SELECT 
    location_id, 
    subscription_id, 
    SPINE_START AS GROUP_START, 
    SPINE_END AS GROUP_END 
  FROM 
    GROUPS G CROSS 
    JOIN LATERAL (
      SELECT 
        SPINE_START, 
        SPINE_END 
      FROM 
        GLOBAL_SPINE S 
      WHERE 
        S.SPINE_START >= G.LOCAL_START
    )
) 
SELECT 
  G.location_id AS GROUP_BY_location_id, 
  G.subscription_id AS GROUP_BY_subscription_id, 
  GROUP_START, 
  GROUP_END, 
  T.* 
FROM 
  GROUP_SPINE G 
  LEFT JOIN My_First_Table T ON start_date >= G.GROUP_START 
  AND start_date < G.GROUP_END 
  AND G.location_id = T.location_id 
  AND G.subscription_id = T.subscription_id