Group By Date With Empty Groups

Question

我有一个loginAudit表，我正在尝试获取每天所有登录的计数。我想做的是，有一天，没有登录返回他们的一天，登录计数为0。当前，在没有登录的天数内不返回任何行。

可能这是不可能的，并且必须在查询结果返回后在应用程序中填充空组？

  SELECT DATEADD(day, DATEDIFF(day,0,LoginAudit.LoginDateTime), 0) as LoginDate,  
         COUNT(DISTINCT LoginAudit.LoginAuditID) AS Logins  
    FROM LoginAudit 
GROUP BY DATEADD(day, DATEDIFF(day,0,LoginAudit.LoginDateTime), 0)
ORDER BY 1  

Answer 1

本质上，您所要求的是将您的表连接到一个包含日期的“表”。日期表将没有间隔，您将根据日期值进行分组。那么如何创建一个日期表呢？

在SQL for Smarties中，建议您保留一个整数表，以备需要无缝连接的序列时使用。然后，您可以通过将表连接到它来选择所需的任何序列。

因此，如果您有一个整型表，其值从现在()返回到所需的天数，您可能会执行以下操作：

SELECT DATE_SUB(CURDATE(), INTERVAL i.intvalue DAY) AS thedate, 
       COUNT(DISTINCT LoginAudit.LoginAuditID) AS logins
FROM i LEFT JOIN dual ON (DATE_SUB(NOW(), INTERVAL i.intvalue DAY)= day)
GROUP BY DATE_SUB(CURDATE(), INTERVAL i.intvalue DAY)
ORDER BY i DESC;

ETA，对于mysql：

//创建整型表

create table i(i integer not null primary key);
insert into i values (0),(1),(2) ... (9);

如果我需要0-99个连续的数字：

SELECT 10*t.i + u.i AS number
  FROM i AS u
CROSS JOIN 
  i AS t
ORDER BY number;

如果我需要连续的日期：

SELECT date_sub(curdate(), interval (10*t.i + u.i) DAY) as thedate 
  FROM i AS u
CROSS JOIN 
  i AS t
ORDER BY thedate;

Answer 2

我认为我已经相当彻底地寻找了解决方案，但当然在发布我的问题后，我找到了这个链接：

SQL group by day, show orders for each day

多哈！

Answer 3

您使用的是什么DBMS？

如果是Oracle，您可以尝试在子查询中选择日期，如下所示：

SELECT TRUNC(SYSDATE) + 1 - LEVEL AS today,
       TRUNC(SYSDATE) + 2 - LEVEL AS tomorrow
FROM DUAL
CONNECT BY LEVEL <= 30 /* Last 30 days */

然后你可能会这样做：

SELECT today as LoginDate,  
       COUNT(DISTINCT LoginAudit.LoginAuditID) AS Logins  
FROM (
       SELECT TRUNC(SYSDATE) + 1 - LEVEL AS today,
              TRUNC(SYSDATE) + 2 - LEVEL AS tomorrow
       FROM DUAL
       CONNECT BY LEVEL <= 30 /* Last 30 days */
     ),
     LoginAudit 
WHERE LoginAudit.LoginDateTime BETWEEN today AND tomorrow
GROUP BY today
ORDER BY 1