吞吐数据集（throughput_minute / throughput_hour）

对应 SQL：schema/clickhouse/derived/021_throughput.sql。 给 dashboard/研究提供按分钟、按小时的链吞吐指标：出块数、交易数、TPS、gas 用量、 出块间隔（均值/分位数）、用户交易占比。只依赖 {db}.blocks / {db}.transactions （sql/schema.sql），不依赖 traces（还在单独接入）或其他派生表。

字段说明Copy Anchor Link

两张表（throughput_minute 按 toStartOfMinute、throughput_hour 按 toStartOfHour 分桶）字段完全一致：

为什么 txs/tps 要剔除 type = 106（ArbOS 内部交易）Copy Anchor Link

这条 Arbitrum Orbit 链每个区块都会被 ArbOS 自动插入恰好一笔 type = 106（0x6a，即 ArbitrumInternalTx） 的内部系统交易，用于区块元数据与记账。经线上 Robinhood 链（chain id 4663）全量数据核验：

• 线上共存在 40,122,780 笔 type = 106 交易（对比总出块数 40,008,241，比率 ≈ 1:1）。
• 每笔内部交易的 from 与 to 均为 ArbOS 系统地址 0x00000000000000000000000000000000000a4b05。
• 其 gas_used 始终为 0，status = 1。
• 链上实例：
  • 区块 29400000 tx 0: 0xe7a5c9a2a2cec80ea538f4f0b497b1d2699882c325a7f160e3bd949b499ece24
  • 区块 29400001 tx 0: 0x964be9a6111e7bf335baf18b9b92ff0bf6c9bdd8d610507380aff54d5e4589ac
  • 区块 29400002 tx 0: 0xb2475a3874db7511e4d64f28996f22e400d086c2eb63d2125c40bfb67332b849

如果把这类由节点自动注入的系统交易算进“交易数”或“TPS”，每个桶都会凭空多出约等于出块数的交易（在 101 ms 出块率下约每秒多计 10 笔）， 导致 TPS 即使在全链无任何真实用户活动时也无法归零。 因此本数据集将真正的用户业务交易（txs / txs_user）与系统交易（txs_system）拆分呈现， tps 严格按真实业务交易计算，并通过 user_tx_share 展示真实交易在总交易中的占比。 gas_used_sum 统计的是全链实际消耗的 gas，系统交易虽然消耗为 0，但总和依然如实保留全部交易。

出块间隔：整数秒差与 1 秒时钟分辨率Copy Anchor Link

Arbitrum 区块时间戳仅有 1 秒分辨率，而 Robinhood 链平均出块间隔仅约 101 ms（~9.8 - 10.7 块/秒）。 因此，大量相邻区块在时间戳上处于同一秒内：

• 经统计，约 89.8% 的区块与上一区块的时间戳差值为 0 秒（interval_secs = 0），属于正常出块特征，非时钟故障。
• 间隔使用 dateDiff('second', lagInFrame(toNullable(timestamp)) OVER (ORDER BY number), timestamp) 计算。
• 为了防止跨桶边界处丢失上一区块的时间戳，物化视图在查询源表时显式多取一个周期（分钟表多 1 分钟，小时表多 1 小时）的缓冲数据， 使桶内第一个区块能正确关联到前一桶末尾区块的时间戳；最终 GROUP BY 前再剔除缓冲周期，确保统计窗口严格对齐。

为什么用可刷新物化视图（不是 insert-trigger MV）Copy Anchor Link

与 004_daily_chain_stats.sql 遵循相同正确性准则： blocks 和 transactions 底层均为 ReplacingMergeTree(version, is_deleted)，只有 FINAL WHERE is_deleted=0 才是权威状态。 普通的 insert-trigger 物化视图在底层去重之前、每次物理 INSERT 时触发 SELECT：

1. Backfill resume 重叠：历史补算或服务重启时的重叠插入会导致 insert-trigger MV 重复累加。
2. Reorg tombstones：链发生微小重组时，被回滚的块或交易会被追加写入 is_deleted = 1 的新版本，insert-trigger MV 会将 tombstone 误当做新事件或无法自动撤销已计入的数据。

本数据集采用 ClickHouse 的可刷新物化视图（REFRESH EVERY）：

• mv_throughput_minute：REFRESH EVERY 1 MINUTE RANDOMIZE FOR 20 SECOND APPEND，滚动计算最近 15 个已闭合分钟（LOOKBACK_MINUTES=15）。
• mv_throughput_hour：REFRESH EVERY 1 HOUR OFFSET 2 MINUTE RANDOMIZE FOR 10 MINUTE APPEND，滚动计算最近 6 个已闭合小时（LOOKBACK_HOURS=6）。

目标表使用 ReplacingMergeTree(refreshed_at)（而非 AggregatingMergeTree，因 ClickHouse 26.x 要求后者非主键列必须为 AggregateFunction 状态，普通整数列会报错），配合 APPEND 模式：每次刷新向目标表追加新计算的版本，在读取或后台合并时通过 FINAL 自动折叠到最新 refreshed_at 行，完全天然幂等并自愈。

安装与补算Copy Anchor Link

1. 执行 021_throughput.sql（替换 {db} 为目标库，如 robinhood）：

   Copy Text

   sed 's/{db}/robinhood/g' schema/clickhouse/derived/021_throughput.sql | clickhouse-client --multiquery

   建表并创建两个可刷新物化视图，视图建立后会自动触发初始刷新。

2. 历史补算： 可刷新物化视图负责滚动维护当前及近期的闭合窗口（15 分钟 / 6 小时）。如需回填更早的历史数据，可直接运行以下 INSERT SELECT 模版（替换 {db} 以及目标时间区间 [window_lo, window_hi)）：

   分钟表回填模版：

   Copy Text

   INSERT INTO {db}.throughput_minute
   WITH block_intervals AS (
       SELECT
           number,
           toStartOfMinute(timestamp) AS period_start,
           dateDiff('second', lagInFrame(toNullable(timestamp)) OVER (ORDER BY number), timestamp) AS interval_secs
       FROM {db}.blocks FINAL
       WHERE is_deleted = 0
         AND timestamp >= toDateTime('{window_lo}', 'UTC') - INTERVAL 1 MINUTE
         AND timestamp < toDateTime('{window_hi}', 'UTC')
   ),
   block_bucket AS (
       SELECT
           period_start,
           count() AS blocks,
           avg(interval_secs) AS avg_block_interval_secs,
           toFloat64(quantileExact(0.5)(interval_secs)) AS p50_block_interval_secs,
           toFloat64(quantileExact(0.9)(interval_secs)) AS p90_block_interval_secs,
           toFloat64(quantileExact(0.99)(interval_secs)) AS p99_block_interval_secs
       FROM block_intervals
       WHERE period_start >= toDateTime('{window_lo}', 'UTC')
       GROUP BY period_start
   ),
   tx_bucket AS (
       SELECT
           toStartOfMinute(block_timestamp) AS period_start,
           countIf(type != 106) AS txs,
           countIf(type = 106) AS txs_system,
           sum(gas_used) AS gas_used_sum
       FROM {db}.transactions FINAL
       WHERE is_deleted = 0
         AND block_timestamp >= toDateTime('{window_lo}', 'UTC')
         AND block_timestamp < toDateTime('{window_hi}', 'UTC')
       GROUP BY period_start
   )
   SELECT
       block_bucket.period_start AS period_start,
       block_bucket.blocks AS blocks,
       coalesce(tx_bucket.txs, 0) AS txs,
       coalesce(tx_bucket.txs_system, 0) AS txs_system,
       coalesce(tx_bucket.txs, 0) / 60.0 AS tps,
       if(coalesce(tx_bucket.txs, 0) + coalesce(tx_bucket.txs_system, 0) > 0,
          coalesce(tx_bucket.txs, 0) / (coalesce(tx_bucket.txs, 0) + coalesce(tx_bucket.txs_system, 0)),
          NULL) AS user_tx_share,
       coalesce(tx_bucket.gas_used_sum, 0) AS gas_used_sum,
       block_bucket.avg_block_interval_secs AS avg_block_interval_secs,
       block_bucket.p50_block_interval_secs AS p50_block_interval_secs,
       block_bucket.p90_block_interval_secs AS p90_block_interval_secs,
       block_bucket.p99_block_interval_secs AS p99_block_interval_secs,
       now('UTC') AS refreshed_at
   FROM block_bucket
   LEFT JOIN tx_bucket ON block_bucket.period_start = tx_bucket.period_start;

   小时表回填模版：

   Copy Text

   INSERT INTO {db}.throughput_hour
   WITH block_intervals AS (
       SELECT
           number,
           toStartOfHour(timestamp) AS period_start,
           dateDiff('second', lagInFrame(toNullable(timestamp)) OVER (ORDER BY number), timestamp) AS interval_secs
       FROM {db}.blocks FINAL
       WHERE is_deleted = 0
         AND timestamp >= toDateTime('{window_lo}', 'UTC') - INTERVAL 1 HOUR
         AND timestamp < toDateTime('{window_hi}', 'UTC')
   ),
   block_bucket AS (
       SELECT
           period_start,
           count() AS blocks,
           avg(interval_secs) AS avg_block_interval_secs,
           toFloat64(quantileExact(0.5)(interval_secs)) AS p50_block_interval_secs,
           toFloat64(quantileExact(0.9)(interval_secs)) AS p90_block_interval_secs,
           toFloat64(quantileExact(0.99)(interval_secs)) AS p99_block_interval_secs
       FROM block_intervals
       WHERE period_start >= toDateTime('{window_lo}', 'UTC')
       GROUP BY period_start
   ),
   tx_bucket AS (
       SELECT
           toStartOfHour(block_timestamp) AS period_start,
           countIf(type != 106) AS txs,
           countIf(type = 106) AS txs_system,
           sum(gas_used) AS gas_used_sum
       FROM {db}.transactions FINAL
       WHERE is_deleted = 0
         AND block_timestamp >= toDateTime('{window_lo}', 'UTC')
         AND block_timestamp < toDateTime('{window_hi}', 'UTC')
       GROUP BY period_start
   )
   SELECT
       block_bucket.period_start AS period_start,
       block_bucket.blocks AS blocks,
       coalesce(tx_bucket.txs, 0) AS txs,
       coalesce(tx_bucket.txs_system, 0) AS txs_system,
       coalesce(tx_bucket.txs, 0) / 3600.0 AS tps,
       if(coalesce(tx_bucket.txs, 0) + coalesce(tx_bucket.txs_system, 0) > 0,
          coalesce(tx_bucket.txs, 0) / (coalesce(tx_bucket.txs, 0) + coalesce(tx_bucket.txs_system, 0)),
          NULL) AS user_tx_share,
       coalesce(tx_bucket.gas_used_sum, 0) AS gas_used_sum,
       block_bucket.avg_block_interval_secs AS avg_block_interval_secs,
       block_bucket.p50_block_interval_secs AS p50_block_interval_secs,
       block_bucket.p90_block_interval_secs AS p90_block_interval_secs,
       block_bucket.p99_block_interval_secs AS p99_block_interval_secs,
       now('UTC') AS refreshed_at
   FROM block_bucket
   LEFT JOIN tx_bucket ON block_bucket.period_start = tx_bucket.period_start;

3. 手动刷新物化视图命令：

   Copy Text

   SYSTEM REFRESH VIEW {db}.mv_throughput_minute;
   SYSTEM REFRESH VIEW {db}.mv_throughput_hour;

验证方法与实测结果Copy Anchor Link

1. 本地 Scratch 数据库实测对照验证Copy Anchor Link

基于本地 ClickHouse（c4_throughput_scratch 包含区块 72057983 - 72062482，共 4500 块、27888 笔交易）进行全量对照验证。

区间 1：2026-09-25 07:45:00 UTCCopy Anchor Link

• 衍生表查询（throughput_minute FINAL WHERE period_start = '2026-09-25 07:45:00'）：
  • blocks: 586
  • txs (txs_user): 2,763
  • txs_system: 586
  • tps: 46.05
  • user_tx_share: 0.8250 (2763 / 3349)
  • gas_used_sum: 584,970,391
  • avg_block_interval_secs: 0.1024 s
  • p50: 0 s, p90: 1 s, p99: 1 s
• 源表直接 FINAL GROUP BY 校验：
  • SELECT count() FROM c4_throughput_scratch.blocks FINAL WHERE is_deleted=0 AND toStartOfMinute(timestamp)='2026-09-25 07:45:00' -> 586
  • SELECT countIf(type != 106), countIf(type = 106), sum(gas_used) FROM c4_throughput_scratch.transactions FINAL WHERE is_deleted=0 AND toStartOfMinute(block_timestamp)='2026-09-25 07:45:00' -> 2763, 586, 584970391
  • Python 独立核算出块间隔：586 块中 526 块间隔为 0s、60 块间隔为 1s，均值 60/586 = 0.102389s，p50=0, p90=1, p99=1。
  • 结果：100% 精确一致。

区间 2：2026-09-25 07:46:00 UTCCopy Anchor Link

• 衍生表查询（throughput_minute FINAL WHERE period_start = '2026-09-25 07:46:00'）：
  • blocks: 594
  • txs (txs_user): 3,438
  • txs_system: 594
  • tps: 57.30
  • user_tx_share: 0.8527 (3438 / 4032)
  • gas_used_sum: 849,525,560
  • avg_block_interval_secs: 0.1010 s
  • p50: 0 s, p90: 1 s, p99: 1 s
• 源表直接 FINAL GROUP BY 校验：
  • SELECT count() FROM c4_throughput_scratch.blocks FINAL WHERE is_deleted=0 AND toStartOfMinute(timestamp)='2026-09-25 07:46:00' -> 594
  • SELECT countIf(type != 106), countIf(type = 106), sum(gas_used) FROM c4_throughput_scratch.transactions FINAL WHERE is_deleted=0 AND toStartOfMinute(block_timestamp)='2026-09-25 07:46:00' -> 3438, 594, 849525560
  • Python 独立核算出块间隔：594 块中 534 块间隔为 0s、60 块间隔为 1s，均值 60/594 = 0.101010s，p50=0, p90=1, p99=1。
  • 结果：100% 精确一致。

2. 线上库生产数据小时桶校验Copy Anchor Link

线上库（robinhood 真实数据）小时桶 2026-09-25 08:00:00 UTC 对照校验：

• 物化视图聚合计算：
  • blocks: 35,707
  • txs: 257,780
  • txs_system: 35,811
  • tps: 71.6056
  • user_tx_share: 0.8780
  • gas_used_sum: 53,260,027,647
  • avg_block_interval_secs: 0.1008 s
  • p50: 0 s, p90: 1 s, p99: 1 s
• 源表直接 FINAL GROUP BY 校验：
  • SELECT count() FROM robinhood.blocks FINAL WHERE is_deleted = 0 AND toStartOfHour(timestamp) = '2026-09-25 08:00:00' -> 35707
  • SELECT countIf(type != 106), countIf(type = 106), sum(gas_used) FROM robinhood.transactions FINAL WHERE is_deleted = 0 AND toStartOfHour(block_timestamp) = '2026-09-25 08:00:00' -> 257780, 35811, 53260027647
• 结果：100% 精确一致。