PostgreSQL查询：实现累计求和至指定阈值并返回对应日期

需求说明 #

需要编写PostgreSQL查询语句，找出每个客户累计求和达到指定金额（示例中为1000）的最早日期。现有基础查询是按客户和日期分组求和的语句，表结构及数据示例如下：

现有基础查询：

select client_id, dt, SUM(value)
from my_table
group by 1, 2;

表结构及数据：

client_id |    dt      |  value
----------+------------+-------
    23    | 2023-01-01 |   200
    23    | 2023-01-02 |   800
    23    | 2023-01-03 |   500

期望结果：针对client_id=23，累计求和达到1000（200+800）时，返回对应的日期2023-01-02。

实现方案 #

利用窗口函数计算累计求和，再筛选出每个客户首次达标的记录：

完整查询语句（PostgreSQL 13+） #

WITH daily_sums AS (
    -- 按客户+日期计算每日总和（若原表无重复日期，可直接跳过此步）
    SELECT client_id, dt, SUM(value) AS daily_value
    FROM my_table
    GROUP BY client_id, dt
),
cumulative_sums AS (
    -- 按客户分组、日期排序，计算累计求和
    SELECT 
        client_id,
        dt,
        SUM(daily_value) OVER (PARTITION BY client_id ORDER BY dt) AS total_cumulative
    FROM daily_sums
)
-- 筛选每个客户首次累计金额≥指定值的记录
SELECT 
    client_id,
    dt AS reach_target_date
FROM cumulative_sums
WHERE total_cumulative >= 1000 -- 替换为你的目标金额
QUALIFY ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY dt) = 1;

低版本兼容写法（PostgreSQL <13） #

如果你的PostgreSQL版本不支持QUALIFY，可以用子查询实现：

WITH daily_sums AS (
    SELECT client_id, dt, SUM(value) AS daily_value
    FROM my_table
    GROUP BY client_id, dt
),
cumulative_sums AS (
    SELECT 
        client_id,
        dt,
        SUM(daily_value) OVER (PARTITION BY client_id ORDER BY dt) AS total_cumulative
    FROM daily_sums
)
SELECT client_id, reach_target_date
FROM (
    SELECT 
        client_id,
        dt AS reach_target_date,
        ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY dt) AS rn
    FROM cumulative_sums
    WHERE total_cumulative >= 1000
) t
WHERE rn = 1;

关键说明 #

1. PARTITION BY client_id：确保每个客户的累计求和独立计算
2. ORDER BY dt：保证累计求和按日期从早到晚顺序累加
3. ROW_NUMBER()：用于筛选每个客户的第一条达标记录，确保返回的是最早达标日期

测试结果 #

针对示例数据执行后，会得到：

client_id | reach_target_date
----------+-------------------
    23    | 2023-01-02

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