次の2つのテーブルがあります。
私が取得したいのは次のとおりです。
+--------+--------+----------+----------+
| Player | Team | Start | End |
+--------+--------+----------+----------+
| John | Red | 20180100 | 20180300 |
| John | Red | 20180600 | 20180700 |
| Luke | Yellow | 20180100 | 20180100 |
| Luke | Yellow | 20190100 | 20190100 |
+--------+--------+----------+----------+
ピリオドが不連続であるため、MIN関数とMAX関数を使用できません...どうすれば解決できますか?MIN / MAXをGROUPBYと組み合わせて試してみましたが、何も役に立ちません。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
これは一種のギャップと島の問題です。SQL Server 2005のようなサポートされていない古いソフトウェアでも、そのバージョンにはが含まれているため、解決可能row_number()
です。
1つのトリックは、時刻IDを真正な日付/時刻に変換することです。もう1つのトリックは、日付/時刻の値から連続する月数を引くことによってグループを定義することです。
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 <>フィドルです。
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加