推荐 Dune SQL 代码风格

此指南提供了在 Dune 上编写干净、可读和可维护的 SQL 查询的最佳实践。无论你是在分析 DeFi 协议、NFT 收藏，还是区块链指标，遵循这些规范将帮助你创建更高效和协作的查询。

通过采用这些实践，我们可以建立一个更易于维护的查询库，惠及整个 web3 分析社区。

优先使用 CTE 而不是子查询

CTE（公用表表达式）优于子查询，因为它们使 SQL 更易读、更易维护，且通常性能更好。与嵌套子查询不同，嵌套子查询可能会变得难以理解，CTE 创建了一种逻辑清晰的逐步流程，更易于调试。有关为什么 CTE 是我们 SQL 风格的基本部分的详细解释，我们建议阅读在 dbt 讨论论坛上的这篇详细讨论。

基本 CTE 结构和组织

✅ 使用 CTE 将查询分解为更小、更易于管理的部分 - 将每个 CTE 视为完成一个特定任务的构建块

✅ 将所有数据源（使用 {{ ref('...') }} 或 sources）放在查询的开头，以方便查看数据来源（仅适用于 dbt，不适用于 dune）

✅ 尽早在查询中过滤掉不必要的数据，以加快执行速度

✅ 为你的 CTE 赋予清晰、有意义的名称，以解释它们的作用 - 例如，使用 'daily_transactions' 而不是 'cte1'

✅ 添加注释以解释 CTE 中的复杂计算或逻辑 - 这有助于其他人（以及你自己）在后续理解代码

✅ 如果发现自己多次复制相同的 CTE，可以考虑将其制作成一个单独的模型（这适用于 dbt 用户）

✅ 始终以 'final' 或 'finally' CTE 结束查询，以显示最终结果 - 这使得通过查看中间步骤来检查工作更容易，也方便调试

CTE 结构和最佳实践

✅ 每个新的 CTE 开头使用逗号 - 这使得添加或删除 CTE 更加方便，而不会破坏你的查询

✅ 编写注释，帮助其他人理解你的代码，尤其是对复杂计算的解释

✅ 以合理的顺序排列 CTE - 先从原始数据开始，然后逐步转换，直到获得最终结果

✅ 通过缩进 CTE 内部的内容，使代码更易于阅读 - 这有助于在视觉上区分查询的不同部分

示例

with
nft_sales as (
    select * from {{ ref('ethereum_nft_sales') }}
)
-- 过滤特定集合的 NFT 销售
, filtered_sales as (
    select *
    from nft_sales
    where nft_contract_address = lower('0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d') -- BAYC
        and block_time >= now() - interval '30' day
)
select * from filtered_sales;

编写清晰一致的 SQL 代码

在 Dune 上分析链上数据时，编写清晰和一致的 SQL 代码不仅是为了让查询能够正常工作，还能使整个 web3 分析社区的成员能够理解和维护。这为何重要：

干净、可读的 SQL 代码帮助其他分析师在你的基础上构建，能够更容易地调试复杂的区块链查询，同时确保随着协议的演变，你的分析仍然有价值。无论你是在跟踪 DeFi 指标、NFT 收藏还是其他区块链数据，遵循一致的格式化实践使得你的查询更加易于初学者和经验丰富的分析师接触。

让我们深入探讨编写清晰 SQL 代码的关键原则……

SQL 代码格式和样式标准

✅ 在行首放置逗号（而不是末尾），这样更容易添加新列或注释掉现有列。这有助于避免在修改查询时出现语法错误。

-- ❌ 错误：使用尾随逗号
select
    block_number,
    tx_hash,
    from_address,  -- 尾随逗号使得注释掉行变得困难
from ethereum.transactions

-- ✅ 正确：使用首行逗号
select
    block_number
    , tx_hash
    , from_address  -- 可以轻松注释任何行而不破坏语法
from ethereum.transactions

✅ 通过使用一致的间距使代码更易读。除 WHERE 条件外，每行开头添加四个空格，WHERE 条件应与 WHERE 对齐。

-- ❌ 错误：不一致的缩进
select
tx_hash,
block_number,
     from_address
from ethereum.transactions
where block_time >= '2025-01-01'
and value > 0

-- ✅ 正确：一致的四个空格缩进
select
    tx_hash
    , block_number
    , from_address
    , value / 1e18 as value_in_eth
from ethereum.transactions
where block_time >= '2025-01-01'
    and value > 0
    and success = true

✅ 保持 SQL 代码易读，避免写超长的行 - 如果某行过长，请将其拆分为多行。这使得你的查询更易读，且无需横向滚动。基准：80/120 个字符。

-- ❌ 错误：长行需要横向滚动
select tx_hash, block_number, from_address, to_address, value, gas_used, gas_price, block_time, success, case when value > 0 then true else false end as is_value_transfer from ethereum.transactions

-- ✅ 正确：拆分长行以增强可读性
select
    tx_hash
    , block_number
    , from_address
    , to_address
    , value / 1e18 as value_in_eth
    , gas_used
    , gas_price / 1e9 as gas_price_gwei
    , block_time
    , success
    , case
        when value > 0 then true
        else false
    end as is_value_transfer
from ethereum.transactions

命名约定

✅ 在 Dune 中编写 SQL 查询时，保持所有内容小写：这包括字段名（如 'wallet_address'）、函数（如 'sum' 或 'count'）和 SQL 命令（如 'select' 或 'from'）

-- ❌ 错误：混合大小写和不清晰的名称
SELECT
    WalletAddress,
    Token_Name,
    BLOCK_TIME,
    temp_val1
FROM EthTransactions t
WHERE t.ChainId = '1';

-- ✅ 正确：一致的小写和清晰的名称
select
    wallet_address
    , token_name
    , block_time
    , eth_value
from ethereum.transactions
where chain_id = '1';

✅ 在为表或列赋予新名称时始终使用 as。例如，使用 'txs' 作为交易的表名或 'evts' 作为事件的表名，而不是简单地写出 't'。这使得代码对其他人（以及你自己后续阅读）更易理解。

-- ❌ 错误：不清晰的表别名和缺失的 AS 关键字
SELECT t.tx_hash, e.amount
FROM ethereum.transactions t
JOIN ethereum.erc20_events e ON t.tx_hash = e.tx_hash;
-- ✅ 正确：清晰的表名和明确的 AS
-- ✅ 正确：清晰的联接
select
    txs.tx_hash
    , evt.amount
from ethereum.transactions as txs
    join ethereum.erc20_events as evt
        on txs.tx_hash = evt.tx_hash;

✅ 在联接时保持表名清晰 - 使用完整的名称（如 'customers'）比短版本（如 'c'）更好，除非短版本使查询更易读。此时使用三个字母是个好主意。

上述为示例

✅ 为表和列赋予有意义的名称，以描述其包含内容 - 而不是使用像 'temp_table' 或 'data1' 这样的名称，请使用类似于 'daily_trades' 或 'wallet_balances' 的描述性名称。

上述为示例

✅ 当需要唯一标识表中的行时，使用一致的命名规范，用于此 ID 列 - 例如，可以为所有表使用 'unique_row_id'

✅ 当跟踪数据最后更新时间时，始终使用相同的格式：current_timestamp as last_updated_at

🚧 提示：努力保持表命名的一致性 - 在查询中始终使用单数（如 'trade', 'wallet'）或复数（如 'trades', 'wallets'），但不要混合使用

SQL 组织最佳实践

✅ 首先列出所有常规列（如 user_id、email、name），然后在最后添加任何计算或总计（如 sum(amount) 或 row_number()）。这使得查询更易于阅读和理解。

-- ❌ 错误：状态字段与窗口函数混合
select
    wallet_address,
    row_number() over (partition by wallet_address order by block_time) as tx_number,
    token_symbol,
    token_name,
    sum(token_amount) over (partition by wallet_address) as total_transferred
from ethereum.erc20_transfers;

-- ✅ 正确：状态字段分组在前，窗口函数在后
select
    wallet_address
    , token_symbol
    , token_name
    , row_number() over (partition by wallet_address order by block_time) as tx_number
    , sum(token_amount) over (partition by wallet_address) as total_transferred
from ethereum.erc20_transfers;

✅ 尽早将数据结合（或 aggregate）成更小的分组，然后再与其他表进行联接。这有助于查询更快运行，减少处理的数据量。例如，如果要计数每日交易，请先统计每一天的交易数量，然后将该较小的数据集与你需要的其他信息关联。

-- ❌ 错误：在聚合之前进行联接
select
    w.wallet_address,
    w.ens_name,
    sum(t.value/1e18) as total_eth_transferred
from ethereum.wallets w
    left join ethereum.transactions t
        on w.wallet_address = t.from_address
group by 1, 2;

-- ✅ 正确：在联接之前进行聚合
with transfer_totals as (
    select
        from_address
        , sum(value/1e18) as total_eth_transferred
    from ethereum.transactions
    group by 1
)
select
    w.wallet_address
    , w.ens_name
    , tt.total_eth_transferred
from ethereum.wallets as w
left join transfer_totals as tt
    on w.wallet_address = tt.from_address;

✅ 始终写出完整的联接类型 - 使用 inner join 而不仅仅是 join。这有助于明确你希望使用的联接类型（内连接仅保留两个表中匹配的行）。清楚你在做什么比依赖 SQL 的默认行为更好。

-- ❌ 错误：隐式联接类型
select usr.wallet_address, txn.tx_hash
from ethereum.users usr
    join ethereum.transactions txn
        on usr.wallet_address = txn.from_address;

-- ✅ 正确：显式联接类型
select
    usr.wallet_address
    , txn.tx_hash
from ethereum.users as usr
inner join ethereum.transactions as txn
    on usr.wallet_address = txn.from_address;

✅ 在联接表时，首选使用 left join 而不是 right join。

-- ❌ 错误：使用 right join
select
    w.ens_name
    , t.tx_hash
from ethereum.transactions t
    right join ethereum.wallets w
        on w.wallet_address = t.from_address;

-- ✅ 正确：重构为使用 left join
select
    w.ens_name
    , t.tx_hash
from ethereum.wallets as w
left join ethereum.transactions as t
    on w.wallet_address = t.from_address;

✅ 在联接多个表时，总是将表名或其简写（别名）放在列名之前。例如，写 'users.name' 或 'u.name' 而不是单独的 'name'。这有助于避免对列来自哪个表的混淆。只有在从单个表选择时，才可跳过此步骤。

-- ❌ 错误：模糊的列引用
select
    name,
    token_symbol,
    tx_hash,
    value
from ethereum.wallets w
    join ethereum.transactions t on w.address = t.from_address;
-- ✅ 正确：清晰的表前缀
select
    wlt.name
    , wlt.token_symbol
    , txn.tx_hash
    , txn.value / 1e18 as eth_value
from ethereum.wallets as wlt
inner join ethereum.transactions as txn
    on wlt.address = txn.from_address;

数据分组与排序约定

✅ 尽可能在 GROUP BY 中使用数字而不是列名。这使得 SQL 查询更容易维护，尤其是在列名称更改时。点击这里了解更多。

-- ❌ 错误：在 GROUP BY 中使用列名
select
    wallet_address,
    block_date,
    sum(transaction_value) as total_value
from ethereum.transactions
group by wallet_address, block_date;

-- ✅ 正确：使用位置引用
select
    wallet_address
    , block_date
    , sum(transaction_value) as total_value
from ethereum.transactions
group by 1, 2
-- 或 {{ dbt_utils.group_by(2) }};

✅ 尽可能使用 UNION ALL 而不是 UNION。UNION ALL 更快，因为它不会删除重复项，而 UNION 会进行额外的工作以检查重复。仅在确实需要删除重复项时使用 UNION。如果你确实需要删除重复项，请使用 UNION DISTINCT 来使其明确。

-- ❌ 错误：在重复项无关紧要时使用 UNION
select wallet_address, 'active' as status
from dune.active_users
union
select wallet_address, 'inactive' as status
from dune.inactive_users;
-- ✅ 正确：使用 UNION ALL 以获得更好的性能
select wallet_address, 'active' as status
from dune.active_users
union all
select wallet_address, 'inactive' as status
from dune.inactive_users;

联接与过滤

✅ 在联接时始终将主（左）表列在前，以提高可读性和清晰度。这使得更容易理解查询中数据的主要来源表。

select
    transactions.*,
    senders.reputation as sender_reputation,
    receivers.reputation as receiver_reputation
from ethereum.transactions
left join dune.users as senders
    on transactions.from_address = senders.wallet_address
left join dune.users as receivers
    on transactions.to_address = receivers.wallet_address;

Case 语句

✅ 格式化 CASE 语句以增强可读性：

• 每个 WHEN 和 ELSE 置于新行中。

• THEN 语句缩进以跟随相应的 WHEN。

• END 在新行中且没有缩进。

这使得逻辑和结果一目了然。

, case
    when withdrawal_date is null and expiration_date is not null
      then expiration_date
    when withdrawal_date is null
      then start_date + interval '7 days'
    else withdrawal_date
end as withdrawal_date

最佳实践 Dune 示例

with
transactions_data as (
        -- 可能时列出列
    select * from {{ ref('ethereum_transactions') }}
    where block_timestamp >= '2024-01-01'
        and (
            status = 'success' or
            status = 'pending'
        )
)
, user_data as (
    select * from {{ ref('dune_users') }}
)
, aggregated_transactions as (
    select
        from_address
        , sum(value) as total_value_sent
        , max(gas_used) as max_gas_used
    from transactions_data
    group by 1
)
, final as (
    select distinct
        txns.tx_hash
        , txns.from_address
        , txns.to_address
        -- 使用换行来视觉上分隔逻辑
        , case
            when txns.status = 'success' and txns.confirmations > 10
                then 'finalized'
            when txns.status = 'pending'
                then 'awaiting confirmation'
            else 'failed'
        end as transaction_status
        , aggt.total_value_sent
        , aggt.max_gas_used
    from transactions_data as txns
    left join aggregated_transactions as aggt
        on txns.from_address = aggt.from_address
    having count(*) > 1
)
select * from final;

Dune Web3 用户的最终思考

✔ 优先考虑可读性而非简洁性 – 多出的 SQL 行不会拖慢执行速度，但不清晰的查询浪费宝贵的思维时间。

✔ 避免重复（DRY） – 如果发现自己在重复代码，考虑使用额外的 CTE、宏（dbt）或模型（dbt）以提高可维护性。

✔ 分解复杂的区块链查询 – 在处理链上交易、代币转账和钱包交互时，使用 CTE 简化逻辑。

✔ 遵循一致的格式化和命名约定 – 全部小写，正确对齐 JOIN 条件，格式化 CASE 语句以提高清晰性。

✔ 优化性能，但保持可维护性 – 高效结构化查询确保在处理大型数据集时仍保持可读性，例如历史以太坊交易。

✔ 尽可能将查询控制在 100 行以内 – 长查询可能会拖慢 Dune 的查询优化器，使得调试和维护变得困难。

🚧 优先使用哨兵值代替 NULL – 避免让 NULLs 在查询中未经过检查地传播。使用默认值（例如，交易值为 0，缺失标签为 'unknown'）以防止意外结果。

🚧 使用工具。 可配置的 linter/格式化工具如 sqlfluff 能减轻保持代码格式的乏味工作。 你可以将它们钩入你的编辑器，以在保存时自动运行或作为 git 预提交Hook集成到你的工作流程中。

本指南由 Randall Potter 和 C. Ittevrec 提供，自 Absinthe 供稿。如欲在 Dune 网站贡献类似指南，请联系 alsie@dune.com.

• 原文链接： dune.com/watch/dune-sql-...
• 登链社区 AI 助手，为大家转译优秀英文文章，如有翻译不通的地方，还请包涵～