按接收日期分组并计算处理时间中位数的SQL实现咨询

需要从数据库中选取接收日期（received_date）和输出日期（exported_on），计算处理时间（processing_time = 输出日期与接收日期的天数差），并按接收日期分组，输出每组的处理时间中位数。

示例数据 #

选取的原始数据：

input date | output date | processing time
2022-01-03 | 2022-01-03  | 0
2022-01-03 | 2022-01-06  | 3
2022-01-03 | 2022-01-11  | 8
2022-01-05 | 2022-01-10  | 5
2022-01-05 | 2022-01-15  | 10

期望输出 #

input date | processing time
2022-01-03 | 3
2022-01-05 | 7.5

现有SQL代码 #

SELECT [received_date]
,CONVERT(date, [exported_on])
,DATEDIFF(day, [received_date], [exported_on]) AS processing_time
  FROM [request] WHERE YEAR (received_date) = 2022
  GROUP BY received_date, [exported_on]
  ORDER BY received_date

疑问：如何实现按接收日期分组求中位数？是否需要临时表，还是可以直接修改现有查询？

不需要临时表，直接修改现有查询即可，以下是两种常用实现方式：

方法1：使用内置中位数函数（推荐，适用于SQL Server 2012+、PostgreSQL等） #

利用PERCENTILE_CONT(0.5)函数直接计算连续型中位数，偶数个数据时会返回中间两数的平均值，完全匹配你的示例需求：

SELECT DISTINCT
    received_date AS [input date],
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY processing_time) OVER (PARTITION BY received_date) AS [processing time]
FROM (
    SELECT 
        received_date,
        DATEDIFF(day, received_date, exported_on) AS processing_time
    FROM [request] 
    WHERE YEAR(received_date) = 2022
) AS sub
ORDER BY received_date

如果需要离散中位数（取实际存在的数值），可以替换为PERCENTILE_DISC(0.5)，它会返回分组中处于中间位置的原始值。

方法2：手动用窗口函数计算（兼容更多数据库） #

如果你的数据库不支持内置中位数函数，可通过ROW_NUMBER()和COUNT()手动计算：

WITH processed_data AS (
    SELECT 
        received_date,
        DATEDIFF(day, received_date, exported_on) AS processing_time,
        ROW_NUMBER() OVER (PARTITION BY received_date ORDER BY processing_time) AS row_num,
        COUNT(*) OVER (PARTITION BY received_date) AS total_rows
    FROM [request] 
    WHERE YEAR(received_date) = 2022
)
SELECT 
    received_date AS [input date],
    AVG(CAST(processing_time AS DECIMAL(10,2))) AS [processing time]
FROM processed_data
WHERE row_num IN (FLOOR((total_rows + 1)/2), CEILING((total_rows + 1)/2))
GROUP BY received_date
ORDER BY received_date

逻辑说明 #

1. 先计算每条数据的处理时间，并给每组内的处理时间按顺序编号，同时统计每组的总数据量。
2. 筛选出每组中处于中间位置的行（奇数条取中间1条，偶数条取中间2条）。
3. 对筛选出的行取平均值，得到中位数。

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