交互图谱数据集（interaction_edges_daily / token_flow_edges_daily）

• 对应 SQL：schema/clickhouse/derived/023_interaction_graph.sql
• 依赖：{db}.transactions（见 sql/schema.sql）与 {db}.erc20_transfers（见 schema/clickhouse/derived/001_erc20_transfers.sql）
• 不依赖：{db}.traces（顶层调用图谱仅依赖外层交易与代币转账事件，无需依赖尚未全量覆盖的内部调用 traces）

1. 数据集概述与应用场景Copy Anchor Link

交互图谱数据集按 已收盘的 UTC 自然日 聚合链上地址之间的互动关系，产出两张核心聚合表：

1. interaction_edges_daily：基于顶层交易（transactions）的外层调用边（from -> to），记录每日调用频次、原生代币金额、Gas 消耗与成功笔数。
2. token_flow_edges_daily：基于 ERC20 代币转账（erc20_transfers）的资金流向边（token, from -> to），记录每日转账笔数与累计代币金额。

主要应用场景：

• 地址核心交易对手（Top Counterparties）分析：快速检索某一钱包或合约在指定日期范围内的最大交互对手、收发金额及频次。
• 热门合约分析（Most Unique Callers）：统计每日拥有最多独立调用者（unique EOAs / callers）的合约，辅助生态活跃度与协议流量排名。
• 多跳图谱遍历（Multi-hop Neighbours）：基于预聚合的边进行 2-hop、3-hop 关联地址挖掘（如洗钱路径追踪、关联账户聚合、流动性传导链路）。

2. 口径定义与伪发送方（Pseudo-Sender）排除规则Copy Anchor Link

为保证图谱真实反映用户与合约间的实际交互行为，必须排除协议底层自动化生成的伪交易和系统哨兵账户。

在 Arbitrum Orbit 架构下，transactions.type 的分类与处理规则如下（基于生产 ClickHouse 真实数据实测统计）：

其余过滤细节：

• 合约创建交易（to IS NULL）：没有明确的接收方，不构成 from -> to 的交互关系，通过 to IS NOT NULL 过滤。合约地址创建在 contract_creations 另行追踪。
• 自我调用（from = to）：非排除类型的合约调用或代理模式自我交互予以保留，反映真实的链上操作。

3. 正确性设计：闭区间日与幂等 Refresh (Option b)Copy Anchor Link

为什么不使用 Insert-Trigger 物化视图Copy Anchor Link

{db}.transactions 与 {db}.erc20_transfers 均为 ReplacingMergeTree(version, is_deleted) 表：

1. Backfill 断点续跑重叠：回填任务分片重叠时会重复插入相同版本的数据。
2. 链重组（Reorg）处理：重组发生时会写入相同主键、更高版本、且 is_deleted = 1 的 tombstone 记录。

普通 insert-trigger MV 在物理 INSERT 触发时按行累加计算，无法感知后续的去重折叠，更无法在 tombstone 插入时撤回此前累加的计数，会导致严重的重复计数与数据失真。

采用的方案：可刷新物化视图（Refreshable MV）+ 历史回填脚本Copy Anchor Link

1. 闭区间聚合：每天定时重算已经完全结束并收盘的自然日（toDate(block_timestamp) < today()）。Arbitrum Orbit 链的状态窗口在 15 分钟以内，已收盘的 UTC 日不存在深度重组改写的可能。
2. FINAL 去重读取：查询统一读取 {db}.transactions FINAL 与 {db}.erc20_transfers FINAL，带 is_deleted = 0 过滤，确保重组与重复插入在聚合前被完全折叠。
3. 滚动窗口自愈与重组撤回（Trailing Lookback Window & Retraction）：每天调度任务覆盖 [today() - 3, today()) 的 3 天闭区间窗口。为防止链重组（Reorg）导致整条边因关联交易全部被标记删除而在目标表中遗留虚假边（Phantom Edges），物化视图通过 FULL OUTER JOIN 将当天最新重算结果与目标表中已有边（FINAL WHERE day >= today() - 3 AND day < today() AND is_deleted = 0，配置 SETTINGS join_use_nulls = 1）进行比对：
   • 当某条边在底层数据中完全消失时（current_state.day IS NULL），视图自动发射一条撤回记录（tx_count = 0, ..., is_deleted = 1，带有最新的 refreshed_at）。
   • 目标表基于 ReplacingMergeTree(refreshed_at, is_deleted)，在 FINAL 查询与后台合并时自动折叠并剔除已删除边，实现自动撤回与自愈。
   • 窗口外历史自愈边界：此自动撤回机制覆盖滚动窗口范围。对于超过 3 天的历史数据，若发生极深重组或回填口径变更，需通过分段重算或回填脚本覆盖。
4. ReplacingMergeTree(refreshed_at, is_deleted)：目标表使用 ReplacingMergeTree(refreshed_at, is_deleted) 引擎，同一天、同一边的多次刷新以最新的 refreshed_at 行覆盖旧行，天然具备幂等性与墓碑折叠能力。

4. 表结构设计与数据安全性Copy Anchor Link

4.1 表定义Copy Anchor Link

-- 顶层调用交互边每日表
CREATE TABLE IF NOT EXISTS {db}.interaction_edges_daily
(
    day             Date,
    from            FixedString(20),
    to              FixedString(20),
    tx_count        UInt64,
    value_sum       UInt256,
    gas_used_sum    UInt64,
    success_count   UInt64,
    refreshed_at    DateTime('UTC'),
    is_deleted      UInt8 DEFAULT 0
)
ENGINE = ReplacingMergeTree(refreshed_at, is_deleted)
PARTITION BY toYYYYMM(day)
ORDER BY (day, from, to);

-- ERC20 代币资金流向边每日表
CREATE TABLE IF NOT EXISTS {db}.token_flow_edges_daily
(
    day             Date,
    token           FixedString(20),
    from            FixedString(20),
    to              FixedString(20),
    transfers       UInt64,
    amount_sum      UInt256,
    refreshed_at    DateTime('UTC'),
    is_deleted      UInt8 DEFAULT 0
)
ENGINE = ReplacingMergeTree(refreshed_at, is_deleted)
PARTITION BY toYYYYMM(day)
ORDER BY (day, token, from, to);

4.2 资金安全性（No Floats Rule）与口径说明Copy Anchor Link

所有金额与转账数值均采用 UInt256 存储，杜绝使用浮点数或浮点计算函数（如 pow(), Float64）。查询若需换算展示单位，应在展示层根据代币精度（decimals）进行格式化或使用 toDecimal128。

• interaction_edges_daily.value_sum：为该交互边上所有顶层交易携带的链上原生代币金额之和（以 wei 为单位的 UInt256，与 transactions.value 相同）。包含因合约逻辑 revert (status = 0) 而未实际转移的调用尝试；成功执行的交易次数由 success_count 准确标定。

4.3 分区设计Copy Anchor Link

相比每日单行的汇总表，图谱边表属于高基数表（每日在数百万级别），因此采用 PARTITION BY toYYYYMM(day) 按月分区，既能有效控制单个 partition 的 merge 压力与元数据开销，也为后续按月制定 TTL 或冷热存储策略预留空间。

5. 预期每日数据规模（生产环境实测）Copy Anchor Link

对生产 ClickHouse 进行只读实测（选取完全回填收盘的典型工作日 2026-07-13）：

• transactions 原始数据：
  • 全天交易总笔数：7,772,209 笔
  • 排除伪类型（100, 102, 105, 106）后：6,907,208 笔
  • 进一步排除合约创建（to IS NULL，9,280 笔）后：6,897,928 笔有效调用
  • 产出独立边数量：1,484,137 组 (from, to) 边/天（压缩聚合比约 4.65:1）
• erc20_transfers 原始数据：
  • 全天 ERC20 转账总笔数：15,408,520 笔
  • 涉及代币数量：31,211 种代币
  • 产出资金流向边数量：2,857,666 组 (token, from, to) 边/天（压缩聚合比约 5.39:1）

因此，预计每日两张表合计新增约 430 万 ~ 450 万行。月度存储在采用列存与 ZSTD 压缩后预期开销可控。

6. 核心查询示例Copy Anchor Link

示例 1：查询指定地址的核心交易对手（Top Counterparties）Copy Anchor Link

-- 查询地址 0xa36f7c68fce5ab7a35ceb2d1bad8af2d6405f4fa 过去 30 天内交互次数最多的对手方
SELECT
    if(from = unhex('a36f7c68fce5ab7a35ceb2d1bad8af2d6405f4fa'), hex(to), hex(from)) AS counterparty,
    sum(tx_count) AS total_txs,
    sum(value_sum) AS total_value_wei,
    sum(gas_used_sum) AS total_gas_used,
    sum(success_count) AS successful_txs
FROM robinhood.interaction_edges_daily FINAL
WHERE day >= today() - 30
  AND is_deleted = 0
  AND tx_count > 0
  AND (from = unhex('a36f7c68fce5ab7a35ceb2d1bad8af2d6405f4fa')
    OR to = unhex('a36f7c68fce5ab7a35ceb2d1bad8af2d6405f4fa'))
GROUP BY counterparty
ORDER BY total_txs DESC
LIMIT 10;

示例 2：统计某日独立调用用户最多的合约（Top Contracts by Unique Callers）Copy Anchor Link

-- 统计 2026-07-13 拥有最多独立调用者（EOA）的合约
SELECT
    hex(to) AS contract_address,
    uniqExact(from) AS unique_callers,
    sum(tx_count) AS total_tx_count,
    sum(success_count) AS successful_tx_count
FROM robinhood.interaction_edges_daily FINAL
WHERE day = '2026-07-13'
  AND is_deleted = 0
  AND tx_count > 0
GROUP BY contract_address
ORDER BY unique_callers DESC
LIMIT 10;

示例 3：2-Hop 邻居网络遍历（2-Hop Neighbours）Copy Anchor Link

-- 从目标地址出发，查询经过 1 跳节点扩展到的 2 跳邻居地址及连接边数
WITH hop1 AS (
    SELECT DISTINCT to AS addr
    FROM robinhood.interaction_edges_daily FINAL
    WHERE day = '2026-07-13'
      AND is_deleted = 0
      AND tx_count > 0
      AND from = unhex('d1b43007ab773d87feab705bb71160b6871aa9c1')
)
SELECT
    hex(e.to) AS hop2_neighbour,
    count() AS connecting_edges,
    sum(e.tx_count) AS total_txs
FROM robinhood.interaction_edges_daily AS e FINAL
INNER JOIN hop1 ON e.from = hop1.addr
WHERE e.day = '2026-07-13'
  AND e.is_deleted = 0
  AND e.tx_count > 0
  AND e.to != unhex('d1b43007ab773d87feab705bb71160b6871aa9c1')
GROUP BY hop2_neighbour
ORDER BY connecting_edges DESC, total_txs DESC
LIMIT 10;

示例 4：某代币的核心转账流动边（Token Flows）Copy Anchor Link

-- 查询指定代币在某日内转账金额最大的流向边
SELECT
    hex(from) AS sender,
    hex(to) AS receiver,
    sum(transfers) AS transfer_count,
    sum(amount_sum) AS total_amount
FROM robinhood.token_flow_edges_daily FINAL
WHERE day = '2026-07-13'
  AND is_deleted = 0
  AND transfers > 0
  AND token = unhex('5fc5360d0400a0fd4f2af552add042d716f1d168')
GROUP BY sender, receiver
ORDER BY total_amount DESC
LIMIT 10;

7. 安装与补算Copy Anchor Link

7.1 应用建表与物化视图Copy Anchor Link

执行 SQL 文件中的 DDL 语句：

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

如果通过 HTTP 接口执行，请将每个 CREATE TABLE 和 CREATE MATERIALIZED VIEW 拆分为单独请求发送。

7.2 历史数据一次性回填Copy Anchor Link

DDL 之后，执行文件底部的两条历史补算 SQL（以幂等方式写入当前闭区间日前的数据）：

INSERT INTO robinhood.interaction_edges_daily (day, from, to, tx_count, value_sum, gas_used_sum, success_count, refreshed_at, is_deleted)
SELECT
    toDate(block_timestamp) AS day,
    from,
    to,
    count() AS tx_count,
    sum(value) AS value_sum,
    sum(gas_used) AS gas_used_sum,
    countIf(status = 1) AS success_count,
    now('UTC') AS refreshed_at,
    0 AS is_deleted
FROM robinhood.transactions FINAL
WHERE is_deleted = 0
  AND to IS NOT NULL
  AND type NOT IN (100, 102, 105, 106)
  AND toDate(block_timestamp) < today()
GROUP BY day, from, to;

INSERT INTO robinhood.token_flow_edges_daily (day, token, from, to, transfers, amount_sum, refreshed_at, is_deleted)
SELECT
    toDate(block_timestamp) AS day,
    token,
    from,
    to,
    count() AS transfers,
    sum(amount) AS amount_sum,
    now('UTC') AS refreshed_at,
    0 AS is_deleted
FROM robinhood.erc20_transfers FINAL
WHERE is_deleted = 0
  AND toDate(block_timestamp) < today()
GROUP BY day, token, from, to;

7.3 定时刷新运维Copy Anchor Link

建好的 Materialized View 会在每日 UTC 02:00（OFFSET 2 HOUR，与 004/005 的 01:00 错峰）自动调度触发，重新计算过去 3 天的闭区间数据。也可以在必要时通过 ClickHouse 客户端手动触发即时刷新：

SYSTEM REFRESH VIEW robinhood.mv_interaction_edges_daily;
SYSTEM REFRESH VIEW robinhood.mv_token_flow_edges_daily;

8. 本地验证与一致性核对Copy Anchor Link

在本地隔离的 ClickHouse 测试环境（wt48_igraph_scratch，导入 5,000 个真实区块全链路数据，含 91,517 笔交易与 89,558 笔代币转账）进行了端到端全量验证与独立 Python 脚本核验：

8.1 表级汇总与直接 GROUP BY 对账结果（100% 精确匹配）Copy Anchor Link

1. interaction_edges_daily 聚合核对（2026-09-24 收盘日）：

   • 过滤后合格交易笔数（tx_count）：86,487（原始 91,517 笔 - 5,013 笔伪交易 - 17 笔合约创建）
   • 原生价值总和（value_sum）：13888375683717600243443
   • Gas 消耗总和（gas_used_sum）：10401954124
   • 成功笔数总和（success_count）：44,075
   • 聚合独立边数（count()）：16,878
   • 核对结果：interaction_edges_daily FINAL 的汇总值与直接在 transactions FINAL 上执行全量计算的结果分毫不差，每一位数字完全一致。

2. token_flow_edges_daily 聚合核对（2026-09-24 收盘日）：

   • 合格代币转账笔数（transfers）：89,558
   • 代币金额总和（amount_sum）：425197483488771469038305900394
   • 聚合独立资金流向边数（count()）：31,767
   • 涉及代币数量：1,191
   • 核对结果：token_flow_edges_daily FINAL 的汇总值与直接在 erc20_transfers FINAL 上执行全量计算的结果分毫不差，每一位数字完全一致。

8.2 明细边点对点抽样核验Copy Anchor Link

• 随机抽样 100 条 interaction_edges_daily 边与 100 条 token_flow_edges_daily 边，单独对比单条边在底表原始记录中的逐行聚合值，100/100 样本完全吻合。

8.3 幂等刷新（Idempotency）测试Copy Anchor Link

• 重复执行 SYSTEM REFRESH VIEW 强制刷新，基于 ReplacingMergeTree(refreshed_at, is_deleted) 的 FINAL 读取结果行数和数值未发生任何漂移（零漂移）。

8.4 重组整边回撤（Reorg Retraction）与幽灵边自愈测试Copy Anchor Link

• 模拟链重组场景，注入上游 tombstone 记录使整个区块（如 230 笔交易，涉及 226 条独占交互边）完全失效；
• 执行 SYSTEM REFRESH VIEW 后，物化视图通过与已存在边的 FULL OUTER JOIN 准确识别出 226 条已消失的边并写入 is_deleted = 1 撤回记录；
• 验证 interaction_edges_daily FINAL（及执行 OPTIMIZE TABLE ... FINAL 后），226 条幽灵边全部被彻底移除，存活边数量与直接查询底层 transactions FINAL 精确一致（15,961 / 15,961，0 误差，0 幽灵边存留）；
• 代币流向表同样验证通过：单边被重组 tombstone 标记后，刷新后流向记录干净消除。