什么是大宗交易？大额成交与行情数据

SELECT
    formatDateTime(toTimeZone(sip_timestamp, 'America/New_York'), '%H:%i:%S') AS et_time,
    size AS shares,
    round(toFloat64(size) / 1e6, 2) AS shares_m,
    toFloat64(price) AS price,
    round(toFloat64(size) * toFloat64(price) / 1e6, 1) AS notional_musd,
    multiIf(has(conditions, 8), 'closing auction (exchange)',
            has(conditions, 17), 'opening auction (exchange)',
            exchange = 4, 'off-exchange (FINRA TRF)',
            'on exchange (continuous)') AS where_it_printed
FROM global_markets.stocks_trades
WHERE ticker = 'AAPL'
  AND sip_timestamp >= '2026-07-06 00:00:00' AND sip_timestamp < '2026-07-07 00:00:00'
  AND NOT hasAny(conditions, [15, 16, 38])
ORDER BY shares DESC, sip_timestamp, price
LIMIT 10

WITH
    (
        SELECT countIf(window_start >= toDateTime('2026-07-06 09:30:00', 'America/New_York') AND window_start < toDateTime('2026-07-06 16:00:00', 'America/New_York'))
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY' AND window_start >= toDateTime('2026-07-06 00:00:00', 'America/New_York') AND window_start < toDateTime('2026-07-07 00:00:00', 'America/New_York')
    ) AS bars_jul06
SELECT
    bars_jul06 AS spy_session_bars_jul06,
    count() AS total_prints,
    countIf(size >= 10000) AS block_prints,
    round(100.0 * countIf(size >= 10000) / count(), 2) AS block_pct_of_prints,
    round(toFloat64(sum(size)) / 1e6, 1) AS total_volume_m,
    round(toFloat64(sumIf(size, size >= 10000)) / 1e6, 1) AS block_volume_m,
    round(100.0 * toFloat64(sumIf(size, size >= 10000)) / toFloat64(sum(size)), 1) AS block_pct_of_volume,
    round(quantileDeterministic(0.5)(toFloat64(size), toUInt64(sip_timestamp))) AS median_trade_shares,
    round(quantileDeterministic(0.5)(toFloat64(size) * toFloat64(price), toUInt64(sip_timestamp))) AS median_trade_notional_usd
FROM global_markets.stocks_trades
WHERE ticker = 'AAPL'
  AND sip_timestamp >= '2026-07-06 00:00:00' AND sip_timestamp < '2026-07-07 00:00:00'
  AND NOT hasAny(conditions, [15, 16, 38])

SELECT
    count() AS total_prints,
    countIf(size >= 10000) AS block_prints,
    round(100.0 * countIf(size >= 10000) / count(), 2) AS block_pct_of_prints,
    round(toFloat64(sum(size)) / 1e6, 1) AS total_volume_m,
    round(toFloat64(sumIf(size, size >= 10000)) / 1e6, 1) AS block_volume_m,
    round(100.0 * toFloat64(sumIf(size, size >= 10000)) / toFloat64(sum(size)), 1) AS block_pct_of_volume,
    round(quantileDeterministic(0.5)(toFloat64(size), toUInt64(sip_timestamp))) AS median_trade_shares
FROM global_markets.stocks_trades
WHERE ticker = 'SPY'
  AND sip_timestamp >= '2026-07-06 00:00:00' AND sip_timestamp < '2026-07-07 00:00:00'
  AND NOT hasAny(conditions, [15, 16, 38])

SELECT
    multiIf(has(conditions, 8), 'Closing auction (exchange)',
            has(conditions, 17), 'Opening auction (exchange)',
            exchange = 4, 'Off-exchange (FINRA TRF)',
            'On exchange, continuous') AS venue,
    count() AS block_prints,
    round(toFloat64(sum(size)) / 1e6, 2) AS block_shares_m,
    round(100.0 * toFloat64(sum(size)) / sum(toFloat64(sum(size))) OVER (), 1) AS pct_of_block_volume,
    countIf(trf_timestamp IS NOT NULL AND trf_timestamp > toDateTime64('1970-01-02 00:00:00', 9)) AS prints_with_trf_timestamp
FROM global_markets.stocks_trades
WHERE ticker = 'AAPL'
  AND sip_timestamp >= '2026-07-06 00:00:00' AND sip_timestamp < '2026-07-07 00:00:00'
  AND NOT hasAny(conditions, [15, 16, 38])
  AND size >= 10000
GROUP BY venue
ORDER BY block_shares_m DESC, venue