# Dune Analytics

By [vexved.eth](https://paragraph.com/@vexved) · 2022-12-29

---

Dune 简介
-------

*   [网址](https://dune.com/browse/dashboards)
    
*   区块链的本质是一个分布式的公共账本，所有的数据在链上公开透明，包括每一笔转账，每一次的合约调用，所有过往的交易转账、合约调用形成了一条条的交易记录明细，包含交易时间、转入、转出地址、交易金额、调用信息、合约事件、tx\_hash 等数据
    
    Dune将链上数据聚合到可访问的PostgreSQL数据库中，是进行链上数据分析的工具，用户可以通过PostgreSQL查询链上数据并将数据可视化，汇总成dashboard
    
    Dune 作为一款链上数据研究分析的强大工具，用户通过类SQL的数据库查询语言，从 Dune 缓存的区块链数据仓库中检索、筛选、提取、聚合，形成一个个 Query，再对查询到的数据进行可视化，最终汇总而成一个个 Dashboard 面板
    
*   有助于你更直观的分析和调研感兴趣的项目，我们可以从数据表中查询任何我们需要的数据
    
    但是会写SQL只是数据分析师基本，我认为最重要的还是理解数据并用数据讲故事，在数据中能总结发现出一些有意思的东西
    

常用的数据表
------

*   除了内置的数据表外，还有些常用的数据表，比如： 最常用的 ethereum.transactions，可以查询所有的以太坊链上交易信息，包括了普通转账和合约调用等信息
    
    以ethereum链为例，下图是转移token的过程产生的数据流转：
    
    1.  假设现在要在链上转移USDT，就会调用transfer函数，指定接收者和转移的金额，对此条交易进行签名，那么此时链上就会产生calldata数据，dune将这个数据存在了ethereum.transaction表中，这张表主要包含以下字段～from(发送交易的人的地址/签名者)，to(合约交互的地址，现在转移的是USDT，那么就是USDT的合约地址)，success(这条交易是成功？还是失败？)，value(表示的是转移的ETH数值，那现在转移的是USDT，没有转移ETH，所以在这条交易中value的值为0)
        
    2.  gas\_column(主要记录交易的gas使用情况，有gas limit，gas used，gas price)，hash(在链上进行的每次行为都会产生一个唯一的transaction hash，这个值也是用来多表关联的唯一主键)，blocknumber，blocktime主要记录交易发生的区块和时间
        
    3.  转移USDT的合约调用过程，也称为内部交易/子交易，数据存在了ethereum.traces表中
        
    4.  交易执行的日志数据，包括签名者、接收者、topic、转账金额等等存在ethereum.logs表中
        
*   ethereum.transaction: eth交易数据
    
*   ethereum.traces:eth“内部”交易数据
    
*   ethereum.logs:eth交易事件日志
    

Dune Query
----------

*   点击右上角的 new query
    
*   查看eth链上最近的5条交易， 点击 "Run" 稍等即可在"Query Results"看到查询结果
    
        select * from ethereum.transactions order by "block_time" desc limit 5 ;
        
    
*   查看指定地址的记录数据
    
        select * from ethereum."transactions" where "from" ='\x676Aecc97bF721C3cb3329A22D49C0ea0ED375F7' order by block_time desc limit 100
        ## where后面表示根据指定条件对结果进行过滤，只筛选出字段from=\x676Aecc97bF721C3cb3329A22D49C0ea0ED375F7的数据，即钱包0x676Aecc97bF721C3cb3329A22D49C0ea0ED375F7(三箭资本)的交易数据
        
    
*   按字段过滤
    
        select
          block_time as "date",
          value / 1e18 as "value" -- value 字段的值换算为 ETH 单位
        from
          ethereum.transactions
        where
          "from" = '\x1Db3439a222C519ab44bb1144fC28167b4Fa6EE6' -- 交易的发出地址为 V神钱包
          and value / 1e18 > 0.1 -- 转账金额 > 0.1 ETH
        order by
          "block_time" desc -- 按照区块时间倒序排序
        
    
*   复杂查询
    
        ## 查询这个地址0x676Aecc97bF721C3cb3329A22D49C0ea0ED375F7的账户余额
        
        with
          total_token as (
            SELECT
              token_address,
              sum(amount) as amount
            FROM
              erc20."view_token_balances_latest"
            WHERE
              "wallet_address" IN ('\x676aecc97bf721c3cb3329a22d49c0ea0ed375f7')
            group by
              token_address
          ),
          token_price as (
            select
              contract_address,
              price
            from
              prices.usd
            where
              minute - date_trunc('minute', now()) = '-06:00:00'
              AND contract_address in (
                select
                  token_address
                from
                  total_token
              )
              and price > 100
          )
        Select
          sum(a.amount * b.price) as Value
        from
          total_token a
          left join token_price b on a.token_address = b.contract_address
        limit
          20
        
    
*   聚合函数
    
    group by函数可以根据一列或者多列对结果进行汇总。其中date\_trunc('day', block\_time)函数为时间截断函数，可以根据指定的日期部分(比如hour,day,month等)对时间戳表达式进行截断。group by date\_trunc('day', block\_time) 表示按照date\_trunc('day', block\_time)即date字段进行汇总，也可以简写为group by 1即按照第一个字段汇总。order by date\_trunc('day', block\_time) desc表示按照date字段降序排列结果
    
        select
          date_trunc('day', block_time) date,
          sum(value) / 1e18 as value
        from
          ethereum."transactions"
        where
          "from" = '\x676Aecc97bF721C3cb3329A22D49C0ea0ED375F7'
          and block_time >= '2022-01-01'
          and block_time < now()
        group by
          date_trunc('day', block_time)
        order by
          date_trunc('day', block_time) desc
        
    

可视化图表
-----

*   数据已有，可视化图表也就呼之欲出了，对于随着时间变动的数值，最普遍使用的图标就是bar chart（条形图、柱状图）了，在前一步生成的查询结果顶部点击"New visualization"，选择"Bar Chart"，看到系统已经自动帮我们把 X,Y轴都选好了（如果在数据较多的情况下，则需要手动选择Y轴数据）
    

### Sum函数求和

*   一个最普遍的数据统计需求就是：加总求和
    
    然后在可视化图表区域选择Counter，修改Title，就有了如下汇总数据
    
        select
          sum(value / 1e18) as "total" -- 使用 sum 函数对 value 求和
        from
          ethereum.transactions
        where
          "from" = '\x1Db3439a222C519ab44bb1144fC28167b4Fa6EE6'
        
    

### 计数count

*   `SUM`、`AVG`、`MAX`、`MIN`、`COUNT` 统称为聚合函数，用于将组中的行汇总为单个值。
    
    前面提到的 `sum` 用于对数值求和，`count` 则用于统计查询记录的总数目。
    
    比如我们统计下 V神 钱包总计对外发出交易的次数
    
        select
          count(*) -- count 函数用于计数
        from
          ethereum.transactions
        where
          "from" = '\x1Db3439a222C519ab44bb1144fC28167b4Fa6EE6' -- V神 对外发出交易的次数
        
    

你的第一个Dune数据看板
-------------

*   保存前面每一个 Query 并取名，现在我们就来搭建你的首个 Dune Dashboard
    
    在首页点击"New Dashboard"，或直接在 Query 结果页面点击"Add to dashboard"，输入 Dashboard 名字即可
    
*   Fork神技
    
    站在巨人的肩膀之上，能让我们光速入门。越过陡峭的学习曲线，Fork 大神的代码与劳动成果，然后只需要简单修改核心参数，即可实现同类功能，获取心仪的数据（感恩大神与前辈的付出）
    
    具体到 Dune上，我们在任一 Dune看板的图表区域点击左上角 `Query` 名字即可查看完整 `SQL`查询语句。Fork之，修改关键参数，然后点 `Run` 即可见证奇迹
    
    比如 oxBi 大神做好的一个叫 ”Total Holder by Day - ENS ” 的 Query，统计了 ENS 代币每日的总持币人数。代码有67行之长，语法复杂，艰深无比，我等小白唯有汪洋兴叹的份儿
    
        WITH transfers AS (
            SELECT 
                DAY,
                address,
                token_address,
                sum(amount/power(10,18)) AS amount 
            FROM
            ( 
                SELECT date_trunc('day', evt_block_time) AS DAY,
                    "to" AS address,
                    tr.contract_address AS token_address,
                    value AS amount
                FROM erc20."ERC20_evt_Transfer" tr
                WHERE contract_address =  CONCAT('\x', substring('0xc18360217d8f7ab5e7c516566761ea12ce7f9d72' from 3))::bytea -- Token address
                
                UNION ALL 
                
                SELECT date_trunc('day', evt_block_time) AS DAY,
                    "from" AS address,
                    tr.contract_address AS token_address, -value AS amount
                FROM erc20."ERC20_evt_Transfer" tr
                WHERE contract_address =  CONCAT('\x', substring('0xc18360217d8f7ab5e7c516566761ea12ce7f9d72' from 3))::bytea -- Token address
            ) t
            GROUP BY 1, 2, 3
        ),
        balances_with_gap_days AS ( 
            SELECT 
                t.day,
                address,
                SUM(amount) OVER (PARTITION BY address ORDER BY t.day) AS balance,
                lead(DAY, 1, now()) OVER (PARTITION BY address ORDER BY t.day) AS next_day
            FROM transfers t 
        ),
        days AS ( 
            SELECT generate_series('2021-11-01'::TIMESTAMP, date_trunc('day', NOW()), '1 day') AS DAY
        ),
        balance_all_days AS ( 
            
            SELECT d.day,
                address,
                SUM(balance/18) AS balance
            FROM balances_with_gap_days b
            INNER JOIN days d ON b.day <= d.day
            AND d.day < b.next_day
            GROUP BY 1, 2
            ORDER BY 1, 2 
        ),
        
        Hodler_info as (
        
            SELECT 
                b.day AS stat_date,
                COUNT(address) AS Total_Holder,
                COUNT(address) - lag(COUNT(address)) OVER (ORDER BY b.day) AS Today_Holder_Growth
            FROM balance_all_days b
            WHERE balance > 0
            GROUP BY 1
            ORDER BY stat_date 
        )
        
        select 
            (stat_date + '8 hour'::interval) as stat_date
            ,Total_Holder as 今日累计Holder数
            -- ,Today_Holder_Growth
        from Hodler_info
        order by
            (stat_date + '8 hour'::interval)
        
    

自定义项目看板
-------

*   此处以自己感兴趣的web3项目兔子洞为例，rabbithole\_gg，统计如下数据：
    
    1.  NFT Mint总量、独立钱包数量
        
    2.  三枚不听技能NFT Mint总量
        
    3.  Mint 趋势图
        
    4.  NFT 持有数量/类别的比例
        
    5.    
        
*   网站有很多大神分享的图表，可以多研究学习

---

*Originally published on [vexved.eth](https://paragraph.com/@vexved/dune-analytics)*