questdb

QuestDB 技能

如何使用此技能

重要 — 尽量减少往返：

• 不要探索库的源代码（cryptofeed、questdb 等）
• 不要检查库的版本或验证回调签名
• 不要读取已安装的包文件来“了解 API”
• 不要验证基础设施（Docker 容器、Grafana 健康状态）是否正在运行——相信用户的提示
• 不要单独启动 02_ingest.py——03_dashboard.py 会启动它并自动验证数据
• 对此技能文件已涵盖的主题，不要额外读取参考文件
• 适用时应读取参考文件（例如，身份验证请查看 enterprise.md，复杂面板请查看 grafana-advanced.md）
• 对于直接明了的构建任务，不要使用任务跟踪（TaskCreate/TaskUpdate）
• 不要添加 sleep 命令来等待数据或检查后台进程（部署脚本会处理）
• 03_dashboard.py 启动采集进程后，不要按 Ctrl+C、重启或重新启动该进程
• 不要将 VWAP、Bollinger 或 RSI 放在独立的时间序列面板中——它们是 OHLC 蜡烛图面板上的 refID
• 不要省略或清空 fieldConfig.overrides——它们会将 RSI 放在右侧 Y 轴（0-100%），并将价差放在右侧轴上。没有它们，不同的刻度会把图表压得平平的。
• 不要将仪表板刷新间隔设为 "5s"——默认值是 "250ms"
• 在浏览器中打开仪表板 URL 时，务必在 URL 后追加 ?refresh=250ms。否则，Grafana 会忽略 JSON 中的刷新设置。
• cryptofeed、QuestDB 数据写入和 Grafana 的所有 API 细节均如下所示——请原样使用
• 以下模板已解决常见的 Python 环境问题： uvloop： 创建新虚拟环境时，pip 可能会安装一个在 Python 3.10+ 上崩溃的 uvloop 版本 （RuntimeError: no current event loop）。仅在新建的虚拟环境中，于 pip install 后卸载它。 切勿从用户现有的虚拟环境中卸载 uvloop——他们的环境已经可以正常使用。 macOS SSL： Homebrew Python 缺少系统 CA 证书。没有 certifi，任何出站 HTTPS/WSS 连接（交易所 WebSocket、API 调用）都会失败。在建立网络连接的脚本顶部，务必通过 certifi 设置 SSL_CERT_FILE。 以下 pip 命令和 Python 模板已包含这些修复——请原样复制。

此技能包含可直接使用的 SQL、架构、数据写入代码和 Grafana 查询。**编写文件并运行。**典型的数据管道由 3 个文件组成 （架构设置、数据写入脚本、仪表板部署）——编写并执行这些文件即可。

执行场景

选择与用户请求匹配的场景。运行所示的确切命令。 以下每个 Bash 代码块都是一次 Bash 工具调用。立即执行——不要 纠结于 Shell 语法。同一步骤中的代码块可以作为并行工具调用运行。

场景 A — 从头开始搭建所有内容（Docker + venv + 流水线）： 使用单独的并行 Bash 工具调用（每次调用一条命令）来并行执行：

docker run -d --name questdb -p 9000:9000 -p 9009:9009 -p 8812:8812 questdb/questdb:latest

docker run -d --name grafana -p 3000:3000 -e GF_INSTALL_PLUGINS=questdb-questdb-datasource -e GF_SECURITY_ADMIN_PASSWORD=admin -e GF_DASHBOARDS_MIN_REFRESH_INTERVAL=250ms grafana/grafana:latest

python3 -m venv .venv

然后按顺序执行：

.venv/bin/pip install -q cryptofeed questdb 'psycopg[binary]' requests numpy certifi

.venv/bin/pip uninstall uvloop -y 2>/dev/null

接着等待两个服务启动（每个服务各用一次 Bash 调用）：

curl -sf --retry 30 --retry-delay 1 --retry-all-errors -o /dev/null "http://localhost:9000/exec?query=SELECT+1"

curl -sf --retry 30 --retry-delay 1 --retry-all-errors -o /dev/null http://localhost:3000/api/health

然后配置数据源：

curl -s -X POST http://localhost:3000/api/datasources -u admin:admin -H "Content-Type: application/json" -d '{"name":"QuestDB","type":"questdb-questdb-datasource","access":"proxy","jsonData":{"server":"host.docker.internal","port":8812,"username":"admin","tlsMode":"disable","timeout":"120","queryTimeout":"60"},"secureJsonData":{"password":"quest"}}'

数据源字段（QuestDB Grafana 插件使用 jsonData，而不是标准的 url 字段）：

• server：仅主机名，不含端口或协议（例如 host.docker.internal）
• port：整数，与 server 分开填写（例如 8812）
• tlsMode：本地 Docker 必须设为 "disable"——省略时默认启用 TLS，会导致连接失败
• username/password：QuestDB 默认值为 admin/quest 然后编写 3 个文件（架构、数据摄取、仪表板）并运行它们。

场景 B — 容器正在运行，需要 venv：

python3 -m venv .venv

.venv/bin/pip install -q cryptofeed questdb 'psycopg[binary]' requests numpy certifi

.venv/bin/pip uninstall uvloop -y 2>/dev/null

然后编写 3 个文件并运行它们。

场景 C — 用户提供了现有的 venv 路径： 按原样信任用户的 venv。不要运行 pip install 或卸载任何内容。 编写 3 个文件并运行它们。所有 python 命令都使用用户的 venv 路径。

场景 D — 所有内容都已在运行，只需要流水线脚本： 编写 3 个文件并运行它们。无需设置基础设施。

references/ 目录中还有其他参考资料——仅当用户的请求超出本文档涵盖的范围时才阅读：

• common-mistakes.md — 错误模式 → 正确的 QuestDB 对应写法（编写下方未提供模板的新 SQL 时阅读）
• grafana-advanced.md — 仅在需要 Plotly 订单簿深度图或下方仪表板模板未涵盖的高级功能时阅读
• indicators.md — 用户要求 OHLC/VWAP/Bollinger/RSI 之外的指标时阅读（MACD、ATR、Stochastic、OBV、Drawdown、Keltner、Donchian 等）
• cookbook.md — QuestDB 官方 cookbook 配方索引：金融（滑点、markout、IS、ECN 评分卡、last-look、指标、成交量/订单流、风险）和时间序列模式（FILL 策略、会话窗口、每个分区最新 N 条、稀疏传感器联接）。用户要求执行质量/TCA 指标、指定指标或此处未提供的时间序列模式时阅读。
• sql-grammar.md — QuestDB 关键字、函数（按类别划分并附签名）、数据类型和运算符的完整索引。**需要确认“QuestDB 是否有函数 X？”或查询函数的正确签名时阅读。**包含原生金融内置函数（vwap()、twap()、spread()、mid()、wmid()、l2price()）、统计函数、数组/矩阵运算等。
• enterprise.md — QuestDB 使用身份验证、HTTPS、令牌或 ACL 时阅读（开源版跳过）

关键规则

**QuestDB 不是 PostgreSQL。**它支持用于查询的 PostgreSQL 线协议，但拥有自己的时间序列操作 SQL 扩展。标准 PostgreSQL 模式（如 time_bucket()、DISTINCT ON、HAVING 和 generate_series()）并不存在。

实时文档访问

**每个 QuestDB 文档页面都有一个位于相同 URL、末尾附加 .md 的纯 Markdown 版本。**使用 curl 获取这些版本，可快速、清晰地读取，并且对 LLM 友好——无需清理 HTML，也无需抓取。遇到此处未涵盖的关键字、语法细节或边界情况时，优先获取权威文档，不要猜测。

# Any doc page — just append .md to the URL
curl -sH "Accept: text/markdown" "https://questdb.com/docs/query/sql/horizon-join.md"
curl -sH "Accept: text/markdown" "https://questdb.com/docs/cookbook/sql/finance.md"

# Full documentation index for LLMs — lists every page with its .md URL
curl -s "https://questdb.com/docs/llms.txt"

不知道确切路径时，使用 llms.txt 查找页面。它是包含所有文档和 cookbook 配方的权威目录。

---

SQL 参考

CREATE TABLE

CREATE TABLE IF NOT EXISTS trades (
    ts TIMESTAMP,
    symbol SYMBOL,
    side SYMBOL,
    price DOUBLE,
    amount DOUBLE
) TIMESTAMP(ts) PARTITION BY DAY WAL
DEDUP UPSERT KEYS(ts, symbol);

关键规则：

• TIMESTAMP(col) 指定时间列——SAMPLE BY、LATEST ON、ASOF JOIN 都需要此列
• 对所有重复出现的字符串（股票代码、类别、状态码）使用 SYMBOL 类型——速度远快于 VARCHAR
• PARTITION BY DAY|MONTH|YEAR|HOUR——选择最常用的查询粒度
• WAL 支持并发写入（ILP 摄取所必需）
• DEDUP UPSERT KEYS(ts, symbol) 根据（时间戳、股票代码）去重——支持幂等摄取

列类型： BOOLEAN、BYTE、SHORT、INT、LONG、FLOAT、DOUBLE、CHAR、VARCHAR、 SYMBOL、TIMESTAMP、DATE、LONG256、GEOHASH、UUID、IPv4、DOUBLE[]、FLOAT[]、INT[]、 LONG[]、SHORT[]、UUID[]——以及 DOUBLE[][] 等二维数组

ALTER TABLE：

ALTER TABLE trades ADD COLUMN exchange SYMBOL;
ALTER TABLE trades DROP COLUMN exchange;
ALTER TABLE trades RENAME COLUMN amount TO qty;
ALTER TABLE trades ALTER COLUMN exchange SET TYPE SYMBOL;

SAMPLE BY（时序聚合）

SAMPLE BY 是 QuestDB 的时间分桶功能，可替代 GROUP BY time_bucket()。 需要指定一个时间戳。

SELECT ts, symbol,
    first(price) AS open, max(price) AS high,
    min(price) AS low, last(price) AS close,
    sum(amount) AS volume
FROM trades
WHERE ts > '2025-01-01'
SAMPLE BY 1h;

关键规则：

• 有效间隔：1s、5s、1m、15m、1h、1d、1M（月）
• ALIGN TO CALENDAR 会将分桶对齐到时钟边界（这是默认设置，因此可以省略）
• FILL(PREV | NULL | LINEAR | value) 用于填补空缺——写在 SAMPLE BY 之后
• 在 Grafana 中，除非用户指定了其他柱状图大小，否则默认使用 SAMPLE BY 5s
• first()、last() 返回每个时间桶内的第一个／最后一个值
• 使用 count_distinct(col)，而不是 COUNT(DISTINCT col)
• 不支持 HAVING——请使用子查询：SELECT * FROM (... SAMPLE BY ...) WHERE volume > 1000

LATEST ON（每组最新值）

返回每组中最新的一行。可替代 DISTINCT ON／ROW_NUMBER()。

SELECT * FROM trades
WHERE ts > dateadd('h', -1, now())
LATEST ON ts PARTITION BY symbol;

规则：

• LATEST ON ts 必须引用指定的时间戳
• 必须使用 PARTITION BY
• 添加 WHERE 时间筛选条件以提升性能

联接

QuestDB 提供一系列专用的时序联接。请根据问题选择合适的联接——当 HORIZON 或 WINDOW 更符合惯用写法时，默认使用 ASOF JOIN 是常见的 LLM 错误。

模式	用途
时间点匹配：“交易时的值”	ASOF JOIN（或使用 LIST (0) 的 HORIZON JOIN）
严格匹配交易时间之前的数据	LT JOIN
按时间顺序交错合并两个时间序列	SPLICE JOIN
交易时的价格以及 ±N 偏移处的价格（标记损益、IS 分解、不利选择）	HORIZON JOIN
聚合每笔交易周围 ±N 时间窗口内的报价（滚动统计、报价密度、交易前/后的均值）	WINDOW JOIN
每个外层行的子查询：每组前 N 条、动态筛选器、逐行 SAMPLE BY / LATEST ON / ASOF	LATERAL JOIN

-- ASOF JOIN: nearest right row with ts ≤ left.ts
SELECT * FROM trades ASOF JOIN quotes ON (symbol);

-- LT JOIN: strictly ts < left.ts
SELECT * FROM trades LT JOIN quotes ON (symbol);

-- SPLICE JOIN: merge two time series interleaved
SELECT * FROM trades SPLICE JOIN quotes ON (symbol);

-- HORIZON JOIN: ASOF at a grid of time offsets, one parallel pass.
-- For each trade, computes metrics at -1m, -30s, 0, 30s, 1m, ...
-- Supports multiple right-hand tables (only the last carries RANGE/LIST/AS).
SELECT h.offset / 1_000_000_000 AS horizon_sec, t.symbol,
       avg((m.best_bid + m.best_ask) / 2) AS avg_mid
FROM fx_trades t
HORIZON JOIN market_data m ON (symbol)
RANGE FROM -1m TO 5m STEP 30s AS h
WHERE t.timestamp IN '$yesterday'
ORDER BY t.symbol, horizon_sec;

-- Or with explicit offsets for implementation shortfall:
--   LIST (0, 1800s) AS h  →  at_fill and at_30m columns (via PIVOT)

-- WINDOW JOIN: aggregate right-table rows within a ±time window around each left row.
-- EXCLUDE PREVAILING omits the last-known value before the window start.
SELECT t.symbol, t.timestamp,
       avg(c.bid_price) AS avg_bid_pm5s,
       count()          AS num_quotes
FROM fx_trades t
WINDOW JOIN core_price c ON (t.symbol = c.symbol)
    RANGE BETWEEN 5 seconds PRECEDING AND 5 seconds FOLLOWING
    EXCLUDE PREVAILING
WHERE t.symbol = 'EURUSD' AND t.timestamp IN '$now-1h..$now';

-- LATERAL JOIN: subquery evaluated per outer row, can reference outer columns.
-- Classic top-N per group — two largest fills for each order:
SELECT o.id, o.desk, t.qty
FROM orders o
JOIN LATERAL (
    SELECT qty FROM fills
    WHERE order_id = o.id
    ORDER BY qty DESC LIMIT 2
) t
ORDER BY o.id, t.qty DESC;

联接规则：

• 所有时序联接（ASOF、LT、SPLICE、HORIZON、WINDOW）都要求两个表都具有指定时间戳。时间戳会自动匹配。
• ON (symbol) 会匹配键列；两个表必须使用相同的列名。如果名称不同，请使用 ON (t.sym = q.sym)。
• 标准 INNER JOIN 和 LEFT JOIN 也适用于非时序联接。
• HORIZON JOIN：同一层级不能包含其他联接类型，不能使用 SAMPLE BY，内部不能包含窗口函数，WHERE 只能筛选左表。其他情况请用子查询包装。
• WINDOW JOIN：不能与 GROUP BY、窗口函数或其他联接类型组合。请先将联接包装在 CTE 中，再在外层查询中进行聚合/联接。
• LATERAL JOIN：仅支持 INNER、LEFT 或 CROSS 变体。必须使用括号括起子查询。在子查询中，可以引用外层列，并使用 SAMPLE BY、LATEST ON、ASOF JOIN、窗口函数、UNION ALL 等。

如需查看 HORIZON / WINDOW / LATERAL 的完整语法（包括混合精度时间戳、动态窗口边界和多表 HORIZON 模式），请获取：

• curl -sH "Accept: text/markdown" "https://questdb.com/docs/query/sql/horizon-join.md"
• curl -sH "Accept: text/markdown" "https://questdb.com/docs/query/sql/window-join.md"
• curl -sH "Accept: text/markdown" "https://questdb.com/docs/query/sql/lateral-join.md"

如需现成的执行分析查询（滑点、标记损益、执行差额、最后观察、ECN 评分卡）——这些查询均基于 HORIZON JOIN——请参阅 references/cookbook.md 或 https://questdb.com/docs/cookbook/sql/finance.md。

UNNEST（数组和 JSON 数组转为行）

QuestDB 支持使用 UNNEST 将数组或以 VARCHAR 存储的 JSON 数组展开为行。它出现在 FROM 子句中，行为类似一张表。

-- Native array: expand the sizes at level 2 of each order book row
SELECT t.symbol, u.vol
FROM market_data t, UNNEST(t.asks[2]) u(vol)
WHERE t.timestamp IN '$now-1m..$now' AND t.symbol = 'EURUSD';

-- WITH ORDINALITY gives a 1-based level index (resets per input row)
SELECT m.symbol, u.vol, u.level
FROM market_data m, UNNEST(m.asks[2]) WITH ORDINALITY u(vol, level)
WHERE m.timestamp IN '$now-1m..$now';

-- JSON array (VARCHAR) with typed columns — great for ingesting API payloads
SELECT u.trade_id, u.price, u.size, u.side, u.time
FROM UNNEST(
    payload_varchar::VARCHAR
    COLUMNS(trade_id LONG, price DOUBLE, size DOUBLE, side VARCHAR, time TIMESTAMP)
) u;

主要规则：

• UNNEST 必须位于 FROM 中（或位于 CROSS JOIN / 逗号之后），不能放在 SELECT 中
• 原生数组：目前仅支持 DOUBLE[]。对 DOUBLE[][] 使用 UNNEST 会生成 DOUBLE[] 行——再接一个 UNNEST 即可完全展开。
• JSON：COLUMNS(name TYPE, ...) 用于提取带类型的字段。支持的类型：BOOLEAN、SHORT、INT、LONG、DOUBLE、VARCHAR、TIMESTAMP。无效 JSON / NULL / 空字符串都会生成 0 行（不会报错）。
• ordinality 是保留字——请为其设置别名（u(val, pos)）或加引号。

完整参考：curl -sH "Accept: text/markdown" "https://questdb.com/docs/query/sql/unnest.md"

窗口函数

-- ROWS frame (count-based)
AVG(close) OVER (ORDER BY ts ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS sma20

-- CUMULATIVE shorthand (QuestDB extension, equivalent to ROWS UNBOUNDED PRECEDING)
SUM(volume) OVER (ORDER BY ts CUMULATIVE) AS running_total

-- PARTITION BY
LAG(close) OVER (PARTITION BY symbol ORDER BY ts) AS prev_close

-- EMA (exponential moving average — QuestDB extension)
avg(price, 'period', 14) OVER (ORDER BY ts) AS ema14

支持：ROW_NUMBER()、RANK()、DENSE_RANK()、LAG()、LEAD()、 FIRST_VALUE()、AVG()、SUM()、MIN()、MAX()、COUNT()

重要：stddev_samp() 可能无法在窗口帧中使用。要计算标准差，请手动计算：sqrt(avg(x*x) - avg(x)^2)——请参阅下方的布林带查询。

物化视图

由新数据触发的自动更新增量聚合：

CREATE MATERIALIZED VIEW IF NOT EXISTS candles_5s AS (
    SELECT ts, symbol,
        first(price) AS open, max(price) AS high,
        min(price) AS low, last(price) AS close,
        sum(amount) AS volume
    FROM trades SAMPLE BY 5s
) PARTITION BY DAY;

规则：

• 源查询必须使用 SAMPLE BY
• 只允许使用聚合函数（不能使用 WHERE、JOIN 或窗口函数）
• 使用 IF NOT EXISTS 避免重复运行时报错
• 使用级联视图实现多分辨率：trades → 5s → 1m → 1h（请参阅下方的架构设计）
• 使其失效/重建：ALTER MATERIALIZED VIEW candles_5s INVALIDATE

视图（非物化）

QuestDB 还支持常规视图——由 SELECT 语句定义的虚拟表，并在每次查询时进行计算。对于无需增量预计算的可复用查询抽象，可使用常规视图。

CREATE VIEW IF NOT EXISTS latest_prices AS (
    SELECT * FROM trades
    LATEST ON ts PARTITION BY symbol
);

-- Query it like a table
SELECT * FROM latest_prices WHERE symbol = 'BTC-USDT';

-- Modify or drop
ALTER VIEW latest_prices AS (SELECT ...);   -- redefine
COMPILE VIEW latest_prices;                  -- recompile after schema changes
DROP VIEW latest_prices;

需要增量 SAMPLE BY 汇总时，使用物化视图。其他情况（最新值查找、筛选子集、连接抽象）均使用常规视图。

时间戳筛选 — TICK 语法（首选）

时间筛选始终优先使用 TICK，而非 dateadd() / BETWEEN。 TICK（Temporal Interval Calendar Kit）是 QuestDB 用于表示时间间隔的声明式语法。它可读性更强，能生成经过优化的区间扫描，支持时区和工作日，并可表达复杂的多区间模式；否则，这些模式需要使用 UNION ALL 或应用程序端逻辑。

语法顺序： date [T time] @timezone #dayFilter ;duration

-- Date variables: resolve at query time
WHERE ts IN '$today'                     -- full day (midnight to midnight)
WHERE ts IN '$yesterday'                 -- previous full day
WHERE ts IN '$tomorrow'                  -- next full day

-- Ranges with $now (point-in-time, microsecond precision)
WHERE ts IN '$now - 1h..$now'            -- last hour
WHERE ts IN '$now - 30m..$now'           -- last 30 minutes
WHERE ts IN '$now - 5bd..$now'           -- last 5 business days (skips weekends)

-- Duration suffix: extends forward from a point
WHERE ts IN '$now;1h'                    -- 1 hour starting now (forward)
WHERE ts IN '2025-01-15T09:30;6h30m'     -- NYSE trading session

-- Imprecise dates: month-level and year-level (expand to all days)
WHERE ts IN '[2025-01]'                  -- all of January (no need for [01..31])
WHERE ts IN '[2025]T09:30'              -- all of 2025 at 09:30
WHERE ts IN '2025-02T09:30'             -- bare imprecise (brackets optional alone)

-- Bracket expansion: generates multiple intervals
WHERE ts IN '2025-01-[10..15]'           -- days 10 through 15
WHERE ts IN '2025-01-[5,10..12,20]'      -- specific days + ranges
WHERE ts IN '2025-[01,06]-[10,15]'       -- Cartesian: Jan+Jun x 10th+15th
WHERE ts IN '2025-[01..03]T09:30'        -- month-level range: all days Jan-Mar

-- Day-of-week filters
WHERE ts IN '[2025-01]#workday'          -- weekdays in January
WHERE ts IN '[2025-01]#weekend'          -- weekends in January
WHERE ts IN '[2025-01]#Mon,Wed,Fri'      -- specific days in January

-- Timezone-aware (handles DST)
WHERE ts IN '2025-01-15T09:30@America/New_York;6h30m'

-- Combined: workdays at 09:30 New York time for all of January
WHERE ts IN '[2025-01]T09:30@America/New_York#workday;6h30m'

-- Mixed-precision date lists
WHERE ts IN '[2025-01-15, 2025-02]T09:30'  -- one day + full month

-- Time lists: multiple intraday windows
WHERE ts IN '2025-01-15T[09:00,14:30];1h'  -- two 1h windows on the same day

-- Date lists (brackets required for lists)
WHERE ts IN '[$today, $yesterday, 2025-01-15]'

-- ISO week dates
WHERE ts IN '2025-W01-[1..5]T09:00;8h'  -- Mon-Fri of week 1

主要规则：

• 日期变量不区分大小写（$TODAY = $today）
• $today/$yesterday/$tomorrow 生成全天区间；$now 生成时间点（添加范围或时长后才有用）
• 算术单位：y M w d bd h m s T（毫秒）u（微秒）n（纳秒）。 bd（工作日）仅用于算术，不可用于时长。 区分大小写：M = 月，m = 分钟，T = 毫秒。
• 精度不足的日期： YYYY-MM 扩展为该月的所有日期，YYYY 扩展为该年的所有日期。'[2025-01]T09:30#workday;6h30m' 可替代冗长的 '2025-01-[01..31]T09:30#workday;6h30m'。单独使用时方括号可省略（'2025-02T09:30' = '[2025-02]T09:30'），在列表中则必须使用。
• 以下情况必须使用方括号：列表（[$today, $yesterday]）、带后缀的范围（[$now - 2h..$now]@America/New_York）。独立变量、普通范围（$now - 2h..$now）和不带方括号的精度不足日期（2025-01T09:30）可省略方括号。
• 方括号展开后产生的重叠区间会自动合并。
• 交易所日历（Enterprise）： 使用 #XNYS、#XLON 等（ISO 10383 MIC 代码）代替 #workday，以根据实际交易日程筛选；系统会自动处理节假日、提前收市和午休。 WHERE ts IN '[2025-01]#XNYS' 仅返回 NYSE 交易时段。 参见：curl -sH "Accept: text/markdown" "https://questdb.com/docs/query/operators/exchange-calendars.md"

完整参考：curl -sH "Accept: text/markdown" "https://questdb.com/docs/query/operators/tick.md"

DECLARE（变量）

DECLARE @start := '2025-01-01T00:00:00Z', @end := now();
SELECT * FROM trades WHERE ts BETWEEN @start AND @end;

SQL 执行顺序

FROM → WHERE → SAMPLE BY → SELECT → LIMIT （使用 SAMPLE BY 时无需 GROUP BY。完全没有 HAVING。）

主要函数

时间戳： now()、systimestamp()、dateadd('unit', n, ts)、 datediff('unit', ts1, ts2)、to_timestamp('str', 'fmt')、 timestamp_floor('unit', ts)、timestamp_ceil('unit', ts)、 hour(ts)、day_of_week(ts)、week_of_year(ts)、year(ts)、month(ts)

字符串： concat('a', 'b')、left(s, n)、right(s, n)、length(s)、 starts_with(s, prefix)、lcase(s)、ucase(s)、replace(s, old, new)、 split_part(s, delim, idx)、regexp_replace(s, pattern, replacement)

聚合： first(x)、last(x)、count_distinct(x)、sum(x)、avg(x)、 min(x)、max(x)、haversine_dist_deg(lat1, lon1, lat2, lon2)

数组： array_cum_sum(arr) — 对数组元素求累积和

---

数据摄取

ILP（InfluxDB Line Protocol）——主要方法

所有高吞吐量数据写入都使用 ILP。切勿对流式数据使用 INSERT INTO。

from questdb.ingress import Sender, TimestampNanos
import numpy as np

# Open Source (no auth). For Enterprise: read references/enterprise.md Quick Start
# (admin creates service account + token via REST → ingestion script uses token)
conf = "tcp::addr=localhost:9009;protocol_version=2;"

with Sender.from_conf(conf) as sender:
    sender.row(
        'trades',
        symbols={'symbol': 'BTC-USDT', 'side': 'buy'},
        columns={'price': 42000.50, 'amount': 1.5},
        at=TimestampNanos.now()
    )
    sender.flush()

关键模式：

• Sender.from_conf() 不会建立连接——请使用 with 上下文管理器或调用 sender.establish()
• SYMBOL 列使用 symbols={}，其他列使用 columns={}
• 数组必须是 np.float64 numpy 数组，不能是 Python 列表
• TCP 配置必须设置 protocol_version=2 才支持数组
• HTTP 传输：http::addr=localhost:9000;
• TCP 传输：tcp::addr=localhost:9009;

二维数组写入（订单簿）：

sender.row(
    'order_book',
    symbols={'symbol': 'BTC-USDT'},
    columns={
        'bids': [
            np.array([41999.0, 41998.5, 41998.0], dtype=np.float64),  # prices [1]
            np.array([2.1, 5.3, 10.0], dtype=np.float64),            # sizes  [2]
        ],
        'asks': [
            np.array([42001.0, 42001.5, 42002.0], dtype=np.float64),
            np.array([1.8, 4.2, 8.5], dtype=np.float64),
        ],
    },
    at=TimestampNanos.now()
)

**其他客户端库：**Go、Java、Rust、Node.js、C/C++、.NET——获取 ingestion/clients/{language}

INSERT INTO——仅用于低写入量场景

INSERT INTO trades (ts, symbol, side, price, amount)
VALUES ('2025-02-09T10:00:00.000000Z', 'BTC-USDT', 'buy', 42000.50, 1.5);

通过 PG Wire 查询

import psycopg as pg
conn = pg.connect("user=admin password=quest host=localhost port=8812 dbname=qdb")

HTTP REST API

• 查询：GET http://localhost:9000/exec?query=URL_ENCODED_SQL
• 对所有特殊字符进行 URL 编码，包括括号：( = %28， ) = %29。在 curl 命令中使用未编码的 () 会触发安全提示。 示例：count_distinct%28symbol%29，而不是 count_distinct(symbol)
• 企业版：请参阅 references/enterprise.md 中的快速入门
• exec 端点不支持 POST——仅使用 GET
• 返回 JSON：{ "columns": [...], "dataset": [...] }

---

架构设计

关键原则：

• 每个时序表都需要指定时间戳
• 对所有重复出现的字符串（代码、类别、状态码）使用 SYMBOL
• 按最常见的查询粒度进行分区
• 并发写入工作负载使用 WAL 表
• 使用 DEDUP 实现幂等写入

金融市场数据流水线架构（可直接使用）

CREATE TABLE IF NOT EXISTS trades (
    ts TIMESTAMP,
    symbol SYMBOL,
    side SYMBOL,
    price DOUBLE,
    amount DOUBLE
) TIMESTAMP(ts) PARTITION BY DAY WAL
DEDUP UPSERT KEYS(ts, symbol);

CREATE TABLE IF NOT EXISTS orderbook (
    ts TIMESTAMP,
    symbol SYMBOL,
    bid_prices DOUBLE[],
    bid_sizes DOUBLE[],
    ask_prices DOUBLE[],
    ask_sizes DOUBLE[]
) TIMESTAMP(ts) PARTITION BY DAY WAL
DEDUP UPSERT KEYS(ts, symbol);

-- Cascading materialized views: trades → 5s → 1m → 1h
CREATE MATERIALIZED VIEW IF NOT EXISTS candles_5s AS (
    SELECT ts, symbol,
        first(price) AS open, max(price) AS high,
        min(price) AS low, last(price) AS close,
        sum(amount) AS volume
    FROM trades SAMPLE BY 5s
) PARTITION BY DAY;

CREATE MATERIALIZED VIEW IF NOT EXISTS candles_1m AS (
    SELECT ts, symbol,
        first(open) AS open, max(high) AS high,
        min(low) AS low, last(close) AS close,
        sum(volume) AS volume
    FROM candles_5s SAMPLE BY 1m
) PARTITION BY DAY;

CREATE MATERIALIZED VIEW IF NOT EXISTS candles_1h AS (
    SELECT ts, symbol,
        first(open) AS open, max(high) AS high,
        min(low) AS low, last(close) AS close,
        sum(volume) AS volume
    FROM candles_1m SAMPLE BY 1h
) PARTITION BY MONTH;

以下 Grafana 查询适用于这些确切的表名和视图名。

---

演示与示例数据

基础设施设置命令位于上方的执行场景部分。 选择匹配的场景，运行相应的确切命令，然后继续此处。

实时加密货币数据流（cryptofeed + QuestDB）

请勿探索 cryptofeed 源代码或检查其版本。所需内容都在这里。 原样复制此摄取脚本。certifi/SSL 修复和所有导入都必不可少——请勿省略任何行：

import os
import certifi
os.environ['SSL_CERT_FILE'] = certifi.where()  # Required: macOS lacks system CA certs for HTTPS/WSS

import numpy as np
from cryptofeed import FeedHandler
from cryptofeed.exchanges import OKX
from cryptofeed.defines import TRADES, L2_BOOK
from questdb.ingress import Sender, TimestampNanos

conf = "tcp::addr=localhost:9009;protocol_version=2;"  # Enterprise: see references/enterprise.md

async def trade_cb(t, receipt_timestamp):
    with Sender.from_conf(conf) as sender:
        sender.row(
            'trades',
            symbols={'symbol': t.symbol, 'side': t.side},
            columns={'price': float(t.price), 'amount': float(t.amount)},
            at=TimestampNanos(int(t.timestamp * 1e9))
        )
        sender.flush()

async def book_cb(book, receipt_timestamp):
    bids = book.book['bid']
    asks = book.book['ask']
    # Sort: bids descending by price, asks ascending
    bid_prices = sorted(bids.keys(), reverse=True)[:25]
    ask_prices = sorted(asks.keys())[:25]
    with Sender.from_conf(conf) as sender:
        sender.row(
            'orderbook',
            symbols={'symbol': book.symbol},
            columns={
                'bid_prices': np.array([float(p) for p in bid_prices], dtype=np.float64),
                'bid_sizes':  np.array([float(bids[p]) for p in bid_prices], dtype=np.float64),
                'ask_prices': np.array([float(p) for p in ask_prices], dtype=np.float64),
                'ask_sizes':  np.array([float(asks[p]) for p in ask_prices], dtype=np.float64),
            },
            at=TimestampNanos(int(book.timestamp * 1e9))
        )
        sender.flush()

SYMBOLS = ['BTC-USDT', 'ETH-USDT', 'SOL-USDT']  # add more as needed

f = FeedHandler()
f.add_feed(OKX(
    symbols=SYMBOLS,
    channels=[TRADES, L2_BOOK],
    callbacks={TRADES: trade_cb, L2_BOOK: book_cb}
))
f.run()

cryptofeed API 参考（完整——已通过 v2.4.x 测试——请勿阅读源代码进行验证）：

• t.symbol、t.side（'buy'/'sell'）、t.price、t.amount、t.timestamp（浮点数形式的 epoch 秒）
• t.price 和 t.amount 是 Decimal 类型——传给 QuestDB Sender 前请用 float() 转换
• book.book['bid'] 和 book.book['ask'] 是字典：{Decimal(price): Decimal(size), ...}
• book.symbol、book.timestamp（浮点数形式的 epoch 秒）
• 交易所：OKX、Coinbase、Binance、Kraken、Bybit 等
• 通道：TRADES、L2_BOOK、L3_BOOK、TICKER、CANDLES、OPEN_INTEREST、FUNDING、LIQUIDATIONS
• 交易对格式采用交易所原生格式：OKX 使用 'BTC-USDT'，Coinbase 使用 'BTC-USD'
• **Python 兼容性：**避免使用 X | None 类型提示（需要 3.10+）。使用 Optional[X] 或普通赋值。
• **依赖项（全新 venv）：**运行 pip install cryptofeed questdb 'psycopg[binary]' requests numpy certifi，然后运行 pip uninstall uvloop -y（分别调用 Bash）
• **macOS SSL：**在任何出站 HTTPS/WSS 连接前，始终通过 certifi 设置 SSL_CERT_FILE（Homebrew Python 缺少系统 CA 证书）。 在 Python 代码内部设置（os.environ['SSL_CERT_FILE'] = certifi.where()）， 绝不要使用 Shell 级别的 SSL_CERT_FILE=$(...) 前缀——这会触发安全提示

**性能说明：**为便于理解，上面的示例会在每个回调中打开一个 Sender。 生产环境中，请使用共享 Sender 并定期刷新：

sender = Sender.from_conf(conf)
sender.establish()

async def trade_cb(t, receipt_timestamp):
    sender.row('trades', symbols={...}, columns={...}, at=...)
    # flush periodically or use auto_flush_interval in conf string

执行顺序（必须遵守——不得调整）：

1. 运行 01_schema.py——创建表和视图
2. 运行 03_dashboard.py——此脚本会自动启动数据摄取、等待数据到达，然后部署仪表板并打开浏览器。请勿单独启动 02_ingest.py。

• 数据摄取进程启动后，请勿按 Ctrl+C、重启或以其他方式操作该进程。
• **完成标准：**浏览器中已打开仪表板，且数据摄取进程仍在运行。

运行说明：

• cryptofeed 会将日志输出到 stderr，而不是 stdout。stdout 为空并不表示失败。
• 在仪表板部署脚本末尾使用 open（macOS）或 xdg-open（Linux）， 以自动启动浏览器。请在 URL 中加入 ?refresh=250ms， 使仪表板以正确的刷新率打开：

  url = f"{GRAFANA_URL}{resp.json()['url']}?refresh=250ms&from=now-5m&to=now"
  subprocess.run(["open" if sys.platform == "darwin" else "xdg-open", url])
  

QuestDB 演示实例

QuestDB 的在线演示实例 demo.questdb.io 包含外汇和加密货币数据集。 获取架构参考：curl -sH "Accept: text/markdown" "https://questdb.com/docs/cookbook/demo-data-schema/"

---

Grafana 集成

QuestDB 提供专用的 Grafana 数据源插件（questdb-questdb-datasource）。 通过 8812 端口使用 PG wire 连接。

数据源 API 配置（QuestDB 插件使用 jsonData，而非标准的 url 字段）：

• jsonData.server：仅主机名——不含端口或协议（例如 host.docker.internal）
• jsonData.port：整数，与 server 分开设置（例如 8812）
• jsonData.tlsMode：本地 Docker 使用 "disable"——省略此项会默认启用 TLS，导致连接失败
• jsonData.username + secureJsonData.password：QuestDB 默认凭据为 admin/quest
• 请勿使用 url 字段——QuestDB 插件会忽略它

下面的仪表板部署模板包含创建或查找数据源的逻辑。请使用它。

关键宏：

• $__timeFilter(ts)——Grafana 时间选择器中的时间范围
• 默认 SAMPLE BY 间隔：5s。只有用户指定其他柱状图大小时才更改。

符号下拉变量：SELECT DISTINCT symbol FROM trades

如需了解高级 Grafana 模式（多查询面板、坐标轴覆盖、重复面板、订单簿深度图），请参阅 references/grafana-advanced.md。

可直接使用的 Grafana 查询

用于常见财务面板的完整、经过测试的 SQL。可直接使用。

Grafana 查询模式规则： Grafana 需要 time 列，因此最终的 SELECT 会将 ts AS time 设为别名。切勿在将 ts AS time 设为别名的同一个 SELECT 中使用 OVER() 子句——应将所有窗口函数放入 CTE 中，此时 ts 仍为 ts，然后仅在最终 SELECT 中设置别名。以下所有查询均遵循此模式。

OHLC 蜡烛图（基于原始交易）：

SELECT ts AS time,
    first(price) AS open, max(price) AS high,
    min(price) AS low, last(price) AS close,
    sum(amount) AS volume
FROM trades
WHERE $__timeFilter(ts) AND symbol = '$symbol'
SAMPLE BY 5s;

OHLC 蜡烛图（基于物化视图——较长时间范围下速度更快）：

SELECT ts AS time,
    first(open) AS open, max(high) AS high,
    min(low) AS low, last(close) AS close,
    sum(volume) AS volume
FROM candles_1m
WHERE $__timeFilter(ts) AND symbol = '$symbol'
SAMPLE BY 5m;

VWAP（累计成交量加权平均价）：

WITH ohlc AS (
    SELECT ts, symbol,
        first(price) AS open, max(price) AS high,
        min(price) AS low, last(price) AS close,
        sum(amount) AS volume
    FROM trades
    WHERE $__timeFilter(ts) AND symbol = '$symbol'
    SAMPLE BY 5s
),
vwap AS (
    SELECT ts, close,
        sum((high + low + close) / 3 * volume) OVER (ORDER BY ts CUMULATIVE)
        / sum(volume) OVER (ORDER BY ts CUMULATIVE) AS vwap
    FROM ohlc
)
SELECT ts AS time, close, vwap FROM vwap;

布林带（20 周期 SMA ± 2σ）： 使用手动计算方差，与窗口帧中的 stddev_samp 相比兼容性更好：

WITH ohlc AS (
    SELECT ts, symbol,
        first(price) AS open, max(price) AS high,
        min(price) AS low, last(price) AS close,
        sum(amount) AS volume
    FROM trades
    WHERE $__timeFilter(ts) AND symbol = '$symbol'
    SAMPLE BY 5s
),
stats AS (
    SELECT ts, close,
        AVG(close) OVER (
            ORDER BY ts ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
        ) AS sma20,
        AVG(close * close) OVER (
            ORDER BY ts ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
        ) AS avg_close_sq
    FROM ohlc
)
SELECT ts AS time, close, sma20,
    sma20 + 2 * sqrt(avg_close_sq - (sma20 * sma20)) AS upper_band,
    sma20 - 2 * sqrt(avg_close_sq - (sma20 * sma20)) AS lower_band
FROM stats;

RSI（14 周期，SMA 平滑）： 此 Grafana 版本使用 ROWS BETWEEN 实现 SMA，已在生产环境中验证。 如需独立的 EMA 平滑 RSI，请参阅 references/indicators.md。

WITH ohlc AS (
    SELECT ts, symbol,
        first(price) AS open, max(price) AS high,
        min(price) AS low, last(price) AS close,
        sum(amount) AS volume
    FROM trades
    WHERE $__timeFilter(ts) AND symbol = '$symbol'
    SAMPLE BY 5s
),
changes AS (
    SELECT ts, close,
        close - LAG(close) OVER (ORDER BY ts) AS change
    FROM ohlc
),
gains_losses AS (
    SELECT ts, close,
        CASE WHEN change > 0 THEN change ELSE 0 END AS gain,
        CASE WHEN change < 0 THEN ABS(change) ELSE 0 END AS loss
    FROM changes
),
avg_gl AS (
    SELECT ts, close,
        AVG(gain) OVER (ORDER BY ts
            ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) AS avg_gain,
        AVG(loss) OVER (ORDER BY ts
            ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) AS avg_loss
    FROM gains_losses
)
SELECT ts AS time,
    CASE WHEN avg_loss = 0 THEN 100
         ELSE 100 - (100 / (1 + avg_gain / NULLIF(avg_loss, 0)))
    END AS rsi
FROM avg_gl;

注意：对于 EMA 平滑 RSI（标准方式），将 AVG...ROWS BETWEEN 替换为 avg(gain, 'period', 14) OVER (ORDER BY ts)（QuestDB 原生 EMA）。 对于 Wilder 平滑（α=1/N），使用 avg(gain, 'period', 27)。

VWAP + 布林带组合（单个面板，多条序列）：

WITH ohlc AS (
    SELECT ts, symbol,
        first(price) AS open, max(price) AS high,
        min(price) AS low, last(price) AS close,
        sum(amount) AS volume
    FROM trades
    WHERE $__timeFilter(ts) AND symbol = '$symbol'
    SAMPLE BY 5s
),
indicators AS (
    SELECT ts, close,
        AVG(close) OVER (
            ORDER BY ts ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
        ) AS sma20,
        AVG(close * close) OVER (
            ORDER BY ts ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
        ) AS avg_close_sq,
        sum((high + low + close) / 3 * volume) OVER (ORDER BY ts CUMULATIVE)
        / sum(volume) OVER (ORDER BY ts CUMULATIVE) AS vwap
    FROM ohlc
)
SELECT ts AS time, close, sma20,
    sma20 + 2 * sqrt(avg_close_sq - (sma20 * sma20)) AS upper_band,
    sma20 - 2 * sqrt(avg_close_sq - (sma20 * sma20)) AS lower_band,
    vwap
FROM indicators;

通过 API 部署仪表板

完整可用的部署脚本。此仪表板 JSON 已经过测试并可正常运行 ——所有面板都应严格照此结构复制。请勿拆分或重新排列面板。

面板布局规则： VWAP、布林带和 RSI 必须始终作为额外的 refID（B、C、D）， 叠加在 OHLC 蜡烛图面板上，并设置 includeAllFields: true。 面板类型必须为 candlestick，而非 timeseries。为这些指标创建单独的 timeseries 面板是错误的——它们应放在蜡烛图面板上。 查询模式： refId A 返回 OHLC 列（open、high、low、close、volume）。 refId B、C、D 各自只返回 ts AS time + 对应的指标列——不包含 OHLC。 Grafana 会使用共享的时间列将它们叠加显示。如果指标查询也返回 OHLC 列，蜡烛图渲染就会出错。

覆盖设置是必须的，不是装饰。 将 RSI（0-100）与价格（约 97,000）放在同一 Y 轴上， 会导致蜡烛图不可见。将价差（约 0.01）与买入价/卖出价（约 97,000）放在同一坐标轴上， 会导致价差线不可见。请原样复制模板中的 fieldConfig.overrides 数组——尤其是 RSI 的右侧坐标轴（0-100%）和价差的右侧坐标轴。

仪表板默认设置（请原样复制）：

• "refresh": "250ms" ——不是 "5s"。使用 250ms 刷新是为了支持实时数据。
• "liveNow": false
• "time": {"from": "now-5m", "to": "now"}
• "timepicker": {"refresh_intervals": ["250ms", "500ms", "1s", "5s", "10s", "30s", "1m", "5m", "15m", "30m", "1h", "2h", "1d"]}
• 打开 URL 时，在末尾附加 ?refresh=250ms&from=now-5m&to=now

重要：Grafana 的 min_refresh_interval 默认值为 5s。 除非在启动 Grafana 时设置 GF_DASHBOARDS_MIN_REFRESH_INTERVAL=250ms，否则服务器端会阻止低于 1 秒的 刷新间隔（250ms、500ms、1s）。上方“执行场景”中的 Docker 运行命令 已设置此值。 如果未设置，仪表板 JSON 中的 "refresh": "250ms" 和 URL 中的 ?refresh=250ms 都会被静默忽略，下拉菜单也不会显示低于 5 秒的选项。

每个面板查询的目标结构：

{
    "refId": "A",
    "datasource": {"uid": "QUESTDB_UID", "type": "questdb-questdb-datasource"},
    "format": 1,
    "rawSql": "SELECT ts AS time, ... WHERE $__timeFilter(ts) ..."
}

重要： "format" 必须是整数 1，不能是字符串 "table"。QuestDB Grafana 插件使用 Go 整数枚举（sqlutil.FormatQueryOption）。字符串值 会导致 json: cannot unmarshal string into Go struct field Query.format。Grafana 的 JSON 导出结果会显示 "table"（字符串），但 API POST 要求使用 1（整数）。

import json, subprocess, sys, os, requests, time

QUESTDB_URL = "http://localhost:9000"
GRAFANA_URL = "http://localhost:3000"
GRAFANA_AUTH = ("admin", "admin")
SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))

# --- Start ingestion if not already running ---
already_running = False
try:
    resp = requests.get(f"{QUESTDB_URL}/exec",
        params={"query": "SELECT count() FROM trades WHERE ts > dateadd('s', -10, now())"})
    already_running = resp.json()["dataset"][0][0] > 0
except Exception:
    pass

if already_running:
    print("Ingestion already running — skipping launch")
else:
    ingest_log = open(os.path.join(SCRIPT_DIR, "ingest.log"), "w")
    ingest_proc = subprocess.Popen(
        [sys.executable, "-u", os.path.join(SCRIPT_DIR, "02_ingest.py")],
        stdout=ingest_log, stderr=ingest_log,
        start_new_session=True,  # detach from parent — survives Ctrl+C and script exit
    )
    print(f"Ingestion started: PID {ingest_proc.pid}")

    # --- Gate: wait for data before deploying dashboard ---
    for i in range(15):  # up to 15s
        time.sleep(1)
        if ingest_proc.poll() is not None:
            ingest_log.close()
            print("ERROR: Ingestion process died. Log:")
            print(open(os.path.join(SCRIPT_DIR, "ingest.log")).read())
            sys.exit(1)
        try:
            resp = requests.get(f"{QUESTDB_URL}/exec", params={"query": "SELECT count() FROM trades"})
            count = resp.json()["dataset"][0][0]
            if count > 0:
                print(f"Data gate passed: {count} rows in trades (waited {i+1}s)")
                break
        except Exception:
            pass
    else:
        print("ERROR: No data after 15s. Check ingest.log")
        sys.exit(1)

# --- Create or find QuestDB datasource ---
# QuestDB plugin uses jsonData fields, NOT the standard url field.
# server = hostname only (no port, no protocol), port = integer, tlsMode = "disable"
ds_list = requests.get(f"{GRAFANA_URL}/api/datasources", auth=GRAFANA_AUTH).json()
existing = [d for d in ds_list if d["type"] == "questdb-questdb-datasource"]
if existing:
    questdb_uid = existing[0]["uid"]
else:
    resp = requests.post(f"{GRAFANA_URL}/api/datasources", auth=GRAFANA_AUTH,
        json={
            "name": "QuestDB", "type": "questdb-questdb-datasource", "access": "proxy",
            "jsonData": {
                "server": "host.docker.internal",
                "port": 8812,
                "username": "admin",
                "tlsMode": "disable",
                "timeout": "120",
                "queryTimeout": "60",
            },
            "secureJsonData": {"password": "quest"},
        })
    questdb_uid = resp.json()["datasource"]["uid"]

DS_REF = {"uid": questdb_uid, "type": "questdb-questdb-datasource"}

dashboard = {
    "dashboard": {
        "title": "Crypto Real-Time Market Data",
        "uid": "crypto-realtime",
        "timezone": "browser",
        "refresh": "250ms",  # Default refresh rate — do NOT change to 5s
        "liveNow": False,
        "schemaVersion": 38,
        "time": {"from": "now-5m", "to": "now"},
        "timepicker": {
            "refresh_intervals": ["250ms", "500ms", "1s", "5s", "10s", "30s", "1m", "5m", "15m", "30m", "1h", "2h", "1d"],
        },
        "tags": ["crypto", "questdb", "realtime"],
        "templating": {"list": [{
            "name": "symbol", "type": "query", "label": "Symbol",
            "query": "SELECT DISTINCT symbol FROM trades ORDER BY symbol;",
            "datasource": DS_REF,
            "refresh": 1, "sort": 1,
            "current": {"text": "BTC-USDT", "value": "BTC-USDT"},
        }]},
        "panels": [
            {
                "id": 1, "type": "candlestick",
                "title": "OHLC - $symbol",
                "gridPos": {"h": 10, "w": 24, "x": 0, "y": 0},
                "datasource": DS_REF,
                "fieldConfig": {
                    "defaults": {"custom": {"axisBorderShow": False, "axisPlacement": "auto"}},
                    "overrides": [{"matcher": {"id": "byName", "options": "volume"},
                                   "properties": [{"id": "custom.axisPlacement", "value": "hidden"}]}],
                },
                "options": {
                    "mode": "candles+volume", "includeAllFields": False,
                    "candleStyle": "candles", "colorStrategy": "open-close",
                    "colors": {"up": "green", "down": "red"},
                    "fields": {"open": "open", "high": "high", "low": "low",
                               "close": "close", "volume": "volume"},
                },
                "targets": [{
                    "refId": "A", "datasource": DS_REF, "format": 1,
                    "rawSql": "SELECT ts AS time, first(price) AS open, max(price) AS high, min(price) AS low, last(price) AS close, sum(amount) AS volume FROM trades WHERE $__timeFilter(ts) AND symbol = '$symbol' SAMPLE BY 5s;",
                }],
            },
            {
                "id": 2, "type": "candlestick",
                "title": "OHLC + Indicators - $symbol",
                "gridPos": {"h": 10, "w": 24, "x": 0, "y": 10},
                "datasource": DS_REF,
                "fieldConfig": {
                    "defaults": {"custom": {"axisBorderShow": False, "axisPlacement": "auto"}},
                    # CRITICAL: these overrides are NOT cosmetic — without them:
                    # - RSI (0-100) shares Y-axis with price (~97000) → candlesticks appear flat
                    # - Volume distorts the price axis
                    # - Bollinger bands have no visual distinction from price lines
                    # Copy ALL overrides below exactly. NEVER use "overrides": []
                    "overrides": [
                        {"matcher": {"id": "byName", "options": "volume"},
                         "properties": [{"id": "custom.axisPlacement", "value": "hidden"}]},
                        {"matcher": {"id": "byName", "options": "vwap"},
                         "properties": [{"id": "color", "value": {"fixedColor": "orange", "mode": "fixed"}},
                                        {"id": "custom.lineWidth", "value": 2}]},
                        {"matcher": {"id": "byName", "options": "sma20"},
                         "properties": [{"id": "color", "value": {"fixedColor": "yellow", "mode": "fixed"}},
                                        {"id": "custom.lineWidth", "value": 2}]},
                        {"matcher": {"id": "byName", "options": "upper_band"},
                         "properties": [{"id": "color", "value": {"fixedColor": "light-blue", "mode": "fixed"}},
                                        {"id": "custom.lineStyle", "value": {"fill": "dash", "dash": [10, 10]}},
                                        {"id": "custom.fillBelowTo", "value": "lower_band"},
                                        {"id": "custom.fillOpacity", "value": 8}]},
                        {"matcher": {"id": "byName", "options": "lower_band"},
                         "properties": [{"id": "color", "value": {"fixedColor": "light-blue", "mode": "fixed"}},
                                        {"id": "custom.lineStyle", "value": {"fill": "dash", "dash": [10, 10]}}]},
                        {"matcher": {"id": "byFrameRefID", "options": "D"},
                         "properties": [{"id": "color", "value": {"fixedColor": "purple", "mode": "fixed"}},
                                        {"id": "custom.axisPlacement", "value": "right"},
                                        {"id": "min", "value": 0}, {"id": "max", "value": 100},
                                        {"id": "unit", "value": "percent"}]},
                    ],
                },
                "options": {
                    "mode": "candles+volume", "includeAllFields": True,
                    "candleStyle": "candles", "colorStrategy": "open-close",
                    "colors": {"up": "green", "down": "red"},
                    "fields": {"open": "open", "high": "high", "low": "low",
                               "close": "close", "volume": "volume"},
                },
                # --- Overlay pattern: refId A = OHLC columns, B/C/D = one indicator each ---
                # A MUST return: open, high, low, close, volume (for the candlestick)
                # B/C/D MUST return: ts AS time + indicator columns ONLY (not OHLC)
                # Grafana overlays B/C/D on A using the shared time column
                "targets": [
                    {   # A: OHLC + volume — the candlestick data
                        "refId": "A", "datasource": DS_REF, "format": 1,
                        "rawSql": "SELECT ts AS time, first(price) AS open, max(price) AS high, min(price) AS low, last(price) AS close, sum(amount) AS volume FROM trades WHERE $__timeFilter(ts) AND symbol = '$symbol' SAMPLE BY 5s;",
                    },
                    {   # B: VWAP only — overlays on candlestick
                        "refId": "B", "datasource": DS_REF, "format": 1,
                        "rawSql": "WITH ohlc AS (SELECT ts, symbol, first(price) AS open, max(price) AS high, min(price) AS low, last(price) AS close, sum(amount) AS volume FROM trades WHERE $__timeFilter(ts) AND symbol = '$symbol' SAMPLE BY 5s), vwap AS (SELECT ts, sum((high + low + close) / 3 * volume) OVER (ORDER BY ts CUMULATIVE) / sum(volume) OVER (ORDER BY ts CUMULATIVE) AS vwap FROM ohlc) SELECT ts AS time, vwap FROM vwap;",
                    },
                    {   # C: Bollinger Bands only (sma20, upper_band, lower_band)
                        "refId": "C", "datasource": DS_REF, "format": 1,
                        "rawSql": "WITH ohlc AS (SELECT ts, symbol, first(price) AS open, max(price) AS high, min(price) AS low, last(price) AS close, sum(amount) AS volume FROM trades WHERE $__timeFilter(ts) AND symbol = '$symbol' SAMPLE BY 5s), stats AS (SELECT ts, close, AVG(close) OVER (ORDER BY ts ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS sma20, AVG(close * close) OVER (ORDER BY ts ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS avg_close_sq FROM ohlc) SELECT ts AS time, sma20, sma20 + 2 * sqrt(avg_close_sq - (sma20 * sma20)) AS upper_band, sma20 - 2 * sqrt(avg_close_sq - (sma20 * sma20)) AS lower_band FROM stats;",
                    },
                    {   # D: RSI only — right Y-axis 0-100%
                        "refId": "D", "datasource": DS_REF, "format": 1,
                        "rawSql": "WITH ohlc AS (SELECT ts, symbol, first(price) AS open, max(price) AS high, min(price) AS low, last(price) AS close, sum(amount) AS volume FROM trades WHERE $__timeFilter(ts) AND symbol = '$symbol' SAMPLE BY 5s), changes AS (SELECT ts, close, close - LAG(close) OVER (ORDER BY ts) AS change FROM ohlc), gains_losses AS (SELECT ts, close, CASE WHEN change > 0 THEN change ELSE 0 END AS gain, CASE WHEN change < 0 THEN ABS(change) ELSE 0 END AS loss FROM changes), avg_gl AS (SELECT ts, close, AVG(gain) OVER (ORDER BY ts ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) AS avg_gain, AVG(loss) OVER (ORDER BY ts ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) AS avg_loss FROM gains_losses) SELECT ts AS time, CASE WHEN avg_loss = 0 THEN 100 ELSE 100 - (100 / (1 + avg_gain / NULLIF(avg_loss, 0))) END AS rsi FROM avg_gl;",
                    },
                ],
            },
            {
                "id": 3, "type": "timeseries",
                "title": "Bid-Ask Spread - $symbol",
                "gridPos": {"h": 6, "w": 24, "x": 0, "y": 20},
                "datasource": DS_REF,
                # CRITICAL: overrides REQUIRED — without them spread (~0.01) is invisible
                # next to bid/ask (~97000). Spread MUST use right Y-axis.
                "fieldConfig": {
                    "defaults": {"custom": {"lineWidth": 1, "fillOpacity": 15, "spanNulls": True, "pointSize": 1}},
                    "overrides": [
                        {"matcher": {"id": "byName", "options": "spread"},
                         "properties": [{"id": "color", "value": {"fixedColor": "red", "mode": "fixed"}},
                                        {"id": "custom.axisPlacement", "value": "right"}]},
                        {"matcher": {"id": "byName", "options": "best_bid"},
                         "properties": [{"id": "color", "value": {"fixedColor": "green", "mode": "fixed"}}]},
                        {"matcher": {"id": "byName", "options": "best_ask"},
                         "properties": [{"id": "color", "value": {"fixedColor": "orange", "mode": "fixed"}}]},
                    ],
                },
                "targets": [{
                    "refId": "A", "datasource": DS_REF, "format": 1,
                    "rawSql": "SELECT ts AS time, avg(ask_prices[1] - bid_prices[1]) AS spread, avg(bid_prices[1]) AS best_bid, avg(ask_prices[1]) AS best_ask FROM orderbook WHERE $__timeFilter(ts) AND symbol = '$symbol' SAMPLE BY 5s;",
                }],
            },
        ],
    },
    "overwrite": True,
}

resp = requests.post(f"{GRAFANA_URL}/api/dashboards/db", auth=GRAFANA_AUTH,
                     headers={"Content-Type": "application/json"}, json=dashboard)
url = f"{GRAFANA_URL}{resp.json().get('url', '')}?refresh=250ms&from=now-5m&to=now"
print(f"Dashboard: {resp.status_code} - {url}")

# Open in browser — data gate already confirmed rows are flowing
subprocess.run(["open" if sys.platform == "darwin" else "xdg-open", url])

始终通过 UID 和类型引用数据源，切勿使用显示名称。 请勿添加额外的 sleep 命令——数据门控循环会处理所有等待。