使用日期范围聚合数据

zy1mlcev 于 2021-08-09 发布在 Java

关注(0)|答案(2)|浏览(284)

我有下表（#categoryweight），其中存储了具有日期范围的每个类别的权重和因子值。在可能的情况下，我需要聚合/简化这些数据，以便将连续的数据范围合并到一个范围内，以获得相同的权重和因子值。

DROP TABLE IF EXISTS #CategoryWeight;
CREATE TABLE #CategoryWeight ( [CategoryId] bigint, [weight] float(8), [factor] float(8), [startYear] nvarchar(60), [endYear] nvarchar(60) )
INSERT INTO #CategoryWeight
VALUES
( 42, 1, 0, N'2009', N'2014' ), 
( 42, 1, 0, N'2009', N'2019' ), 
( 42, 1, 0, N'2015', N'2017' ), 
( 42, 1, 0, N'2018', N'2019' ), 
( 42, 1, 1, N'2020', N'9999' ),

( 40, 1, 0, N'2009', N'2014' ), 
( 40, 1, 0, N'2009', N'2017' ), 
( 40, 1, 0, N'2015', N'2017' ), 
( 40, 1, 0, N'2020', N'9999' ), 
( 40, 1, 1, N'2018', N'2019' ),

( 45, 1, 0, N'2009', N'2014' ), 
( 45, 0, 0, N'2015', N'2017' ), 
( 45, 1, 0, N'2020', N'9999' ), 
( 45, 0, 1, N'2018', N'2019' );'

CategoryID  weight  factor  startYear   endYear
42          1       0       2009        2014
42          1       0       2009        2019
42          1       0       2015        2017
42          1       0       2018        2019
42          1       1       2020        9999
40          1       0       2009        2014
40          1       0       2009        2017
40          1       0       2015        2017
40          1       0       2020        9999
40          1       1       2018        2019
45          1       0       2009        2014
45          0       0       2015        2017
45          1       0       2020        9999
45          0       1       2018        2019

预期结果：

CategoryID  weight  factor  startYear   endYear
42          1       0       2009        2019
42          1       1       2020        9999
40          1       0       2009        2017
40          1       1       2018        2019
40          1       0       2020        9999
45          1       0       2009        2014
45          0       0       2015        2017
45          0       1       2018        2019
45          1       0       2020        9999

sql sql-server

来源：https://stackoverflow.com/questions/62617944/aggregate-data-with-date-ranges

2条答案

按热度按时间

kx1ctssn1#

你有重叠的时间段。这使得任何关于数据的假设都很麻烦——因为同一年在不同的行上可能有不同的值（在您的问题中没有什么可以排除这一点）。
因此，我建议的方法是将数据展开，然后将其重新组合成值相同的块。下面使用递归cte展开数据，然后使用间隙和孤岛技巧重新组合数据：

with cte as (
      select categoryid, weight, factor,
             convert(int, startyear) as year, convert(int, endyear) as endyear
      from categoryweight
      union all
      select categoryid, weight, factor,
             year + 1, endyear
      from cte
      where year < endyear
     )
select categoryid, weight, factor, min(year), max(year)
from (select categoryid, weight, factor, year,
             row_number() over (partition by categoryid, weight, factor order by year) as seqnum
      from (select distinct categoryid, weight, factor, year from cte) cte
     ) cte
group by categoryid, weight, factor, (year - seqnum)
order by categoryid, min(year)
option (maxrecursion 0);

这是一把小提琴。
我注意到一些关于你的数据。
您正在使用 float 对于某些值。这是非常危险的，因为两个值可能看起来相同，但实际上相差很小。使用 decimal / numeric 输入，这样你看到的就是你得到的。
这个 year 值是字符串，而它们应该是整数。使用正确的数据类型！

赞(0）回复(0）举报 2021-08-09

uubf1zoe2#

如果您使用的是mysql 8.0、sqlserver或postgresql，那么您可以使用window函数执行以下操作。

select
    distinct CategoryID, 
    weight, 
    factor,
    min(startYear) over (partition by CategoryID, weight, factor) as startYear,
    max(endYear) over (partition by CategoryID, weight, factor) as endYear
from categoryWeight
order by
    CategoryID

赞(0）回复(0）举报 2021-08-09

我来回答

使用日期范围聚合数据

2条答案

相关问题

热门标签

最新问答