在不连续的时间段YYYYMM00中找到MIN和MAX日期

一尘不染

在不连续的时间段YYYYMM00中找到MIN和MAX日期

sql

我有以下两个表：

DimensionTime 是一个表格，其中包含按YYYMM00格式按ID排序的每个月
LogPlayer 是一个表格，其中有一些与玩家和特定月份有关的统计信息。

我想要得到的是以下内容：

+--------+--------+----------+----------+
| Player |  Team  |  Start   |   End    |
+--------+--------+----------+----------+
| John   | Red    | 20180100 | 20180300 |
| John   | Red    | 20180600 | 20180700 |
| Luke   | Yellow | 20180100 | 20180100 |
| Luke   | Yellow | 20190100 | 20190100 |
+--------+--------+----------+----------+

我不能使用MIN和MAX函数，因为周期是不连续的…我该如何解决？我尝试将MIN / MAX与GROUP
BY结合使用，但没有任何用处。我在Stackoverflow上找不到任何问题或答案。

SELECT *
    INTO #DimensionTime
    FROM (
        SELECT 1 AS [ID], 20180100 AS [TIMEID]
        UNION ALL
        SELECT 2 AS [ID], 20180200 AS [TIMEID]
        UNION ALL
        SELECT 3 AS [ID], 20180300 AS [TIMEID]
        UNION ALL
        SELECT 4 AS [ID], 20180400 AS [TIMEID]
        UNION ALL
        SELECT 5 AS [ID], 20180500 AS [TIMEID]
        UNION ALL
        SELECT 6 AS [ID], 20180600 AS [TIMEID]
        UNION ALL
        SELECT 7 AS [ID], 20180700 AS [TIMEID]
        UNION ALL
        SELECT 8 AS [ID], 20180800 AS [TIMEID]
        UNION ALL
        SELECT 9 AS [ID], 20180900 AS [TIMEID]
        UNION ALL
        SELECT 10 AS [ID], 20181000 AS [TIMEID]
        UNION ALL
        SELECT 11 AS [ID], 20181100 AS [TIMEID]
        UNION ALL
        SELECT 12 AS [ID], 20181200 AS [TIMEID]
        UNION ALL
        SELECT 13 AS [ID], 20190100 AS [TIMEID]
        UNION ALL
        SELECT 14 AS [ID], 20190200 AS [TIMEID]
        UNION ALL
        SELECT 15 AS [ID], 20190300 AS [TIMEID]
    ) A

SELECT *
INTO #LogPlayer
FROM (
    SELECT 'John' AS [Player], 'Red' AS [Team], 20180100 AS [TIMEID]
    UNION ALL
    SELECT 'John' AS [Player], 'Red' AS [Team], 20180200 AS [TIMEID]
    UNION ALL
    SELECT 'John' AS [Player], 'Red' AS [Team], 20180300 AS [TIMEID]
    UNION ALL
    SELECT 'John' AS [Player], 'Red' AS [Team], 20180600 AS [TIMEID]
    UNION ALL
    SELECT 'John' AS [Player], 'Red' AS [Team], 20180700 AS [TIMEID]
    UNION ALL
    SELECT 'Luke' AS [Player], 'Yellow' AS [Team], 20180100 AS [TIMEID]
    UNION ALL
    SELECT 'Luke' AS [Player], 'Yellow' AS [Team], 20190100 AS [TIMEID]
) B

阅读 246

2021-05-30

共1个答案

一尘不染

这是一种“空白与孤岛”的问题。即使在不受支持的古老软件（例如SQL Server 2005）中，它也可以解决，因为该版本具有row_number()。

一个技巧是将时间id转换为 真实的 日期/时间。另一个技巧是通过从日期/时间值中减去连续的月数来定义组：

select player, team, min(timeid), max(timeid)
from (select lp.*,
             row_number() over (partition by player, team order by timeid) as seqnum,
             cast(cast(timeid + 1 as varchar(255)) as datetime) as yyyymm
      from logplayer lp
     ) lp
group by player, team, dateadd(month, - seqnum, yyyymm)
order by player, team, min(timeid);

这是一个db<> fiddle。

2021-05-30