用于对连续日期数据进行分组的 SQL Server 查询 [英] SQL Server query to group sequential date data
问题描述
今天下午我有点大脑衰退"，所以如果有人能帮助解决这个 mssql 查询，那就太棒了.
I have got a bit of 'brain fade' going on this afternoon, so if anyone can help with this mssql query it would be fantastic.
我有一个名为seasons"的表，其中包含三列(还有更多列但与示例无关):seasonId、date、关税Id
I have a table called 'seasons' with three columns (there are more but not relevant to the example): seasonId, date, tariffId
SeasonId 是唯一键.一个日期只能有一个关税号，但关税号可以有多个不同的日期.
SeasonId is a unique key. A date can only have one tariffid, but a tariffId can have many different dates.
例如:
seasonId | date | tariffId
----------------------------------
1 | 1 jan 2009 | 1
2 | 2 jan 2009 | 1
3 | 3 jan 2009 | 2
4 | 4 jan 2009 | 3
5 | 5 jan 2009 | 3
我想要一个查询，根据特定的关税 ID 返回日期的序列/范围
I'd like to have a query return the sequence/range of dates against a particular tariffId
例如使用上面的数据，它将返回以下内容:
Eg using the data above, it would return the following:
FromDate | ToDate | TariffId
-----------------------------------
1 | Jan 2009 2 | Jan 2009 1
3 | Jan 2009 3 | Jan 2009 2
4 | Jan 2009 5 | Jan 2009 3
这可能吗?
编辑感谢您到目前为止的所有答案！我总是很惊讶你能得到多远的回应！
EDIT Thanks for all the answers so far! I am always amazed how far you get a response!
但是，我的示例数据可能不够复杂，因为关税可以有 1 个或多个日期范围
However, my example data probably wasn't complex enough as a tariff can have 1 or more date ranges
seasonId | date | tariffId
----------------------------------
1 | 1 jan 2009 | 1
2 | 2 jan 2009 | 1
3 | 3 jan 2009 | 2
4 | 4 jan 2009 | 3
5 | 5 jan 2009 | 3
6 | 6 jan 2009 | 1
7 | 7 jan 2009 | 1
8 | 8 jan 2009 | 3
会给:
FromDate | ToDate | TariffId
------------------------------------
1 Jan 2009 | 2 Jan 2009 | 1
3 Jan 2009 | 3 Jan 2009 | 2
4 Jan 2009 | 5 Jan 2009 | 3
6 Jan 2009 | 7 Jan 2009 | 1
8 Jan 2009 | 8 Jan 2009 | 3
想法?
感谢大家对此的帮助！这个网站很棒！
Thanks everyone for their help on this! This site is AWESOME!
推荐答案
先来一些测试数据:
create table seasons (seasonId int primary key
, "date" datetime not null unique
, tariffId int not null)
insert into seasons values (1, '2009-01-01', 1)
insert into seasons values (2, '2009-01-02', 1)
insert into seasons values (3, '2009-01-03', 2)
insert into seasons values (4, '2009-01-04', 3)
insert into seasons values (5, '2009-01-05', 3)
insert into seasons values (6, '2009-01-06', 1)
insert into seasons values (7, '2009-01-07', 1)
insert into seasons values (8, '2009-01-08', 3)
-- add a tarrif with a datespan larger than 2
insert into seasons values (9, '2009-01-09', 4)
insert into seasons values (10, '2009-01-10', 4)
insert into seasons values (11, '2009-01-11', 4)
基于 Dave Barker 的回答，在内联视图中添加 row_number() 以便我们知道哪个是第一个最小值，哪个是第二个，等等.(实际上由于一个日期不能有多个关税 ID，所以我们不需要按关税 ID 进行分区.)
Building on Dave Barker's answer, within the inline views add row_number() so we know which is the first min values, which is the second, etc. by tariffId. (Actually since a date can't have more than one tariffId, we don't need to partition by tariffId.)
SELECT MinValues.Seasonid, MinValues.Date, MaxValues.Date, MaxValues.tariffid
FROM (
SELECT *, row_number() over (partition by tariffId order by "date") as RN
FROM [dbo].[Seasons] tbl1
WHERE NOT EXISTS (SELECT *
FROM [dbo].[Seasons] tbl2
WHERE tbl1.seasonid - tbl2.seasonid = 1
AND tbl1.tariffId = tbl2.tariffId)) as minValues
JOIN (
SELECT *, row_number() over (partition by tariffId order by "date") as RN
FROM [dbo].[Seasons] tbl1
WHERE NOT EXISTS (SELECT *
FROM [dbo].[Seasons] tbl2
WHERE tbl2.seasonid - tbl1.seasonid = 1
AND tbl1.tariffId = tbl2.tariffId)) as maxValues
ON MinValues.TariffId = MaxValues.tariffId
and MinValues.RN = MaxValues.RN
order by MinValues.Date
结果:
1 2009-01-01 00:00:00.000 2009-01-02 00:00:00.000 1
3 2009-01-03 00:00:00.000 2009-01-03 00:00:00.000 2
4 2009-01-04 00:00:00.000 2009-01-05 00:00:00.000 3
6 2009-01-06 00:00:00.000 2009-01-07 00:00:00.000 1
8 2009-01-08 00:00:00.000 2009-01-08 00:00:00.000 3
9 2009-01-09 00:00:00.000 2009-01-11 00:00:00.000 4
这篇关于用于对连续日期数据进行分组的 SQL Server 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
