PostgreSQL：计算ID累计得分首次达到100的日期

这是窗口函数使用中非常常见的坑，我来帮你捋清楚问题所在和解决方案~

问题原因 #

你遇到的错误column "cumulative_score" does not exist，是因为SQL的执行顺序导致的：WHERE子句的执行逻辑早于窗口函数的计算。当数据库执行WHERE过滤时，cumulative_score这个由窗口函数生成的别名还没有被计算出来，自然找不到这个列。

解决方案 #

我们需要先计算出包含累计得分的完整数据集，再对这个数据集进行过滤和筛选每个ID的首次达标记录。这里推荐用CTE（公共表表达式）的方式，代码可读性更高：

WITH cumulative_scores AS (
    SELECT 
        id,
        date,
        score,
        -- 按ID分区，按日期时间排序计算累计得分
        SUM(score) OVER (
            PARTITION BY id 
            ORDER BY TO_DATE(date, 'MM/DD/YYYY HH24:MI') 
            ROWS UNBOUNDED PRECEDING
        ) AS cumulative_score
    FROM test_q1
)
-- 筛选累计得分≥100的记录，再取每个ID的第一条达标记录
SELECT 
    TO_DATE(date, 'MM/DD/YYYY') AS date, -- 转换为YYYY-MM-DD格式
    id,
    score,
    cumulative_score
FROM cumulative_scores
WHERE cumulative_score >= 100
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY id 
    ORDER BY TO_DATE(date, 'MM/DD/YYYY')
) = 1;

如果你更习惯子查询写法，也可以这样写：

SELECT 
    TO_DATE(date, 'MM/DD/YYYY') AS date,
    id,
    score,
    cumulative_score
FROM (
    SELECT 
        id,
        date,
        score,
        SUM(score) OVER (
            PARTITION BY id 
            ORDER BY TO_DATE(date, 'MM/DD/YYYY HH24:MI') 
            ROWS UNBOUNDED PRECEDING
        ) AS cumulative_score
    FROM test_q1
) AS sub_query
WHERE cumulative_score >= 100
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY id 
    ORDER BY TO_DATE(date, 'MM/DD/YYYY')
) = 1;

关键细节说明 #

1. 日期排序的正确性：你的日期字段是带时间的字符串，直接用字符串排序会出现逻辑错误（比如10/3/2017会排在1/2/2017前面），所以必须用TO_DATE转换为日期时间类型再排序。
2. QUALIFY子句：这个子句专门用来过滤窗口函数的结果，它在窗口函数计算之后执行，刚好满足我们“取每个ID第一条达标记录”的需求。如果你的数据库不支持QUALIFY（比如MySQL），可以改用嵌套子查询结合MIN函数来实现：

SELECT 
    TO_DATE(cs.date, 'MM/DD/YYYY') AS date,
    cs.id,
    cs.score,
    cs.cumulative_score
FROM cumulative_scores cs
JOIN (
    SELECT 
        id,
        MIN(TO_DATE(date, 'MM/DD/YYYY')) AS first_reach_date
    FROM cumulative_scores
    WHERE cumulative_score >= 100
    GROUP BY id
) AS first_dates ON cs.id = first_dates.id AND TO_DATE(cs.date, 'MM/DD/YYYY') = first_dates.first_reach_date;

执行上面的代码后，就能得到你预期的中间结果：

如果只需要id和对应的日期，只需要调整SELECT的字段为TO_DATE(date, 'MM/DD/YYYY') AS date, id即可。

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