米国株の休場日と短縮取引日を日本時間で確認

SELECT
    toString(date)                                 AS holiday_date,
    arrayElement(['月', '火', '水', '木', '金', '土', '日'], toDayOfWeek(date)) AS jp_weekday,
    any(name)                                      AS holiday,
    any(status)                                    AS day_status,
    any(if(status = 'early-close',
           formatDateTime(toTimeZone(close, 'Asia/Tokyo'), '%H:%i'),
           ''))                                    AS jst_close,
    concat(toString(toYear(date)), '年',
           toString(toMonth(date)), '月',
           toString(toDayOfMonth(date)), '日')     AS us_date_label,
    dateDiff('day', today(), date)                 AS days_away
FROM global_markets.stocks_market_holidays
WHERE date >= today()
  AND upper(exchange) IN ('NYSE', 'NASDAQ')
GROUP BY date
ORDER BY date

SELECT
    formatDateTime(toStartOfInterval(jst, INTERVAL 30 MINUTE), '%H:%i') AS jst_time,
    round(sumIf(vol, session_date = '2025-11-28') / 1e6, 1)             AS half_day_shares_mm,
    round(sumIf(vol, session_date = '2025-11-26') / 1e6, 1)             AS full_day_shares_mm
FROM
(
    SELECT
        toTimeZone(window_start, 'Asia/Tokyo')                            AS jst,
        toDate(toTimeZone(window_start, 'America/New_York'))              AS session_date,
        toHour(toTimeZone(window_start, 'America/New_York')) * 60
          + toMinute(toTimeZone(window_start, 'America/New_York'))        AS et_min,
        toFloat64(volume)                                                 AS vol
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= '2025-11-26 14:00:00'
      AND window_start <  '2025-11-29 05:00:00'
)
WHERE session_date IN ('2025-11-26', '2025-11-28')
  AND et_min >= 570
  AND et_min <  960
GROUP BY jst_time
ORDER BY min(et_min)

WITH
spy AS
(
    SELECT
        toDate(toTimeZone(window_start, 'America/New_York'))       AS session_date,
        toHour(toTimeZone(window_start, 'America/New_York')) * 60
          + toMinute(toTimeZone(window_start, 'America/New_York')) AS et_min
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= '2021-12-01'
),
early AS
(
    SELECT session_date
    FROM spy
    WHERE et_min >= 570 AND et_min < 960
    GROUP BY session_date
    HAVING count() BETWEEN 180 AND 240
       AND max(et_min) < 790
),
tape AS
(
    SELECT
        date,
        toFloat64(sum(volume)) AS shares
    FROM global_markets.stocks_daily_aggs
    WHERE date >= '2021-11-01'
    GROUP BY date
),
rolled AS
(
    SELECT
        date,
        shares,
        avg(shares) OVER (ORDER BY date ASC ROWS BETWEEN 20 PRECEDING AND 1 PRECEDING) AS shares_20
    FROM tape
)
SELECT
    toString(r.date)                                 AS session_date,
    concat(toString(toYear(r.date)), '年',
           toString(toMonth(r.date)), '月',
           toString(toDayOfMonth(r.date)), '日')     AS session_label,
    round(r.shares / 1e8, 1)                         AS half_day_oku,
    round(r.shares_20 / 1e8, 1)                      AS trailing20_oku,
    round(100 * r.shares / r.shares_20, 1)           AS vs_trailing20_pct
FROM rolled AS r
INNER JOIN early AS e ON e.session_date = r.date
ORDER BY r.date