SQLを使って株にテクニカル指標を出す⑤ 一目均衡表

WITH RECURSIVE DateTable(i, date) AS (
    SELECT 
        1 AS i,
        MAX(date)::timestamp AS date
    FROM
        nikkei225
    UNION ALL
    SELECT 
        i + 1 AS i,
        CASE WHEN  date_part('dow', date + '1 days') = 6 THEN
            date + '3 days'
        ELSE
            date + '1 days'
        END AS date
        FROM
            DateTable 
        WHERE
            i <= 25
)

SELECT 
    date,
    ROUND(lagging, 2) AS lagging,
    ROUND(standard, 2) AS standard,
    ROUND((FIRST_VALUE(lagging) OVER w4 + FIRST_VALUE(standard) OVER w4) / 2, 2) AS first_leading,
    ROUND((FIRST_VALUE(second) OVER w4), 2)  AS second_leading
FROM (
    SELECT 
        date,
        CASE WHEN (high IS NOT NULL AND COUNT(high) OVER w1 = 26) THEN
            (MAX(high) OVER w1 + MIN(low) OVER w1) / 2.0
        ELSE
            NULL
        END AS lagging,
        CASE WHEN (high IS NOT NULL AND COUNT(high) OVER w2 = 9) THEN
            (MAX(high) OVER w2 + MIN(low) OVER w2) / 2.0
        ELSE
            NULL
        END AS standard,
        CASE WHEN (high IS NOT NULL AND COUNT(high) OVER w3 = 52) THEN
            (MAX(high) OVER w3 + MIN(low) OVER w3) / 2.0
        ELSE
            NULL
        END AS second,
        close
    FROM (
        SELECT 
            date,
            NULL AS high,
            NULL AS low,
            NULL AS close
        FROM 
            (SELECT date AS date FROM DateTable WHERE i <> 1) AS S1
        UNION ALL
            SELECT 
                date, 
                high, 
                low, 
                close 
            FROM
                nikkei225
    ) AS T1 
    WINDOW
        w1 AS (ORDER BY date ASC ROWS BETWEEN 25 PRECEDING AND current ROW),
        w2 AS (ORDER BY date ASC ROWS BETWEEN 8 PRECEDING AND current ROW),
        w3 AS (ORDER BY date ASC ROWS BETWEEN 51 PRECEDING AND current ROW)
) AS L1
WINDOW
    w4 AS (ORDER BY date ASC ROWS BETWEEN 25 PRECEDING AND current ROW)
ORDER BY date ASC
;