如何用SQL/Presto实现每月月末获取客户最新事件日志值？

问题背景 #

现有客户事件日志表结构及数据如下：

原始表数据 #

需求是获取每个客户在每月月末的最新数值，若当月无更新则沿用之前月份的最新值，预期输出如下：

预期输出 #

Presto SQL实现代码 #

-- 步骤1：自动生成数据覆盖范围内的所有月份月末日期
WITH month_ends AS (
    SELECT date_trunc('month', s.month_start) + interval '1 month' - interval '1 day' AS month_end
    FROM (
        SELECT min(timestamp) AS min_date, max(timestamp) AS max_date
        FROM customer_event_log
    ) t
    UNNEST(sequence(t.min_date, t.max_date, interval '1 month')) AS s(month_start)
),

-- 步骤2：提取每个客户每月的最新记录
customer_monthly_latest AS (
    SELECT
        customer_id,
        date_trunc('month', timestamp) + interval '1 month' - interval '1 day' AS month_end,
        last_value(value) OVER (
            PARTITION BY customer_id, date_trunc('month', timestamp)
            ORDER BY timestamp
        ) AS latest_value
    FROM customer_event_log
    GROUP BY customer_id, timestamp, value
),

-- 步骤3：获取所有唯一客户ID
all_customers AS (
    SELECT DISTINCT customer_id FROM customer_event_log
),

-- 步骤4：生成客户与所有月份的全量关联
customer_month_full AS (
    SELECT
        ac.customer_id,
        me.month_end,
        cm.latest_value
    FROM all_customers ac
    CROSS JOIN month_ends me
    LEFT JOIN customer_monthly_latest cm
        ON ac.customer_id = cm.customer_id
        AND me.month_end = cm.month_end
)

-- 最终查询：用窗口函数向前填充缺失值
SELECT
    customer_id AS "客户ID（Customer ID）",
    date_format(month_end, 'yyyy MMM') AS "月末日期（Month End）",
    last_value(latest_value IGNORE NULLS) OVER (
        PARTITION BY customer_id
        ORDER BY month_end
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS "数值（Value）"
FROM customer_month_full
ORDER BY customer_id, month_end;

代码说明 #

• month_ends：无需静态编码月份，自动根据数据中最早和最晚的日期生成覆盖范围内的所有月末日期。
• customer_monthly_latest：通过窗口函数last_value取每个客户当月时间戳最晚的数值，作为当月有效记录。
• customer_month_full：交叉关联所有客户和所有月份，确保每个客户每个月都有一条记录，当月无数据时latest_value为NULL。
• 最终查询使用last_value(IGNORE NULLS)窗口函数，将NULL值替换为该客户之前最近的非NULL数值，实现“沿用之前最新值”的需求。

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