在PostgreSQL查询中如何基于分位数对结果进行分区？

嗨，这个需求PostgreSQL其实有非常直接的解决方案——你完全不需要提前单独计算分位数再传入，用内置的窗口函数就能直接在查询里完成按行业+分位数的分区。下面我结合你的原代码和不同拆分场景给你具体实现：

核心方案：用NTILE()实现均分记录的分位数分区 #

PostgreSQL的NTILE(n)窗口函数专门用来把一个分组内的数据均匀拆分成n个“桶”，每个桶对应一个分位区间。它会自动帮你完成分组内的排序和均分，正好匹配你要给每个行业拆分成四分位的需求。

四分位数分区（对应你的场景） #

直接修改你原查询里的groupingNumRank字段，用NTILE(4)替代原来的ROW_NUMBER()，同时保持按行业分区、收入排序的逻辑：

with data as ( 
select 
    g.ticker, 
    g.industry, 
    g.countryname, 
    g.exchange, 
    c.year, 
    c.revenue, 
    -- 按行业分组，将收入从小到大拆成4个四分位桶，1=最低位，4=最高位
    NTILE(4) OVER (PARTITION BY g.industry ORDER BY c.revenue ASC) AS quartile_rank, 
    AVG(c.revenue) over (PARTITION BY g.industry) as industavg, 
    ... -- 你的其他字段
)

这样tech和retail两个行业各自的记录都会被分配1-4的编号，同一个行业内收入相近的记录会被分到同一个桶里。

其他常见数据拆分方式的实现 #

1. 十分位数分区 #

如果要拆成十分位，只需要把NTILE()的参数改成10就行，每个行业会被拆成10个规模相近的分组：

NTILE(10) OVER (PARTITION BY g.industry ORDER BY c.revenue ASC) AS decile_rank

2. 百分比级拆分（比如5%/1%区间） #

要做更细的百分比拆分，调整NTILE()的参数即可：

• 按5%区间拆分（共20组）：NTILE(20) OVER (...) AS pct5_rank
• 按1%区间拆分（共100组）：NTILE(100) OVER (...) AS pct1_rank

3. 基于固定分位数值的分区（非均分记录数） #

如果你的需求不是均分记录数，而是基于行业收入的实际分位数值（比如必须按行业收入的25%/50%/75%阈值划分），可以先计算分位阈值，再关联原数据划分区间：

-- 第一步：先算出每个行业的四分位阈值
with industry_quantiles as (
    select 
        industry,
        -- PERCENTILE_CONT是连续型分位数，PERCENTILE_DISC是离散型，按需选择
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY revenue) as q1, -- 25%分位
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY revenue) as q2,  -- 中位数
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY revenue) as q3  -- 75%分位
    from your_revenue_table -- 替换成你的实际收入表
    group by industry
),
-- 第二步：关联原数据，用CASE按阈值划分区间
data as (
    select 
        g.ticker, 
        g.industry, 
        g.countryname, 
        g.exchange, 
        c.year, 
        c.revenue,
        case
            when c.revenue <= iq.q1 then 1
            when c.revenue <= iq.q2 then 2
            when c.revenue <= iq.q3 then 3
            else 4
        end as quartile_by_threshold,
        AVG(c.revenue) over (PARTITION BY g.industry) as industavg,
        ... -- 你的其他字段
    from your_company_table g -- 替换成你的实际公司信息表
    join your_revenue_table c on g.ticker = c.ticker -- 替换成你的关联条件
    join industry_quantiles iq on g.industry = iq.industry
)
select * from data;

这种方式适合需要明确知道分位具体数值，或者要求同一分区内的收入必须落在固定数值区间的场景。

几个关键注意点 #

• NTILE()会尽量保证每个桶的记录数相等，如果分组内的记录数不能被n整除，前几个桶会多一条记录。
• 窗口函数里的ORDER BY必须指定，否则分位划分毫无意义——排序规则决定了分位的高低顺序。
• 如果需要处理空值，记得提前用WHERE过滤或者在ORDER BY里指定NULLS FIRST/LAST。

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