按连续日期和计数分组(Group by contiguous dates and Count)

按连续日期和计数分组(Group by contiguous dates and Count)

我有一个表格，其中包含与Date一起访问的报告的信息。我需要根据日期范围对报告进行分组并计算它们。

我正在使用T-SQL

表

EventId ReportId Date 60 4 11/24/2015 59 11 11/23/2015 58 6 11/22/2015 57 11 11/22/2015 56 9 11/21/2015 55 3 11/20/2015 54 5 11/20/2015 53 6 11/19/2015 52 5 11/19/2015 51 4 11/18/2015 50 3 11/17/2015 49 9 11/16/2015

如果天差是3，那么我需要格式的结果

StartDate EndDate ReportsAccessed 11/22/2015 11/24/2015 4 11/19/2015 11/21/2015 5 11/16/2015 11/18/2015 3

但日子之间的差异可能会改变。

I have a table which contains information about reports being accessed along with the Date.I need to group reports being accessed according to a date range and count them.

I'm using T-SQL

Table

EventId ReportId Date 60 4 11/24/2015 59 11 11/23/2015 58 6 11/22/2015 57 11 11/22/2015 56 9 11/21/2015 55 3 11/20/2015 54 5 11/20/2015 53 6 11/19/2015 52 5 11/19/2015 51 4 11/18/2015 50 3 11/17/2015 49 9 11/16/2015

If days' difference is 3 then I need result in the format

StartDate EndDate ReportsAccessed 11/22/2015 11/24/2015 4 11/19/2015 11/21/2015 5 11/16/2015 11/18/2015 3

but the difference between days could change.

最满意答案

假设您有所有日期的值，那么您可以计算每个日期与最大（或最小）日期之间的差异。 然后将其除以三，然后将其用于聚合：

select min(date), max(date), count(*) as ReportsAccessed from (select t.*, max(date) over () as maxd from table t ) t group by (datediff(day, date, maxd) / 3) order by min(date);

“3”是我认为你所说的“天差”。

Assuming you have values for all the dates, then you can calculate the difference in days between each date and the maximum (or minimum) date. Then divide this by three and use that for aggregation:

select min(date), max(date), count(*) as ReportsAccessed from (select t.*, max(date) over () as maxd from table t ) t group by (datediff(day, date, maxd) / 3) order by min(date);

"3" is what I think you are referring to as the "difference in days".