同じテーブルの異なる行を組み合わせる-Postgres

それはLEAD、LAG機能を使用して簡単かつ効率的に行うことができます。少なくとも、テーブルの自己結合よりもはるかに効率的です：O(n)vs O(n*n)。

使用して次のおよび前の行の最初の追加列にLEAD及びLAG適切有しますPARTITION BY。

次に、2セットのペアを作成します。1つINIT_INTERVAL目はEND_INTERVAL。で始まり、2つ目は。で終わります。InitとEndの両方を持つペアがある場合、それは2回含まれ、後で削除されUNIONます。

SQLフィドル

サンプルデータ（これは、スクリーンショットに加えて質問に含める必要があるものです）

CREATE TABLE INTERVAL_TABLE (
  INTERVAL_ID int,
  USER_ID int,
  INTERVAL_TYPE_ID int,
  INTERVAL_TIMESTAMP timestamp,
  ENTRY_TYPE varchar(255));

INSERT INTO INTERVAL_TABLE (INTERVAL_ID, USER_ID, INTERVAL_TYPE_ID, INTERVAL_TIMESTAMP, ENTRY_TYPE) VALUES
(1, 1, 1, '2018-03-08 14:00:00', 'INIT_INTERVAL'),
(2, 1, 1, '2018-03-08 15:00:00', 'END_INTERVAL' ),
(3, 1, 1, '2018-03-08 15:30:00', 'END_INTERVAL' ),
(4, 1, 1, '2018-03-08 15:45:00', 'INIT_INTERVAL'),
(5, 1, 1, '2018-03-08 15:50:00', 'INIT_INTERVAL');

クエリ

WITH
CTE
AS
(
  SELECT
    USER_ID
    ,INTERVAL_TYPE_ID
    ,ENTRY_TYPE AS Curr_Entry_Type
    ,INTERVAL_TIMESTAMP AS Curr_Interval_Timestamp
    ,INTERVAL_ID AS Curr_Interval_ID

    ,LAG(ENTRY_TYPE) OVER(PARTITION BY USER_ID, INTERVAL_TYPE_ID ORDER BY INTERVAL_TIMESTAMP) AS Prev_Entry_Type
    ,LAG(INTERVAL_TIMESTAMP) OVER(PARTITION BY USER_ID, INTERVAL_TYPE_ID ORDER BY INTERVAL_TIMESTAMP) AS Prev_Interval_Timestamp
    ,LAG(INTERVAL_ID) OVER(PARTITION BY USER_ID, INTERVAL_TYPE_ID ORDER BY INTERVAL_TIMESTAMP) AS Prev_Interval_ID

    ,LEAD(ENTRY_TYPE) OVER(PARTITION BY USER_ID, INTERVAL_TYPE_ID ORDER BY INTERVAL_TIMESTAMP) AS Next_Entry_Type
    ,LEAD(INTERVAL_TIMESTAMP) OVER(PARTITION BY USER_ID, INTERVAL_TYPE_ID ORDER BY INTERVAL_TIMESTAMP) AS Next_Interval_Timestamp
    ,LEAD(INTERVAL_ID) OVER(PARTITION BY USER_ID, INTERVAL_TYPE_ID ORDER BY INTERVAL_TIMESTAMP) AS Next_Interval_ID
  FROM
    INTERVAL_TABLE
)
,CTE_Result
AS
(
  SELECT
    USER_ID
    ,INTERVAL_TYPE_ID
    ,Curr_Entry_Type AS Entry_Type_Init
    ,Curr_Interval_Timestamp AS Interval_Timestamp_Init
    ,Curr_Interval_ID AS Interval_ID_Init
    ,Next_Entry_Type AS Entry_Type_End
    ,CASE WHEN Next_Entry_Type = 'END_INTERVAL' THEN Next_Interval_Timestamp END AS Interval_Timestamp_End
    ,CASE WHEN Next_Entry_Type = 'END_INTERVAL' THEN Next_Interval_ID END AS Interval_ID_End
  FROM CTE
  WHERE Curr_Entry_Type = 'INIT_INTERVAL'

  UNION -- sic! not UNION ALL

  SELECT
    USER_ID
    ,INTERVAL_TYPE_ID
    ,Prev_Entry_Type AS Entry_Type_Init
    ,CASE WHEN Prev_Entry_Type = 'INIT_INTERVAL' THEN Prev_Interval_Timestamp END AS Interval_Timestamp_Init
    ,CASE WHEN Prev_Entry_Type = 'INIT_INTERVAL' THEN Prev_Interval_ID END AS Interval_ID_Init
    ,Curr_Entry_Type AS Entry_Type_End
    ,Curr_Interval_Timestamp AS Interval_Timestamp_End
    ,Curr_Interval_ID AS Interval_ID_End
  FROM CTE
  WHERE Curr_Entry_Type = 'END_INTERVAL'
)
SELECT
    USER_ID
    ,INTERVAL_TYPE_ID
    ,Interval_Timestamp_Init
    ,Interval_Timestamp_End
    ,Interval_ID_Init
    ,Interval_ID_End
FROM CTE_Result
ORDER BY
  USER_ID
  ,INTERVAL_TYPE_ID
  ,COALESCE(Interval_Timestamp_Init, Interval_Timestamp_End)

結果

| user_id | interval_type_id | interval_timestamp_init | interval_timestamp_end | interval_id_init | interval_id_end |
|---------|------------------|-------------------------|------------------------|------------------|-----------------|
|       1 |                1 |    2018-03-08T14:00:00Z |   2018-03-08T15:00:00Z |                1 |               2 |
|       1 |                1 |                  (null) |   2018-03-08T15:30:00Z |           (null) |               3 |
|       1 |                1 |    2018-03-08T15:45:00Z |                 (null) |                4 |          (null) |
|       1 |                1 |    2018-03-08T15:50:00Z |                 (null) |                5 |          (null) |