Consultando o dcrdata
1. Introdução
O dcrdata fornece uma interface web para analisar o conteúdo de blocos, transações e endereços de carteiras, além de tickets e votos. Para conferir o funcionamento do dcrdata acesse https://dcrdata.decred.org/.
Por trás dessa interface web há um banco de dados PostgreSQL que armazena a informação contida na blockchain do Decred. Essa informação também pode ser consultada através da linguagem SQL.
Para saber mais sobre a instalação do dcrdata, veja o artigo dcrdata: executando o seu próprio explorador de blocos.
2. Realizando Consultas
Se precisar de ajudar para se encontrar na linguagem SQL, por favor veja https://www.postgresql.org/docs/12/tutorial-sql.html
Após fazer logon no terminal, acesse a console do psql para começar a a fazer consultas no banco de dados.
$ sudo su - postgres
postgres@ip-172-31:~$ psql
psql (12.2 (Ubuntu 12.2-2.pgdg18.04+1))
Type "help" for help.
postgres=#
O comando \l mostra uma lista de bancos de dados:
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+---------+-----------------------
dcrdata | dcrdata | UTF8 | C.UTF-8 | C.UTF-8 |
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
postgres=#
Você pode ver a lista de roles (papéis) com o comando \du:
dcrdata=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
dcrdata | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
dcrdata=#
Vamos tentar algumas consultas. Primeiro, nos conectamos ao banco de dados dcrdata:
postgres=# \c dcrdata
You are now connected to database "dcrdata" as user "postgres".
Agora, vemos a lista de tabelas:
dcrdata=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------------+-------+---------
public | addresses | table | dcrdata
public | agenda_votes | table | dcrdata
public | agendas | table | dcrdata
public | block_chain | table | dcrdata
public | blocks | table | dcrdata
public | meta | table | dcrdata
public | misses | table | dcrdata
public | proposal_votes | table | dcrdata
public | proposals | table | dcrdata
public | stats | table | dcrdata
public | testing | table | dcrdata
public | tickets | table | dcrdata
public | transactions | table | dcrdata
public | vins | table | dcrdata
public | votes | table | dcrdata
public | vouts | table | dcrdata
(16 rows)
dcrdata=#
Vamos dar uma olhada na tabela blocks e listar suas colunas:
dcrdata=# \d blocks
Table "public.blocks"
Column | Type | Collation | Nullable | Default
---------------+--------------------------+-----------+----------+------------------------------------
id | integer | | not null | nextval('blocks_id_seq'::regclass)
hash | text | | not null |
height | integer | | |
size | integer | | |
is_valid | boolean | | |
is_mainchain | boolean | | |
version | integer | | |
numtx | integer | | |
num_rtx | integer | | |
tx | text[] | | |
txdbids | bigint[] | | |
num_stx | integer | | |
stx | text[] | | |
stxdbids | bigint[] | | |
time | timestamp with time zone | | |
nonce | bigint | | |
vote_bits | smallint | | |
voters | smallint | | |
fresh_stake | smallint | | |
revocations | smallint | | |
pool_size | integer | | |
bits | integer | | |
sbits | bigint | | |
difficulty | double precision | | |
stake_version | integer | | |
previous_hash | text | | |
chainwork | text | | |
winners | text[] | | |
Indexes:
"blocks_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "stats" CONSTRAINT "stats_blocks_id_fkey" FOREIGN KEY (blocks_id) REFERENCES blocks(id) ON DELETE CASCADE
dcrdata=#
Agora que sabemos o nome das colunas (campos), vamos realizar uam consulta bem simples, mostrando apenas o ID do bloco, o hash e a data e hora de quando esse bloco foi minerado, apenas para o último bloco disponível na tabela:
dcrdata=# select id, hash, height, time from blocks where is_mainchain order by height desc limit 1;
id | hash | height | time
--------+------------------------------------------------------------------+--------+------------------------
428923 | 00000000000000001407d9857e98a398bcbdcfcc68f3b790b56eff8b7a265d6d | 428922 | 2020-03-04 08:03:09+00
(1 row)
dcrdata=#
Você pode escrever comando SQL em caixa baixa (select) ou caixa alta (SELECT). Você também pode escrever as consultas em um editor de texto e depois copia-las para o psql:
dcrdata=# SELECT id, hash, height, time
dcrdata-# FROM blocks
dcrdata-# WHERE is_mainchain
dcrdata-# ORDER BY height DESC
dcrdata-# LIMIT 1;
id | hash | height | time
--------+------------------------------------------------------------------+--------+------------------------
428923 | 00000000000000001407d9857e98a398bcbdcfcc68f3b790b56eff8b7a265d6d | 428922 | 2020-03-04 08:03:09+00
(1 row)
Mostrar data e hora do bloco Genesis:
dcrdata=# SELECT time FROM blocks WHERE height = 0 AND is_mainchain;
time
------------------------
2016-02-08 18:00:00+00
(1 row)
Para contar quantos votos/tickets foram perdidos em cada bloco juntamos as tabelas blocks e misses onde o hash do bloco é o mesmo:
dcrdata=# SELECT blocks.height, count(*)
FROM blocks
LEFT JOIN misses ON misses.block_hash = blocks.hash
WHERE blocks.is_mainchain
AND misses.block_hash IS NOT NULL
GROUP BY blocks.hash, blocks.height, misses.block_hash
ORDER BY blocks.height DESC
LIMIT 10;
height | count
--------+-------
430235 | 1
430213 | 1
430211 | 1
430192 | 1
430179 | 1
430143 | 1
430046 | 1
430034 | 1
430025 | 1
430024 | 1
(10 rows)
Para exportar a informação que acabamos de consultar, inserimos a consulta dentro de um comando COPY e o resultado será exportado para um arquivo CSV local no servidor PostgreSQL:
dcrdata=# COPY (SELECT blocks.height, count(*)
FROM blocks
LEFT JOIN misses ON misses.block_hash = blocks.hash
WHERE blocks.is_mainchain
AND misses.block_hash IS NOT NULL
GROUP BY blocks.hash, blocks.height, misses.block_hash
ORDER BY blocks.height)
TO '/opt/decred/query_blocks_number_missing_votes.copy' (DELIMITER ',');
COPY 27786
Se o usuário tiver permissão de escrita no diretório selecionado e a consulta for válida, o comando COPY retornará o número de blocos afetados pela consulta como mostrado no bloco de código mais abaixo. No entanto, se o usuário que executa o psql não possuir permissão de escrita (neste exemplo, postgres), o psql retornará um erro:
ERROR: could not open file "/opt/decred/query_blocks_number_missing_votes.copy" for writing: Permission denied
Também é possível exportar uma única tabela para a tela ou para um arquivo CSV usando o seguinte comando:
dcrdata=# COPY agendas TO STDOUT (DELIMITER ',');
1,fixlnseqlocks,4,334720,342784,0
2,headercommitments,3,423424,431488,0
3,lnfeatures,4,181504,189568,0
4,lnsupport,4,141184,149248,0
5,sdiffalgorithm,4,141184,149248,0
dcrdata=# COPY agendas TO '/tmp/table_agendas.copy' (DELIMITER ',');
COPY 5
O resultado da consulta pode depois ser importado em uma planilha:
$ cat /tmp/table_agendas.copy
1,fixlnseqlocks,4,334720,342784,0
2,headercommitments,3,423424,431488,0
3,lnfeatures,4,181504,189568,0
4,lnsupport,4,141184,149248,0
5,sdiffalgorithm,4,141184,149248,0
Os dados também podem ser exportados diretamente para um arquivo comprimido com gzip:
dcrdata=# COPY agendas TO PROGRAM 'gzip > /tmp/table_agendas.gz' (DELIMITER ',');
COPY 5
$ cd /tmp
$ gunzip table_agendas.gz
$ cat table_agendas
1,fixlnseqlocks,4,334720,342784,0
2,headercommitments,3,423424,431488,0
3,lnfeatures,4,181504,189568,0
4,lnsupport,4,141184,149248,0
5,sdiffalgorithm,4,141184,149248,0
Consultas podem ser salvas em arquivo e executadas a partir da interface psql:
$ cat saved_query.sql
select id, hash, time from blocks order by id desc limit 1;
On psql console:
dcrdata=# \i /tmp/saved_query.sql
id | hash | time
--------+------------------------------------------------------------------+------------------------
429359 | 0000000000000000071a908d391de522359772584cfe01cdfed7679526c26e23 | 2020-03-05 20:23:58+00
(1 row)
A mesma consulta também pode ser executada a partir do terminal, chamando o psql com a opção ‘-f’, o arquivo com as consultas e o nome do banco de dados:
$ sudo su - postgres
$ psql -f /tmp/saved_query.sql dcrdata
id | hash | time
--------+------------------------------------------------------------------+------------------------
429360 | 000000000000000017abdd65034289701cb5459999432c67a974db7b03ab6952 | 2020-03-05 20:39:14+00
(1 row)
Para exportar essa informação para um arquivo CSV onde se encontra o cliente psql poemos usar o comando psql \copy. Nesta arquitetura o comando pode ser executado na estação de trabalho:
dcrdata=# \copy (SELECT blocks.height, count(*)
FROM blocks
LEFT JOIN misses ON misses.block_hash = blocks.hash
WHERE blocks.is_mainchain
AND misses.block_hash IS NOT NULL
GROUP BY blocks.hash, blocks.height, misses.block_hash
ORDER BY blocks.height)
TO '/tmp/psql_copy_query_blocks_number_missing_votes.copy' (DELIMITER ',');
COPY 27795
Para contar o número de transações vistas em um único dia:
dcrdata=#
SELECT date_trunc('day', transactions.time) "day", count(*) count
FROM transactions
GROUP BY 1
ORDER BY 1 DESC
LIMIT 5;
day | count
------------------------+-------
2020-03-05 00:00:00+00 | 3485
2020-03-04 00:00:00+00 | 4320
2020-03-03 00:00:00+00 | 3776
2020-03-02 00:00:00+00 | 4518
2020-03-01 00:00:00+00 | 4249
(5 rows)
Se quisermos contar as transações por dia do mês, veremos que não há uma diferença considerável entre os dias, exceto pelo dia 31 que só aparece em 5 meses do ano.
dcrdata=#
SELECT date_part('day', date_trunc('day', transactions.time)) "day", count(*) count
FROM transactions
WHERE transactions.is_mainchain
GROUP BY 1
ORDER BY 1 ASC;
day | count
-----+--------
1 | 246694
2 | 233318
3 | 254283
4 | 233808
5 | 240129
6 | 233029
7 | 252154
8 | 243241
9 | 254109
10 | 249953
11 | 243353
12 | 238609
13 | 248959
14 | 234290
15 | 246270
16 | 241284
17 | 256803
18 | 240874
19 | 249321
20 | 243396
21 | 253549
22 | 228318
23 | 254519
24 | 247674
25 | 242932
26 | 248428
27 | 249318
28 | 249812
29 | 227964
30 | 223903
31 | 139490
(31 rows)
O começo das semanas têm sido geralmente mais movimentado do que o seu final, de Segunda (1) a Domingo (7).
dcrdata=#
SELECT date_part('isodow', date_trunc('day', transactions.time)) "day", count(*) count
FROM transactions
GROUP BY 1
ORDER BY 1 asc;
day | count
-----+---------
1 | 1085754
2 | 1079497
3 | 1067169
4 | 1061087
5 | 1052175
6 | 1053308
7 | 1037039
(7 rows)
3. Analisando a blockchain
Agora que já vimos como as informações podem ser acessadas e extraídas do banco de dados, vamos tentar consultas mais úteis:
Número de votos por bloco:
dcrdata=#
SELECT voters, count(*) count
FROM blocks
WHERE blocks.is_mainchain
GROUP BY 1
ORDER BY 1 ASC;
voters | count
--------+--------
0 | 4096
3 | 5594
4 | 22219
5 | 398405
(4 rows)
Votos em propostas de hard-fork, por tipo de voto:
dcrdata=#
SELECT agendas.id, agendas.name, agendas.locked_in, count(agenda_votes.agenda_vote_choice), agenda_votes.agenda_vote_choice
FROM agenda_votes
LEFT JOIN agendas ON agendas.id = agenda_votes.agendas_row_id
GROUP BY 1, 2, 5
ORDER BY 1;
id | name | locked_in | count | agenda_vote_choice
----+-------------------+-----------+--------+--------------------
1 | fixlnseqlocks | 334720 | 206426 | 0
1 | fixlnseqlocks | 334720 | 206679 | 1
1 | fixlnseqlocks | 334720 | 383 | 2
2 | headercommitments | 423424 | 60315 | 0
2 | headercommitments | 423424 | 52830 | 1
2 | headercommitments | 423424 | 31 | 2
3 | lnfeatures | 181504 | 336637 | 0
3 | lnfeatures | 181504 | 398081 | 1
3 | lnfeatures | 181504 | 3933 | 2
4 | lnsupport | 141184 | 130743 | 0
4 | lnsupport | 141184 | 82563 | 1
4 | lnsupport | 141184 | 2318 | 2
5 | sdiffalgorithm | 141184 | 135855 | 0
5 | sdiffalgorithm | 141184 | 77558 | 1
5 | sdiffalgorithm | 141184 | 2211 | 2
(15 rows)
Preços de tickets por dia, ordenados pelo preço do ticket, ordem decrescente:
dcrdata=#
SELECT tickets.price, date_trunc('day', blocks.time) "day"
FROM tickets
LEFT JOIN blocks ON tickets.block_hash = blocks.hash
GROUP BY 1, 2
ORDER BY 1 DESC
LIMIT 10;
price | day
--------------+------------------------
238.87091996 | 2017-05-17 00:00:00+00
229.42820188 | 2017-07-02 00:00:00+00
210.51896924 | 2017-05-04 00:00:00+00
192.19848074 | 2017-06-10 00:00:00+00
182.41479251 | 2017-05-08 00:00:00+00
180.42908734 | 2017-06-08 00:00:00+00
177.77634486 | 2017-06-04 00:00:00+00
175.588235 | 2017-04-23 00:00:00+00
168.90177663 | 2017-05-16 00:00:00+00
167.67392382 | 2017-04-06 00:00:00+00
(10 rows)
Preços de tickets ordenados por data:
dcrdata=#
SELECT round(avg(tickets.price)::numeric, 8) AS price, date_trunc('day', blocks.time) "day"
FROM tickets
LEFT JOIN blocks ON tickets.block_hash = blocks.hash
GROUP BY 2
ORDER BY 2 DESC
LIMIT 10;
price | day
--------------+------------------------
159.87989362 | 2020-03-06 00:00:00+00
163.78460567 | 2020-03-05 00:00:00+00
166.42117114 | 2020-03-04 00:00:00+00
165.62853168 | 2020-03-03 00:00:00+00
161.34051020 | 2020-03-02 00:00:00+00
159.19549174 | 2020-03-01 00:00:00+00
157.08347541 | 2020-02-29 00:00:00+00
152.40068677 | 2020-02-28 00:00:00+00
147.12867181 | 2020-02-27 00:00:00+00
139.57991829 | 2020-02-26 00:00:00+00
(10 rows)
Preços de tickets, tamanho da pool de tickets em unidades e tamanho da pool de tickets em DCRs para um conjunto selecionado de tempo/blocos:
dcrdata=#
SELECT split_part(CAST (date_trunc('day', blocks.time) AS text), ' ', 1) "day", round(avg(tickets.price)::numeric, 8) AS price_avg, round(avg(stats.pool_size)::numeric,0) AS pool_avg, to_char(round(avg(pool_val / 100000000)::numeric,8), '999G999G999D99999999') AS pool_dcr_avg
FROM tickets
LEFT JOIN blocks ON tickets.block_hash = blocks.hash
LEFT JOIN stats ON tickets.block_height = stats.height
GROUP BY 1
ORDER BY 1 DESC
LIMIT 10;
day | price_avg | pool_avg | pool_dcr_avg
------------+--------------+----------+-----------------------
2020-03-07 | 155.11263808 | 40493 | 5,589,930.60869565
2020-03-06 | 159.55606587 | 40655 | 5,597,250.48536355
2020-03-05 | 163.78460567 | 40975 | 5,610,394.03621908
2020-03-04 | 166.42117114 | 41329 | 5,625,127.40570934
2020-03-03 | 165.62853168 | 41502 | 5,612,721.80111008
2020-03-02 | 161.34051020 | 41256 | 5,536,460.62266501
2020-03-01 | 159.19549174 | 41523 | 5,548,136.23614275
2020-02-29 | 157.08347541 | 41723 | 5,545,257.63585434
2020-02-28 | 152.40068677 | 41857 | 5,541,337.43271375
2020-02-27 | 147.12867181 | 42131 | 5,562,362.08414726
(10 rows)
Recompensas PoS para um conjunto selecionado de tempo/blocos:
dcrdata=#
SELECT vins.block_time, to_char(round(sum(vins.value_in)/100000000, 8), '999G999G999D99999999') as total_reward
FROM vins
JOIN transactions ON vins.tx_hash = transactions.tx_hash
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND transactions.block_height > 429700
AND NOT (vins.is_valid = false AND vins.tx_tree = 0)
AND vins.is_mainchain
GROUP BY vins.block_time, transactions.block_height
ORDER BY transactions.block_height DESC
LIMIT 10;
block_time | total_reward
------------------------+-----------------------
2020-03-07 03:15:19+00 | 15.70079703
2020-03-07 03:02:02+00 | 15.70079703
2020-03-07 03:00:21+00 | 15.70079703
2020-03-07 02:49:05+00 | 15.70079703
2020-03-07 02:43:05+00 | 15.70079703
2020-03-07 02:38:51+00 | 15.70079703
2020-03-07 02:38:17+00 | 15.70079703
2020-03-07 02:37:34+00 | 15.70079703
2020-03-07 02:37:08+00 | 15.70079703
2020-03-07 02:36:28+00 | 15.70079703
(10 rows)
Recompensas para o Tesouro (fundo da rede) para um conjunto selecionado de tempo/blocos:
dcrdata=#
SELECT blocks.time, transactions.block_height, transactions.tx_hash, vins.value_in, vouts.value AS value_out, vouts.script_addresses
FROM transactions
JOIN vins ON transactions.tx_hash = vins.tx_hash
JOIN vouts ON transactions.tx_hash = vouts.tx_hash
JOIN blocks ON blocks.height = transactions.block_height
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND vouts.script_addresses = '{Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}'
AND transactions.block_height > 430220
ORDER BY transactions.block_height DESC;
time | block_height | tx_hash | value_in | value_out | script_addresses
------------------------+--------------+------------------------------------------------------------------+------------+-----------+---------------------------------------
2020-03-08 18:23:41+00 | 430236 | 21e6946dc6ff2db0026d45f4d6d8eb1dd313d39fbb829812850e327c9a76d212 | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 18:21:05+00 | 430235 | 0fd423064536a758d0979cc7cfe3e3ccf5d0ba11ea61acc6ee58a679e1a81dd7 | 870539240 | 124362748 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 18:19:16+00 | 430234 | 72b10362b2f6ef8c1748ce404579eca6a67abccd718f63446f3a5fe18fba3fbb | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 17:47:45+00 | 430233 | 1069b2b00523146224a25d2a1b53533e7005bc34aa3e3ee989fb058e73d626bf | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 17:36:06+00 | 430232 | 6c98cb143474302b2212adcaf0a603e5b834f82c2e8dcf90a4f0c1741ca86d6c | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 17:32:00+00 | 430231 | cd6c8597e4e8fd90c21dc7e2d99ad09708add3bbde860053bcf3aad9b9bba50c | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 17:07:53+00 | 430230 | 61689f6a2c2371d6822bb3dd690ae4ef6a0cd2f8335756398c71431845306ee2 | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 17:05:52+00 | 430229 | 6521268dd452e1ca27d060948120318a032ee20abe1f48a7ea20e5f17e010d78 | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 17:03:35+00 | 430228 | af4a02431fd28dc1d389c5095ffd2a207deb04279f963a7ed3c916de0f156c72 | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 17:00:14+00 | 430227 | 54f1a566f3f034e4abc92c8c7c2f75f728d7d7293ecea60fc5bbe1481dd404ee | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 16:53:34+00 | 430226 | 29f2aae9f77f3bec1453e1b155b346cbe57b79d0e321e6d160564b91c28402be | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 16:39:13+00 | 430225 | 6cc3ef2f6dff3d4d2ecbcb0257fef8eedf9742d67249872b66e9fc05a3b3978c | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 16:31:46+00 | 430224 | f20090e4093d96936180e1415df39db2db386af6b45c1c52e97902357a399d88 | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 16:19:20+00 | 430223 | 05e6c5c614d8c60530473a9c218e2fb9b7f8d1af591bcea9b5fcf39e8eeb71bf | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 16:07:27+00 | 430222 | cbc400c2e345481d266211b8f33624a0f72f70c0c5724a6060d510b293192c66 | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
2020-03-08 15:56:40+00 | 430221 | e677fcc23c17b9ba0bf9ba872bfa42876badaa37afe30e4c6400dab5ad34f76a | 1088174052 | 155453436 | {Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}
(16 rows)
dcrdata=#
Recompensa PoW + PoS quando em um bloco onde foram minerados apenas 4 tickets:
dcrdata=#
SELECT blocks.height, transactions.tx_type, transactions.tx_hash, vins.value_in
FROM transactions
JOIN vins ON transactions.tx_hash = vins.tx_hash
JOIN blocks ON blocks.height = transactions.block_height
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND transactions.block_height = 430235;
height | tx_type | tx_hash | value_in
--------+---------+------------------------------------------------------------------+-----------
430235 | 0 | 0fd423064536a758d0979cc7cfe3e3ccf5d0ba11ea61acc6ee58a679e1a81dd7 | 870539240
430235 | 2 | 3ba558b0d30d6b05ebc9ad410f6fd4f31b2cf727cdf585006579d2b4d40bd201 | 93272061
430235 | 2 | 7eab6f02288e3b81132d80a7a9ac4e7249e2568a3930a885b4e286432a0e28d8 | 93272061
430235 | 2 | 956effcd7de8baba1fcd457ebb9b460dcb0424d0a53239328759bafe38d19b19 | 93272061
430235 | 2 | 98231fbb4a1d5221043f8f7ea65ec48bd8c76dc9a7b24e4f6616dd690ac72a35 | 93272061
(5 rows)
Recompensas PoW + PoS em um bloco com 5 tickets minerados:
dcrdata=#
SELECT blocks.height, transactions.tx_type, transactions.tx_hash, vins.value_in
FROM transactions
JOIN vins ON transactions.tx_hash = vins.tx_hash
JOIN blocks ON blocks.height = transactions.block_height
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND transactions.block_height = 430236;
height | tx_type | tx_hash | value_in
--------+---------+------------------------------------------------------------------+------------
430236 | 0 | 21e6946dc6ff2db0026d45f4d6d8eb1dd313d39fbb829812850e327c9a76d212 | 1088174052
430236 | 2 | cbc7e8016459d074339e8100305d4116edea76273227af6428bb6d53a10bb4f1 | 93272061
430236 | 2 | c3586bed4d251913375f0bd785b667b25c75cfed6643f20d8d0cd201047c3f37 | 93272061
430236 | 2 | 90d2a41a776f29c434b00887cc9419a6ee80ed3d1bdc54b4be74802ac1750554 | 93272061
430236 | 2 | d973db0f97a4f02fe54dba6e0fc3f031e7ad2572d359b6f9a48eee1ff4120e43 | 93272061
430236 | 2 | 4654fee23f11e273546f78691112ed828623b03be779be474ec0d3f5ca8a7fa8 | 93272061
Transações de voto dos mineradores PoS quanto à validade do bloco.
dcrdata=#
SELECT transactions.block_height, transactions.tx_type, transactions.tx_hash, vins.value_in, votes.block_valid
FROM transactions
JOIN vins ON transactions.tx_hash = vins.tx_hash
JOIN votes ON transactions.tx_hash = votes.tx_hash
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND transactions.block_height = 430211;
height | tx_type | tx_hash | value_in | block_valid
--------+---------+------------------------------------------------------------------+----------+-------------
430211 | 2 | 2c9c7fd6be58e30e51a0615b4959d70d4aa07e0f02770072c689a687205468c9 | 93272061 | t
430211 | 2 | a4bac8236396cb697ca7ed80fb747ddb19b08696b3e2ea99e4d358c00d9936cf | 93272061 | t
430211 | 2 | e8cea42de220f2437a4735fb74f0ae458ceecaae9480b18424398cf2e3b88653 | 93272061 | t
430211 | 2 | 7be75a5c122d274c34414175cf7e6bdd9621c43687bce1131fcb92b6d36815dd | 93272061 | t
(4 rows)
Número de tickets minerados por bloco e total das recompensas PoS por bloco:
dcrdata=#
WITH pre_select AS (
SELECT DISTINCT transactions.block_height, transactions.tx_hash, vins.value_in, transactions.is_mainchain
FROM transactions
JOIN vins ON transactions.tx_hash = vins.tx_hash
JOIN votes ON transactions.tx_hash = votes.tx_hash
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND transactions.is_mainchain
AND transactions.block_height > 430210
AND transactions.block_height < 430215
)
SELECT pre_select.block_height, count(*) AS tickets, SUM(pre_select.value_in) AS total_pos_reward
FROM pre_select
GROUP BY 1
ORDER BY pre_select.block_height DESC;
block_height | tickets | total_pos_reward
--------------+---------+------------------
430214 | 5 | 466360305
430213 | 4 | 373088244
430212 | 5 | 466360305
430211 | 4 | 373088244
(4 rows)
O Tesouro do Decred usa o endereço Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx hardcoded para receber a recompensa do fundo da rede:
dcrdata=#
SELECT transactions.block_height, transactions.tx_type, transactions.tx_hash, vouts.value
FROM transactions
JOIN vins ON transactions.tx_hash = vins.tx_hash
JOIN vouts ON transactions.tx_hash = vouts.tx_hash
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND vouts.script_addresses = '{Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}'
AND transactions.is_mainchain
AND transactions.block_height = 430211;
block_height | tx_type | tx_hash | value
--------------+---------+------------------------------------------------------------------+-----------
430211 | 0 | 16ba1d52c2142866be6f9cab0b3a2ab909ccb7409325ae975e97ddcb7eeeb0e2 | 124362748
(1 row)
Recompensa total do minerador PoW (novas moedas + taxas) podem ser obtidas com a seguinte consulta:
dcrdata=#
SELECT transactions.block_height, transactions.tx_hash, vouts.value, vouts.script_addresses
FROM transactions
JOIN vins ON transactions.tx_hash = vins.tx_hash
JOIN vouts ON transactions.tx_hash = vouts.tx_hash
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND NOT (vouts.script_addresses = '{Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}' OR vouts.script_addresses = '{}')
AND transactions.is_mainchain
AND transactions.tx_type = 0
AND transactions.block_height = 430211;
block_height | tx_hash | value | script_addresses
--------------+------------------------------------------------------------------+-----------+---------------------------------------
430211 | 16ba1d52c2142866be6f9cab0b3a2ab909ccb7409325ae975e97ddcb7eeeb0e2 | 747152367 | {DsnxqhJX2tjyjbfb9y4yPdpJ744G9fLhbbF}
(1 row)
Recompensas e taxas por bloco:
WITH pre_select AS (
SELECT transactions.block_time as block_time, transactions.block_height as block_height, transactions.tx_hash as tx_hash, transactions.tx_type as tx_type, vins.prev_tx_hash, vins.value_in as value_in, vouts.value as vouts_value, vouts.script_addresses as addresses
FROM transactions
JOIN vins ON transactions.tx_hash = vins.tx_hash
JOIN vouts ON transactions.tx_hash = vouts.tx_hash
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND transactions.is_mainchain
AND transactions.block_height > 430350
AND transactions.block_height < 430355
),
table_pos_reward AS (
SELECT pre_select.block_height, pre_select.value_in as value_in, pre_select.tx_hash
FROM pre_select
JOIN votes ON pre_select.tx_hash = votes.tx_hash
WHERE pre_select.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND pre_select.tx_type = 2
AND NOT pre_select.vouts_value = 0
GROUP BY 1,2,3
),
table_pow_reward AS (
SELECT pre_select.block_height, pre_select.vouts_value as value_out, pre_select.addresses
FROM pre_select
WHERE NOT (pre_select.addresses = '{Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}' OR pre_select.addresses = '{}')
AND pre_select.tx_type = 0
),
table_net_fund AS (
SELECT pre_select.block_height, pre_select.vouts_value as value_out
FROM pre_select
WHERE pre_select.addresses = '{Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}'
)
SELECT pre_select.block_time, pre_select.block_height, SUM(table_pos_reward.value_in) AS pos_reward,
pre_select.value_in AS pow_and_netfund, table_pow_reward.value_out AS fees_pow_reward,
table_net_fund.value_out AS net_fund, (pre_select.value_in - table_net_fund.value_out) AS pow_reward,
(table_pow_reward.value_out - (pre_select.value_in - table_net_fund.value_out)) AS fees
FROM pre_select
JOIN table_pos_reward ON table_pos_reward.block_height = pre_select.block_height
JOIN table_pow_reward ON table_pow_reward.block_height = pre_select.block_height
JOIN table_net_fund ON table_net_fund.block_height = pre_select.block_height
WHERE pre_select.addresses = '{Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}'
GROUP BY 1,2,4,5,6
ORDER BY pre_select.block_height DESC;
block_time | block_height | pos_reward | pow_and_netfund | fees_pow_reward | net_fund | pow_reward | fees
------------------------+--------------+------------+-----------------+-----------------+-----------+------------+---------
2020-03-09 03:38:00+00 | 430354 | 466360305 | 1088174052 | 932736880 | 155453436 | 932720616 | 16264
2020-03-09 03:37:49+00 | 430353 | 466360305 | 1088174052 | 932800966 | 155453436 | 932720616 | 80350
2020-03-09 03:36:44+00 | 430352 | 466360305 | 1088174052 | 936213068 | 155453436 | 932720616 | 3492452
2020-03-09 03:25:11+00 | 430351 | 466360305 | 1088174052 | 935152952 | 155453436 | 932720616 | 2432336
(4 rows)
Se você quiser saber quanto tempo está gastando em cada consulta, ligue/desligue o medidor de tempo com o comando \timing:
dcrdata=# \timing
Timing is on.
dcrdata=#
Esta consulta será exeuctada após ser preparada e salva na memória com o nome ‘prep_query’. Consultas preparadas podem usar variáveis (criadas na primeira linha de código do próximo bloco eusadas no seu interior com o comando WITH) que serão definidas quando a consulta for executada (no bloco de código após este):
dcrdata=#
PREPARE prep_query(int, int) AS
WITH pre_select AS (
SELECT transactions.block_time as block_time, transactions.block_height as block_height, transactions.tx_hash as tx_hash, transactions.tx_type as tx_type, vins.prev_tx_hash, vins.value_in as value_in, vouts.value as vouts_value, vouts.script_addresses as addresses
FROM transactions
LEFT JOIN vins ON transactions.tx_hash = vins.tx_hash
LEFT JOIN vouts ON transactions.tx_hash = vouts.tx_hash
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND transactions.is_mainchain
AND transactions.block_height > $1
AND transactions.block_height < $2
),
table_pos_reward AS (
SELECT pre_select.block_height, pre_select.value_in as value_in
FROM pre_select
LEFT JOIN votes ON pre_select.tx_hash = votes.tx_hash
WHERE pre_select.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND pre_select.tx_type = 2
AND NOT pre_select.vouts_value = 0
GROUP BY 1,2
),
table_pow_reward AS (
SELECT pre_select.block_height, pre_select.vouts_value as value_out
FROM pre_select
WHERE NOT (pre_select.addresses = '{Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}' OR pre_select.addresses = '{}')
AND pre_select.tx_type = 0
),
table_net_fund AS (
SELECT pre_select.block_height, pre_select.vouts_value as value_out
FROM pre_select
WHERE pre_select.addresses = '{Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}'
)
SELECT split_part(CAST (date_trunc('day', pre_select.block_time) AS text), ' ', 1) "day",
pre_select.block_height,
table_pos_reward.value_in AS pos_reward,
pre_select.value_in AS pow_and_netfund,
table_pow_reward.value_out AS fees_pow_reward,
table_net_fund.value_out AS net_fund,
(pre_select.value_in - table_net_fund.value_out) AS pow_reward,
(table_pow_reward.value_out - (pre_select.value_in - table_net_fund.value_out)) AS fees
FROM pre_select
LEFT JOIN table_pos_reward ON table_pos_reward.block_height = pre_select.block_height
LEFT JOIN table_pow_reward ON table_pow_reward.block_height = pre_select.block_height
LEFT JOIN table_net_fund ON table_net_fund.block_height = pre_select.block_height
WHERE pre_select.addresses = '{Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}'
ORDER BY 1,2 DESC;
O comando anterior retorna ‘PREPARE’. Com a consulta preparada na memória, é possível seguir e executá-la, definindo as variáveis no momento da execução:
PREPARE
dcrdata=# EXECUTE prep_query(430350, 430356);
day | block_height | pos_reward | pow_and_netfund | fees_pow_reward | net_fund | pow_reward | fees
------------+--------------+------------+-----------------+-----------------+-----------+------------+---------
2020-03-09 | 430355 | 93272061 | 1088174052 | 933059337 | 155453436 | 932720616 | 338721
2020-03-09 | 430354 | 93272061 | 1088174052 | 932736880 | 155453436 | 932720616 | 16264
2020-03-09 | 430353 | 93272061 | 1088174052 | 932800966 | 155453436 | 932720616 | 80350
2020-03-09 | 430352 | 93272061 | 1088174052 | 936213068 | 155453436 | 932720616 | 3492452
2020-03-09 | 430351 | 93272061 | 1088174052 | 935152952 | 155453436 | 932720616 | 2432336
(5 rows)
Time: 1337.231 ms (00:01.337)
Se uma consulta está demorando muito tempo você pode usar o comando EXPLAIN para fazer com que o PostgreSQL mostre como está gastando os recursos planejando e executando as consultas para que você tente otimizá-la. Para consultas já preparadas, apenas insira ‘EXPLAIN ANALYZE’ antes do comando de execução:
dcrdata=# EXPLAIN ANALYZE EXECUTE query(430350, 430356);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=381285.52..381285.53 rows=1 width=88) (actual time=1323.605..1323.609 rows=5 loops=1)
Sort Key: (split_part((date_trunc('day'::text, pre_select.block_time))::text, ' '::text, 1)), pre_select.block_height DESC
Sort Method: quicksort Memory: 25kB
CTE pre_select
-> Nested Loop Left Join (cost=1001.25..381276.45 rows=4 width=213) (actual time=672.349..1321.550 rows=100 loops=1)
-> Nested Loop (cost=1000.56..381203.29 rows=1 width=158) (actual time=672.313..1320.606 rows=30 loops=1)
-> Gather (cost=1000.00..381122.32 rows=1 width=85) (actual time=672.250..1324.611 rows=89 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on transactions (cost=0.00..380122.22 rows=1 width=85) (actual time=657.914..1306.638 rows=30 loops=3)
Filter: (is_mainchain AND (block_height > 430350) AND (block_height < 430356))
Rows Removed by Filter: 2486292
-> Index Scan using uix_vin on vins (cost=0.56..80.94 rows=3 width=138) (actual time=0.020..0.021 rows=0 loops=89)
Index Cond: (tx_hash = transactions.tx_hash)
Filter: (prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'::text)
Rows Removed by Filter: 6
-> Index Scan using uix_vout_txhash_ind on vouts (cost=0.69..72.98 rows=17 width=120) (actual time=0.019..0.022 rows=3 loops=30)
Index Cond: (tx_hash = transactions.tx_hash)
(...)
Para executar PREPARE em outra consulta com o mesmo nome, é necessário desalocar a consulta anterior com o comando DEALLOCATE:
dcrdata=# DEALLOCATE prep_query;
DEALLOCATE
4. Analisansdo a governança
Para analisar as caractirísticas da governança do Decred podemos coletar estatísticas por dia e por bloco. O bloco abaixo pode ser salvo em um arquivo de consultas que será utilizado logo a seguir. Esses números serão utilizados em outro artigo.
-- # Daily statistics
-- ## PoS rewards
COPY (
SELECT split_part(CAST (date_trunc('day', transactions.block_time) AS text), ' ', 1) "day",
round(sum(vins.value_in)/ 100000000::numeric, 8) AS sum_pos_reward
FROM transactions
LEFT JOIN vins ON transactions.tx_hash = vins.tx_hash
LEFT JOIN votes ON transactions.tx_hash = votes.tx_hash
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND transactions.is_mainchain
AND transactions.tx_type = 2
GROUP BY 1
ORDER BY 1)
TO '/tmp/psql_daily_pos-rewards.csv' CSV HEADER;
-- ## Transactions count, volume
COPY (
SELECT split_part(CAST (date_trunc('day', transactions.block_time) AS text), ' ', 1) "day",
count(*) AS tx_count,
round(sum(transactions.sent)/ 100000000::numeric, 8) AS sum_sent,
round(sum(transactions.fees)/ 100000000::numeric, 8) AS sum_fees
FROM transactions
WHERE transactions.fees > 0
AND transactions.is_mainchain
GROUP BY 1
ORDER BY 1)
TO '/tmp/psql_daily_transactions-volume.csv' CSV HEADER;
-- ## Block count
COPY (
SELECT split_part(CAST (date_trunc('day', blocks.time) AS text), ' ', 1) "day",
COUNT(DISTINCT blocks.height) AS block_count
FROM blocks
WHERE blocks.is_mainchain
GROUP BY 1
ORDER BY 1)
TO '/tmp/psql_daily_block-count.csv' CSV HEADER;
-- ## Block count, historical average (count) per day
COPY (
SELECT split_part(CAST (date_trunc('day', blocks.time) AS text), ' ', 1) "day",
COUNT(*) AS block_count
FROM blocks
WHERE blocks.is_mainchain
GROUP BY 1
UNION ALL
SELECT 'avg = ', round(avg(a.block_count), 2) FROM
(SELECT split_part(CAST (date_trunc('day', blocks.time) AS text), ' ', 1) "day",
COUNT(*) AS block_count
FROM blocks
WHERE blocks.is_mainchain
GROUP BY 1) a
ORDER BY 1)
TO '/tmp/psql_daily_block-time-historical-average.csv' CSV HEADER;
-- ## PoS reward per ticket / per block / per day
COPY (
SELECT tickets.tx_hash, tickets.block_height, tickets.spend_height,
(tickets.spend_height - tickets.block_height) AS waited_blocks,
tickets.price, votes.vote_reward,
split_part(CAST (date_trunc('day', votes.block_time) AS text), ' ', 1) "day"
FROM tickets
LEFT JOIN votes ON tickets.tx_hash = votes.ticket_hash
WHERE tickets.is_mainchain
AND tickets.spend_type = 2
ORDER BY 3)
TO '/tmp/psql_block_pos-reward-time-ticket-blocks.csv' CSV HEADER;
-- ## Ticket price / pool size / pool value
COPY (
SELECT split_part(CAST (date_trunc('day', blocks.time) AS text), ' ', 1) "day",
round(avg(tickets.price)::numeric, 8) AS avg_price,
round(avg(stats.pool_size)::numeric, 0) AS avg_pool,
round(avg(pool_val / 100000000)::numeric, 8) AS avg_pool_dcr
FROM tickets
LEFT JOIN blocks ON tickets.block_hash = blocks.hash
LEFT JOIN stats ON tickets.block_height = stats.height
WHERE tickets.is_mainchain
GROUP BY 1
ORDER BY 1)
TO '/tmp/psql_daily_ticket-price_pool-size.csv' CSV HEADER;
-- ## Coin Issuance / Supply
COPY (
SELECT x.day,
x.total_reward AS sum_total_reward,
sum(x.total_reward) OVER (ORDER BY x.day ASC) as sum_reward
FROM (
SELECT t.day,
sum(t.total_reward) as total_reward
FROM (
SELECT split_part(CAST (date_trunc('day', transactions.block_time) AS text), ' ', 1) "day",
transactions.block_height,
round(sum(vins.value_in)/100000000, 8) as total_reward
FROM vins
JOIN transactions ON vins.tx_hash = transactions.tx_hash
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND NOT (vins.is_valid = false)
AND vins.is_mainchain
GROUP BY 1,2
ORDER BY 2 ASC
) AS t
GROUP BY 1
) AS x)
TO '/tmp/psql_daily_coin-issuance.csv' CSV HEADER;
-- ## Average voters per block per day, average difficulty and hashpower (GH/s) per day
COPY (
SELECT split_part(CAST (date_trunc('day', blocks.time) AS text), ' ', 1) "day",
round(avg(blocks.voters::numeric), 0) AS avg_block_voters,
round(avg(blocks.pool_size)::numeric, 0) AS avg_block_poolsize,
round(avg(blocks.difficulty)::numeric, 8) AS avg_block_difficulty,
round(avg(blocks.difficulty*(2^32)/300/1000000000)::numeric, 8) AS avg_hashrate
FROM blocks
WHERE blocks.is_mainchain
GROUP BY 1
ORDER BY 1)
TO '/tmp/psql_daily_voters_difficulty.csv' CSV HEADER;
-- ## Network funding per day
COPY (
SELECT split_part(CAST (date_trunc('day', transactions.block_time) AS text), ' ', 1) "day",
round(sum(vouts.value)/100000000::numeric, 8) AS netfund_reward
FROM transactions
JOIN vins ON transactions.tx_hash = vins.tx_hash
JOIN vouts ON transactions.tx_hash = vouts.tx_hash
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND vouts.script_addresses = '{Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}'
AND transactions.is_mainchain
GROUP BY 1
ORDER BY 1)
TO '/tmp/psql_daily_network-funding.csv' CSV HEADER;
-- ## Fees, difficulty, transaction count per day
COPY (
SELECT split_part(CAST (date_trunc('day', transactions.block_time) AS text), ' ', 1) "day",
round(sum(transactions.fees) / 100000000::numeric, 8) as sum_fees,
blocks.difficulty,
count(*) as count_tx
FROM transactions
LEFT JOIN blocks ON blocks.height = transactions.block_height
WHERE transactions.fees > 0
AND transactions.is_mainchain
GROUP BY 1,3
ORDER BY 1)
TO '/tmp/psql_daily_fees-difficulty-txcount.csv' CSV HEADER;
-- # Block statistics
-- ## Transactions count, volume
COPY (
SELECT transactions.block_height,
count(*) AS tx_count,
sum(transactions.sent) AS sum_sent,
sum(transactions.fees) AS sum_fees
FROM transactions
WHERE transactions.is_mainchain
GROUP BY 1
ORDER BY 1)
TO '/tmp/psql_block_transactions-volume.csv' (DELIMITER ',');
-- ## Ticket price / pool size / pool value
COPY (
SELECT blocks.height,
round(avg(tickets.price)::numeric, 8) AS avg_price,
round(avg(stats.pool_size)::numeric, 0) AS avg_pool,
round(avg(pool_val / 100000000)::numeric, 8) AS avg_pool_dcr
FROM tickets
LEFT JOIN blocks ON tickets.block_hash = blocks.hash
LEFT JOIN stats ON tickets.block_height = stats.height
WHERE tickets.is_mainchain
GROUP BY 1
ORDER BY 1)
TO '/tmp/psql_block_ticket-price_pool-size.csv' CSV HEADER;
-- ## Coin Issuance / Supply (total reward)
COPY (
SELECT t.block_time,
t.block_height,
t.total_reward AS sum_total_reward,
round(sum(t.total_reward) OVER (ORDER BY t.block_time ASC), 8) as acum_total_reward
FROM (
SELECT vins.block_time,
transactions.block_height,
round(sum(vins.value_in)/100000000, 8) as total_reward
FROM vins
JOIN transactions ON vins.tx_hash = transactions.tx_hash
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND NOT (vins.is_valid = false)
AND vins.is_mainchain
GROUP BY 1,2
ORDER BY 2 ASC)
AS t LIMIT 10)
TO '/tmp/psql_block_coin-issuance.csv' CSV HEADER;
-- ## Coin Issuance / Supply (PoS reward)
COPY (
SELECT transactions.block_time, transactions.block_height, transactions.tx_type, round(round(vins.value_in, 8)/100000000, 8) AS pos_reward, votes.block_valid
FROM transactions
JOIN vins ON transactions.tx_hash = vins.tx_hash
JOIN blocks ON blocks.height = transactions.block_height
JOIN votes ON transactions.tx_hash = votes.tx_hash
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND blocks.is_mainchain
ORDER BY 1)
TO '/tmp/psql_block_coin-issuance-pos_reward.csv' CSV HEADER;
-- ## Coin Issuance / Supply (Network fund)
COPY (
SELECT transactions.block_time, transactions.block_height, transactions.tx_type, round(round(vouts.value, 8)/100000000, 8) AS net_fund
FROM transactions
JOIN vins ON transactions.tx_hash = vins.tx_hash
JOIN vouts ON transactions.tx_hash = vouts.tx_hash
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND vouts.script_addresses = '{Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}'
AND transactions.is_mainchain
ORDER BY 1)
TO '/tmp/psql_block_coin-issuance-network_fund.csv' CSV HEADER;
-- ## Coin Issuance / Supply (PoW reward)
COPY (
SELECT transactions.block_time, transactions.block_height, transactions.tx_type, round(round(vouts.value, 8)/100000000, 8) AS pow_reward, vouts.script_addresses
FROM transactions
JOIN vins ON transactions.tx_hash = vins.tx_hash
JOIN vouts ON transactions.tx_hash = vouts.tx_hash
WHERE vins.prev_tx_hash = '0000000000000000000000000000000000000000000000000000000000000000'
AND NOT (vouts.script_addresses = '{Dcur2mcGjmENx4DhNqDctW5wJCVyT3Qeqkx}' OR vouts.script_addresses = '{}')
AND transactions.is_mainchain
AND transactions.tx_type = 0
ORDER BY 1)
TO '/tmp/psql_block_coin-issuance-pow_reward.csv' CSV HEADER;
-- ## Voters / Difficulty
COPY (
SELECT blocks.time, blocks.height, blocks.voters, blocks.pool_size, blocks.difficulty
FROM blocks
WHERE blocks.is_mainchain
ORDER BY blocks.height)
TO '/tmp/psql_block_voters_difficulty.csv' CSV HEADER;
-- ## Fees, difficulty, transaction count per block
COPY (
SELECT transactions.block_height,
round(sum(transactions.fees) / 100000000::numeric, 8) AS sum_fees,
blocks.difficulty, count(*) AS tx_count
FROM transactions
LEFT JOIN blocks ON blocks.height = transactions.block_height
WHERE transactions.fees > 0
AND transactions.is_mainchain
GROUP BY 1,3
ORDER BY 1)
TO '/tmp/psql_block_fees-difficulty-txcount.csv' CSV HEADER;
-- # General statistics
-- ## Voters per block, mined tickets
COPY (
SELECT voters AS voters_per_block, count(*) count
FROM blocks
WHERE blocks.is_mainchain
GROUP BY 1
ORDER BY 1 ASC)
TO '/tmp/psql_general_voters-block-count-alltime-aggregate.csv' CSV HEADER;
COPY (
SELECT voters AS voters_per_block, count(*) count
FROM blocks
WHERE blocks.is_mainchain
AND split_part(CAST (date_trunc('day', blocks.time) AS text), ' ', 1) between '2016-02-08' and '2020-02-10'
GROUP BY 1
ORDER BY 1 ASC)
TO '/tmp/psql_general_voters-block-count-2016-02-08-to-2020-02-10-aggregate.csv' CSV HEADER;
COPY (
SELECT blocks.time, blocks.height, voters AS voters_per_block
FROM blocks
WHERE blocks.is_mainchain
ORDER BY 1 ASC)
TO '/tmp/psql_general_voters_per_block-alltime.csv' CSV HEADER;
-- ## Agendas, lock-in blocks (on-chain)
COPY (
SELECT agendas.id, agendas.name, agendas.status, agendas.locked_in, blocks.time AS lockin_time, agendas.activated
FROM agendas
LEFT JOIN blocks ON blocks.height = agendas.locked_in)
TO '/tmp/psql_general_agendas-lockin.csv' CSV HEADER;
-- ## Agendas, voting results (on-chain)
COPY (
SELECT agendas.id, agendas.name,
COUNT(CASE WHEN agenda_votes.agenda_vote_choice = '0' THEN 1 ELSE NULL END) AS yes,
COUNT(CASE WHEN agenda_votes.agenda_vote_choice = '1' THEN 1 ELSE NULL END) AS abs,
COUNT(CASE WHEN agenda_votes.agenda_vote_choice = '2' THEN 1 ELSE NULL END) AS no
FROM agenda_votes
LEFT JOIN agendas ON agendas.id = agenda_votes.agendas_row_id
LEFT JOIN votes ON votes.id = agenda_votes.votes_row_id
LEFT JOIN blocks ON blocks.height = votes.height
WHERE votes.is_mainchain
AND votes.height <= agendas.locked_in
AND votes.height > agendas.locked_in - 8064
GROUP BY 1, 2
ORDER BY 1)
TO '/tmp/psql_general_agenda_vote_results.csv' CSV HEADER;
-- ## Votes per proposal per day (off-chain)
COPY (
SELECT proposals.token, split_part(CAST (date_trunc('day', proposals.time) AS text), ' ', 1) "day",
COUNT(CASE WHEN proposal_votes.choice = 'No' THEN 1 ELSE NULL END) AS no,
COUNT(CASE WHEN proposal_votes.choice = 'Yes' THEN 1 ELSE NULL END) AS yes
FROM proposal_votes
INNER JOIN proposals on proposals.id = proposal_votes.proposals_row_id
GROUP BY 1,2
ORDER BY 1)
TO '/tmp/psql_general_vote-change.csv' CSV HEADER;
-- ## Votes per proposal (off-chain)
COPY (
SELECT proposals.token, split_part(CAST (date_trunc('year', proposals.time) AS text), '-', 1) "year",
COUNT(CASE WHEN proposal_votes.choice = 'No' THEN 1 ELSE NULL END) AS no,
COUNT(CASE WHEN proposal_votes.choice = 'Yes' THEN 1 ELSE NULL END) AS yes,
COUNT(*) AS total
FROM proposal_votes
INNER JOIN proposals on proposals.id = proposal_votes.proposals_row_id
GROUP BY 1,2
ORDER BY 1)
TO '/tmp/psql_general_vote-total-number.csv' CSV HEADER;
-- ## Historical average ticket pool size
COPY (
SELECT 'avg = ', round(avg(a.pool_size), 0) FROM
(SELECT pool_size FROM stats) a)
TO '/tmp/psql_general_average-ticket-pool-size.csv' CSV HEADER;
-- ## Ticket time to draw
COPY (
SELECT votes.block_time, votes.ticket_hash, votes.height, tickets.block_height, votes.ticket_price, votes.vote_reward, tickets.is_split
FROM votes
LEFT JOIN tickets ON tickets.tx_hash = votes.ticket_hash
WHERE tickets.is_mainchain
ORDER BY 1)
TO '/tmp/psql_ticket-time-draw.csv' CSV HEADER;
-- ## Revoked tickets (transactions)
COPY (
SELECT transactions.block_height, transactions.tx_hash, transactions.tx_type
FROM transactions
WHERE transactions.is_mainchain
AND transactions.is_valid
AND transactions.tx_type = 3
ORDER BY 1)
TO '/tmp/psql_ticket-revoked.csv' CSV HEADER;
-- ## Genesis block coinbase spent, airdrop users, spent or not
COPY (
SELECT vouts.script_addresses, vouts.value, transactions.tx_hash, transactions.block_height, transactions.num_vout
FROM vouts
LEFT JOIN transactions ON vouts.spend_tx_row_id = transactions.id
WHERE vouts.tx_hash = '5e29cdb355b3fc7e76c98a9983cd44324b3efdd7815c866e33f6c72292cb8be6'
AND vouts.value = 28263795424
ORDER BY 4,2)
TO '/tmp/psql_transactions-genesis_block-airdrop.csv' CSV HEADER;
-- ## Genesis block coinbase spent, airdrop users, spent likely in tickets
COPY (
SELECT vouts.script_addresses, vouts.value, transactions.tx_hash, transactions.block_height, transactions.num_vout
FROM vouts
LEFT JOIN transactions ON vouts.spend_tx_row_id = transactions.id
WHERE vouts.tx_hash = '5e29cdb355b3fc7e76c98a9983cd44324b3efdd7815c866e33f6c72292cb8be6'
AND vouts.value = 28263795424
AND transactions.num_vout > 2
ORDER BY 4,2)
TO '/tmp/psql_transactions-genesis_block-airdrop-spent-likely-tickets.csv' CSV HEADER;
-- ## Genesis block coinbase spent, founders, spent or not
COPY (
SELECT vouts.script_addresses, vouts.value, transactions.tx_hash, transactions.block_height, transactions.num_vout
FROM vouts
LEFT JOIN transactions ON vouts.spend_tx_row_id = transactions.id
WHERE vouts.tx_hash = '5e29cdb355b3fc7e76c98a9983cd44324b3efdd7815c866e33f6c72292cb8be6'
AND NOT vouts.value = 28263795424
ORDER BY 4,2)
TO '/tmp/psql_transactions-genesis_block-founders.csv' CSV HEADER;
-- ## Genesis block coinbase, founders, spent likely in tickets
COPY (
SELECT vouts.script_addresses, vouts.value, transactions.tx_hash, transactions.block_height, transactions.num_vout, unnest(transactions.vout_db_ids) spent_id
FROM vouts
LEFT JOIN transactions ON vouts.spend_tx_row_id = transactions.id
WHERE vouts.tx_hash = '5e29cdb355b3fc7e76c98a9983cd44324b3efdd7815c866e33f6c72292cb8be6'
AND NOT vouts.value = 28263795424
AND transactions.num_vout > 2
ORDER BY 4,2)
TO '/tmp/psql_transactions-genesis_block-founders-spent-likely-tickets.csv' CSV HEADER;
O conjunto anterior de consultas foi copiado para um arquivo de texto e executado pelo psql como mostrado a seguir:
$ psql -f /tmp/psql-queries.txt dcrdata
COPY 1514
COPY 1527
(...)