Access查询需求：计算同ID不同状态记录的时间差及中位数

嘿，我来帮你一步步搞定这个Access查询的需求，从计算同一ID的状态变更时间差，到最终统计时间差的中位数，全给你安排明白：

先明确原始表信息 #

假设你的表名为OrderStatus，结构和数据整理成表格更直观：

步骤1：计算每个ID的基础时间差数据 #

先创建一个基础查询qry_BaseTimeDiff，用来提取每个ID的两个状态时间，并计算总秒数、总分钟数：

SELECT
    ID,
    Min(modifiedDate) AS ShippedTime,
    Max(modifiedDate) AS ClosedTime,
    DateDiff("s", ShippedTime, ClosedTime) AS diffSeconds,
    DateDiff("n", ShippedTime, ClosedTime) AS diffMinutes
FROM OrderStatus
GROUP BY ID;

注：因为每个ID的Shipped状态时间一定早于Closed，所以用Min和Max就能精准获取两个时间点

步骤2：格式化时间差为预期的字符串格式 #

基于上面的基础查询，创建qry_FormattedDiff，把总秒数转换成你要的带天/时分秒的友好格式：

SELECT
    ID,
    -- 区分是否超过1天，分别格式化
    IIf(diffSeconds >= 86400,
        CStr(DateDiff("d", ShippedTime, ClosedTime)) & " day " & Format(DateAdd("s", diffSeconds Mod 86400, #00:00:00#), "hh\h:n\m:s\s"),
        Format(DateAdd("s", diffSeconds, #00:00:00#), "hh\h:n\m:s\s")
    ) AS diffHours,
    diffMinutes,
    diffSeconds
FROM qry_BaseTimeDiff;

运行这个查询就能得到你想要的预期结果：

步骤3：计算时间差的中位数 #

Access没有内置的中位数函数，我们可以用排序后取中间值的方式实现。这里用总秒数计算（最精准，也可以换成分钟数，逻辑一致），创建qry_Median：

SELECT
    Avg(diffSeconds) AS MedianSeconds,
    Avg(diffMinutes) AS MedianMinutes
FROM (
    SELECT
        diffSeconds,
        diffMinutes,
        -- 统计总记录数
        (SELECT Count(*) FROM qry_FormattedDiff) AS TotalCount,
        -- 按秒数排序并给每条记录编号
        (SELECT Count(*) FROM qry_FormattedDiff AS t2 WHERE t2.diffSeconds <= t1.diffSeconds) AS RowNum
    FROM qry_FormattedDiff AS t1
    ORDER BY diffSeconds
) AS RankedDiffs
WHERE
    -- 自动适配奇数/偶数记录：奇数取中间1条，偶数取中间2条的平均值
    RowNum = (TotalCount + 1) / 2
    OR RowNum = (TotalCount + 2) / 2;

你的示例数据有3条记录（奇数），运行后会得到中位数为39045秒（650分钟）。

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