分析数据

Timescale Cloud：性能、规模、企业级

自托管产品

MST

加载数据集后，您可以创建一些连续聚合，并开始构建查询以发现数据所揭示的信息。本教程使用 Timescale 超函数 (hyperfunctions) 来构建标准 PostgreSQL 中不可能实现的查询。

在本节中，您将学习如何编写回答以下问题的查询

创建连续聚合

您可以使用连续聚合来简化和加速查询。本教程需要三个连续聚合，分别侧重于数据集的三个方面：比特币交易、区块和 coinbase 交易。在每个连续聚合定义中，time_bucket() 函数控制时间桶的大小。所有示例均使用 1 小时的时间桶。

连续聚合：交易

连接到包含比特币数据集的 Timescale 数据库。

在 psql 提示符下，创建一个名为 one_hour_transactions 的连续聚合。此视图包含每小时交易的聚合数据


CREATE MATERIALIZED VIEW one_hour_transactions
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS bucket,
   count(*) AS tx_count,
   sum(fee) AS total_fee_sat,
   sum(fee_usd) AS total_fee_usd,
   stats_agg(fee) AS stats_fee_sat,
   avg(size) AS avg_tx_size,
   avg(weight) AS avg_tx_weight,
   count(
         CASE
            WHEN (fee > output_total) THEN hash
            ELSE NULL
         END) AS high_fee_count
  FROM transactions
  WHERE (is_coinbase IS NOT TRUE)
GROUP BY bucket;

添加刷新策略以保持连续聚合最新


SELECT add_continuous_aggregate_policy('one_hour_transactions',
   start_offset => INTERVAL '3 hours',
   end_offset => INTERVAL '1 hour',
   schedule_interval => INTERVAL '1 hour');

创建一个名为 one_hour_blocks 的连续聚合。此视图包含每小时挖出的所有区块的聚合数据


CREATE MATERIALIZED VIEW one_hour_blocks
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS bucket,
   block_id,
   count(*) AS tx_count,
   sum(fee) AS block_fee_sat,
   sum(fee_usd) AS block_fee_usd,
   stats_agg(fee) AS stats_tx_fee_sat,
   avg(size) AS avg_tx_size,
   avg(weight) AS avg_tx_weight,
   sum(size) AS block_size,
   sum(weight) AS block_weight,
   max(size) AS max_tx_size,
   max(weight) AS max_tx_weight,
   min(size) AS min_tx_size,
   min(weight) AS min_tx_weight
FROM transactions
WHERE is_coinbase IS NOT TRUE
GROUP BY bucket, block_id;

添加刷新策略以保持连续聚合最新


SELECT add_continuous_aggregate_policy('one_hour_blocks',
   start_offset => INTERVAL '3 hours',
   end_offset => INTERVAL '1 hour',
   schedule_interval => INTERVAL '1 hour');

创建一个名为 one_hour_coinbase 的连续聚合。此视图包含矿工每小时作为奖励收到的所有交易的聚合数据


CREATE MATERIALIZED VIEW one_hour_coinbase
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS bucket,
   count(*) AS tx_count,
   stats_agg(output_total, output_total_usd) AS stats_miner_revenue,
   min(output_total) AS min_miner_revenue,
   max(output_total) AS max_miner_revenue
FROM transactions
WHERE is_coinbase IS TRUE
GROUP BY bucket;

添加刷新策略以保持连续聚合最新


SELECT add_continuous_aggregate_policy('one_hour_coinbase',
   start_offset => INTERVAL '3 hours',
   end_offset => INTERVAL '1 hour',
   schedule_interval => INTERVAL '1 hour');

交易数量与交易费用之间是否存在联系？

交易费用是区块链用户关注的主要问题。如果区块链费用过高，您可能就不想使用它。此查询显示了比特币交易数量与费用之间是否存在关联。此分析的时间范围是过去 2 天。

如果您选择在 Grafana 中可视化查询，您可以查看随时间变化的平均交易量和每笔交易的平均费用。这些趋势可以帮助您决定是立即提交交易还是等待几天让费用降低。

查找交易数量与交易费用之间的联系

连接到包含比特币数据集的 Timescale 数据库。

在 psql 提示符下，使用此查询从 one_hour_transactions 连续聚合中获取平均交易量和费用


SELECT
 bucket AS "time",
 tx_count as "tx volume",
 average(stats_fee_sat) as fees
FROM one_hour_transactions
WHERE bucket > date_add('2023-11-22 00:00:00+00', INTERVAL '-2 days')
ORDER BY 1;

您返回的数据看起来有点像这样


time          | tx volume |        fees
------------------------+-----------+--------------------
2023-11-20 01:00:00+00 |      2602 | 105963.45810914681
2023-11-20 02:00:00+00 |     33037 | 26686.814117504615
2023-11-20 03:00:00+00 |     42077 | 22875.286546094067
2023-11-20 04:00:00+00 |     46021 | 20280.843180287262
2023-11-20 05:00:00+00 |     20828 | 24694.472969080085
...

可选要在 Grafana 中将其可视化，请创建一个新面板，选择比特币数据集作为数据源，并键入上一步中的查询。在 Format as（格式化为）部分，选择 Time series（时间序列）。

交易量是否影响 BTC-USD 汇率？

在加密货币交易中，存在大量投机行为。您可以通过查看区块链指标之间的关联，例如交易量与比特币和美元之间的当前汇率，来采用基于数据的交易策略。

如果您选择在 Grafana 中可视化查询，您可以看到平均交易量以及比特币兑美元的转换汇率。

查找交易量和 BTC-USD 汇率

连接到包含比特币数据集的 Timescale 数据库。

在 psql 提示符下，使用此查询返回交易量和比特币兑美元汇率


SELECT
 bucket AS "time",
 tx_count as "tx volume",
 total_fee_usd / (total_fee_sat*0.00000001) AS "btc-usd rate"
FROM one_hour_transactions
WHERE bucket > date_add('2023-11-22 00:00:00+00', INTERVAL '-2 days')
ORDER BY 1;

您返回的数据看起来有点像这样


time          | tx volume |    btc-usd rate
------------------------+-----------+--------------------
 2023-06-13 08:00:00+00 |     20063 | 25975.888587931426
 2023-06-13 09:00:00+00 |     16984 |  25976.00446352126
 2023-06-13 10:00:00+00 |     15856 | 25975.988587014584
 2023-06-13 11:00:00+00 |     24967 |  25975.89166787936
 2023-06-13 12:00:00+00 |      8575 | 25976.004209699528
 ...

可选要在 Grafana 中将其可视化，请创建一个新面板，选择比特币数据集作为数据源，并键入上一步中的查询。在 Format as（格式化为）部分，选择 Time series（时间序列）。
可选为了使此可视化更具用性，添加一个覆盖以将费用放在不同的 Y 轴上。在选项面板中，为 btc-usd rate 字段添加一个覆盖，对于 Axis > Placement（轴 > 位置）选择 Right（右侧）。

区块中交易越多，挖矿成本是否越高？

区块中的交易数量会影响总区块挖矿费用。对于此分析，需要更大的时间范围，因此将分析时间范围增加到 5 天。

如果您选择在 Grafana 中可视化查询，您可以看到区块中的交易越多，挖矿费用就越高。

查找区块中交易增多是否意味着挖矿成本更高

连接到包含比特币数据集的 Timescale 数据库。

在 psql 提示符下，使用此查询返回区块中的交易数量与挖矿费用对比数据


SELECT
 bucket as "time",
 avg(tx_count) AS transactions,
 avg(block_fee_sat)*0.00000001 AS "mining fee"
FROM one_hour_blocks
WHERE bucket > date_add('2023-11-22 00:00:00+00', INTERVAL '-5 days')
GROUP BY bucket
ORDER BY 1;

您返回的数据看起来有点像这样


time          |     transactions      |       mining fee
------------------------+-----------------------+------------------------
 2023-06-10 08:00:00+00 | 2322.2500000000000000 | 0.29221418750000000000
 2023-06-10 09:00:00+00 | 3305.0000000000000000 | 0.50512649666666666667
 2023-06-10 10:00:00+00 | 3011.7500000000000000 | 0.44783255750000000000
 2023-06-10 11:00:00+00 | 2874.7500000000000000 | 0.39303009500000000000
 2023-06-10 12:00:00+00 | 2339.5714285714285714 | 0.25590717142857142857
...

可选要在 Grafana 中将其可视化，请创建一个新面板，选择比特币数据集作为数据源，并键入上一步中的查询。在 Format as（格式化为）部分，选择 Time series（时间序列）。
可选为了使此可视化更具用性，添加一个覆盖以将费用放在不同的 Y 轴上。在选项面板中，为 mining fee 字段添加一个覆盖，对于 Axis > Placement（轴 > 位置）选择 Right（右侧）。

您可以扩展此分析，以查找区块权重与挖矿费用之间是否存在相同的关联。更多交易应该会增加区块权重，并同时提高矿工费用。

如果您选择在 Grafana 中可视化查询，您可以看到区块权重与挖矿费用之间存在相同类型的高度关联。当区块权重接近其最大值（400 万权重单位）时，这种关系会减弱，在这种情况下，区块不可能包含更多交易。

查找更高的区块权重是否意味着挖矿成本更高

连接到包含比特币数据集的 Timescale 数据库。

在 psql 提示符下，使用此查询返回区块权重与挖矿费用对比数据


SELECT
 bucket as "time",
 avg(block_weight) as "block weight",
 avg(block_fee_sat*0.00000001) as "mining fee"
FROM one_hour_blocks
WHERE bucket > date_add('2023-11-22 00:00:00+00', INTERVAL '-5 days')
group by bucket
ORDER BY 1;

您返回的数据看起来有点像这样


time          |     block weight     |       mining fee
------------------------+----------------------+------------------------
 2023-06-10 08:00:00+00 | 3992809.250000000000 | 0.29221418750000000000
 2023-06-10 09:00:00+00 | 3991766.333333333333 | 0.50512649666666666667
 2023-06-10 10:00:00+00 | 3992918.250000000000 | 0.44783255750000000000
 2023-06-10 11:00:00+00 | 3991873.000000000000 | 0.39303009500000000000
 2023-06-10 12:00:00+00 | 3992934.000000000000 | 0.25590717142857142857
...

可选要在 Grafana 中将其可视化，请创建一个新面板，选择比特币数据集作为数据源，并键入上一步中的查询。在 Format as（格式化为）部分，选择 Time series（时间序列）。
可选为了使此可视化更具用性，添加一个覆盖以将费用放在不同的 Y 轴上。在选项面板中，为 mining fee 字段添加一个覆盖，对于 Axis > Placement（轴 > 位置）选择 Right（右侧）。

与区块奖励相比，平均矿工收入中费用占比多少？

在之前的查询中，您看到当区块权重和交易量较高时，挖矿费用也较高。此查询从不同角度分析数据。矿工收入不仅包括矿工费用，还包括挖出新区块的区块奖励。目前，此奖励为 6.25 BTC，每四年减半。此查询着眼于矿工收入中有多少来自费用，并与区块奖励进行比较。

如果您选择在 Grafana 中可视化查询，您可以看到大多数矿工收入实际上来自区块奖励。费用从未占总收入的百分之几以上。

查找与区块奖励相比，平均矿工收入中费用占比多少

连接到包含比特币数据集的 Timescale 数据库。

在 psql 提示符下，使用此查询返回 coinbase 交易，以及区块费用和奖励


WITH coinbase AS (
   SELECT block_id, output_total AS coinbase_tx FROM transactions
   WHERE is_coinbase IS TRUE and time > date_add('2023-11-22 00:00:00+00', INTERVAL '-5 days')
)
SELECT
   bucket as "time",
   avg(block_fee_sat)*0.00000001 AS "fees",
   FIRST((c.coinbase_tx - block_fee_sat), bucket)*0.00000001 AS "reward"
FROM one_hour_blocks b
INNER JOIN coinbase c ON c.block_id = b.block_id
GROUP BY bucket
ORDER BY 1;

您返回的数据看起来有点像这样


time          |          fees          |   reward
------------------------+------------------------+------------
 2023-06-10 08:00:00+00 | 0.28247062857142857143 | 6.25000000
 2023-06-10 09:00:00+00 | 0.50512649666666666667 | 6.25000000
 2023-06-10 10:00:00+00 | 0.44783255750000000000 | 6.25000000
 2023-06-10 11:00:00+00 | 0.39303009500000000000 | 6.25000000
 2023-06-10 12:00:00+00 | 0.25590717142857142857 | 6.25000000
...

可选要在 Grafana 中将其可视化，请创建一个新面板，选择比特币数据集作为数据源，并键入上一步中的查询。在 Format as（格式化为）部分，选择 Time series（时间序列）。
可选为了使此可视化更具用性，将系列堆叠至 100%。在选项面板的 Graph styles（图表样式）部分，对于 Stack series（堆叠系列）选择 100%。

区块权重如何影响矿工费用？

您已经发现区块中交易越多，挖矿成本就越高。在此查询中，您询问区块权重是否也如此？一个区块的交易越多，其权重越大，因此区块权重和挖矿费用应紧密相关。此查询使用 12 小时移动平均线来计算随时间变化的区块权重和区块挖矿费用。

如果您选择在 Grafana 中可视化查询，您可以看到区块权重和区块挖矿费用紧密相关。实际上，您还可以看到 400 万权重单位的大小限制。这意味着单个区块仍有增长空间，并且它们可以包含更多交易。

查找区块权重如何影响矿工费用

连接到包含比特币数据集的 Timescale 数据库。

在 psql 提示符下，使用此查询返回区块权重，以及区块费用和奖励


WITH stats AS (
   SELECT
       bucket,
       stats_agg(block_weight, block_fee_sat) AS block_stats
   FROM one_hour_blocks
   WHERE bucket > date_add('2023-11-22 00:00:00+00', INTERVAL '-5 days')
   GROUP BY bucket
)
SELECT
   bucket as "time",
   average_y(rolling(block_stats) OVER (ORDER BY bucket RANGE '12 hours' PRECEDING)) AS "block weight",
   average_x(rolling(block_stats) OVER (ORDER BY bucket RANGE '12 hours' PRECEDING))*0.00000001 AS "mining fee"
FROM stats
ORDER BY 1;

您返回的数据看起来有点像这样


time          |    block weight    |     mining fee
------------------------+--------------------+---------------------
 2023-06-10 09:00:00+00 | 3991766.3333333335 |  0.5051264966666666
 2023-06-10 10:00:00+00 | 3992424.5714285714 | 0.47238710285714286
 2023-06-10 11:00:00+00 |            3992224 | 0.44353000909090906
 2023-06-10 12:00:00+00 |  3992500.111111111 | 0.37056557222222225
 2023-06-10 13:00:00+00 |         3992446.65 | 0.39728022799999996
...

可选要在 Grafana 中将其可视化，请创建一个新面板，选择比特币数据集作为数据源，并键入上一步中的查询。在 Format as（格式化为）部分，选择 Time series（时间序列）。
可选为了使此可视化更具用性，添加一个覆盖以将费用放在不同的 Y 轴上。在选项面板中，为 mining fee 字段添加一个覆盖，对于 Axis > Placement（轴 > 位置）选择 Right（右侧）。

每个区块的平均矿工收入是多少？

在此最终查询中，您分析了矿工通过在区块链上挖出新区块实际产生了多少收入，包括费用和区块奖励。为了使分析更有趣，添加比特币兑美元汇率，并增加时间范围。

查找每个区块的平均矿工收入

连接到包含比特币数据集的 Timescale 数据库。

在 psql 提示符下，使用此查询返回每个区块的平均矿工收入，并附带 12 小时移动平均线


SELECT
   bucket as "time",
   average_y(rolling(stats_miner_revenue) OVER (ORDER BY bucket RANGE '12 hours' PRECEDING))*0.00000001 AS "revenue in BTC",
    average_x(rolling(stats_miner_revenue) OVER (ORDER BY bucket RANGE '12 hours' PRECEDING)) AS "revenue in USD"
FROM one_hour_coinbase
WHERE bucket > date_add('2023-11-22 00:00:00+00', INTERVAL '-5 days')
ORDER BY 1;

您返回的数据看起来有点像这样


time          |   revenue in BTC   |   revenue in USD
------------------------+--------------------+--------------------
 2023-06-09 14:00:00+00 |       6.6732841925 |        176922.1133
 2023-06-09 15:00:00+00 |  6.785046736363636 |  179885.1576818182
 2023-06-09 16:00:00+00 |       6.7252952905 | 178301.02735000002
 2023-06-09 17:00:00+00 |  6.716377454814815 |  178064.5978074074
 2023-06-09 18:00:00+00 |    6.7784206471875 |   179709.487309375
...

可选要在 Grafana 中将其可视化，请创建一个新面板，选择比特币数据集作为数据源，并键入上一步中的查询。在 Format as（格式化为）部分，选择 Time series（时间序列）。
可选为了使此可视化更具用性，添加一个覆盖以将美元放在不同的 Y 轴上。在选项面板中，为 mining fee 字段添加一个覆盖，对于 Axis > Placement（轴 > 位置）选择 Right（右侧）。