如何在QuickSight中按季度维度计算时段内的移动中位数？

由于QuickSight原生没有periodToDateMedianOverTime()这类直接计算时段内移动中位数的函数，我们可以通过以下两种变通方案实现需求：

方案一：自定义计算字段+窗口函数 #

步骤1：构建日期维度字段 #

先对原始Date字段做维度转换，生成两个关键字段：

• 季度年份字段：concat(extract('year', Date), '-Q', extract('quarter', Date))，命名为YearQuarter
• 年月字段：formatDate(Date, 'MM/yyyy')，命名为MonthYear

步骤2：编写移动中位数计算字段 #

使用窗口函数median()，限定季度分组范围，按年月排序实现季度内截至当前月的移动中位数计算，公式如下：

median(Value) over (
    partition by YearQuarter
    order by MonthYear
    rows between unbounded preceding and current row
)

将该字段命名为QuarterPTDMedian

步骤3：可视化配置 #

在分析面板中，将MonthYear拖入横轴，QuarterPTDMedian拖入纵轴，即可得到你期望的聚合结果。

方案二：数据源预聚合（适配大数据集） #

如果数据集规模较大，窗口函数性能受限，可以在数据源层面（如SQL数据库）提前完成聚合计算：

1. 按YearQuarter和MonthYear分组，提取每个分组下所有历史Value数据
2. 计算每组数据的中位数，将预聚合后的结果导入QuickSight直接使用

结果验证 #

用你提供的测试数据验证方案一：

• 01/2023：仅包含Value=1，中位数为1
• 02/2023：包含Value=1、2、3、4，中位数为(2+3)/2=2.5
• 03/2023：包含Value=1、2、3、4、5，中位数为3
• 04/2023：属于Q2，仅包含Value=6、7，中位数为(6+7)/2=6.5
• 05/2023：包含Value=6、7、8，中位数为7
  完全匹配你期望的结果。

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