做市商如何通过买卖价差赚钱

WITH (
  SELECT max(toDate(sip_timestamp))
  FROM global_markets.cache_stocks_quotes
  WHERE ticker = 'AAPL' AND sip_timestamp >= now() - INTERVAL 7 DAY
) AS last_session
SELECT
  toString(last_session) AS session,
  round(sum(updates) / 1e6, 2) AS updates_millions,
  round(sum(updates) / dateDiff('minute', min(second), max(second))) AS avg_per_minute,
  max(updates)                 AS busiest_second
FROM (
  SELECT toStartOfSecond(sip_timestamp) AS second, count() AS updates
  FROM global_markets.cache_stocks_quotes
  WHERE ticker = 'AAPL'
    AND sip_timestamp >= now() - INTERVAL 7 DAY
    AND toDate(sip_timestamp) = last_session
  GROUP BY second
)

WITH early AS (
    SELECT date AS d,
           any(toHour(toTimeZone(close, 'America/New_York')) * 60
               + toMinute(toTimeZone(close, 'America/New_York'))) AS close_min
    FROM global_markets.stocks_market_holidays
    WHERE status = 'early-close'
    GROUP BY date
),
closed AS (
    SELECT DISTINCT date AS d
    FROM global_markets.stocks_market_holidays
    WHERE status = 'closed'
)
SELECT
  q.ticker                                                                           AS ticker,
  round(avg(q.ask_price - q.bid_price) * 100, 1)                                     AS avg_spread_cents,
  round(avg(q.ask_price - q.bid_price) / avg((q.ask_price + q.bid_price) / 2) * 100, 3)  AS spread_pct_of_price,
  round(avg(q.ask_price - q.bid_price) * 100, 2)                                     AS round_trip_100_shares_dollars
FROM (
    SELECT ticker, bid_price, ask_price,
           toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS d,
           toHour(toTimeZone(sip_timestamp, 'America/New_York')) * 60
             + toMinute(toTimeZone(sip_timestamp, 'America/New_York')) AS et_min
    FROM global_markets.cache_stocks_quotes
    WHERE ticker IN ('AAPL', 'KO', 'PDEX')
      AND sip_timestamp >= now() - INTERVAL 7 DAY
      AND bid_price > 0 AND ask_price > bid_price
) AS q
LEFT JOIN early AS e ON q.d = e.d
WHERE q.d NOT IN (SELECT d FROM closed)
  AND q.et_min >= 570
  AND q.et_min < if(e.close_min > 0, e.close_min, 960)
GROUP BY q.ticker
ORDER BY q.ticker

WITH (
  SELECT max(toDate(sip_timestamp))
  FROM global_markets.cache_stocks_quotes
  WHERE ticker = 'AAPL' AND sip_timestamp >= now() - INTERVAL 7 DAY
) AS last_session
SELECT
  formatDateTime(toStartOfInterval(toTimeZone(sip_timestamp, 'America/New_York'), INTERVAL 30 MINUTE), '%H:%i') AS et_time,
  round(avg(ask_price - bid_price) * 100, 1) AS avg_spread_cents
FROM global_markets.cache_stocks_quotes
WHERE ticker = 'AAPL'
  AND sip_timestamp >= now() - INTERVAL 7 DAY
  AND toDate(sip_timestamp) = last_session
  AND bid_price > 0 AND ask_price > bid_price
GROUP BY et_time
ORDER BY et_time

SELECT
  if(type = 'TRF', 'off-exchange reporting facility (TRF)', 'public exchange') AS venue_type,
  count() AS venues,
  arrayStringConcat(arraySort(groupArray(name)), ' | ') AS venue_names
FROM global_markets.stocks_exchanges
WHERE asset_class = 'stocks' AND locale = 'us' AND type IN ('exchange', 'TRF')
GROUP BY venue_type
ORDER BY venues DESC

WITH (
  SELECT max(toDate(sip_timestamp))
  FROM global_markets.stocks_trades
  WHERE ticker = 'AAPL' AND sip_timestamp >= now() - INTERVAL 7 DAY
) AS last_session
SELECT
  if(x.name = '', concat('Unmapped venue ', toString(t.exchange)), x.name) AS venue,
  multiIf(x.type = 'TRF', 'TRF', x.type = '', 'unmapped', x.type)          AS venue_type,
  round(count() / 1000, 1)                                                 AS trades_thousands,
  round(100 * count() / sum(count()) OVER (), 1)                           AS pct_of_trades
FROM global_markets.stocks_trades AS t
LEFT JOIN (
  SELECT id, name, type FROM global_markets.stocks_exchanges
  WHERE asset_class = 'stocks' AND locale = 'us'
) AS x ON t.exchange = x.id
WHERE t.ticker = 'AAPL'
  AND t.sip_timestamp >= now() - INTERVAL 7 DAY
  AND toDate(t.sip_timestamp) = last_session
GROUP BY venue, venue_type
ORDER BY venue_type = 'TRF' DESC, trades_thousands DESC