Topic: [BEGINNER WORKSHOP]: bitcoincharts + postgres = cool sql queries (Read 5760 times)

#!/bin/bash

db="mtgox"
user="postgres"
symbol="mtgoxUSD"
PSQL="psql -q -t -U ${user} ${db}"
DL="curl -s"

function sql() {
  rc=$(echo "$1" | $PSQL)
}

function extend() {
  sql "drop table if exists import;"
  sql "create table import (id serial, unixtime int, price numeric(32,10), volume numeric(32,8), type smallint);"
  
  len=$(( 4 * 60 * 60 ))
  while true; do
    sql "select max(unixtime) from trades;"
    start=$rc
    start_human=$(date -d "@$start")
    echo "--- $start_human ----------------------------------------------------------"
    end=$(( $start + $len ))
    echo "start ($start) + len ($len) = end ($end)"
    
    # download
    $DL "http://bitcoincharts.com/t/trades.csv?symbol=$symbol&start=$start&end=$end" > trades.csv
    end_file=$(head -n 1 trades.csv | cut -d , -f 1)
    start_file=$(tail -n 1 trades.csv | cut -d , -f 1)
    echo "start_file ($start_file) - start ($start) = $(( $start_file - $start ))"
    echo "end_file ($end_file) - end ($end) = $(( $end_file - $end ))"
    if [ $start_file -eq $start ]; then
      echo start times match, updating trades table
      # put into import table
      sql "delete from import;"
      sql "\copy import(unixtime,price,volume) from 'trades.csv' delimiters ',' csv;"
      sql "delete from trades where unixtime >= $start;"
      sql "insert into trades (unixtime, t, price, volume) select unixtime, TIMESTAMP 'epoch' + unixtime * INTERVAL '1 second', price, volume from import order by id desc;"
      if [ $start_file -eq $end_file ]; then
        echo "end detected, sleeping for 10 minutes,...."
        sleep 10m;
      fi
    else
      echo "start_file != start, exiting, check code"
      exit 1
    fi
  done
}

extend

mtgox=# drop table trades;
DROP TABLE
mtgox=# create table trades (id serial, unixtime int, t timestamp, price numeric(32,10), volume numeric(32,8));
NOTICE:  CREATE TABLE will create implicit sequence "trades_id_seq" for serial column "trades.id"
CREATE TABLE
mtgox=# \copy trades(unixtime,price,volume) from 'trades.csv' delimiters ',' csv;
mtgox=# update trades set t = TIMESTAMP 'epoch' + unixtime * INTERVAL '1 second';
UPDATE 3563178

mtgox=# select extract(year from t) as y, count(*) as cnt, sum(volume) as vol_btc, sum(volume*price) as vol_usd, sum(volume*price) / sum(volume) as vwap from trades group by y order by y;
  y   |   cnt   |      vol_btc      |           vol_usd            |          vwap
------+---------+-------------------+------------------------------+------------------------
 2010 |    8757 |  2666237.77200000 |    433663.023527280000000000 | 0.16264979368362200219
 2011 | 1279900 | 13525744.44429303 |  86008826.163108379280100000 |   6.358897768425487151
 2012 | 1970995 | 20228998.28350803 | 152645161.420049849453300000 |   7.545858637226536507
 2013 |  303526 |  2692668.75713098 |  54049100.207568686632600000 |  20.072688133076431703

mtgox=# select extract(year from t) as y, count(*) as cnt, sum(volume) as vol_btc, sum(volume*price) as vol_usd from trades group by y order by y;
  y   |   cnt   |      vol_btc      |           vol_usd
------+---------+-------------------+------------------------------
 2010 |    8757 |  2666237.77200000 |    433663.023527280000000000
 2011 | 1279900 | 13525744.44429303 |  86008826.163108379280100000
 2012 | 1970995 | 20228998.28350803 | 152645161.420049849453300000
 2013 |  303526 |  2692668.75713098 |  54049100.207568686632600000

mtgox=# update trades set t = TIMESTAMP 'epoch' + unixtime * INTERVAL '1 second';

#> curl "http://api.bitcoincharts.com/v1/csv/mtgoxUSD.csv" > trades.csv

#> psql -U postgres
postgres=# create database mtgox;
CREATE DATABASE

postgres=# \connect mtgox;
You are now connected to database "mtgox" as user "postgres".

mtgox=# \q
#>

#> psql -U postgres mtgox
mtgox=# create table trades (id serial, unixtime int, t timestamp, price numeric(16,8), volume numeric(16,8));
NOTICE:  CREATE TABLE will create implicit sequence "trades_id_seq" for serial column "trades.id"
CREATE TABLE

mtgox=# \copy trades(unixtime,price,volume) from 'trades.csv' delimiters ',' csv;

mtgox=# update trades set t = TIMESTAMP 'epoch' + unixtime * INTERVAL '1 second';
UPDATE 3563178

mtgox=#

mtgox=# select count(*) as cnt, sum(volume) as vol_btc, sum(volume*price) as vol_usd from trades;
   cnt   |      vol_btc      |           vol_usd
---------+-------------------+------------------------------
 3563178 | 39113649.25693204 | 293136750.814254195366000000   cnt   | vol_btc  |  vol_usd