钱包画像数据集（wallet_profiles）

来源：schema/clickhouse/derived/024_wallet_profiles.sql。 面向直接用 SQL 分析链上活跃地址特征的内部团队：单地址终生画像、生命周期统计、累计交易与失败数、合约部署数、累计消耗的手续费、交互对手方去重数、触达的 ERC-20 代币种类数。

依赖 {db}.transactions（见 sql/schema.sql）与 {db}.erc20_transfers（见 schema/clickhouse/derived/001_erc20_transfers.sql）。

1. 字段说明与计算口径Copy Anchor Link

{db}.wallet_profiles 为终生画像聚合视图（普通 VIEW），底层由按天去重存储的中间表 {db}.wallet_daily_activity（ReplacingMergeTree）驱动。

1.1 "钱包" 的纳入范围（Scope）Copy Anchor Link

1. 发起行为定界：只有在 {db}.transactions.from 出现过至少一次的主动发起方，才会被收入本表。只作为交易接收方（to）的合约地址，或者从未发过交易但接收过转账的纯被动地址，不会生成画像。
2. ERC-20 代币触达（erc20_tokens_touched）：在满足前述发起条件的前提下，钱包在 erc20_transfers 中作为转出方（from）或转入方（to）所涉及的所有 ERC-20 代币合约地址均计入统计（跨天状态通过 uniqExactMerge 准确求并集，不会因同一代币在多日出现而重复计数）。
3. 对手方去重（distinct_counterparties）：仅统计普通交易的直接接收方（to IS NOT NULL）。顶层部署合约交易（to IS NULL）不增加对手方计数。

1.2 金额精度安全性（Money-safety）Copy Anchor Link

手续费计算严格遵守项目规范，全程使用整数：

sum(toUInt256(gas_used) * ifNull(effective_gas_price, ifNull(gas_price, toUInt256(0)))) AS fees_paid

gas_used 与 effective_gas_price 均为链上原始无损整数，乘积精确保留至 UInt256，禁止使用任何浮点数、缩放或浮点除法。

2. 正确性设计：为什么采用日粒度 ReplacingMergeTree + 最终视图，而非 AggregatingMergeTreeCopy Anchor Link

在设计每日刷新派生表时，面临两种经典方案的选择：

2.1 为什么不能用插入触发型物化视图（Double-counting）Copy Anchor Link

源表 {db}.transactions 和 {db}.erc20_transfers 均为 ReplacingMergeTree(version, is_deleted)。在 backfill 断点续跑重叠插入或链发生深度重组写入墓碑行（is_deleted=1, 更高版本）时，插入触发型 MV 会在去重前直接累加，导致重复计数或无法撤销已回滚的交易。

2.2 为什么拒绝按 address 聚合的 AggregatingMergeTreeCopy Anchor Link

直觉上可能倾向于建立一张以 address 为主键的 AggregatingMergeTree，各字段存 AggregateFunction 状态（如 sumState、minState、uniqExactState），每天用物化视图刷新。该方案已被实证否定：

• AggregatingMergeTree 在后台 merge 时对同 key 记录只能做累加合并（combine），无法做"替换（replace）"。
• 为了在链重组或修复时自动自愈，定时刷新通常包含滑动回看窗口（如 LOOKBACK_DAYS = 3）。如果使用 AggregatingMergeTree，同一天的数据被重跑 3 次，其 tx_sent、fees_paid 等状态就会被累加 3 次，产生严重漂移。
• 即便按 (day, address) 建 AggregatingMergeTree，由于 minState/maxState/uniqExactState 等状态不可逆，一旦某天重组导致交易减少，也无法通过累加扣减。

2.3 选定架构：ReplacingMergeTree(refreshed_at) 日粒度局部汇总 + 视图终化Copy Anchor Link

1. 日粒度中间表 {db}.wallet_daily_activity：
   • 引擎：ReplacingMergeTree(refreshed_at)，主键 ORDER BY (day, address)。
   • 数据源对齐：通过 tx_day FULL OUTER JOIN erc20_day USING (day, address) 统一按天汇总发送交易与代币触达。即使某地址在某日只有 ERC-20 代币转入/转出而未主动发交易，当天的触达代币集合状态也会完整保存入库。
   • 数值指标（first_seen_block/time, last_seen_block/time）：类型为 Nullable，在仅有 ERC-20 交互的日期为 NULL，防止被 0 或 1970 纪元值污染。
   • 交易标量（tx_sent, tx_failed, contracts_created, fees_paid）：非空整数，无交易日默认补 0。
   • 集合指标（distinct_counterparties_state, erc20_tokens_touched_state）保存为 uniqExactState。
   • 幂等替换：无论是定时重算回看窗口还是手动重跑历史，重新插入同一 (day, address) 会写入新的 refreshed_at，在 FINAL/merge 时整行覆盖旧版本，完全免疫重复运行与状态漂移。
2. 终化汇总视图 {db}.wallet_profiles：
   • 普通 VIEW，执行 FROM {db}.wallet_daily_activity FINAL GROUP BY address HAVING tx_sent > 0。
   • 发起者边界保障：通过 HAVING tx_sent > 0 严格保障仅纳入主动发起过交易的钱包，纯被动接收代币的地址自动排除。
   • 跨天自然正交：每个交易严格归属于某一天，因此数值指标跨天执行 min、max、sum 绝对不会跨日重复计算；首末现高度与时间通过 assumeNotNull(min(...)) 精确恢复为非空类型。
   • 跨天集合合并：对手方与触达代币通过 uniqExactMerge 准确求全生命周期的并集去重，即使代币触达分散在无交易日也能完整并入。

3. 已知边界与局限说明Copy Anchor Link

1. 已收盘日约束（Closed Days）： 刷新物化视图只处理严格已收盘的 UTC 日期（toDate(block_timestamp) < today()），当天尚未收盘的数据不会计入画像，需等次日 02:00 UTC 定时刷新。
2. 重组至零活跃的极端场景： 若某地址在某日仅发送了 1 笔交易，随后该交易被重组回滚且该地址当天再无其他交易，则重跑该日时查询结果为 0 行，ClickHouse 不会写入新行，因此无法自动覆盖旧版本（与 004_daily_chain_stats、005_daily_token_stats 属同一已知特性）。在实际 L2 环境中极端罕见。

4. 安装与补算Copy Anchor Link

4.1 前置依赖Copy Anchor Link

• {db}.transactions（由 raw loader / chain-indexer 创建）。
• {db}.erc20_transfers（由 schema/clickhouse/derived/001_erc20_transfers.sql 创建并回填）。

4.2 执行安装与历史补算Copy Anchor Link

替换 {db}（例如 robinhood）后执行 024_wallet_profiles.sql：

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

如果通过 HTTP 接口执行（例如 curl），请将文件中的语句拆分为独立请求依次发送：

1. CREATE TABLE {db}.wallet_daily_activity
2. CREATE MATERIALIZED VIEW {db}.mv_wallet_daily_activity
3. INSERT INTO {db}.wallet_daily_activity ...（一次性回填全量已收盘历史 < today()）
4. CREATE VIEW {db}.wallet_profiles

4.3 调度刷新与运维Copy Anchor Link

• 物化视图定义了 REFRESH EVERY 1 DAY OFFSET 2 HOUR RANDOMIZE FOR 30 MINUTE APPEND，在每日 UTC 02:00（带随机抖动）自动重新计算过去 3 天的数据追加写入。
• 手动触发重跑：

  Copy Text

  SYSTEM REFRESH VIEW {db}.mv_wallet_daily_activity;
  -- 查看刷新执行情况
  SELECT database, view, status, last_refresh_result, next_refresh_time
  FROM system.view_refreshes
  WHERE database = 'robinhood' AND view = 'mv_wallet_daily_activity';

• 补算幂等性验证：多次重新执行第 3 步的 INSERT 语句或多次触发刷新，中间表因 ReplacingMergeTree(refreshed_at) 机制保证在 FINAL 视图查询时结果完全一致，不会产生数据漂移。

5. 查询示例Copy Anchor Link

5.1 累计消耗手续费最高的 10 个钱包Copy Anchor Link

SELECT
    hex(address) AS wallet,
    tx_sent,
    tx_failed,
    contracts_created,
    fees_paid,
    distinct_counterparties,
    erc20_tokens_touched
FROM robinhood.wallet_profiles
ORDER BY fees_paid DESC
LIMIT 10;

5.2 部署合约最多的活跃钱包Copy Anchor Link

SELECT
    hex(address) AS deployer,
    contracts_created,
    tx_sent,
    first_seen_time,
    last_seen_time
FROM robinhood.wallet_profiles
WHERE contracts_created > 0
ORDER BY contracts_created DESC, tx_sent DESC
LIMIT 10;

5.3 查询指定钱包地址画像Copy Anchor Link

SELECT
    hex(address) AS wallet,
    first_seen_block,
    first_seen_time,
    last_seen_block,
    last_seen_time,
    tx_sent,
    tx_failed,
    contracts_created,
    fees_paid,
    distinct_counterparties,
    erc20_tokens_touched
FROM robinhood.wallet_profiles
WHERE address = unhex('40FFC583EFEF7396F623F73DBC37F5CA79744AEB');

6. 本地验证结果（真实链上数据）Copy Anchor Link

在本地 ClickHouse（wallet_profiles_wt49 库）中回填了真实 Robinhood 链（chain id 4663）跨收盘日与当前日的 4,000 个区块（71780000–71781999 归属 2026-09-24 已收盘日，16,333 笔交易；72060000–72061999 归属 2026-09-25 正在进行中，13,288 笔交易）。

6.1 5 个典型地址全字段独立双路比对Copy Anchor Link

选取 5 个具有不同行为特征的代表性地址（包含合约创建者、多代币交互者、高失败率账户、单笔部署账户等），分别从（A）{db}.wallet_profiles 视图，以及（B）直接针对原始 {db}.transactions FINAL 与 {db}.erc20_transfers FINAL 执行独立 SQL 查询进行对比。

全部 5 个地址的 11 个字段逐项 100% 精确匹配：

6.2 链上证据追踪（真实交易哈希）Copy Anchor Link

• 地址 0x3a19f687a521ba84003279d04373f1fc433f3b99：
  • 区块 71780864 发起交易 0x0229abc5e5110e45fe40d19fa7384c1c90d079f83e4e5925f11619a4dc294619 部署合约 0xd22d0fa778ba24daf7459df4af93f77aa894f68e（gas_used=3,730,937, effective_gas_price=42,844,000）。
  • 区块 71780904 发起交易 0xe9d1f0ca455445f712cdfb212fed337de87accf1396bf3ed16cbf38a6495f143 部署合约 0x9198e4c13fdc2e445cbba4c33ce77d21bdddab106（gas_used=3,023,069, effective_gas_price=43,052,000）。
• 地址 0x8debbf0e76a8c8499760bc6448b31813592eb5ad（高失败率典型）：
  • 区块 71780043 发起失败交易 0x1c06467d03496167cc4b9981d841ffd3652fc9d32781664de31c93dc4cbdbe81（status=0, gas_used=34,071, price=43,138,000）。
  • 区块 71780225 发起失败交易 0x6709a4703571b37e3d0ad36a34cc7971f8c545de30a11008250de095350baeb4（status=0, gas_used=33,591, price=43,120,000）。
• 地址 0x8fa127024719c7a9b429002c38855f90320fd08e：
  • 区块 71781801 仅发 1 笔交易 0xb56d0c78f857c3e0ef4741900e5fb3a09ce80756c252ec02da518062505fcbfd 部署合约 0x6a23d69cd3272007be82a35b16f37524d1869e51。

6.3 幂等性与重跑验证Copy Anchor Link

在已完成初始安装与回填的前提下，重复执行全量已收盘日回填 INSERT 语句至第 3 次：

• wallet_daily_activity 表物理行数因追加增加至 14,172 行。
• wallet_profiles 视图经过 FINAL 折叠后：
  • 活跃钱包总数：4,724
  • 累计发送交易总数：16,333
  • 累计失败交易总数：3,948
  • 累计合约部署总数：10
  • 累计消耗手续费总和：112,014,177,042,136,000 wei 所有汇总数字与单次执行完全一致，零误差，零漂移。

6.4 跨天多日代币触达全量比对验证（5 天 / 5,000 区块 / 11,982 钱包）Copy Anchor Link

为了彻底验证地址在非交易发送日的 ERC-20 转账代币能否被准确捕获，使用真实链上 5,000 个区块（60,222 笔交易、72,152 笔 ERC-20 转账）分布于 5 个已收盘日（today()-8, today()-5 位于回看窗口外；today()-3, today()-2, today()-1 位于回看窗口内）进行全量独立重新计算与双路比对：

1. 全量 11,982 个活跃钱包全字段比对：
   • 视图 wallet_profiles 与直接针对原始 transactions FINAL + erc20_transfers FINAL 独立聚合的结果执行 FULL OUTER JOIN 逐字段比对。
   • 全部 11 个字段在全部 11,982 个钱包上 100% 精确一致（首现高度、首现时间、末现高度、末现时间、发送交易数、失败交易数、部署合约数、手续费、对手方去重数、触达代币去重数等差值均为 0）。
2. 典型跨天被动接收代币地址抽检：
   • 地址 0xCD6B980029E6E6E0733AC8EC3E02BE9410D09799 仅在 2026-09-22 发送了 7 笔交易（涉及 7 种代币），但在 5 个不同日期均存在 ERC-20 接收/转出记录，终生触达 24 种不同代币。
   • 修复前因 LEFT JOIN 导致无主动交易日的 17 种代币被丢弃（仅显示 7）；修复后 FULL OUTER JOIN 准确汇总全部 5 日状态，wallet_profiles 正确返回 24。
3. 幂等性与重组自愈验证：
   • 执行 2 次历史补算追加 + 2 次强制物化视图刷新（SYSTEM REFRESH VIEW）后执行 OPTIMIZE TABLE wallet_daily_activity FINAL，物理记录精确折叠为 26,019 行（唯一 (day, address) 主键数），wallet_profiles 结果完全一致。
   • 对回看窗口内某钱包的交易注入重组墓碑记录（is_deleted=1, version=version+1），触发物化视图刷新后，对手方数与手续费等指标正确回退，全量比对保持 0 误差。