SQL查询如何新增汇总行 同时展示SUM求和与MEAN均值结果

可以实现，你可以通过UNION ALL拼接明细查询结果和汇总统计结果的方式达成需求，具体修改方案如下：

实现逻辑 #

• 先保留你原有的明细行查询逻辑，获取每个policy_vintage对应的各项指标
• 单独写2条聚合查询，分别计算你需要汇总的指标列的SUM和MEAN，第一列对应填"SUM"、"Mean"标识，其他非汇总列可以补空值或者按需求填
• 用UNION ALL把明细行和2条汇总行拼接起来即可，注意要保证拼接的前后查询字段数量、字段类型完全匹配

修改后的PROC SQL代码 #

proc sql;
   create table diff as
       /* 第一部分：原有明细数据 */
       select 
            today.policy_vintage
            , today.number_policy as POLICY_TODAY
            , prior.number_policy as POLICY_PRIOR
            , today.number_policy - prior.number_policy as DIFFERENCE
            , avg(prior.number_policy) as POLICY_MEAN_PRIOR
            , today.number_policy - mean(prior.number_policy) as DIFFRENCE_MEAN
       from policy_vintage_weekly today
       LEFT JOIN
            (select *
             from _work.POLICY_VINTAGE_WEEKLY
             where run_date < today()
             having run_date = max(run_date)
            ) prior
       ON today.policy_vintage = prior.policy_vintage
       
       UNION ALL
       
       /* 第二部分：SUM汇总行 */
       select
            "SUM" as policy_vintage
            , sum(today.number_policy) as POLICY_TODAY
            , sum(prior.number_policy) as POLICY_PRIOR
            , sum(today.number_policy - prior.number_policy) as DIFFERENCE
            , . as POLICY_MEAN_PRIOR /* 数值型不需要汇总的列补空值用. 字符型补"" */
            , . as DIFFRENCE_MEAN
       from policy_vintage_weekly today
       LEFT JOIN
            (select *
             from _work.POLICY_VINTAGE_WEEKLY
             where run_date < today()
             having run_date = max(run_date)
            ) prior
       ON today.policy_vintage = prior.policy_vintage
       
       UNION ALL
       
       /* 第三部分：MEAN汇总行 */
       select
            "Mean" as policy_vintage
            , avg(today.number_policy) as POLICY_TODAY
            , avg(prior.number_policy) as POLICY_PRIOR
            , avg(today.number_policy - prior.number_policy) as DIFFERENCE
            , . as POLICY_MEAN_PRIOR
            , . as DIFFRENCE_MEAN
       from policy_vintage_weekly today
       LEFT JOIN
            (select *
             from _work.POLICY_VINTAGE_WEEKLY
             where run_date < today()
             having run_date = max(run_date)
            ) prior
       ON today.policy_vintage = prior.policy_vintage
  ;
quit;

可根据你实际要统计的维度调整汇总部分的聚合函数，不需要展示的汇总字段直接补空值即可。

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