SQL组按日期范围 [英] SQL Group by Date Range

问题描述

我有以下数据：

Date        Code
2014-08-01  A
2014-08-02  A
2014-08-03  A
2014-08-04  A
2014-08-05  A
2014-08-06  A
2014-08-07  A
2014-08-08  XXXX
2014-08-09  XXXX
2014-08-10  BB
2014-08-11  CCC
2014-08-12  CCC
2014-08-13  CCC
2014-08-14  CCC
2014-08-15  CCC
2014-08-16  CCC
2014-08-17  CCC
2014-08-18  XXXX
2014-08-19  XXXX
2014-08-20  XXXX
2014-08-21  XXXX
2014-08-22  XXXX
2014-08-23  XXXX
2014-08-24  XXXX
2014-08-25  XXXX
2014-08-26  XXXX
2014-08-27  XXXX
2014-08-28  XXXX
2014-08-29  XXXX
2014-08-30  XXXX
2014-08-31  XXXX

我想将数据与代码分组，但还要与日期范围组合，以便输出变为：

I want to group the data with codes but also with date ranges so that the output becomes:

Min Date    Max Date    Code
2014-08-01  2014-08-07  A
2014-08-08  2014-08-09  XXXX
2014-08-10  2014-08-10  BB
2014-08-11  2014-08-17  CCC
2014-08-18  2014-08-31  XXXX

我已经想过了，但是不能想到如何使用SQL对这些数据进行分组。有任何想法吗？

I have thought about it but cannot think of how to group this data using SQL. Any ideas? Thanks!

推荐答案

因此，你想根据相同的日期找到序列。

So, you want to find sequences according to the date that are the same.

这里是一个窍门：如果你采取 row_number（）在整个组和 row_number / code>由代码分区，那么对于具有相同代码的相邻行，它将是常量。其余的只是聚合：

Here is a trick: if you take the difference between row_number() over the entire group and row_number() partitioned by code, then it will be constant for adjacent rows with the same code. The rest is just aggregation:

select  min(date), max(date), code
from (select t.*,
             (row_number() over (order by date) -
              row_number() over (partition by code order by date)
             ) as grpid
      from followingdata t
     ) t
group by grpid, code;

这篇关于SQL组按日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！