SQL Server按天统计用户IP计数时如何保留用户名且仅返回每日最大值

你可以通过窗口函数对每日的用户IP计数结果做排名，筛选出每日排名第一的记录即可，完整实现代码如下：

WITH DailyUserIPCount AS (
    SELECT 
        FORMAT(O.[UTCTimestamp], 'yyyy-MM-dd') AS [DATE],
        T.Username,
        COUNT(O.clientIP) AS CountClientIP
    FROM dbo.tablename O
    -- 原逻辑左连T表后筛选用户名非空，等价于内连，执行效率更高
    INNER JOIN [DBNAME2]..vwAD_tablename T ON T.UserID = O.userID
    -- 若无需使用Event表的字段做过滤/关联，可删除下一行LEFT JOIN减少开销
    LEFT JOIN [DBNAME1]..Event E ON E.Code = O.Code  
    WHERE 
        -- 替换原FORMAT函数筛选逻辑，可命中UTCTimestamp的索引大幅提升查询效率
        O.UTCTimestamp >= '2018-01-01' AND O.UTCTimestamp < '2018-02-01'
        AND T.Username IS NOT NULL
    GROUP BY T.Username, FORMAT(O.[UTCTimestamp], 'yyyy-MM-dd')
),
DailyIPRank AS (
    SELECT 
        *,
        -- 按日期分组，对IP计数倒序排名
        ROW_NUMBER() OVER (PARTITION BY [DATE] ORDER BY CountClientIP DESC) AS rank_num
        -- 若需要保留同天IP计数并列最高的所有用户，将上一行替换为：
        -- RANK() OVER (PARTITION BY [DATE] ORDER BY CountClientIP DESC) AS rank_num
    FROM DailyUserIPCount
)
SELECT [DATE], Username, CountClientIP
FROM DailyIPRank
WHERE rank_num = 1
ORDER BY [DATE]

说明要点 #

• 移除了原查询冗余的DISTINCT关键字，GROUP BY分组后的结果本身不存在重复行
• 优化了日期筛选条件，避免对字段使用函数运算，可触发UTCTimestamp字段的索引优化查询性能
• 可根据业务需要选择ROW_NUMBER()或RANK()窗口函数：前者单日仅返回一条最高记录，后者可返回单日所有并列最高的用户记录

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