日期范围标记算法与SQL一起使用

Question

我有一个体育项目。

Country     League      StartDate           EndDate
------------------------------------------------------------
USA         UPA         10.10.2015          13.06.2018

当我使用下面的代码时，我得到了如上的结果。

SELECT Country
      ,League
      ,MIN(MatchDateTimeUtc) AS StartDate
      ,MAX(MatchDateTimeUtc) AS EndDate
FROM Games
WHERE Country = 'USA'
GROUP BY Country
        ,League

我需要通过日期范围来标记季节。但是日期范围并不是恒定的。我的意思是，每个联赛的开始或结束日期都不确定。任何联赛的开始或结束日期可能会改变。只有国家和联盟字段是恒定的。例如，实际上上面的结果已经有3个赛季了。我的预期结果如下。

Country     League      StartDate           EndDate
------------------------------------------------------------
USA         UPA         10.10.2015          09.04.2016
USA         UPA         22.10.2016          11.05.2017
USA         UPA         30.09.2017          13.06.2018

对于这一点，有什么聪明的逻辑吗？

Answer 1

显然，你需要更多关于如何区分不同赛季的比赛的信息。

最简单的例子是进行限制性假设，例如“所有赛季的比赛都在同一年，并且每年只包含一个赛季的比赛”。在这种情况下，您可以添加按年份分组，例如：year(startDate)

SELECT Country, 
    League, 
    MIN(MatchDateTimeUtc) AS StartDate, 
    MAX(MatchDateTimeUtc) AS EndDate 
FROM Games 
WHERE Country = 'USA' 
GROUP BY Country, League, year(startDate)

如果没有这样简单的季节约定可以固定，那么您需要为league_seasons引入另一个表，并连接league_season表进行分组。

由于“季节”是由OP注释定义的，是由游戏之间的间隔长度定义的，因此您可以使用如下查询：

;with removedDuplicates as (
    select distinct * from games    
),
gamesWithSeasonNumber AS (
    select g.*,
    SUM(
        case when not exists (
            select 1 from removedDuplicates previousG
            where previousG.Country = g.Country and previousG.League = g.League
                and previousG.MatchDateTimeUtc < g.MatchDateTimeUtc
                and (DATEDIFF(d, previousG.MatchDateTimeUtc, g.MatchDateTimeUtc) < 60)) 
            then 1 else 0 
        end) 
    OVER(Partition By Country, LEague ORDER BY MatchDateTimeUtc) as SeasonNumber
    from removedDuplicates g
)
select Country, League, min(MatchDateTimeUtc) as startDate, max(MatchDateTimeUtc) as EndDate
from gamesWithSeasonNumber 
group by Country, League, SeasonNumber

此解决方案的关键部分包括：

1. 我们知道哪些比赛是赛季开始比赛-它们不能有任何比赛在他们之前开始得太近(我假设60天是最小的淡季长度)
2. 如果你按比赛日期订购，那么首发比赛的累积总和会给出所有行的赛季编号。See OVER-clause in documentation.
3. Once你有季号，它是最小日期/最大日期的微不足道的分组。

Common Table Expressions，以使查询按步骤保持可读。

Answer 2

SELECT Country, League, 
       MIN(MatchDateTimeUtc) AS StartDate, 
       MAX(MatchDateTimeUtc) AS EndDate 
FROM Games 
WHERE Country = 'USA' 
GROUP BY Country, League, 
      YEAR(DATEADD(month, -8, startDate));

这应该适用于从9月到12月到8月之前结束的任何季节。您的所有数据都支持此定义。