株式分割とは？仕組みや比率を解説

SELECT toString(day) AS session,
       round(argMinIf(toFloat64(open), window_start, rth), 2) AS regular_open,
       round(argMaxIf(toFloat64(close), window_start, rth), 2) AS regular_close
FROM (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS day,
           window_start, open, close,
           toTimeZone(window_start, 'America/New_York') >= toDateTime(concat(toString(toDate(toTimeZone(window_start, 'America/New_York'))), ' 09:30:00'), 'America/New_York')
           AND toTimeZone(window_start, 'America/New_York') < toDateTime(concat(toString(toDate(toTimeZone(window_start, 'America/New_York'))), ' 16:00:00'), 'America/New_York') AS rth
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'NVDA'
      AND window_start >= '2024-06-07 04:00:00'
      AND window_start < '2024-06-11 04:00:00'
)
GROUP BY day
ORDER BY day

SELECT ticker, executed_on, ratio, recorded_as, stock_21_sessions_pct, spy_21_sessions_pct
FROM (
    WITH events AS (
        SELECT ticker, execution_date,
               concat(toString(toInt32(split_to)), '-for-', toString(toInt32(split_from))) AS ratio,
               if(adjustment_type = 'stock_dividend', 'stock dividend', 'forward split') AS recorded_as
        FROM global_markets.stocks_splits
        WHERE ticker IN ('AAPL', 'TSLA', 'GOOGL', 'AMZN', 'NVDA', 'WMT', 'CMG', 'AVGO', 'SMCI', 'LRCX', 'DECK', 'ORLY', 'NFLX')
          AND execution_date >= '2020-01-01' AND execution_date <= '2026-06-30'
          AND split_to > split_from
    ),
    daily AS (
        SELECT ticker,
               toDate(toTimeZone(window_start, 'America/New_York')) AS d,
               argMaxIf(toFloat64(close), window_start,
                        (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) AS close_rth
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker IN (SELECT ticker FROM events UNION ALL SELECT 'SPY')
          AND window_start >= '2020-08-01 00:00:00' AND window_start < '2026-01-15 00:00:00'
        GROUP BY ticker, d
        HAVING close_rth > 0
    ),
    series AS (
        SELECT ticker, arraySort(x -> x.1, groupArray((d, close_rth))) AS dc
        FROM daily
        GROUP BY ticker
    ),
    spy AS (
        SELECT dc AS spy_dc FROM series WHERE ticker = 'SPY'
    )
    SELECT e.ticker AS ticker,
           toString(e.execution_date) AS executed_on,
           e.ratio AS ratio,
           e.recorded_as AS recorded_as,
           arrayFirstIndex(x -> x.1 >= e.execution_date, s.dc) AS idx,
           arrayFirstIndex(x -> x.1 >= e.execution_date, spy_dc) AS sidx,
           round((s.dc[idx + 21].2 / s.dc[idx].2 - 1) * 100, 1) AS stock_21_sessions_pct,
           round((spy_dc[sidx + 21].2 / spy_dc[sidx].2 - 1) * 100, 1) AS spy_21_sessions_pct,
           e.execution_date AS ed
    FROM events e
    JOIN series s ON s.ticker = e.ticker
    CROSS JOIN spy
    ORDER BY ed DESC, ticker ASC
)

SELECT concat(toString(toInt32(split_to)), '-for-1') AS ratio,
       count() AS splits
FROM global_markets.stocks_splits
WHERE execution_date >= '2025-07-01' AND execution_date <= '2026-06-30'
  AND split_from = 1 AND split_to >= 2 AND split_to = round(split_to)
GROUP BY ratio
ORDER BY splits DESC
LIMIT 6

WITH events AS (
    SELECT ticker, execution_date
    FROM global_markets.stocks_splits
    WHERE execution_date >= '2025-01-01' AND execution_date <= '2025-12-31'
      AND adjustment_type = 'forward_split' AND split_from = 1
      AND split_to >= 2 AND split_to = round(split_to)
),
daily AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           argMaxIf(toFloat64(close), window_start,
                    (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) AS close_rth
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN (SELECT ticker FROM events UNION ALL SELECT 'SPY')
      AND window_start >= '2025-01-01 00:00:00' AND window_start < '2026-03-01 00:00:00'
    GROUP BY ticker, d
    HAVING close_rth > 0
),
series AS (
    SELECT ticker, arraySort(x -> x.1, groupArray((d, close_rth))) AS dc
    FROM daily
    GROUP BY ticker
),
spy AS (
    SELECT dc AS spy_dc FROM series WHERE ticker = 'SPY'
),
per_event AS (
    SELECT e.ticker AS tkr,
           arrayFirstIndex(x -> x.1 >= e.execution_date, s.dc) AS idx,
           arrayFirstIndex(x -> x.1 >= e.execution_date, spy_dc) AS sidx,
           s.dc AS dc, spy_dc
    FROM events e
    JOIN series s ON s.ticker = e.ticker
    CROSS JOIN spy
),
measured AS (
    SELECT tkr,
           (dc[idx + 5].2 / dc[idx].2 - 1) * 100 AS r5,
           (dc[idx + 21].2 / dc[idx].2 - 1) * 100 AS r21,
           (spy_dc[sidx + 21].2 / spy_dc[sidx].2 - 1) * 100 AS spy21
    FROM per_event
    WHERE idx > 0 AND length(dc) >= idx + 21
      AND sidx > 0 AND length(spy_dc) >= sidx + 21
      AND dc[idx].2 > 0
)
SELECT count() AS splits_measured,
       round(quantileDeterministic(0.5)(r5, cityHash64(tkr)), 1) AS median_5_session_pct,
       round(quantileDeterministic(0.5)(r21, cityHash64(tkr)), 1) AS median_21_session_pct,
       round(100.0 * countIf(r21 > 0) / count(), 0) AS pct_up_after_21_sessions,
       round(quantileDeterministic(0.5)(spy21, cityHash64(tkr)), 1) AS median_spy_21_session_pct
FROM measured

WITH daily AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           sum(toFloat64(volume)) AS shares,
           sum(toFloat64(close) * toFloat64(volume)) AS dollars
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'NVDA'
      AND window_start >= '2024-05-08 00:00:00' AND window_start < '2024-07-12 00:00:00'
    GROUP BY d
),
ranked AS (
    SELECT d, shares, dollars,
           d >= toDate('2024-06-10') AS post,
           row_number() OVER (PARTITION BY d >= toDate('2024-06-10')
                              ORDER BY if(d < toDate('2024-06-10'), -toUInt32(d), toUInt32(d))) AS rn
    FROM daily
)
SELECT if(post = 0, 'Last 20 sessions before the split', 'First 20 sessions from the split') AS phase,
       count() AS sessions,
       round(avg(shares) / 1e6, 1) AS avg_daily_shares_millions,
       round(avg(dollars) / 1e9, 1) AS avg_daily_dollars_billions
FROM ranked
WHERE rn <= 20
GROUP BY phase, post
ORDER BY post

SELECT sum(forward_splits) AS forward_total,
       sum(reverse_splits) AS reverse_total,
       round(sum(reverse_splits) / sum(forward_splits), 2) AS reverse_per_forward,
       countIf(reverse_splits > forward_splits) AS years_reverse_ahead
FROM (
    SELECT toYear(execution_date) AS year,
           countIf(split_to > split_from) AS forward_splits,
           countIf(split_from > split_to) AS reverse_splits
    FROM global_markets.stocks_splits
    WHERE execution_date >= '2016-01-01' AND execution_date < '2026-01-01'
    GROUP BY year
)

SELECT toYear(execution_date) AS year,
       countIf(split_to > split_from) AS forward_splits,
       countIf(split_from > split_to) AS reverse_splits
FROM global_markets.stocks_splits
WHERE execution_date >= '2016-01-01' AND execution_date <= '2026-06-30'
GROUP BY year
ORDER BY year