SQL Server 2012中如何计算每日Drawdown的平均值与中位数？

我来帮你搞定这个SQL计算的问题！针对你已经算出每日maxprice和drawdown的TEMP表，要按日期分组计算Drawdown的平均值和中位数，其实核心难点在中位数的实现——毕竟不同数据库的函数支持有差异，下面分几种常见场景给出具体代码：

基础思路梳理 #

• 平均值：非常直接，用AVG()聚合函数配合GROUP BY Date就能实现。
• 中位数：不同数据库的实现方式不同，有的有内置函数，有的需要用窗口函数手动计算，下面分库说明：

1. PostgreSQL（9.4+版本） #

PostgreSQL直接提供了专门的中位数计算函数，有两种可选：

• PERCENTILE_CONT(0.5)：返回连续型中位数（如果数据是偶数个，会取中间两个值的平均值）
• PERCENTILE_DISC(0.5)：返回离散型中位数（直接取中间位置的实际值）

SELECT
    Date,
    AVG(drawdown) AS avg_drawdown,
    -- 连续型中位数
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY drawdown) AS median_drawdown_cont,
    -- 离散型中位数
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY drawdown) AS median_drawdown_disc
FROM TEMP
GROUP BY Date
ORDER BY Date;

2. SQL Server（2012+版本） #

SQL Server同样支持上述两个中位数函数，也可以用窗口函数手动实现：

-- 方式一：用内置函数
SELECT
    Date,
    AVG(drawdown) AS avg_drawdown,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY drawdown) OVER (PARTITION BY Date) AS median_drawdown_cont,
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY drawdown) OVER (PARTITION BY Date) AS median_drawdown_disc
FROM TEMP
GROUP BY Date
ORDER BY Date;

-- 方式二：窗口函数手动计算
WITH ranked_drawdown AS (
    SELECT
        Date,
        drawdown,
        ROW_NUMBER() OVER (PARTITION BY Date ORDER BY drawdown) AS rn,
        COUNT(*) OVER (PARTITION BY Date) AS total_count
    FROM TEMP
)
SELECT
    Date,
    AVG(drawdown) AS avg_drawdown,
    AVG(CASE WHEN rn IN ((total_count+1)/2, (total_count+2)/2) THEN drawdown END) AS median_drawdown
FROM ranked_drawdown
GROUP BY Date
ORDER BY Date;

3. MySQL（8.0+版本） #

MySQL 8.0.22+支持PERCENTILE_CONT()，低版本可以用变量或窗口函数手动计算：

-- 高版本直接用内置函数
SELECT
    Date,
    AVG(drawdown) AS avg_drawdown,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY drawdown) OVER (PARTITION BY Date) AS median_drawdown
FROM TEMP
GROUP BY Date
ORDER BY Date;

-- 低版本兼容方案
WITH ranked_drawdown AS (
    SELECT
        Date,
        drawdown,
        ROW_NUMBER() OVER (PARTITION BY Date ORDER BY drawdown) AS rn,
        COUNT(*) OVER (PARTITION BY Date) AS total_count
    FROM TEMP
)
SELECT
    Date,
    AVG(drawdown) AS avg_drawdown,
    CASE
        WHEN total_count % 2 = 1 THEN MAX(CASE WHEN rn = (total_count+1)/2 THEN drawdown END)
        ELSE AVG(CASE WHEN rn IN (total_count/2, total_count/2+1) THEN drawdown END)
    END AS median_drawdown
FROM ranked_drawdown
GROUP BY Date
ORDER BY Date;

4. 通用兼容方案（适配多数支持窗口函数的数据库） #

如果你的数据库版本较低，不支持内置中位数函数，这个通用写法可以覆盖大部分场景：

WITH drawdown_rank AS (
    SELECT
        Date,
        drawdown,
        ROW_NUMBER() OVER (PARTITION BY Date ORDER BY drawdown) AS row_num,
        COUNT(*) OVER (PARTITION BY Date) AS total_rows
    FROM TEMP
)
SELECT
    Date,
    AVG(drawdown) AS avg_drawdown,
    CASE
        -- 奇数行取中间值
        WHEN total_rows % 2 = 1 THEN MAX(CASE WHEN row_num = (total_rows + 1)/2 THEN drawdown END)
        -- 偶数行取中间两个值的平均
        ELSE AVG(CASE WHEN row_num IN (total_rows/2, total_rows/2 + 1) THEN drawdown END)
    END AS median_drawdown
FROM drawdown_rank
GROUP BY Date, total_rows
ORDER BY Date;

小提示 #

• 如果drawdown字段存在NULL值，AVG()会自动忽略它们；如果需要把NULL视为0计算，记得用AVG(COALESCE(drawdown, 0))替代。
• 确保drawdown是数值类型（比如DECIMAL、FLOAT），否则无法进行聚合计算。

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