05 调查价格变动
用明确的 ClickHouse SQL 回答四个市场运营问题。
macOS terminal: Run workshop commands in Terminal using zsh or bash.
起点
原始表和一分钟表中都有最新数据。
问题 1：当前概率是多少？
SELECT
m.token_id,
m.question,
m.outcome,
round(argMax(t.midpoint, t.event_at) * 100, 2) AS probability_percent,
max(t.event_at) AS last_update
FROM polymarket.price_ticks AS t
INNER JOIN
(
SELECT token_id, question, outcome
FROM polymarket.markets FINAL
) AS m ON m.token_id = t.token_id
WHERE t.midpoint > 0
AND t.event_at >= now() - INTERVAL 30 MINUTE
GROUP BY m.token_id, m.question, m.outcome
ORDER BY m.question, m.outcome;中间价是由最优买价和最优卖价推出的指示性概率，并不保证你能按这个价格 成交。
问题 2：哪个结果选项变动最大？
WITH now() AS current_time
SELECT
m.token_id,
m.question,
m.outcome,
round(argMaxIf(t.midpoint, t.event_at, t.event_at > current_time - INTERVAL 1 MINUTE) * 100, 2) AS now_percent,
round(argMaxIf(t.midpoint, t.event_at, t.event_at <= current_time - INTERVAL 5 MINUTE) * 100, 2) AS five_minutes_ago_percent,
round(now_percent - five_minutes_ago_percent, 2) AS move_points
FROM polymarket.price_ticks AS t
INNER JOIN
(
SELECT token_id, question, outcome
FROM polymarket.markets FINAL
) AS m ON m.token_id = t.token_id
WHERE t.midpoint > 0
AND t.event_at >= current_time - INTERVAL 15 MINUTE
GROUP BY m.token_id, m.question, m.outcome
HAVING now_percent > 0 AND five_minutes_ago_percent > 0
ORDER BY abs(move_points) DESC;如果结果为空，说明数据源还没有积累到五分钟的数据。先继续做下面的 查询，稍后再回来。
问题 3：是价差太宽，还是数据不新鲜？
SELECT
m.token_id,
m.question,
m.outcome,
round(argMax(t.best_bid, t.event_at) * 100, 2) AS bid_percent,
round(argMax(t.best_ask, t.event_at) * 100, 2) AS ask_percent,
round(ask_percent - bid_percent, 2) AS spread_points,
dateDiff('second', max(t.event_at), now()) AS age_seconds
FROM polymarket.price_ticks AS t
INNER JOIN
(
SELECT token_id, question, outcome
FROM polymarket.markets FINAL
) AS m ON m.token_id = t.token_id
WHERE t.best_bid > 0
AND t.best_ask > 0
AND t.event_at >= now() - INTERVAL 30 MINUTE
GROUP BY m.token_id, m.question, m.outcome
ORDER BY spread_points DESC;相比一个新鲜、价差很窄的市场，伴随宽价差或陈旧报价的变动更不值得 信任。
问题 4：近期成交量是否加速了？
SELECT
condition_id,
token_id,
title,
outcome,
round(sumIf(price * size, event_at >= now() - INTERVAL 5 MINUTE), 2) AS current_5m_usd,
round(sumIf(
price * size,
event_at >= now() - INTERVAL 10 MINUTE
AND event_at < now() - INTERVAL 5 MINUTE
), 2) AS previous_5m_usd,
round(current_5m_usd / greatest(previous_5m_usd, 0.01), 2) AS velocity_ratio
FROM polymarket.trades_clean
WHERE event_at >= now() - INTERVAL 10 MINUTE
GROUP BY condition_id, token_id, title, outcome
ORDER BY current_5m_usd DESC;这是以 price * size 表示的公开撮合成交量；它属于分析，不是任何
投资建议。
完成标准
至少当前概率、价差/新鲜度和成交量这三个查询能够无错误返回结果。五分钟之后， 涨跌榜查询也应该能返回数据行。
下一步：发布 Cloud 仪表板。