Snowflake中基于不同时间戳聚合含数组字段的账单数据需求

需求分析 #

需将表中Bill_sub数组字段拆分为独立行，结合Bill_main_item形成主-子项组合，分别按BillStarttime的日期维度统计提交账单数（Bill_Submitted求和），按Billpaidtime的日期维度统计已支付账单数（仅统计Billpaid=1的记录并求和）。

实现SQL #

WITH flattened_data AS (
    -- 拆分Bill_sub数组，生成主项+子项的关联行
    SELECT
        Bill_id,
        DATE(BillStarttime) AS submit_date,
        DATE(Billpaidtime) AS paid_date,
        Bill_Submitted,
        CASE WHEN Billpaid = 1 THEN 1 ELSE 0 END AS valid_paid,
        Bill_main_item,
        TRIM(value) AS sub_item  -- 统一子项格式，可按需添加UPPER/LOWER统一大小写
    FROM your_table_name,
         LATERAL FLATTEN(input => Bill_sub)
),
submit_stats AS (
    -- 按提交日期、主项、子项统计提交数
    SELECT
        submit_date AS stat_date,
        Bill_main_item,
        sub_item,
        SUM(Bill_Submitted) AS total_submitted
    FROM flattened_data
    GROUP BY submit_date, Bill_main_item, sub_item
),
paid_stats AS (
    -- 按支付日期、主项、子项统计有效支付数
    SELECT
        paid_date AS stat_date,
        Bill_main_item,
        sub_item,
        SUM(valid_paid) AS total_paid
    FROM flattened_data
    GROUP BY paid_date, Bill_main_item, sub_item
)
-- 关联两个统计结果，补全缺失值为0
SELECT
    COALESCE(s.stat_date, p.stat_date) AS stat_date,
    COALESCE(s.Bill_main_item, p.Bill_main_item) AS main_item,
    COALESCE(s.sub_item, p.sub_item) AS sub_item,
    COALESCE(s.total_submitted, 0) AS total_submitted,
    COALESCE(p.total_paid, 0) AS total_paid
FROM submit_stats s
FULL OUTER JOIN paid_stats p
    ON s.stat_date = p.stat_date
    AND s.Bill_main_item = p.Bill_main_item
    AND s.sub_item = p.sub_item
ORDER BY stat_date, main_item, sub_item;

代码说明 #

1. flattened_data CTE：用LATERAL FLATTEN拆分Bill_sub数组，将每个数组元素转为独立行；同时提取时间戳的日期部分，标记有效支付记录（仅Billpaid=1的计入统计）。
2. submit_stats CTE：按提交日期、主项、子项分组，求和得到每个组合的提交账单总数。
3. paid_stats CTE：按支付日期、主项、子项分组，求和得到每个组合的有效支付账单总数。
4. 最终关联：通过FULL OUTER JOIN关联两个统计结果，用COALESCE将缺失的统计值补为0，确保所有日期和组合的统计数据完整。

示例结果验证 #

以2024-09-18为例：

• 主项Iron、子项Cast/Ore的total_submitted为2，total_paid为0（无对应支付记录落在该日期）
• 主项Steel、子项Cast/Alloy的total_submitted为1，total_paid为0

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