实用比特币链上数据查询指南：不依赖三方平台的完整思路与代码

比特币区块链是一座取之不尽的公开数据金矿。链上数据、每日交易数量、UTXO年龄、比特币总供应量与币热度等指标，对投资者、研究员乃至普通爱好者都极具价值。本文手把手教你如何在无需昂贵商业分析平台的前提下，借助 Google BigQuery 公共数据集 与 DuckDB 的高效查询能力，从 0 到 1 提炼比特币核心链上信息，同时避免数据超量收费。

“当你看得懂比特币转账日志，才算真正摸着区块链脉搏。” ——一位老矿工的口头禅

在 BigQuery 安全开启链上数据旅程

为什么选择 BigQuery

Google BigQuery 托管了完整、实时更新的比特币公共数据集 crypto_bitcoin.transactions。其核心优势：

全量节点同步：每笔交易、每个区块高度、每个 UTXO 的生成及消耗一目了然。
SQL 原生体验：一条查询即可层叠分析链上活动，规避了节点同步与解析的门槛。
全球即用：无需自建节点与带宽，只消耗查询扫描量。

不过，整张交易表现在已超 2 TB，若不加以限制，单次查询就可能触发高额付费。最佳实践是：

先用 WHERE 缩小范围，如限制 block_timestamp 区段。
把所需子集导出为 Parquet 并转移 Google Cloud Storage（导出免费，强烈推荐压缩）。
本地 DuckDB 离线分析：在本地硬盘用 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`
GROUP BY 1
ORDER BY 1 DESC;

在实际生产环境中，你可以把范围限制为过去 7 天或过去 30 天，既节省费用又足够跑策略。

核心查询示例二：实时计算比特币总供应量

理解 UTXO 模型是做好供应量的钥匙：任何未被当成输入再次花掉的输出都算“流通中的比特币”。

SQL 解析

DECLARE DateEnd DATE DEFAULT CURRENT_DATE();

-- 先把所有输出 clean 出来
WITH outs AS (
  SELECT
    tx_hash AS tx_id,
    output_index AS index,
    value
  FROM `bigquery-public-data.crypto_bitcoin.transactions`,
    UNNEST(outputs) AS o
  WHERE block_timestamp_month <= DateEnd
),

-- 再筛掉被花费的输出
ins AS (
  SELECT
    spent_transaction_hash AS tx_id,
    spent_output_index AS index
  FROM `bigquery-public-data.crypto_bitcoin.transactions`,
    UNNEST(inputs) AS i
  WHERE block_timestamp_month <= DateEnd
)

-- 最终 UTXO 总和 = 流通量
SELECT
  SUM(value)/1e8 AS btc_total_supply
FROM outs
LEFT JOIN ins
  ON outs.tx_id = ins.tx_id
  AND outs.index = ins.index
WHERE ins.tx_id IS NULL;

运行后会得到精确的流通 BTC 数量，实时刷新也能捕捉到 矿工奖励 尚未流入交易所的“新铸币”区段。

核心查询示例三：剖析 UTXO 年龄与币热度

定义币热度

“币热度”可理解为“UTXO 在钱包里沉睡多久”。小于 7 天的 UTXO 属于“极热”，大于 5 年的 UTXO 视为“极度冰冷”。我们按以下常用分层：

<7 天
7~35 天
35~90 天
90~180 天
180 天~1 年
1 年~5 年
>5 年

通过统计每层占比，可一目了然判断市场抛压及 长期持币者（HODLer）动向。

分层 SQL 示例

DECLARE DateEnd DATE DEFAULT CURRENT_DATE();
DECLARE Days DATE DEFAULT DateEnd;

WITH outs AS (
  SELECT
    d AS date,
    block_timestamp,
    tx_hash AS tx_id,
    output_index AS index,
    value
  FROM `bigquery-public-data.crypto_bitcoin.transactions`,
    UNNEST(outputs) AS o
  WHERE block_timestamp_month <= DateEnd
    AND DATE(block_timestamp) <= d
),
ins AS (
  SELECT
    d AS date,
    spent_transaction_hash AS tx_id,
    spent_output_index AS index
  FROM `bigquery-public-data.crypto_bitcoin.transactions`,
    UNNEST(inputs) AS i
  WHERE block_timestamp_month <= DateEnd
    AND DATE(block_timestamp) <= d
),
age_bucket AS (
  SELECT
    date,
    CASE
      WHEN DATE_DIFF(date, block_timestamp, DAY) < 7   THEN '<7d'
      WHEN DATE_DIFF(date, block_timestamp, DAY) < 35  THEN '7-35d'
      WHEN DATE_DIFF(date, block_timestamp, DAY) < 90  THEN '35-90d'
      WHEN DATE_DIFF(date, block_timestamp, DAY) < 180 THEN '90-180d'
      WHEN DATE_DIFF(date, block_timestamp, DAY) < 365 THEN '180d-1y'
      WHEN DATE_DIFF(date, block_timestamp, DAY) < 1825 THEN '1-5y'
      ELSE '>5y'
    END AS age_bucket,
    SUM(value/1e8) AS btc_sum
  FROM outs
  LEFT JOIN ins
    ON outs.date = ins.date AND outs.tx_id = ins.tx_id AND outs.index = ins.index
  WHERE ins.tx_id IS NULL
  GROUP BY 1,2
)

SELECT
  date,
  age_bucket,
  btc_sum,
  ROUND(btc_sum/SUM(btc_sum) OVER(PARTITION BY date)*100, 2) AS percent
FROM age_bucket
ORDER BY date DESC, age_bucket;

结果告诉我们，“<7 天”占比大说明短线资金活跃，“>5 年”占比攀升往往对应牛市后期 长期筹码集中兑现。

场景扩展：常见洞见外的三条深挖思路

交易所热钱包流量：通过锁定已知交易所地址的“转入/转出” UTXO，量化 交易所储备 增减。
矿工流通行为：用区块奖励原始 Coinbase UTXO 的首度转移，判断矿工是否倾向于套现。
MEV & 手续费战场：对输入输出 UTXO 的 Fee 做模型，评估近期 交易优先级 需求波动。

👉 掌握以上高阶方法，打开实时比特币链上雷达图。

常见问题解答（FAQ）

Q1：BigQuery 为什么会有 2 TB 免费门槛却仍然可能收费？
A：2 TB 对应的是 月度查询扫描量。一次性读整张 transactions 就会瞬间耗尽额度。建议每次加 WHERE block_timestamp BETWEEN xxx AND yyy 限制区间，或干脆导出至 GCS 用 DuckDB。

Q2：UTXO 年龄体系的意义在哪？
A：它直观刻画 币的活跃/沉淀比例。短线筹码激增往往预示波动加大；长线筹码上升则显 HODLer 信心+潜在抛压预期收敛。

Q3：BQL 与 DuckDB 语法差异大吗？
A：两者均兼容 标准 SQL。唯一差异在函数细节，例如 DuckDB 的日期转换函数叫作 STRFTIME()，而 BigQuery 使用 DATE()，TIMESTAMP_SUB() 等。迁移时 1:1 改一下即可。

Q4：如何快速锁定某个已知地址的全部 UTXO？
A：思路是用该地址作为 output.pubkey_script 过滤条件，再 LEFT JOIN input 检查是否已花费即可简单枚举。

Q5：有没有轻量级脚本一键同步最新区块？
A：推荐 Python + bitcoin-etl-airflow 或直接调用 BlockCypher API 抓 header 后再匹配自家 UTXO 库，成本低、实时性高。

Q6：如何验证自己的计算结果没有误差？
A：可利用公开跑分网站（如 bitcoinvisuals.com）进行 比特币总供应量 数值比对；误差 <0.01% 即可认为逻辑正确。若差异偏大，检查脚本是否漏掉 孤儿块 Coinbase 即可。

小结与实践清单

✅ 第一步：用受限时间窗口跑实测 SQL，掌握付费边界。
✅ 第二步：导出 Parquet → DuckDB，建立可复用本地仓库。
✅ 第三步：叠加自定义地址标签，构建属于个人的链上数据仪表盘。

善用 SQL，人人都能在千亿笔交易中提炼出金矿。祝你探索顺利，下一篇我们将深入 以太坊 Layer2 手续费模型。