WITH HORIZONAL_LOG AS (
    SELECT
        STEP1.SESSION_ID
        , STEP1.PROCESS_ID AS P_STEP1
        , STEP1.EX_TIMESTAMP AS T_STEP1
        , STEP2.PROCESS_ID AS P_STEP2
        , STEP2.EX_TIMESTAMP AS T_STEP2
        , STEP3.PROCESS_ID AS P_STEP3
        , STEP3.EX_TIMESTAMP AS T_STEP3
        , STEP4.PROCESS_ID AS P_STEP4
        , STEP4.EX_TIMESTAMP AS T_STEP4
        , STEP5.PROCESS_ID AS P_STEP5
        , STEP5.EX_TIMESTAMP AS T_STEP5
    FROM
       (
            SELECT
                *
            FROM
                PROCESS_LOG
            WHERE
                PROCESS_ID = 'STEP1'
        ) AS STEP1
    LEFT JOIN
        (
            SELECT
                *
            FROM
                PROCESS_LOG
            WHERE
                PROCESS_ID = 'STEP2'
        ) AS STEP2 ON STEP1.SESSION_ID = STEP2.SESSION_ID
    LEFT JOIN
        (
            SELECT
                *
            FROM
                PROCESS_LOG
            WHERE
                PROCESS_ID = 'STEP3'
        ) AS STEP3 ON STEP1.SESSION_ID = STEP3.SESSION_ID
    LEFT JOIN
        (
            SELECT
                *
            FROM
                PROCESS_LOG
            WHERE
                PROCESS_ID = 'STEP4'
        ) AS STEP4 ON STEP1.SESSION_ID = STEP4.SESSION_ID
    LEFT JOIN
        (
            SELECT
                *
            FROM
                PROCESS_LOG
            WHERE
                PROCESS_ID = 'STEP5'
        ) AS STEP5 ON STEP1.SESSION_ID = STEP5.SESSION_ID
)
, HORIZONAL_PROCESS_CNT AS (
    SELECT
        SESSION_ID
        , P_STEP1
        , T_STEP1
        , P_STEP2
        , T_STEP2
        , P_STEP3
        , T_STEP3
        , P_STEP4
        , T_STEP4
        , P_STEP5
        , T_STEP5
        , CASE 
            WHEN T_STEP1 < IFNULL(T_STEP2, '9999-99-99') -- STEP1よりも先に他のSTEPが実行されていないこと
                AND T_STEP1 < IFNULL(T_STEP3, '9999-99-99')
                AND T_STEP1 < IFNULL(T_STEP4, '9999-99-99')
                AND T_STEP1 < IFNULL(T_STEP5, '9999-99-99')
                THEN 1
            ELSE 0
            END
            AS STEP1_CNT
        , CASE
            WHEN P_STEP1 IS NOT NULL -- STEP1以外のSTEPは、1つ前のSTEPが実行されていること
                AND P_STEP2 IS NOT NULL
                AND T_STEP1 < T_STEP2  -- STEP1~5が、実行日時(EX_TIMESTAMP)が早い順に実行されているSTEPまでが対象
                AND T_STEP2 < IFNULL(T_STEP3, '9999-99-99')
                AND T_STEP2 < IFNULL(T_STEP4, '9999-99-99')
                AND T_STEP2 < IFNULL(T_STEP5, '9999-99-99')
                THEN 1
            ELSE 0
            END
            AS STEP2_CNT
        , CASE
            WHEN P_STEP1 IS NOT NULL
                AND P_STEP2 IS NOT NULL
                AND P_STEP3 IS NOT NULL
                AND T_STEP1 < T_STEP2
                AND T_STEP2 < T_STEP3
                AND T_STEP3 < IFNULL(T_STEP4, '9999-99-99')
                AND T_STEP3 < IFNULL(T_STEP5, '9999-99-99')
                THEN 1
            ELSE 0
            END
            AS STEP3_CNT
        , CASE
            WHEN P_STEP1 IS NOT NULL
                AND P_STEP2 IS NOT NULL
                AND P_STEP3 IS NOT NULL
                AND P_STEP4 IS NOT NULL
                AND T_STEP1 < T_STEP2
                AND T_STEP2 < T_STEP3
                AND T_STEP3 < T_STEP4
                AND T_STEP4 < IFNULL(T_STEP5, '9999-99-99')
                THEN 1
            ELSE 0
            END
            AS STEP4_CNT
        , CASE
            WHEN P_STEP1 IS NOT NULL
                AND P_STEP2 IS NOT NULL
                AND P_STEP3 IS NOT NULL
                AND P_STEP4 IS NOT NULL
                AND P_STEP5 IS NOT NULL
                AND T_STEP1 < T_STEP2
                AND T_STEP2 < T_STEP3
                AND T_STEP3 < T_STEP4
                AND T_STEP4 < T_STEP5
                THEN 1
            ELSE 0
            END
            AS STEP5_CNT
    FROM
        HORIZONAL_LOG
)
, PROCESS_CNT AS (
    SELECT
        SUM(STEP1_CNT) AS STEP1_CNT
        , SUM(STEP2_CNT) AS STEP2_CNT
        , SUM(STEP3_CNT) AS STEP3_CNT
        , SUM(STEP4_CNT) AS STEP4_CNT
        , SUM(STEP5_CNT) AS STEP5_CNT
    FROM
        HORIZONAL_PROCESS_CNT
)
SELECT
    'STEP1' AS PROCESS
    , STEP1_CNT AS CNT
FROM
    PROCESS_CNT
UNION
SELECT
    'STEP2'
    , STEP2_CNT
FROM
    PROCESS_CNT
UNION
SELECT
    'STEP3'
    , STEP3_CNT
FROM
    PROCESS_CNT
UNION
SELECT
    'STEP4'
    , STEP4_CNT
FROM
    PROCESS_CNT
UNION
SELECT
    'STEP5'
    , STEP5_CNT
FROM
    PROCESS_CNT

;