如何合并SQL查询自动将交易耗时中位数转时分秒格式

需求：计算指定时间段（2023-01-02 00:00 至 2023-01-08 23:59）内交易完成耗时的中位数（秒），并自动转换为「天-时-分-秒」格式，无需手动输入中位数数值。

原始查询 #

1. 计算中位数（秒） #

SELECT  TxnStartDT, TxnCompleteDT, TxnDuration,
PERCENTILE_CONT(.5) WITHIN GROUP (ORDER BY TxnDuration) OVER() AS MEDIAN 
FROM(
SELECT DISTINCT TxnStartDT, TxnCompleteDT, 
DATEDIFF(SECOND, TxnStartDT, TxnCompleteDT) AS TxnDuration
FROM MyTable
WHERE (TxnStartDT >= '2023-01-02 00:00' and TxnStartDT <= '2023-01-08 23:59') and  TxnCompleteDT is not null) AS D

该查询输出中位数为7333秒。

2. 手动转换格式 #

DECLARE @MD DATETIME = DATEADD(SECOND, 7333, 0)
SELECT 
CAST(DATEPART(DAY, @MD) - 1 AS VARCHAR(2)) + ' day(s) ' 
+ CAST(DATEPART(HOUR, @MD) AS VARCHAR(2)) + ' hour(s) '
+ CAST(DATEPART(MINUTE, @MD) AS VARCHAR(2)) + ' minute(s) ' 
+ CAST(DATEPART(SECOND, @MD) AS VARCHAR(2)) + ' second(s)' as 'Median Time' 

输出结果：0 day(s) 2 hour(s) 2 minute(s) 13 second(s)

错误尝试及报错 #

尝试合并查询时出现语法错误，错误代码：

SELECT  TxnStartDT, TxnCompleteDT, TxnDuration,
PERCENTILE_CONT(.5) WITHIN GROUP (ORDER BY TxnDuration) OVER() AS MEDIAN 
FROM(
SELECT DISTINCT TxnStartDT, TxnCompleteDT, 
DATEDIFF(SECOND,  TxnStartDT, TxnCompleteDT) AS TxnDuration
FROM MyTable
WHERE (TxnStartDT >= '2023-01-02 00:00' and TxnStartDT <= '2023-01-08 23:59') and  TxnCompleteDT is not null) AS D

DECLARE @MD DATETIME = CAST(DATEADD(SECOND, MEDIAN,  0))
SELECT --CAST(DATEPART(MONTH, @VARDT) - 1 AS VARCHAR(2)) + ' month(s) '
+ CAST(DATEPART(DAY, @MD) - 1 AS VARCHAR(2)) + ' day(s) ' 
+ CAST(DATEPART(HOUR, @MD) AS VARCHAR(2)) + ' hour(s) '
+ CAST(DATEPART(MINUTE, @MD) AS VARCHAR(2)) + ' minute(s) ' 
+ CAST(DATEPART(SECOND, @MD) AS VARCHAR(2)) + ' second(s)' as 'Median Time' 

错误信息：

Msg 1035, Level 15, State 10, Line 32
Incorrect syntax near 'CAST', expected 'AS'.
Msg 137, Level 15, State 2, Line 34
Must declare the scalar variable "@MD".

错误原因 #

1. 批处理顺序错误：SQL Server中变量声明DECLARE必须放在批处理开头，不能在SELECT语句之后。
2. 列名引用错误：MEDIAN是前一个查询的列名，无法直接在变量赋值语句中引用，必须先提取该值。
3. CAST语法错误：CAST(DATEADD(SECOND, MEDIAN, 0))缺少目标类型声明，且DATEADD本身返回datetime类型，无需额外CAST。

正确解决方案 #

方案1：使用变量存储中位数 #

先计算中位数并赋值给变量，再执行格式转换：

-- 1. 声明变量并计算中位数
DECLARE @MedianSeconds INT;
SELECT @MedianSeconds = PERCENTILE_CONT(.5) WITHIN GROUP (ORDER BY TxnDuration) OVER()
FROM(
    SELECT DISTINCT 
        DATEDIFF(SECOND, TxnStartDT, TxnCompleteDT) AS TxnDuration
    FROM MyTable
    WHERE TxnStartDT >= '2023-01-02 00:00' 
      AND TxnStartDT <= '2023-01-08 23:59' 
      AND TxnCompleteDT IS NOT NULL
) AS D;
-- 去重，确保变量只存唯一的中位数
SET @MedianSeconds = (SELECT DISTINCT @MedianSeconds);

-- 2. 转换为天-时-分-秒格式
DECLARE @MD DATETIME = DATEADD(SECOND, @MedianSeconds, 0);
SELECT 
    CAST(DATEPART(DAY, @MD) - 1 AS VARCHAR(2)) + ' day(s) ' 
    + CAST(DATEPART(HOUR, @MD) AS VARCHAR(2)) + ' hour(s) '
    + CAST(DATEPART(MINUTE, @MD) AS VARCHAR(2)) + ' minute(s) ' 
    + CAST(DATEPART(SECOND, @MD) AS VARCHAR(2)) + ' second(s)' AS 'Median Time';

方案2：使用CTE一次性完成（无变量） #

通过公用表表达式(CTE)直接计算并转换，无需单独变量：

WITH TxnDurations AS (
    -- 计算所有符合条件的交易耗时
    SELECT DISTINCT 
        DATEDIFF(SECOND, TxnStartDT, TxnCompleteDT) AS TxnDuration
    FROM MyTable
    WHERE TxnStartDT >= '2023-01-02 00:00' 
      AND TxnStartDT <= '2023-01-08 23:59' 
      AND TxnCompleteDT IS NOT NULL
),
MedianResult AS (
    -- 计算中位数
    SELECT PERCENTILE_CONT(.5) WITHIN GROUP (ORDER BY TxnDuration) OVER() AS MedianSeconds
    FROM TxnDurations
)
-- 转换格式并返回结果（GROUP BY去重，只输出一行）
SELECT 
    CAST(DATEPART(DAY, DATEADD(SECOND, MedianSeconds, 0)) - 1 AS VARCHAR(2)) + ' day(s) ' 
    + CAST(DATEPART(HOUR, DATEADD(SECOND, MedianSeconds, 0)) AS VARCHAR(2)) + ' hour(s) '
    + CAST(DATEPART(MINUTE, DATEADD(SECOND, MedianSeconds, 0)) AS VARCHAR(2)) + ' minute(s) ' 
    + CAST(DATEPART(SECOND, DATEADD(SECOND, MedianSeconds, 0)) AS VARCHAR(2)) + ' second(s)' AS 'Median Time'
FROM MedianResult
GROUP BY MedianSeconds;

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