140(1): デフォルトの名無しさん [sage] 2021/10/24(日) 00:25:59.14 ID:4lHZz/Ub(1) AAS
SQLだけでやってみた
WITH
ブレークチェック AS (
SELECT
CASE WHEN LAG(status,1,NULL) OVER (ORDER BY [time]) < status
THEN 0
ELSE 1
END AS st,
[time]
FROM 元のテーブル),
開始終了 AS (
SELECT
[time],
(SELECT MIN([time]) FROM ブレークチェック WHERE st = 1 AND [time] > t.[time]) AS end_time
FROM ブレークチェック t
WHERE st = 1),
t1 AS (SELECT [time] FROM 元のテーブル WHERE [status] = 1),
t2 AS (SELECT [time] FROM 元のテーブル WHERE [status] = 2),
t3 AS (SELECT [time] FROM 元のテーブル WHERE [status] = 3),
t4 AS (SELECT [time] FROM 元のテーブル WHERE [status] = 4)
SELECT ROW_NUMBER() OVER (ORDER BY 開始終了.[time]) AS rep_id,
t1.[time] AS status_1,
t2.[time] AS status_2,
t3.[time] AS status_3,
t4.[time] AS status_4
FROM 開始終了
LEFT JOIN t1 ON t1.[time] >= 開始終了.[time] AND (t1.[time] < 開始終了.[end_time] OR 開始終了.end_time IS NULL)
LEFT JOIN t2 ON t2.[time] >= 開始終了.[time] AND (t2.[time] < 開始終了.[end_time] OR 開始終了.end_time IS NULL)
LEFT JOIN t3 ON t3.[time] >= 開始終了.[time] AND (t3.[time] < 開始終了.[end_time] OR 開始終了.end_time IS NULL)
LEFT JOIN t4 ON t4.[time] >= 開始終了.[time] AND (t4.[time] < 開始終了.[end_time] OR 開始終了.end_time IS NULL)
素直にループ回すほうがいいな