从0到1构建你的Dune Analytics看板（实战篇）

May 3rd, 2022

写在前边

本篇内容主要是一步一步地说明构建一个看板的过程，做一个整理钱包行为与以太坊币价关系的分析看板，以孙哥钱包(0x3ddfa8ec3052539b6c9549f12cea2c295cff5296)为例

有任何问题或者建议欢迎DM Twitter@Pro_0xBi

一、我想看哪些数据？

我们观察整理钱包的行为其实就是看钱包跟交易所之前的充提行为。但是有的时候整理钱包并不会直接整理到交易所，有可能中转一下再进入交易所。所以理论用一个钱包以及他的所有关联钱包作为一个集合来观察这个行为会比较合适。这里我们做1个简化：以孙哥为例，做一个钱包转入、转出ETH的行为与ETH价格之间的关系的分析。

另外

如果你想按照相对严谨的计算口径做分析，我这里提供两个可能有用的东西

1.这个Query可以用来取某一个钱包的疑似关联钱包，里边的逻辑可以跟自己需要做调整

(查找跟这个钱包有ETH转入转出关系的所有钱包，累积转入或者转出需要超过100ETH)

2.如果你要看充提交易所的行为，那需要首先有交易所的地址。我自己整理了一批交易所标签，可以按照如下方式取到

select
  "address"
from  dune_user_generated.pro_0xBi_address_label_dim 
where  cate in ('Exchange地址')
group by
  "address"

二、怎么确定数据源在哪以及SQL的计算口径？

简单来讲我们需要2个数据

ETH Transfer数据
ETH 价格数据

1.确定ETH Transfer的计算口径

首先ETH Transfer数据，其实我也不知道怎么算。我之前算ERC20的数据比较多，但是纯ETH的数据还真没算过，一般这种情况下我会有几个解决方案：

1.去官网Discover板块搜关键词，看看别人是怎么写的，比如搜ETH Balance 、ETH Transfer 之类的。

2.官方文档里搜关键词

3.去Discord里问官方的工作人员

我自己首先试了试Discover板块搜ETH Balance（之所搜Balance是因为一般Balance是从明细聚合过来的，如果有Balance的计算口径，自己拆一下就成了Transfer数据）。发现刚好有人写过类似的逻辑就点进去看看

自己输入了几个地址跟区块链浏览器对比了一个看起来是没有问题的，然后就去点击具体的Query去看具体的计算逻辑

把SQL贴到编译器软件(我自己用的sublime)里，调整一下成自己习惯的缩进形式

大概读一下SQL的整体逻辑

子查询1

看起来是算了一个钱包所有的转账明细记录，子查询的结果可以从

tbl_transactions_raw中取
子查询2

基于tbl_transactions_raw，将所有的转账的数值按天聚合(group by)，子查询的结果可以从tbl_intermediate中取
子查询3

通过generate_series函数创建一个日期序列，然后left join 子查询2的结果tbl_intermediate，子查询的结果可以从tbl_output中取
最终的Query

从tbl_output中取所有列然后加一个2个时间变量（开始时间跟结束时间），这样Query可以选开始日期以及结束日期

SQL查询的结果如下图，可以发现几乎跟我们需要的一样的，那么研究清楚这个SQL基本上后续想算类似的数据都知道该怎么写了。这里我就不挨个部分解释了，大家自己研究吧，语法相关的东西非常多。总之ETH Transfer数据的计算逻辑搞定了。

2.确定ETH Price的计算口径

同样的方法你去Discover里搜 ETH Price ，也能找到别人写好SQL或者去官方DOC里搜索也能找到相关的计算口径。这里放个计算口径就不详细说了

--这里取了一个平均值
SELECT  date_trunc('day', minute) as day,
        AVG(price) as average
FROM prices."layer1_usd"
WHERE "symbol" = 'ETH' AND  date_trunc('day', minute) > date_trunc('day', now()) - interval '30 days'
GROUP BY 1
ORDER BY day DESC