求提取多客户日终交易值并计算平均值的SQL代码

需求对应SQL脚本 #

假设交易数据表名为 transactions，字段分别为 date（日期）、client_id（客户ID）、transaction_id（交易ID）、transaction_value（交易值），以下是实现需求的SQL代码：

-- 筛选每个客户当日最大transaction_id对应的交易记录
WITH daily_last_trans AS (
    SELECT
        date,
        transaction_value,
        ROW_NUMBER() OVER (PARTITION BY date, client_id ORDER BY transaction_id DESC) AS rn
    FROM transactions
),
-- 按日期汇总每日符合条件的交易值总和
daily_totals AS (
    SELECT
        date,
        SUM(transaction_value) AS daily_sum
    FROM daily_last_trans
    WHERE rn = 1
    GROUP BY date
)
-- 计算所有日期总和的平均值
SELECT AVG(daily_sum) AS avg_daily_total
FROM daily_totals;

脚本逻辑说明 #

1. 提取客户当日最后一笔交易：使用窗口函数 ROW_NUMBER()，按date和client_id分区，对每个分区内的交易按transaction_id降序排序，排名为1的记录即为该客户当日最后一笔交易（以最大transaction_id为准）。
2. 按日期汇总总和：过滤出排名为1的记录，按date分组并求和，得到每日的汇总值。
3. 计算平均值：对每日汇总值求平均，得到最终结果。

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