Snowflake中计算两个日期间指定星期几的出现次数

以下是两种生产环境常用的实现方案，可根据场景按需选择：

方案1：生成日期序列统计（灵活易读，适合大多数场景） #

核心思路是先生成两个日期之间的所有完整日期，再筛选符合星期要求的日期计数即可。
使用DAYOFWEEKISO函数可以直接匹配ISO标准星期计数：周一返回1、周二返回2...周日返回7，避免默认星期函数的计数歧义。
示例代码如下：

-- 示例：统计2024-01-01到2024-01-31之间，周一（1）和周三（3）的总出现次数
SELECT COUNT(*) AS target_weekday_count
FROM TABLE(GENERATE_SERIES(
  '2024-01-01'::DATE,
  '2024-01-31'::DATE,
  INTERVAL '1 DAY'
)) AS date_series(d)
WHERE DAYOFWEEKISO(d) IN (1,3);

使用时替换两个边界日期、IN后的星期编号列表即可。

方案2：数学公式计算（性能最优，无序列生成开销，适合大流量计算场景） #

不需要生成完整日期序列，直接通过周数、剩余天数数学计算得到结果，适合需要对表中大量行的两个日期字段批量计算的场景。
示例代码如下：

WITH params AS (
  SELECT
    '2024-01-01'::DATE AS date1,
    '2024-01-31'::DATE AS date2,
    ARRAY_CONSTRUCT(1,3) AS target_weekdays -- 要统计的星期ISO编号：周一、周三
),
calc AS (
  SELECT
    date1,
    date2,
    target_weekdays,
    DATEDIFF('day', date1, date2) + 1 AS total_days,
    FLOOR(total_days /7) AS full_weeks,
    MOD(total_days,7) AS remaining_days,
    DAYOFWEEKISO(date1) AS start_weekday
  FROM params
)
SELECT
  -- 完整周的目标星期数 + 剩余天数里的目标星期数
  full_weeks * ARRAY_SIZE(target_weekdays) +
  (SELECT COUNT(*)
   FROM TABLE(FLATTEN(input => target_weekdays)) t
   WHERE t.value BETWEEN start_weekday AND start_weekday + remaining_days -1
      OR t.value +7 BETWEEN start_weekday AND start_weekday + remaining_days -1
  ) AS target_weekday_count
FROM calc;

注意事项 #

• 如果习惯用周日作为一周第一天，可以把DAYOFWEEKISO替换为DAYOFWEEK，注意对应调整目标星期的数值：Snowflake默认DAYOFWEEK函数周日返回1，周六返回7。
• 两种方案均支持直接和表字段绑定，实现批量计算。

内容的提问来源于stack exchange，提问作者Jimpats