Power Query/Excel/SQL实现：按日期将多行数据转列并计算周期平均分

现有客户数据集，每位客户有多条评估记录，评估日期不统一。需要转成包含以下字段的列格式：

• Customer（客户ID）
• Initial Score（首次评估分数）
• Avg_3_months（首次评估后3个月内的平均分数）
• Avg_6_months（首次评估后6个月内的平均分数）
• Avg_9_months（首次评估后9个月内的平均分数）
• Avg_12_months（首次评估后12个月内的平均分数）

示例数据源：

CustomerId Date       Score 
C1         1/1/2020   9
C1         1/7/2020   14
C1         1/14/2020  26

C2         1/9/2020   34
C2         3/9/2020   30
C2         6/9/2020   24

一、Excel 实操方法 #

1. 提取首次评估日期和初始分数 #

• 用MINIFS获取每个客户最早的评估日期：
  =MINIFS($B$2:$B$7,$A$2:$A$7,A2)
• 用XLOOKUP匹配该日期对应的分数，得到初始分数：
  =XLOOKUP(MINIFS($B$2:$B$7,$A$2:$A$7,A2),$B$2:$B$7,$C$2:$C$7,,0,1)

2. 计算各时间段平均分 #

以3个月周期为例，用AVERAGEIFS筛选首次日期后3个月内的分数求平均：

=AVERAGEIFS($C$2:$C$7,
            $A$2:$A$7,A2,
            $B$2:$B$7,">="&MINIFS($B$2:$B$7,$A$2:$A$7,A2),
            $B$2:$B$7,"<="&EDATE(MINIFS($B$2:$B$7,$A$2:$A$7,A2),3))

把EDATE参数里的3换成6、9、12，即可得到另外三个时间段的平均分。

3. 去重保留唯一客户记录 #

将计算结果全选复制，粘贴为数值后，用「数据」选项卡的「删除重复值」功能，保留每个客户的唯一记录即可。

二、Power Query 实操方法 #

1. 导入数据并调整类型 #

把数据导入Power Query，确认CustomerId为文本类型、Date为日期类型、Score为数值类型，类型不符可在「转换」选项卡修改。

2. 分组获取首次评估信息 #

• 点击「转换」→「分组依据」，按CustomerId分组，新增两个聚合列：
  • 列名Initial Date，聚合方式选「最小值」，字段选Date；
  • 列名Initial Score，聚合方式选「所有行」，编辑公式筛选首次日期对应的分数：

    List.First(Table.SelectRows([Grouped Rows], each [Date] = [Initial Date])[Score])
    

3. 添加各时间段平均分自定义列 #

对每个分组，添加自定义列计算平均分：

• Avg_3_months的公式：

  let
      initialDate = [Initial Date],
      endDate = Date.AddMonths(initialDate, 3),
      filteredRows = Table.SelectRows([Grouped Rows], each [Date] >= initialDate and [Date] <= endDate),
      avgScore = List.Average(filteredRows[Score])
  in avgScore
  

  将Date.AddMonths(initialDate, 3)中的3替换为6、9、12，分别创建另外三个平均分列。

4. 整理输出结果 #

删除Grouped Rows列，调整列顺序后，点击「关闭并上载」将结果导回Excel。

三、SQL 实操方法 #

假设数据存储在CustomerScores表中，字段为CustomerId（文本）、Date（日期）、Score（数值），执行以下SQL语句即可：

WITH CustomerInitial AS (
    SELECT 
        CustomerId,
        MIN(Date) AS InitialDate,
        MAX(CASE WHEN Date = MIN(Date) THEN Score END) AS InitialScore
    FROM CustomerScores
    GROUP BY CustomerId
)
SELECT 
    ci.CustomerId AS Customer,
    ci.InitialScore,
    ROUND(AVG(CASE WHEN cs.Date BETWEEN ci.InitialDate AND DATEADD(MONTH, 3, ci.InitialDate) THEN cs.Score END), 2) AS Avg_3_months,
    ROUND(AVG(CASE WHEN cs.Date BETWEEN ci.InitialDate AND DATEADD(MONTH, 6, ci.InitialDate) THEN cs.Score END), 2) AS Avg_6_months,
    ROUND(AVG(CASE WHEN cs.Date BETWEEN ci.InitialDate AND DATEADD(MONTH, 9, ci.InitialDate) THEN cs.Score END), 2) AS Avg_9_months,
    ROUND(AVG(CASE WHEN cs.Date BETWEEN ci.InitialDate AND DATEADD(MONTH, 12, ci.InitialDate) THEN cs.Score END), 2) AS Avg_12_months
FROM CustomerInitial ci
LEFT JOIN CustomerScores cs ON ci.CustomerId = cs.CustomerId
GROUP BY ci.CustomerId, ci.InitialScore;

说明：

• CustomerInitial CTE用于提取每个客户的首次评估日期和分数；
• 通过LEFT JOIN关联原表，用CASE WHEN筛选对应时间段的分数，再用AVG计算平均值；
• ROUND用于控制小数位数，不需要可直接删除。

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