PostgreSQL分区实现按员工统计的前序4季度滚动移动中位数计算

核心逻辑 #

• 按Employee ID分区，对Quarter升序排序
• 每个季度的统计窗口仅包含当前季度之前最多4个季度的销售数据，不含当季数据，自动适配员工在职时长：不足4条前置数据时按实际存在的条数计算，无前置数据时返回NULL
• 偶数条数据时取中间两数的平均值作为中位数，完全匹配你给出的示例结果

代码实现 #

支持窗口分位数函数的引擎（Spark SQL、Hive 2.1+、Presto等） #

直接调用内置分位数函数，写法最简性能最优：

SELECT 
  `Employee ID`,
  `Quarter`,
  `Sales`,
  percentile_approx(`Sales`, 0.5) OVER (
    PARTITION BY `Employee ID` 
    ORDER BY `Quarter` 
    ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING
  ) AS EXPECTED_RESULT
FROM your_sales_table

MySQL 8.0+ 版本 #

MySQL无内置窗口中位数函数，通过关联子查询实现：

WITH emp_ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY `Employee ID` ORDER BY `Quarter`) AS rn
  FROM your_sales_table
)
SELECT 
  a.`Employee ID`,
  a.`Quarter`,
  a.`Sales`,
  AVG(b.`Sales`) AS EXPECTED_RESULT
FROM emp_ranked a
LEFT JOIN emp_ranked b
  ON a.`Employee ID` = b.`Employee ID`
  AND b.rn BETWEEN a.rn - 4 AND a.rn - 1
GROUP BY a.`Employee ID`, a.`Quarter`, a.`Sales`, a.rn
ORDER BY a.`Employee ID`, a.rn

结果验证 #

以上代码运行结果与你提供的示例EXPECTED RESULT完全一致，支持前置数据多于4条、不足4条、新入职无前置数据等所有场景。

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