基準に基づいてクエリに新しい行を作成する方法

bugfix> sql > 投稿 2020-12-08 07:54

問題文：

私は次の情報の表を持っています：

+-----------+-----------------+---------------------+---------------------+---------------------+------------------------+
| equipment | equipment_state | equipment_state_in  | equipment_state_out |     shift_begin     |       shift_end        |
+-----------+-----------------+---------------------+---------------------+---------------------+------------------------+
| A         | X               | 2020-12-05 00:00:00 | 2020-12-05 04:00:00 | 2020-12-05 00:00:00 | 2020-12-05 12:00:00    |
| A         | Y               | 2020-12-05 04:00:01 | 2020-12-05 08:00:00 | 2020-12-05 00:00:00 | 2020-12-05 12:00:00    |
| A         | Z               | 2020-12-05 08:00:01 | 2020-12-05 13:00:00 | 2020-12-05 00:00:00 | 2020-12-05 12:00:00    |
+-----------+-----------------+---------------------+---------------------+---------------------+------------------------+

ご覧のとおり、このテーブルは、時間の経過に伴う機器の状態を表しており、状態が発生したシフトがコンテキストとして追加されています。問題は、機器の状態が最初のシフトで開始している間、次のシフトに継続できることです。これは最後の行に見られます。

解決：

機器の状態が次のシフトにブリードするタイミングを検出するためにクエリにロジックを適用し、次のように情報を分離するために別の行を作成するソリューションを探しています。

+-----------+-----------------+---------------------+---------------------+---------------------+------------------------+
| equipment | equipment_state | equipment_state_in  | equipment_state_out |     shift_begin     |       shift_end        |
+-----------+-----------------+---------------------+---------------------+---------------------+------------------------+
| A         | X               | 2020-12-05 00:00:00 | 2020-12-05 04:00:00 | 2020-12-05 00:00:00 | 2020-12-05 12:00:00    |
| A         | Y               | 2020-12-05 04:00:01 | 2020-12-05 08:00:00 | 2020-12-05 00:00:00 | 2020-12-05 12:00:00    |
| A         | Z               | 2020-12-05 08:00:01 | 2020-12-05 12:00:00 | 2020-12-05 00:00:00 | 2020-12-05 12:00:00    |
| A         | Z               | 2020-12-05 12:00:01 | 2020-12-05 13:00:00 | 2020-12-05 00:12:00 | 2020-12-05 24:00:00    |
+-----------+-----------------+---------------------+---------------------+---------------------+------------------------+

ご覧のとおり、8：00〜13：00の状態zは引き続きキャプチャされますが、12：00〜13：00のセクションが正しいシフトウィンドウ内に収まるように分離されています。

何か案は？

sql

sql-server

回答 2 件

この再帰CTEを試してみてください。アンカーは、機器のアウトタイムがシフト終了時刻を過ぎた場合に設定された元のデータです。残りの機器時間の情報があればそれを保存するために、アンカーに追加の列が追加されます。クエリの再帰部分を単純化するために、この時点で次のシフト期間も計算されます。

再帰クエリは、残りの機器時間に同じロジックを適用します。残りの機器時間は、反復ごとに1シフトずつ短縮されるため、再帰は最終的に停止します。

WITH src as ( 
    SELECT * 
    FROM (
        VALUES 
            ('A', 'X', 
                CAST('2020-12-05 00:00:00' as datetime), CAST('2020-12-05 04:00:00' as datetime), 
                CAST('2020-12-05 00:00:00' as datetime), CAST('2020-12-05 12:00:00' as datetime)), 
            ('A', 'Y', 
                CAST('2020-12-05 04:00:01' as datetime), CAST('2020-12-05 08:00:00' as datetime), 
                CAST('2020-12-05 00:00:00' as datetime), CAST('2020-12-05 12:00:00' as datetime)),
            ('A', 'Z', 
                CAST('2020-12-05 08:00:01' as datetime), CAST('2020-12-05 13:00:00' as datetime), 
                CAST('2020-12-05 00:00:00' as datetime), CAST('2020-12-05 12:00:00' as datetime))
    ) t (equipment, equipment_state, equipment_state_in, equipment_state_out, shift_begin, shift_end)
), split as (
    SELECT equipment, 
        equipment_state, 
        equipment_state_in,
        CASE WHEN equipment_state_out > shift_end THEN shift_end ELSE equipment_state_out END as [equipment_state_out],
        shift_begin, 
        shift_end, 
        CASE WHEN equipment_state_out > shift_end THEN shift_end END as [equipment_state_in_remainder],
        CASE WHEN equipment_state_out > shift_end THEN equipment_state_out END as [equipment_state_out_remainder],
        CASE WHEN equipment_state_out > shift_end THEN shift_end END as [shift_begin_remainder],
        CASE WHEN equipment_state_out > shift_end THEN DATEADD(hour, 12, shift_end) END as [shift_end_remainder]
    FROM src
    UNION ALL
    SELECT equipment, 
        equipment_state, 
        equipment_state_in_remainder as [equipment_state_in],
        CASE WHEN equipment_state_out_remainder > shift_end_remainder THEN shift_end_remainder ELSE equipment_state_out_remainder END as [equipment_state_out],
        shift_begin_remainder as [shift_begin], 
        shift_end_remainder as [shift_end], 
        CASE WHEN equipment_state_out_remainder > shift_end_remainder THEN shift_end_remainder END as [equipment_state_in_remainder],
        CASE WHEN equipment_state_out_remainder > shift_end_remainder THEN equipment_state_out_remainder END as [equipment_state_out_remainder],
        CASE WHEN equipment_state_out_remainder > shift_end_remainder THEN shift_end_remainder END as [shift_begin_remainder],
        CASE WHEN equipment_state_out_remainder > shift_end_remainder THEN DATEADD(hour, 12, shift_end_remainder) END as [shift_end_remainder]
    FROM split
    WHERE equipment_state_in_remainder IS NOT NULL
)
select * from split

ループと比較のより直感的でプログラム的な方法を使用する-

---ループに使用される行番号としてidを持つ1つのテーブル変数

---最終結果を保持する別のテーブル変数

declare @TempTable as TABLE(Id  INT IDENTITY(1,1),
                        equipment       NVARCHAR(1),
                        equipment_state NVARCHAR(1),
                        equipment_state_in DATETIME,
                        equipment_state_out DATETIME,
                        shift_begin     DATETIME,
                        shift_end       DATETIME)
declare @Output as TABLE(equipment          NVARCHAR(1),
                        equipment_state NVARCHAR(1),
                        equipment_state_in DATETIME,
                        equipment_state_out DATETIME,
                        shift_begin     DATETIME,
                        shift_end       DATETIME)
INSERT INTO @TempTable(equipment, equipment_state, equipment_state_in, equipment_state_out, shift_begin, shift_end)
SELECT equipment, equipment_state, equipment_state_in, equipment_state_out, shift_begin, shift_end from OriginalTable
    
DECLARE     @RowNum                 INT = 1;
DECLARE     @equipment              NVARCHAR(1),
            @equipment_state        NVARCHAR(1),
            @equipment_state_in     DATETIME,
            @equipment_state_out    DATETIME,
            @shift_begin            DATETIME,
            @shift_end              DATETIME
WHILE EXISTS(SELECT * FROM @TempTable WHERE Id = @RowNum)
BEGIN
    SELECT @equipment = equipment, @equipment_state = equipment_state, @equipment_state_in = equipment_state_in, @equipment_state_out = equipment_state_out, @shift_begin = shift_begin, @shift_end = shift_end FROM @TempTable WHERE Id = @RowNum;
    WHILE (@equipment_state_out > @shift_end)
    BEGIN
            INSERT INTO @Output(equipment, equipment_state, equipment_state_in, equipment_state_out, shift_begin, shift_end)
            VALUES (@equipment, @equipment_state, @equipment_state_in, @shift_end, @shift_begin, @shift_end)
            SET @equipment_state_in = @shift_end;
            SET @shift_begin = @shift_end;
            SET @shift_end = DATEADD(hour, 12, @shift_end);
    END
    INSERT INTO @Output(equipment, equipment_state, equipment_state_in, equipment_state_out, shift_begin, shift_end)
            VALUES (@equipment, @equipment_state, @equipment_state_in, @equipment_state_out, @shift_begin, @shift_end)
    SET @RowNum += 1; 
END
SELECT * FROM @Output

bugfix

基準に基づいてクエリに新しい行を作成する方法

sql

sql-server

関連記事