Consultando o dcrdata

25 minuto(s) de leitura

Atualizado em:

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, per day

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

-- ## PoS rewards

COPY (
SELECT transactions.block_height,
sum(vins.value_in) AS sum_pos_reward
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.tx_type = 2
GROUP BY 1
ORDER BY 1)
TO '/tmp/psql_block_pos-rewards.csv' CSV HEADER;


-- ## 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

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)
TO '/tmp/psql_block_coin-issuance.csv' CSV HEADER;


-- ## Voters per block, difficulty and hashpower (GH/s) per block

COPY (
SELECT blocks.time, blocks.height, blocks.voters, blocks.pool_size, blocks.difficulty,
round((blocks.difficulty*(2^32)/300/1000000000)::numeric, 8) AS hashrate
FROM blocks
WHERE blocks.is_mainchain 
ORDER BY 1)
TO '/tmp/psql_block_voters_difficulty.csv' CSV HEADER;


-- ## Network funding per block

COPY (
SELECT transactions.block_time, transactions.block_height, round(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
ORDER BY 1)
TO '/tmp/psql_block_network-funding.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;


-- ## Blocks invalidated by PoS

COPY (
SELECT blocks.height, blocks.hash
FROM blocks
WHERE is_valid = false
ORDER BY 1)
TO '/tmp/psql_block_pos-invalidated-blocks.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.csv' CSV HEADER;


-- ## Agendas, lock-in blocks

COPY (
SELECT agendas.id, agendas.name, agendas.locked_in, blocks.time, agendas.activated
FROM agendas
LEFT JOIN blocks ON blocks.height = agendas.locked_in)
TO '/tmp/psql_general_agendas-lockin.csv' CSV HEADER;


-- ## Votes per proposal per day

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

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;

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
COPY 1527
COPY 1528
COPY 2147690
COPY 1526
COPY 1527
COPY 1527
COPY 1527
COPY 4574
COPY 436398
COPY 440494
COPY 329726
COPY 440494
COPY 440494
COPY 440492
COPY 434939
COPY 9
COPY 4
COPY 5
COPY 474
COPY 59
COPY 1