实用指南: 查询比特币链上数据

探索如何利用公共数据来分析比特币交易、UTXO 和币龄

照片由 Aleksi Räisä 拍摄于 Unsplash

比特币区块链不仅仅是一个流行语；它是一个公开可访问的账本，充满了有价值的数据，等待着被探索。因为它对所有人开放，我们拥有直接进入和查询比特币网络核心的独特能力。

在哪里查询比特币区块链数据？

有很多不同的方法可以做到这一点，但是，我首选的方法是使用 Google BigQuery 中的公共数据集。

SELECT 
  COUNT(*) as transactions
FROM `bigquery-public-data.crypto_bitcoin.transactions`

不过，这里有一个小问题，BigQuery 只有高达 1TB 的数据读取是免费的，并且 transactions 表的当前大小为 2TB（截至 2025 年 2 月 25 日）。请注意这个警告，因为需要一些谨慎措施，以避免产生可能没有预料到的费用。

现在，当处理如此庞大的数据集时，BigQuery 的定价可能有点可怕。为了避免任何意外的费用，这里有一个我使用过的小技巧：我将整个 transactions 表从 BigQuery 导出到 Google Cloud Storage (GCS)。这样做的好处是，从 BigQuery 导出 是完全免费的！此外，我将其保存为压缩的 Parquet 文件，以最大限度地减少存储空间。

从 GCS，你可以将这些 parquet 文件下载到你的本地机器或外部驱动器。 砰！ 你已经掌握了完整的比特币交易历史记录，可以随时进行分析，而无需支付 BigQuery 费用。

现在你有大量的 parquet 文件，但是我们如何查询它呢？ 使用 duckdb，就我个人而言，我喜欢使用它，它可以直接从硬盘驱动器读取 parquet 文件。

无论你最终在哪里查询它，查询中的概念都将在不同的来源之间传递，因此可能不需要过多地担心在哪里进行查询。

每日交易数量

表中的单行是一笔交易，因此获取每日交易量非常容易。

SELECT
  DATE(block_timestamp, 'America/Los_Angeles') as date,
  COUNT(DISTINCT tx.hash) as num_tx
FROM `bigquery-public-data.crypto_bitcoin.transactions` as tx
GROUP BY 1
ORDER BY 1 DESC

作者截图。

计算比特币的总供应量

让我们建立在我们对比特币交易组成的了解之上。 每笔交易都有输入和输出。 输入是交易的“资金”来源，输出是资金的去向。

为了计算比特币的总供应量，也就是目前流通中的所有比特币，或者用第三种方式来说，所有挖出的比特币，我们需要对所有 UTXO 求和，这是区块链术语，表示未花费的交易输出。

简而言之，UTXO 是未作为任何其他交易的输入的输出。

BigQuery 交易表中的输入和输出是附加到交易行本身的嵌套字段。 非常方便！

DECLARE DateEnd DATE DEFAULT CURRENT_DATE() + 1;

WITH outs AS (
  SELECT
    tx.hash as tx_id,
    o.index,
    o.value
  FROM `bigquery-public-data.crypto_bitcoin.transactions` as tx, UNNEST(tx.outputs) as o
  WHERE block_timestamp_month <= DateEnd
),

ins AS (
  SELECT
    i.spent_transaction_hash as tx_id,
    i.spent_output_index as index
  FROM `bigquery-public-data.crypto_bitcoin.transactions` as tx, UNNEST(tx.inputs) as i
  WHERE block_timestamp_month <= DateEnd
)

SELECT
  SUM(outs.value)/1e8 as btc_total_supply
FROM outs
LEFT JOIN ins
ON
    ins.tx_id = outs.tx_id
    AND ins.index = outs.index
WHERE ins.tx_id IS NULL -- all unspend outputs (outputs without a following input) form total supply

作者截图。

计算币热度

币热度衡量 UTXO 的年龄。 如果昨天从交易中创建的 UTXO 在第二天被花费，它将被认为是“非常热的”，因为 UTXO 中的币在一天内移动了。 另一方面，可以有人将 UTXO 存储在冷存储中作为储蓄帐户，并且从未移动过他们的币，那么可能 5 年多都没有移动过的 UTXO 将被认为是冷的。

该计算显示了哪些类型的 UTXO 正在被花费。 最好随着时间的推移来查看它以捕捉趋势。 最常见的情况是，你可能会看到当长期持有者想要获取利润时，旧的 UTXO 会被花费。

DECLARE DateEnd DATE DEFAULT CURRENT_DATE() + 1; # '2013-01-01';
DECLARE Days DEFAULT [DateEnd]; # GENERATE_DATE_ARRAY("2009-01-01", DateEnd);

WITH outs AS (
  SELECT
    d as date,
    tx.block_timestamp,
    tx.hash as tx_id,
    o.index,
    o.value
  FROM `bigquery-public-data.crypto_bitcoin.transactions` as tx,
    UNNEST(tx.outputs) as o,
    UNNEST(Days) as d
  WHERE
    block_timestamp_month <= DateEnd
    AND DATE(tx.block_timestamp) <= d
),

ins AS (
  SELECT
    d as date,
    tx.block_timestamp,
    i.spent_transaction_hash as tx_id,
    i.spent_output_index as index
  FROM `bigquery-public-data.crypto_bitcoin.transactions` as tx,
    UNNEST(tx.inputs) as i,
    UNNEST(Days) as d
  WHERE
    block_timestamp_month <= DateEnd
    AND DATE(tx.block_timestamp) <= d
)

SELECT
  *,
  SUM(btc) OVER (PARTITION BY date) as btc_total_supply,
  ROUND(100 * btc / SUM(btc) OVER (PARTITION BY date), 2) as btc_supply_share
FROM (
  SELECT
    outs.date,
    CASE
      WHEN
        DATE_DIFF(outs.date, DATE(outs.block_timestamp), DAY) < 7 THEN '1. <7 DAY'
      WHEN
        DATE_DIFF(outs.date, DATE(outs.block_timestamp), DAY) < 35 THEN '2. <35 DAY'
      WHEN
        DATE_DIFF(outs.date, DATE(outs.block_timestamp), DAY) < 90 THEN '3. <90 DAY'
      WHEN
        DATE_DIFF(outs.date, DATE(outs.block_timestamp), DAY) < 180 THEN '4. <180 DAY'
      WHEN
        DATE_DIFF(outs.date, DATE(outs.block_timestamp), DAY) < 365 THEN '5. <1 YEAR'
      WHEN
        DATE_DIFF(outs.date, DATE(outs.block_timestamp), DAY) < 365*5 THEN '6. <5 YEARS'
      WHEN
        DATE_DIFF(outs.date, DATE(outs.block_timestamp), DAY) >= 365*5 THEN '7. >5 YEARS'
    END as hotness,
    SUM(outs.value/1e8) as btc

  FROM outs
  LEFT JOIN ins
  ON
      ins.date = outs.date
      AND ins.tx_id = outs.tx_id
      AND ins.index = outs.index
  WHERE ins.tx_id IS NULL
  GROUP BY 1, 2
)
ORDER BY 1 DESC, 2

总结

这就是全部！我们已经演练了一个真正基本的查询和几个可能超出“超级”基本级别的其他查询，所有这些查询都旨在让你了解比特币区块链的内部运作。 我希望你现在看到如何利用你现有的 SQL 技能来剖析、理解和发现其中的酷炫趋势。

这只是冰山一角。 隐藏在区块链中的潜在见解实际上是无限的，并且你会惊讶于数据的结构会多么迅速地变得像第二天性一样。

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