如何从设备在线时长占比SQL查询中去除重复数据？

你的核心问题是源数据里的重复记录被多次计入计数，导致计算出的在线占比虚高（甚至超过100%）。原查询里的DISTINCT是对最终结果去重，但在GROUP BY阶段已经把重复记录算进COUNT(*)了，根本没解决重复计数的问题；而MAX(dtReading)在你的分组逻辑里也起不到去重作用，因为你是按日期分组，不是针对每条重复的记录去重。

下面给你两个最优的去重解决方案，你可以根据实际数据情况选择：

方案一：精准去重（针对完全重复的记录） #

如果你的重复记录是controllerID、dtReading和EventType完全一致的（就像你给出的源数据例子），可以先对ReaderData表做去重子查询，过滤掉重复的记录后再进行关联统计：

SELECT 
    workStationNo, 
    controllerID, 
    CAST(DATEPART(DAY, DATEADD(DAY, DATEDIFF(DAY, 0, rd.dtReading), 0)) AS varchar) AS date, 
    (CAST(COUNT(*) AS float) / CAST((DATEPART(HOUR, GETDATE()) * 30) AS float) * 100) AS dayPercentage 
FROM 
    Controller 
INNER JOIN 
    ManufacturingLayout ON Controller.machID = ManufacturingLayout.workStationID 
INNER JOIN 
    (
        -- 先过滤ReaderData中的重复记录，保留唯一的controllerID + dtReading + EventType组合
        SELECT DISTINCT controllerID, dtReading, EventType
        FROM ReaderData
        WHERE EventType = '(0x05)Open switch'
    ) rd ON Controller.ctrlID = rd.controllerID 
WHERE 
    rd.dtReading >= CONVERT(datetime, '09/05/2018', 103) 
    AND rd.dtReading <= DATEADD(HOUR, 24, CONVERT(datetime, '16/05/2018 23:59:59', 103)) 
GROUP BY 
    controllerID, 
    DATEADD(DAY, DATEDIFF(DAY, 0, rd.dtReading), 0), 
    workStationNo 
ORDER BY 
    workStationNo, controllerID

方案二：按时间粒度去重（处理毫秒/秒级重复） #

如果你的重复记录只是时间戳有细微差异（比如同一分钟内的多条重复上报），可以把时间截断到分钟级，确保每个设备每分钟只统计一次：

SELECT 
    workStationNo, 
    controllerID, 
    CAST(DATEPART(DAY, DATEADD(DAY, DATEDIFF(DAY, 0, rd.dtTruncated), 0)) AS varchar) AS date, 
    (CAST(COUNT(*) AS float) / CAST((DATEPART(HOUR, GETDATE()) * 30) AS float) * 100) AS dayPercentage 
FROM 
    Controller 
INNER JOIN 
    ManufacturingLayout ON Controller.machID = ManufacturingLayout.workStationID 
INNER JOIN 
    (
        -- 按controllerID + 分钟级时间分组去重，避免同一分钟内的重复上报
        SELECT 
            controllerID, 
            DATEADD(MINUTE, DATEDIFF(MINUTE, 0, dtReading), 0) AS dtTruncated,
            EventType
        FROM ReaderData
        WHERE EventType = '(0x05)Open switch'
        GROUP BY controllerID, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, dtReading), 0), EventType
    ) rd ON Controller.ctrlID = rd.controllerID 
WHERE 
    rd.dtTruncated >= CONVERT(datetime, '09/05/2018', 103) 
    AND rd.dtTruncated <= DATEADD(HOUR, 24, CONVERT(datetime, '16/05/2018 23:59:59', 103)) 
GROUP BY 
    controllerID, 
    DATEADD(DAY, DATEDIFF(DAY, 0, rd.dtTruncated), 0), 
    workStationNo 
ORDER BY 
    workStationNo, controllerID

为什么原查询无效？ #

• DISTINCT是作用在最终的聚合结果上，而你的重复数据是在ReaderData里，GROUP BY的时候已经把所有重复记录都计入COUNT(*)了，所以这个DISTINCT根本没解决重复计数的问题。
• MAX(dtReading)在你的原查询里是作为分组字段的一部分（通过DATEADD(DAY, DATEDIFF(DAY, 0, dtReading), 0)），它只是把日期统一到当天的起始时间，并没有针对每条重复的记录去重。

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