从0到1构建你的Dune Analytics看板(基础篇)
0x5727
April 25th, 2022

写在前边

本篇内容主要是目的是介绍一些略微偏技术层面的基础知识,这些是学习使用Dune Analytics的前置条件。不要对这些稍微稍微偏技术内容有抵触心理。实际使用中经常用到的SQL语法其实就那几种,你可以认为其实就是学了几个单词的用法,花时间认真看完基本都能看懂

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


一、基础概念

首先搞明白3个基础的问题?

1、Dune Analytics是什么?

简单来说是:

1.Dune Analytics团队把区块链上的数据清洗后形成结构化的数据存入数据仓库,然后做了一个工具让有一些SQL基础的人能够按照自己的需求灵活地查询数据。

2.平台还的提供了数据可视化的能力,查出来的数据可以方便地做成做成图以及看板

2、数据仓库是什么?

说人话就是说就是出于数据统计的需要,把一些数据分门别类地存储起来,存储的载体是【数据表】。针对某一个或者一些主题的一系列【数据表】合在一起就是数据仓库。

注意:

这里的数据可以是结果数据(比如Uniswap上线以来某个交易对每天的交易量统计)

也可以是过程数据(Uniswap上线以来某个交易对发生的每一条交易记录明细:谁发起的,用A换B,交易时间,tx_hash,交易数量….)

3、SQL是什么?

假设你想吃脆香米巧克力,但是你这会儿出不了门,你就叫个跑腿说:我需要一盒巧克力,他的牌子是脆香米。跑腿去了趟超市把巧克力买来送到你家。

类比过来SQL就是你说的那句话,Dune Analytics就是个跑腿儿,他可以让你可以跟数据仓库对话,并且将数据仓库里的数据给你搬出来给你。

二、数据表长什么样?

你可以认为表就是一个一个的Excel 表,每一个Excel 表里存的不同的数据。以我最常用的表为例erc20."ERC20_evt_Transfer"(ERC20代币的转账日志)

表的字段如下

  • from:从哪个钱包转的币
  • to:币转到了哪个钱包
  • value:转了多少币
  • contract_address:转的哪个币(ERC20代币的合约地址,一般可以在CMC查某一个代币的合约地址)
  • evt_tx_hash:这笔转账的tx hash是什么
  • evt_index:这条数据是这个交易的第几笔,1个交易可能存在多笔数据,比如你在uniswap交易你用APE兑换AAVE,可能就会被拆成2笔数据APE→ETH,ETH→AAVE
  • evt_block_time:交易被打包的时间
  • evt_block_number:交易被打包的区块高度

三、SQL怎么写?

1.基本结构

SQL最基本的结构或者语法就是

  • select : 取哪个字段?
  • from :从哪个表里取?
  • where :限制条件是什么?

举个🌰,具体的解释见下图

select
     "from"
     ,"to"
     ,"value"
from erc20."ERC20_evt_Transfer"
where "from"  = '\xb1a2b43a7433dd150bb82227ed519cd6b142d382'
and "contract_address" = '\xe6ee69495b571e1042f760d7f34009164aff87a2'

额外说明一下

  • 关于钱包/合约地址或者是tx hash,实际存到数据库里都做了一层处理:所有的【0x】中的【0】都替换成了【\】
  • select多个字段用【英文】逗号隔开,SQL中涉及的逗号引号都必须是英文格式,否则会报错
  • 【where】后边如果有多个条件,想多个条件取交集的时候用 【and】连接这些条件,取并集用【or】连接
  • 带引号的from是表里的一个字段名,表名前的from是SQL的语法词

最终查出来的结果见下图,符合上边条件的所有交易数据就只有4条

2.聚合函数

常见的聚合函数

  • count()

    计数,计算符合要求的数据一共有多少行,如果需要按照某个字段去重,比如按照【to】去重,就是count(distinct “to“ )

  • sum()

    求和,比如算孙哥今年以来一共向币安转了多少ETH,就需要用sum()加一下

  • max()

    取最大,比如算孙哥今年以来一共向币安最多一次转了多少ETH,就需要用max()算一下

  • min()

    取最小

  • avg()

    • 取平均

聚合函数就是在你where限制条件下取出来所有的数据,然后再对他们用函数聚合

这里为了比较好举🌰,这里我们换一个contract_address

-- 换成 '\x5ca9a71b1d01849c0a95490cc00559717fcf0d1d'
select
     "from"
     ,"to"
     ,"value"
from erc20."ERC20_evt_Transfer"
where "from"  = '\xb1a2b43a7433dd150bb82227ed519cd6b142d382'
and "contract_address" = '\x5ca9a71b1d01849c0a95490cc00559717fcf0d1d'

结果如下图:

聚合函数就是在你上边where限制条件下取出来所有的数据,然后再对他们用函数聚合,我们用上聚合函数

select
     count("to") as data_count
     ,count(distinct "to") as data_count_v2
     ,sum("value") as data_sum
     ,max("value")  as data_max
     ,min("value") as data_min
     ,avg("value") as data_avg
from erc20."ERC20_evt_Transfer"
where "from"  = '\xb1a2b43a7433dd150bb82227ed519cd6b142d382'
and "contract_address" = '\x5ca9a71b1d01849c0a95490cc00559717fcf0d1d'

算出来的结果:一共出来8条转账记录,转给了4个不同的钱包,总计转了5.45e+23,其中最大一笔2.5e+23….

可以自己对照前一个SQL里的数据看一下是不是能对得上(必然可以对得上🐶)

3.分组聚合

分组聚合的语法group by。分组聚合顾名思义就是先分组后聚合。

假设上边表格是一个家庭(3个人)2020年前2个月的生活开销明细,如果你只用简单的sum,那你只能得到总计的12900;如果你想的到右边2种统计数据,那就需要用到分组聚合group by(按照【人员】分组聚合或者按照【月份】分组聚合)

举个🌰

我们还是按照之前的脚本取明细数据,不过多加一个字段‘evt_block_time’

如果我们想看这8笔交易一共给每个人(to)转了多少币就可以

1.【value】用sum()聚合,

2.把【to】放在select 后

3.最下方加group by,以及group by跟着分组字段【to】

select
     "to"
     ,sum("value") as value_amount
from erc20."ERC20_evt_Transfer"
where "from"  = '\xb1a2b43a7433dd150bb82227ed519cd6b142d382'
and "contract_address" = '\x5ca9a71b1d01849c0a95490cc00559717fcf0d1d'
group by  "to"

4.其他语法

基础的其实就上边那些,还有一些其他语法就不在这里挨个说了,我把关键词列出来,感兴趣自己去搜索引擎搜索(目前dune上以太坊的表用的PostgreSQL,solana的表用的SparkSQL ,其实差不多,只不过函数使用上有细微差异)

  • 分组排序:row_number()
  • 字符串处理:substr() , concat() , replace()
  • 联表查询:join , left outer join , right join
  • 多条件判断:case when , if
  • 转换数据类型:cast()
  • 保留小数点位数:round()
  • 子查询:with as

以上所有灵活组合使用,基本能解决日常95%以上的查询数据的需求

Arweave TX
ofXpu9nxCWxf3vDG9bC_SaxBRuglh9KGP0ZX6jtJuwY
Ethereum Address
0x5727F6dEcd0011edB8420758Ae9EE8A3D4D19aB8
Content Digest
6cbedGOx0GwZdvuxHeyTAgn333jaT34y-2qryvh8Fio