SQlite3 as CSV aggregator

parse-speeds.sql

#!/bin/bash

sqlite3 <<_EOF_
.mode csv
create table wget (ts DATETIME, speed REAL);
.import "speeds.csv" wget
.header on
.mode column
--select min(speed)/1024, max(speed)/1024, avg(speed)/1024 from wget;
select AVG((t.speed - sub.a) * (t.speed - sub.a)) as var from wget AS t, (select AVG(speed) AS a FROM wget) AS sub;
select STRFTIME("%H", ts), min(speed)/1024, max(speed)/1024, avg(speed)/1024 from wget GROUP by 1;
select STRFTIME("%Y-%m-%d", ts), min(speed)/1024, max(speed)/1024, avg(speed)/1024 from wget GROUP by 1;

-- http://gnuplot.sourceforge.net/docs_4.2/node140.html
select STRFTIME("%m", ts),\
       max(speed)/1024,\
       avg(speed)/1024,\
       AVG((t.speed - sub.a) * (t.speed - sub.a)) as var\
    FROM wget AS t, (select AVG(speed) AS a FROM wget) AS sub
    GROUP by 1;
_EOF_

echo -- http://gnuplot.sourceforge.net/docs_4.2/node140.html