為什麼 Crypto 沒有 NBBO：沒有 SIP 與整合行情

WITH
    win AS
    (
        SELECT
            toUInt16(exchange)                 AS ex_id,
            toFloat64(price) * toFloat64(size) AS notional,
            toFloat64(size)                    AS shares
        FROM global_markets.stocks_trades
        WHERE ticker = 'AAPL'
          AND sip_timestamp >= '2026-06-10 14:00:00'
          AND sip_timestamp <  '2026-06-10 14:30:00'
          AND price > 0
          AND size > 0
    ),
    venues AS
    (
        SELECT
            toUInt16(id) AS ex_id,
            name
        FROM global_markets.stocks_exchanges
        WHERE asset_class = 'stocks'
    ),
    by_venue AS
    (
        SELECT
            ex_id,
            sum(notional) AS notional,
            sum(shares)   AS shares,
            count()       AS print_count
        FROM win
        GROUP BY ex_id
    )
SELECT
    ifNull(nullIf(v.name, ''), concat('Venue ', toString(b.ex_id)))          AS venue,
    round(100 * b.shares / sum(b.shares) OVER (), 2)                         AS pct_of_shares,
    b.print_count                                                            AS print_count,
    round(abs(10000 * ((b.notional / b.shares)
        / (sum(b.notional) OVER () / sum(b.shares) OVER ()) - 1)), 2)        AS abs_gap_bps
FROM by_venue AS b
LEFT JOIN venues AS v ON v.ex_id = b.ex_id
ORDER BY abs_gap_bps DESC

WITH
    q AS
    (
        SELECT
            toDateTime(toTimeZone(sip_timestamp, 'America/New_York')) AS sec,
            toFloat64(bid_price)                                      AS bid,
            toFloat64(ask_price)                                      AS ask
        FROM global_markets.cache_stocks_quotes
        WHERE ticker = 'AAPL'
          AND sip_timestamp >= '2026-06-10 14:00:00'
          AND sip_timestamp <  '2026-06-10 14:30:00'
          AND bid_price > 0
          AND ask_price > bid_price
    ),
    per_sec AS
    (
        SELECT
            sec,
            max(bid)             AS top_bid,
            min(ask)             AS top_ask,
            avg(ask - bid)       AS venue_spread,
            avg((ask + bid) / 2) AS mid
        FROM q
        GROUP BY sec
    )
SELECT
    formatDateTime(toStartOfMinute(sec), '%H:%i')                         AS et_time,
    round(100 * countIf(top_bid >= top_ask) / count(), 1)                 AS locked_or_crossed_pct,
    round(avgIf(10000 * (top_ask - top_bid) / mid, top_bid < top_ask), 2) AS top_of_book_spread_bps,
    round(avg(10000 * venue_spread / mid), 2)                             AS venue_quote_spread_bps
FROM per_sec
GROUP BY toStartOfMinute(sec)
HAVING countIf(top_bid < top_ask) > 0
ORDER BY toStartOfMinute(sec)

WITH bars AS
(
    SELECT
        toTimeZone(window_start, 'America/New_York') AS et,
        toFloat64(volume)                            AS vol
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'AAPL'
      AND window_start >= '2026-06-10 19:30:00'
      AND window_start <  '2026-06-10 20:01:00'
)
SELECT
    formatDateTime(et, '%H:%i')            AS et_time,
    round(vol / 1e6, 2)                    AS volume_millions,
    round(100 * vol / sum(vol) OVER (), 2) AS share_of_window_pct
FROM bars
ORDER BY et