使用Pivot时如何结合Group by计算模拟数据的均值？

现有一张存储模拟数据的表，每个日期对应1000条模拟值，需计算这些值的均值，但不清楚如何在Pivot中结合Group by实现。

现有SQL语句 #

select *
    from (select date_data,id, name, scenery, 
                 (extract(month from date)-extract(month from data_date))+12*(extract(year from date)-extract(year from data_date)) as T ,simulation,value   
            from escen
            where date_data = '30/09/2022'
              and scenario in ('BASE')
          )    
    pivot ( avg(value) 
            for T between 0 and 120
    order by 1, 2, 3, 4;

数据样例 #

date_data|ID|Name|Scenery|N Simu|Simul|   Date   | Value
30/09/22 |1|  A  |  Base | 1000 |  1  | 30/09/28 | 0,0397
30/09/22 |1|  A  |  Base | 1000 |  2  | 30/09/28 | -0,069

当前结果 #

date_data|ID|Name|Scenery|Simul|   30/09/28   |  31/10/28
30/09/22 |1|  A  |  Base |  1  |    0,0397    |  0,0521
30/09/22 |1|  A  |  Base |  2  |    -0,069    |  -0,0222

期望结果 #

date_data|ID|Name|Scenery|     30/09/28     |  31/10/28
30/09/22 |1|  A  |  Base |    avg(value)    |  avg(value)

核心问题是子查询中保留了simulation（模拟序号）字段，导致Pivot时会按该字段分组，最终保留每条模拟记录。要实现按date_data、id、name、scenery分组计算均值，需做以下调整：

1. 移除子查询中的simulation字段：该字段是模拟的唯一标识，保留它会让聚合逻辑失效，我们需要聚合掉这个维度。
2. 确保Pivot语法适配数据库：不同数据库对Pivot的范围语法支持有差异，需根据实际使用的数据库调整。

修改后的SQL语句 #

select *
from (
    select date_data, id, name, scenery, 
           (extract(month from date) - extract(month from data_date)) + 12*(extract(year from date) - extract(year from data_date)) as T,
           value   
    from escen
    where date_data = '30/09/2022'
      and scenario in ('BASE')
)    
pivot ( 
    avg(value) 
    for T in (0, 1, 2, ..., 120) -- 若数据库支持范围语法，可替换为`for T between 0 and 120`
)
order by date_data, id, name, scenery;

关键说明 #

• 子查询仅保留需要分组的维度字段（date_data、id、name、scenery）、透视字段T和聚合字段value，Pivot时会自动按这些维度分组计算均值。
• 关于for T的语法：Oracle支持between 0 and 120的范围写法，但SQL Server等数据库需要明确列出所有T的取值，需根据实际环境调整。
• 最终结果会按date_data+id+name+scenery的组合返回唯一行，每个T对应的列是该组合下所有模拟值的均值。

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