如何编写SQL语句生成含计算字段factor的完整查询结果？

针对你的需求，我们可以利用窗口函数实现高效计算，仅需一次全表扫描即可完成，适合百万级数据量的场景：

基础实现 #

SELECT
    data_date,
    portfolio,
    weight,
    comments,
    -- 按data_date+portfolio分组，计算非Exclude权重总和与总权重总和的比值
    SUM(CASE WHEN comments != 'Exclude' THEN weight ELSE 0 END) OVER (PARTITION BY data_date, portfolio)
    /
    SUM(weight) OVER (PARTITION BY data_date, portfolio) AS factor
FROM TABLE_A;

优化版（处理除数为0的情况） #

如果存在分组总权重为0的情况，直接除法会触发错误，可添加判断逻辑：

SELECT
    data_date,
    portfolio,
    weight,
    comments,
    CASE
        WHEN SUM(weight) OVER (PARTITION BY data_date, portfolio) = 0 THEN 0 -- 也可改为NULL，根据业务需求调整
        ELSE SUM(CASE WHEN comments != 'Exclude' THEN weight ELSE 0 END) OVER (PARTITION BY data_date, portfolio)
             /
             SUM(weight) OVER (PARTITION BY data_date, portfolio)
    END AS factor
FROM TABLE_A;

逻辑说明 #

• 用PARTITION BY data_date, portfolio将数据按日期和组合分组，确保每个分组内的计算独立
• SUM(CASE...) OVER (...)计算分组内排除Exclude记录后的权重总和
• SUM(weight) OVER (...)计算分组内的总权重总和
• 两者相除得到每行对应的factor值，无需额外子查询或WITH子句重复扫描表，性能更优

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