Postgres中如何同时获取个体数据与对应聚合数据进行对比

核心使用Postgres的窗口函数即可实现，无需拆分多次查询或复杂嵌套，既能保留单企业的明细数据，也能同步计算同维度的行业聚合值。

完整查询示例 #

with ibm_industry as (
    -- 预先获取IBM所属行业
    select industry
    from "company"."General"
    where lower(ticker) = 'ibm.us'
),
industry_all_firm_data as (
    -- 拉取同行业所有企业的年度财务+碳排放数据
    select
        extract(year from fisy.date) as report_year,
        lower(fisy.ticker) as ticker,
        (c.carbon / ln(fisy.totalrevenue - fisy.grossprofit)) as emissions_per_cogs,
        fisy.totalrevenue as firm_revenue
    from company."financials_Income_Statement_yearly" fisy
    join company.carbon c on lower(c.ticker) = lower(fisy.ticker)
    join "company"."General" g on lower(g.ticker) = lower(fisy.ticker)
    where g.industry = (select industry from ibm_industry)
)
select
    report_year,
    -- IBM个体维度数据
    emissions_per_cogs as ibm_emissions_per_cogs,
    firm_revenue as ibm_total_revenue,
    -- 同行业同年度聚合维度数据
    sum(firm_revenue) over(partition by report_year) as industry_total_revenue,
    avg(emissions_per_cogs) over(partition by report_year) as industry_avg_emissions_per_cogs,
    -- 直接计算所需的对比指标（示例为IBM营收占行业总营收比例）
    round(firm_revenue * 100.0 / sum(firm_revenue) over(partition by report_year), 2) as ibm_revenue_percent_of_industry
from industry_all_firm_data
where ticker = 'ibm.us'
order by report_year desc;

逻辑说明 #

• 窗口函数over(partition by report_year)会按照年份分组计算行业聚合值，不会改变原有行的结构，因此可以同时保留单家企业的明细字段和聚合计算结果
• 如果需要更多聚合指标，直接新增对应的窗口函数即可，比如行业最高/最低碳排放、行业营收中位数等
• 最终过滤ticker = 'ibm.us'就可以只保留IBM的行，同时每行都携带对应年份的全行业聚合数据

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