Snowflake缺失日期返回0值的低成本多用户查询实现问询

一、基础查询：补全缺失日期并返回0 #

原查询的问题是无法生成连续日期范围，导致缺失日期不会出现在结果中。通过生成过去6个月的完整日期序列，再左连接用户访问数据，即可让无访问记录的日期返回0：

WITH date_range AS (
    -- 生成过去6个月到今天的所有连续日期
    SELECT DATEADD(day, seq4(), DATEADD(month, -6, CURRENT_DATE())) AS visit_dt
    FROM TABLE(GENERATOR(ROWCOUNT => (SELECT DATEDIFF(day, DATEADD(month, -6, CURRENT_DATE()), CURRENT_DATE()) + 1)))
)
SELECT 
    dr.visit_dt,
    -- 用COALESCE将NULL转换为0
    COALESCE(COUNT(DISTINCT ud.visits), 0) AS distinct_visits
FROM date_range dr
LEFT JOIN User_Data ud 
    ON dr.visit_dt = ud.visit_dt 
    AND ud.user = 'Alec'
    AND ud.visit_dt >= DATEADD(month, -6, CURRENT_DATE())
GROUP BY dr.visit_dt
ORDER BY dr.visit_dt DESC;

二、成本效益优化方案 #

针对每日多次针对不同用户执行的场景，可通过以下方式降低计算和存储成本：

1. 预建日期维度表

避免每次查询都生成日期序列，一次性创建覆盖足够范围的日期维度表，后续查询直接复用：

-- 创建日期维度表
CREATE OR REPLACE TABLE date_dimension (
    date_dt DATE PRIMARY KEY,
    year INT,
    month INT,
    day INT
);

-- 插入30年的日期数据（按需调整范围）
INSERT INTO date_dimension (date_dt)
SELECT DATEADD(day, seq4(), '2020-01-01')
FROM TABLE(GENERATOR(ROWCOUNT => 10950))
ORDER BY date_dt;

使用日期维度表的查询：

SELECT 
    dd.date_dt AS visit_dt,
    COALESCE(COUNT(DISTINCT ud.visits), 0) AS distinct_visits
FROM date_dimension dd
LEFT JOIN User_Data ud 
    ON dd.date_dt = ud.visit_dt 
    AND ud.user = 'Alec'
WHERE dd.date_dt BETWEEN DATEADD(month, -6, CURRENT_DATE()) AND CURRENT_DATE()
GROUP BY dd.date_dt
ORDER BY dd.date_dt DESC;

2. 优化源表存储结构

• 按日期分区：减少查询时扫描的数据量，仅读取过去6个月的分区：

  ALTER TABLE User_Data PARTITION BY (visit_dt);
  

• 按用户+日期聚类：让相同用户、相同日期的数据物理聚合，加速过滤和连接操作：

  ALTER TABLE User_Data CLUSTER BY (user, visit_dt);
  

3. 用物化视图预聚合数据

预计算每个用户每日的去重访问量，避免每次查询重复执行COUNT(DISTINCT)：

CREATE OR REPLACE MATERIALIZED VIEW user_daily_visits
AS
SELECT 
    user,
    visit_dt,
    COUNT(DISTINCT visits) AS distinct_visits
FROM User_Data
GROUP BY user, visit_dt;

基于物化视图的查询（速度更快、成本更低）：

WITH date_range AS (
    SELECT DATEADD(day, seq4(), DATEADD(month, -6, CURRENT_DATE())) AS visit_dt
    FROM TABLE(GENERATOR(ROWCOUNT => (SELECT DATEDIFF(day, DATEADD(month, -6, CURRENT_DATE()), CURRENT_DATE()) + 1)))
)
SELECT 
    dr.visit_dt,
    COALESCE(udv.distinct_visits, 0) AS distinct_visits
FROM date_range dr
LEFT JOIN user_daily_visits udv 
    ON dr.visit_dt = udv.visit_dt 
    AND udv.user = 'Alec'
ORDER BY dr.visit_dt DESC;

4. 使用绑定变量传递用户参数

通过客户端或脚本执行查询时，用绑定变量（如:user_param）代替硬编码用户名，让Snowflake复用查询计划，减少编译开销：

WITH date_range AS (
    SELECT DATEADD(day, seq4(), DATEADD(month, -6, CURRENT_DATE())) AS visit_dt
    FROM TABLE(GENERATOR(ROWCOUNT => (SELECT DATEDIFF(day, DATEADD(month, -6, CURRENT_DATE()), CURRENT_DATE()) + 1)))
)
SELECT 
    dr.visit_dt,
    COALESCE(COUNT(DISTINCT ud.visits), 0) AS distinct_visits
FROM date_range dr
LEFT JOIN User_Data ud 
    ON dr.visit_dt = ud.visit_dt 
    AND ud.user = :user_param
    AND ud.visit_dt >= DATEADD(month, -6, CURRENT_DATE())
GROUP BY dr.visit_dt
ORDER BY dr.visit_dt DESC;

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