如何汇总按列和日期分组的数据，说明缺少数据的日期

debugcn 发表于 Dev

蓝色的龙

我正在创建一个报告，希望获得按天和标记分组的事件数，并按特定标记（例如'％sample％'）和特定时间段（例如过去一周）进行过滤。

我正在使用SQL Server 2008。

但是，有几天没有发生带有特定标签的事件。我的问题是如何为几天不存在任何行的行生成行并将其值设置为零。类似于以下内容：

Tag                 Date            Count
=================== =============== ====================
Sample              2013-07-07      0
Sample              2013-07-08      0
Sample              2013-07-09      0
Sample              2013-07-10      0
Sample              2013-07-11      0
Sample              2013-07-12      1
Sample              2013-07-13      0
xxx Sample xxx      2013-07-07      0
xxx Sample xxx      2013-07-08      0
xxx Sample xxx      2013-07-09      0
xxx Sample xxx      2013-07-10      3
xxx Sample xxx      2013-07-11      0
xxx Sample xxx      2013-07-12      0
xxx Sample xxx      2013-07-13      0
yyy Sample yyy      2013-07-07      0
yyy Sample yyy      2013-07-08      0
yyy Sample yyy      2013-07-09      0
yyy Sample yyy      2013-07-10      1
yyy Sample yyy      2013-07-11      0
yyy Sample yyy      2013-07-12      0
yyy Sample yyy      2013-07-13      0

零日对于在图表中呈现数据非常重要，其中每个“标签”都是其自己的图表，其中时间是X轴，计数是Y轴。

架构图

标签表如下所示：

CREATE TABLE Tags
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](64) NOT NULL,
    CONSTRAINT [PK_Tags] PRIMARY KEY CLUSTERED
    (
          [Id] ASC
    ) ON [PRIMARY]
) ON [PRIMARY]

和事件表如下所示：

CREATE TABLE [dbo].[Events](
      [Id] [int] IDENTITY(1,1) NOT NULL,
      [Message] [varchar](128) NULL,
      [TagId] [int] NOT NULL,
      [CreatedAt] [datetime] NULL,
    CONSTRAINT [PK_Events] PRIMARY KEY CLUSTERED
    (
      [Id] ASC
    ) ON [PRIMARY]
)

其中TagId是“标签”表的外键。

样本数据

事件表具有以下数据

Id  Message     TagId   CreatedAt
=== =========== ======= =========================
1   Message 1   1       2013-07-10 18:46:04.967
2   Message 2   2       2013-07-14 18:46:10.547
3   Message 3   3       2013-07-12 18:46:15.190
4   Message 4   4       2013-07-14 18:46:20.673
5   Message 5   2       2013-07-14 18:46:28.133
8   Message 6   1       2013-07-10 14:46:04.967
9   Message 7   1       2013-07-10 12:46:04.967
10  Message 6   2       2013-07-10 14:46:04.967

标签表具有以下数据：

Id  Name
=== ===========================
3   Sample
4   Test1
5   Test2
6   Test3
1   xxx Sample xxx
2   yyy Sample yyy

我尝试过的

因此，我将其与一个表合并以获取以下内容：

SELECT Tags.Name, CONVERT(date, Events.CreatedAt) AS Date,COUNT(*) AS Count
FROM
Events
INNER JOIN Tags ON Events.TagId = Tags.Id
where tags.Name like '%sample%'
GROUP BY Tags.Name, CONVERT(date, Events.CreatedAt)
ORDER BY Tags.Name, CONVERT(date, Events.CreatedAt)

哪个回来了

Name                Date            Count
=================== =============== ================
Sample              2013-07-12      1
xxx Sample xxx      2013-07-10      3
yyy Sample yyy      2013-07-10      1
yyy Sample yyy      2013-07-14      2

我搜索了几天中没有数据的行生成方法。我找到了一个条目SQL Server：即使几天没有数据，但是如何使日期无法工作，如何选择日期范围内的所有日期。

为了验证我的日子是否合适，我运行了以下查询：

WITH DateTable
AS
(
    SELECT CONVERT(date, DateAdd(WEEK, -1, GETDATE())) AS [DATE]
    UNION ALL
    SELECT DATEADD(dd, 1, [DATE])
    FROM DateTable
    WHERE DATEADD(dd, 1, [DATE]) < CONVERT(date, GETDATE())
)
select DateTable.DATE
FROM DateTable

哪个返回：

我的第一个尝试是使其工作而未LIKE '%sample%'在where子句中指定。

WITH DateTable
AS
(
    SELECT CONVERT(date, DateAdd(WEEK, -1, GETDATE())) AS [DATE]
    UNION ALL
    SELECT DATEADD(dd, 1, [DATE])
    FROM DateTable
    WHERE DATEADD(dd, 1, [DATE]) < CONVERT(date, GETDATE())
)
SELECT Tags.Name, dt.[DATE] as Date, COUNT(Events.ID) as Count
FROM
      Events
      INNER JOIN Tags ON Tags.Id = Events.TagId
RIGHT JOIN [DateTable] dt ON dt.[DATE] = CONVERT(date, Events.[CreatedAt])
WHERE TagId IS NOT NULL
GROUP BY Tags.Name, dt.[DATE]

我得到以下结果：

Name                Date            Count
=================== =============== ================
xxx Sample xxx      2013-07-10      3
yyy Sample yyy      2013-07-10      1
Sample              2013-07-12      1

我尝试了其他操作，例如将更RIGHT JOIN改为LEFT JOIN，但无法获得所需的结果。

Praveen Prasannan

WITH DateTable
AS
(
    SELECT CONVERT(date, DateAdd(WEEK, -1, GETDATE())) AS [DATE]
    UNION ALL
    SELECT DATEADD(dd, 1, [DATE])
    FROM DateTable
    WHERE DATEADD(dd, 1, [DATE]) < CONVERT(date, GETDATE())
)
SELECT Tags.Name, dt.DATE as Date,COUNT(Events.ID) as Count
FROM DateTable dt
CROSS JOIN Tags Tags
LEFT JOIN Events ON dt.[DATE] = CONVERT(date, Events.[CreatedAt])
and Tags.Id = Events.TagId
where tags.Name like '%sample%'
GROUP BY Tags.Name, Date
ORDER BY Tags.Name, Date;