PostgreSQL/DBT：如何基于current=1子集计算全量value的cume_dist百分位数

需求说明 #

需要基于表中current=1的记录子集，为所有记录（包括current=0的历史数据）计算value的百分位数，模拟这些历史数据作为当前数据的对比结果。

解决方案 #

直接在窗口函数中通过filter子句限定基准数据集为current=1的记录，即可实现需求：

SELECT
    target,
    question,
    value,
    current,
    -- 基于current=1的子集计算所有记录的百分位数
    (cume_dist() OVER (
        PARTITION BY question
        ORDER BY value
        FILTER (WHERE current = 1)
    )) * 100 AS percentile
FROM your_table_name;

逻辑解释 #

• PARTITION BY question：按问题分组，保证每个问题的百分位数独立计算
• ORDER BY value：按value字段排序，作为累积分布计算的排序依据
• FILTER (WHERE current = 1)：限定窗口内仅包含current=1的记录作为基准数据集，这样所有记录（包括current=0的历史数据）的百分位数都是基于当前有效数据计算的

关于cume_dist()的参数说明 #

PostgreSQL中的cume_dist()是无参数的窗口函数，不存在额外的输入参数。你可能混淆了窗口子句（如PARTITION BY、ORDER BY）和函数参数，这类窗口函数的行为完全由窗口子句定义，没有可传入的函数参数。

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