如何在 SQL Server 中获取值为 0 的缺失日期? [英] How to get missing dates with 0 value in SQL Server?

问题描述

我在 SQL Server 中使用以下查询来查找过去 7 天(不包括今天的日期)distinct 的登录次数:

I am using below query in SQL Server to find distinct number of logins made in last 7 days(excluding today's date):

SELECT TOP (7) CONVERT(date, LoginTime) AS ActivityDate, COUNT(DISTINCT LoginID) AS UserCount
FROM Login
WHERE CONVERT(date, LoginTime) < CONVERT(date, GETDATE())
GROUP BY CONVERT(date, LoginTime)  
ORDER BY ActivityDate DESC; 

它生成以下输出:

ActivityDate | UserCount
----------------------
2019-02-21   | 2
2019-02-20   | 3
2019-02-19   | 2
2019-02-15   | 2
2019-02-14   | 1
2019-02-13   | 2
2019-02-12   | 3

我的期望是将过去 7 天按顺序排列(不像当前输出，日期 2019-02-16、2019-02-17 和 <2019-02-19 之后缺少 code>2019-02-18).我需要那个，如果缺少日期，它必须显示为 0 计数.

My expectation is to have all last 7 days in a sequence (not like as current output where date 2019-02-16, 2019-02-17 and 2019-02-18 are missing after 2019-02-19). I need that, if a date is missing it must be displayed with 0 count.

我的预期输出如下:

ActivityDate | UserCount
----------------------
2019-02-21   | 2
2019-02-20   | 3
2019-02-19   | 2
2019-02-18   | 0
2019-02-17   | 0
2019-02-16   | 0
2019-02-15   | 2

推荐答案

要查看特定值，该值必须来自一行.因此，要查看登录表中不存在的日期，您必须将它们生成为行某处.

To see a particular value, the value must come from a row. So to see dates that don't exist on your login table, you must generated them as rows somewhere.

您可以使用简单的递归 CTE 在特定时间间隔之间每天生成 1 行，然后使用 LEFT JOIN 加入与该特定日期匹配的登录.不匹配的仍然会显示，因为我们使用的是 LEFT JOIN.

You can use a simple recursive CTE to generate 1 row per day between a particular interval, then use a LEFT JOIN to join logins that match on that particular day. The ones that don't match will still be displayed, since we are using LEFT JOIN.

DECLARE @GeneratingDateFrom DATE = DATEADD(DAY, -7, GETDATE())
DECLARE @GeneratingDateTo DATE = GETDATE()

;WITH GeneratedDates AS
(
    SELECT
        GeneratedDate = @GeneratingDateFrom

    UNION ALL

    SELECT
        GeneratedDate = DATEADD(DAY, 1, G.GeneratedDate)
    FROM
        GeneratedDates AS G
    WHERE
        DATEADD(DAY, 1, G.GeneratedDate) < @GeneratingDateTo
)
SELECT
    G.GeneratedDate,
    count(distinct L.LoginID) as UserCount
FROM 
    GeneratedDates AS G
    LEFT JOIN [Login] AS L ON G.GeneratedDate = CONVERT(date, L.LoginTime)
GROUP BY
    G.GeneratedDate
ORDER BY 
    G.GeneratedDate desc

这篇关于如何在 SQL Server 中获取值为 0 的缺失日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！