Dune实战：写第一个链上数据查询

Day 13: Dune 实战 - 写第一个查询

本周学习路径

Week 2: DeFi核心机制
├── Day 8: AMM原理(x*y=k)、无常损失 ✅
├── Day 9: 订单簿vs AMM对比 ✅
├── Day 10: 借贷协议原理 ✅
├── Day 11: 稳定币机制 ✅
├── Day 12: Dune入门(1) - SQL基础 ✅
├── Day 13: Dune入门(2) - 写简单查询 ✅ ← 今天
└── Day 14: 复习 + EthPrice多币种支持

核心概念

从模仿到创造

> 类比理解：昨天我们学会了看菜谱（读懂SQL），今天要开始自己炒菜（写查询）。就像学做菜一样，先从简单的"番茄炒蛋"开始，不要一上来就挑战"满汉全席"。

今日目标
═══════════════════════════════════════════════════════════
1. 写出第一个独立查询（不是Fork修改）
2. 学会 JOIN 连接多个表
3. 理解 Decoded Tables 的命名规律
4. 产出一个可分享的查询
═══════════════════════════════════════════════════════════

JOIN 操作详解

为什么需要 JOIN？

现实问题：
═══════════════════════════════════════════════════════════
表A: erc20_ethereum.evt_Transfer（转账记录）
├── contract_address: 0xA0b8...（这是什么Token？不知道）
├── "from": 发送方
├── "to": 接收方
└── value: 数量（什么单位？不知道）

表B: tokens.erc20（Token信息表）
├── contract_address: 0xA0b8...
├── symbol: USDC（原来是USDC！）
├── decimals: 6（原来是6位小数！）
└── name: USD Coin

JOIN 的作用：把两个表通过共同的列（contract_address）连接起来
═══════════════════════════════════════════════════════════

JOIN 类型速查

-- 1. INNER JOIN（最常用）
-- 只返回两边都有匹配的数据
SELECT *
FROM table_a a
INNER JOIN table_b b ON a.id = b.id

-- 2. LEFT JOIN
-- 返回左表全部 + 右表匹配的（右表没有则为NULL）
SELECT *
FROM table_a a
LEFT JOIN table_b b ON a.id = b.id

-- 3. 实际例子：转账记录 + Token信息
SELECT
    t.block_time,
    t."from",
    t."to",
    t.value / POWER(10, tk.decimals) as amount,  -- 用decimals转换
    tk.symbol
FROM erc20_ethereum.evt_Transfer t
INNER JOIN tokens.erc20 tk
    ON t.contract_address = tk.contract_address
WHERE t.block_time > now() - interval '1 hour'
LIMIT 100

JOIN 图解

INNER JOIN（交集）
┌─────────┐   ┌─────────┐
│  表A    │   │  表B    │
│    ┌────┼───┼────┐    │
│    │匹配│   │匹配│    │
│    └────┼───┼────┘    │
└─────────┘   └─────────┘
      返回中间重叠部分

LEFT JOIN（左表全部 + 右表匹配）
┌─────────┐   ┌─────────┐
│████████ │   │  表B    │
│████┌────┼───┼────┐    │
│████│匹配│   │    │    │
│████└────┼───┼────┘    │
└─────────┘   └─────────┘
  返回左边全部（阴影部分）

实战查询 1：Token 持有者数量

需求分析

目标：查询 USDC 的持有者地址数量

思路：
1. 找到所有 USDC 的转账记录
2. 计算每个地址的余额（收到 - 发出）
3. 筛选余额 > 0 的地址
4. 统计地址数量

难点：
- 需要同时考虑收到和发出
- 要排除销毁地址（0x000...）

完整查询

-- USDC 持有者数量分析
-- USDC 合约地址：0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48

WITH transfers AS (
    -- 先计算每个地址的净流入
    SELECT
        address,
        SUM(amount) as balance
    FROM (
        -- 收到的（正数）
        SELECT
            "to" as address,
            CAST(value AS DOUBLE) / 1e6 as amount  -- USDC是6位小数
        FROM erc20_ethereum.evt_Transfer
        WHERE contract_address = 0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48

        UNION ALL

        -- 发出的（负数）
        SELECT
            "from" as address,
            -CAST(value AS DOUBLE) / 1e6 as amount
        FROM erc20_ethereum.evt_Transfer
        WHERE contract_address = 0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48
    )
    GROUP BY address
)

SELECT
    COUNT(*) as total_holders,
    COUNT(CASE WHEN balance > 1000000 THEN 1 END) as whale_holders,  -- >100万
    COUNT(CASE WHEN balance > 10000 THEN 1 END) as large_holders,    -- >1万
    COUNT(CASE WHEN balance > 100 THEN 1 END) as medium_holders      -- >100
FROM transfers
WHERE
    balance > 0
    AND address != 0x0000000000000000000000000000000000000000  -- 排除销毁地址

查询解析

新语法讲解：
═══════════════════════════════════════════════════════════

1. WITH ... AS（CTE，公共表表达式）
   └── 相当于创建一个临时表，让查询更清晰
   └── 可以被后面的查询引用

2. UNION ALL
   └── 把两个查询结果上下拼接
   └── UNION 会去重，UNION ALL 不去重（更快）

3. CAST(value AS DOUBLE)
   └── 类型转换，把大数转成小数类型
   └── 避免整数溢出

4. CASE WHEN ... THEN ... END
   └── SQL的条件判断
   └── 配合 COUNT 实现分类统计
═══════════════════════════════════════════════════════════

实战查询 2：DEX 交易量排行

需求分析

目标：最近7天各 DEX 的交易量排行

使用表：dex.trades（Spellbook聚合表）
优势：不用自己JOIN价格，已经算好了 amount_usd

完整查询

-- 最近7天 DEX 交易量排行
SELECT
    project as dex_name,
    COUNT(*) as trade_count,
    COUNT(DISTINCT taker) as unique_traders,
    SUM(amount_usd) as total_volume_usd,
    AVG(amount_usd) as avg_trade_size
FROM dex.trades
WHERE
    block_time > now() - interval '7 days'
    AND blockchain = 'ethereum'
    AND amount_usd > 0  -- 排除无效交易
GROUP BY project
ORDER BY total_volume_usd DESC
LIMIT 20

进阶：每日趋势

-- Uniswap vs Curve 每日交易量对比
SELECT
    date_trunc('day', block_time) as day,
    project,
    SUM(amount_usd) as daily_volume
FROM dex.trades
WHERE
    block_time > now() - interval '30 days'
    AND blockchain = 'ethereum'
    AND project IN ('uniswap', 'curve')
GROUP BY 1, 2
ORDER BY 1 DESC, 3 DESC

实战查询 3：巨鲸地址追踪

需求分析

目标：找出最近24小时大额 ETH 转账的发送者

思路：
1. 筛选大额转账（>100 ETH）
2. 按发送地址分组
3. 统计每个地址的转账次数和总金额

完整查询

-- 最近24小时大额ETH转账的巨鲸地址
SELECT
    "from" as whale_address,
    COUNT(*) as tx_count,
    SUM(value / 1e18) as total_eth_sent,
    AVG(value / 1e18) as avg_eth_per_tx,
    MIN(block_time) as first_tx,
    MAX(block_time) as last_tx
FROM ethereum.transactions
WHERE
    block_time > now() - interval '24 hours'
    AND value > 100 * 1e18  -- >100 ETH
    AND success = true
GROUP BY "from"
HAVING COUNT(*) >= 2  -- 至少2笔交易
ORDER BY total_eth_sent DESC
LIMIT 50

新语法：HAVING

-- WHERE vs HAVING
-- WHERE: 在分组前筛选原始行
-- HAVING: 在分组后筛选聚合结果

SELECT
    "from",
    COUNT(*) as tx_count
FROM ethereum.transactions
WHERE value > 10 * 1e18        -- 先筛选：只看>10ETH的交易
GROUP BY "from"
HAVING COUNT(*) > 5            -- 再筛选：只要交易次数>5的地址

Decoded Tables 命名规律

命名模式

[协议名]_[链名].[合约名]_[类型]_[函数/事件名]

类型：
├── evt_  事件（Event，合约emit的日志）
├── call_ 函数调用（Function call）
└── 无前缀 一般是视图或聚合

示例：
═══════════════════════════════════════════════════════════
uniswap_v3_ethereum.Pair_evt_Swap
│           │        │     │   └── 事件名：Swap
│           │        │     └── 类型：事件
│           │        └── 合约名：Pair
│           └── 链：ethereum
└── 协议：uniswap_v3

aave_v3_ethereum.Pool_evt_Borrow
└── Aave V3 的借款事件

erc20_ethereum.evt_Transfer
└── 所有ERC20的转账事件（通用）
═══════════════════════════════════════════════════════════

如何找到正确的表？

方法1：Dune搜索框
├── 输入协议名（如 "uniswap"）
└── 筛选 Tables

方法2：从Dashboard反查
├── 找到想要的图表
├── 点击查看SQL
└── 看它用了什么表

方法3：官方文档
├── Dune Docs 有常用表说明
└── 协议的Dune页面通常有表目录

方法4：Spellbook优先
├── dex.trades - 所有DEX
├── nft.trades - 所有NFT
├── lending.borrow - 所有借贷
└── 这些聚合表最好用！

链上实操记录

我的第一个查询

-- 查询目标：WETH 最近7天每日转账量
-- 表选择：erc20_ethereum.evt_Transfer
-- WETH地址：0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2

SELECT
    date_trunc('day', evt_block_time) as day,
    COUNT(*) as transfer_count,
    COUNT(DISTINCT "from") as unique_senders,
    COUNT(DISTINCT "to") as unique_receivers,
    SUM(CAST(value AS DOUBLE)) / 1e18 as total_weth
FROM erc20_ethereum.evt_Transfer
WHERE
    contract_address = 0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2
    AND evt_block_time > now() - interval '7 days'
GROUP BY 1
ORDER BY 1 DESC

-- 运行结果观察：
-- 1. 每天约50-100万笔WETH转账
-- 2. 独立发送者约10-20万地址
-- 3. WETH是DeFi最活跃的资产之一

遇到的问题与解决

问题1：查询超时
原因：扫描数据太多
解决：加时间限制、加LIMIT

问题2：数值显示异常（很大的数字）
原因：没有除以decimals
解决：/ 1e18（ETH系）或 / 1e6（USDC）

问题3：找不到想要的表
解决：
├── 先用 Spellbook 聚合表
├── 搜索协议名 + 事件名
└── 从现有Dashboard学习

今日思考

问题1：写查询的思维框架？

五步法：
═══════════════════════════════════════════════════════════

Step 1: 明确目标
└── 我要回答什么问题？

Step 2: 选择数据源
└── 哪个表有我需要的数据？
└── 优先用 Spellbook > Decoded > Raw

Step 3: 确定维度
└── 按什么分组？（时间/地址/协议）
└── 需要什么聚合？（求和/计数/平均）

Step 4: 写出框架
└── SELECT → FROM → WHERE → GROUP BY → ORDER BY

Step 5: 逐步调试
└── 先跑简单版本，确认数据对
└── 再加条件、加JOIN、加计算

═══════════════════════════════════════════════════════════

问题2：PM 最常用的查询类型？

Top 5 高频查询：

1. 时间趋势
   └── 每日/每周的交易量、用户数、TVL

2. 排行榜
   └── Top地址、Top交易对、Top协议

3. 分布分析
   └── 用户持仓分布、交易金额分布

4. 对比分析
   └── 我们 vs 竞品的数据对比

5. 漏斗分析
   └── 用户从连接钱包到完成交易的转化

问题3：如何让查询跑得更快？

优化技巧：
├── 加时间限制（最重要！）
├── 用 LIMIT 限制返回行数
├── 选择合适的表（聚合表比原始表快）
├── 避免 SELECT *，只选需要的列
└── 复杂查询用 CTE 分步骤

反例（慢）：
SELECT * FROM ethereum.transactions

正例（快）：
SELECT block_time, "from", value
FROM ethereum.transactions
WHERE block_time > now() - interval '1 day'
LIMIT 1000

面试题准备

Q: 你会如何用数据分析评估一个DeFi协议？

30秒版本：

> 我会从四个维度分析：1）规模指标——TVL、日交易量、独立用户数；2）增长指标——周环比、月环比变化；3）健康指标——用户留存、大户占比、收入vs激励；4）竞品对比——市场份额变化。用Dune可以查到这些数据，关键是看趋势而非绝对值，以及识别数据是否被刷量。

追问准备：

• Q: 如何识别刷量？ → 看地址行为模式：同一地址反复交易、交易间隔极短、金额规律相同
• Q: TVL高一定好吗？ → 不一定，要看是否靠高APY激励堆出来的，激励停了TVL可能暴跌

学习资源

明日预告

Day 14: Week 2 复习 + EthPrice组件多币种

• 复习 DeFi 核心概念（AMM、借贷、稳定币）
• 开发：EthPrice 组件支持多币种显示
• 对比3个DEX的ETH价格
• 产出：代码提交

Week 2 即将完成！准备动手写代码了！