jonatas/timevector.sql

## timevector.sql
set search_path to public, toolkit_experimental, timescaledb_experimental ;

DROP TABLE if exists ticks CASCADE;

CREATE TABLE ticks ( time TIMESTAMP NOT NULL, symbol varchar, price decimal, volume int);

SELECT create_hypertable('ticks', 'time');
CREATE MATERIALIZED VIEW tv_1m
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 minute', time) as bucket,
        symbol,
        timevector(time AT TIME ZONE 'GMT', price) as tv_price,
        timevector(time AT TIME ZONE 'GMT', volume) as tv_volume
        FROM ticks
        GROUP BY 1, 2
WITH DATA;

SELECT add_continuous_aggregate_policy('tv_1m', start_offset => INTERVAL '1 month', end_offset => INTERVAL '1 minute', schedule_interval => INTERVAL '1 minute');
\timing on

INSERT INTO ticks
SELECT time, 'SYMBOL', (random()*30)::int, 100*(random()*10)::int
FROM generate_series(TIMESTAMP '2000-01-01 00:00:00',
                 TIMESTAMP '2000-01-01 23:59:59',
             INTERVAL '1 second') AS time;


CREATE OR REPLACE FUNCTION volatility()
RETURNS pipelinethensum IMMUTABLE PARALLEL SAFE LANGUAGE SQL AS $$
SELECT ( sort() -> delta() -> abs() -> sum()) ;
$$;


SELECT time_bucket('15 min', bucket),
  sum(tv_price -> volatility()) as price_volatility,
  sum(tv_volume -> sum()) as traded_volume
FROM tv_1m group by 1 order by 1;
	set search_path to public, toolkit_experimental, timescaledb_experimental ;

	DROP TABLE if exists ticks CASCADE;

	CREATE TABLE ticks ( time TIMESTAMP NOT NULL, symbol varchar, price decimal, volume int);

	SELECT create_hypertable('ticks', 'time');
	CREATE MATERIALIZED VIEW tv_1m
	WITH (timescaledb.continuous) AS
	SELECT time_bucket('1 minute', time) as bucket,
	symbol,
	timevector(time AT TIME ZONE 'GMT', price) as tv_price,
	timevector(time AT TIME ZONE 'GMT', volume) as tv_volume
	FROM ticks
	GROUP BY 1, 2
	WITH DATA;

	SELECT add_continuous_aggregate_policy('tv_1m', start_offset => INTERVAL '1 month', end_offset => INTERVAL '1 minute', schedule_interval => INTERVAL '1 minute');
	\timing on

	INSERT INTO ticks
	SELECT time, 'SYMBOL', (random()30)::int, 100(random()*10)::int
	FROM generate_series(TIMESTAMP '2000-01-01 00:00:00',
	TIMESTAMP '2000-01-01 23:59:59',
	INTERVAL '1 second') AS time;



	CREATE OR REPLACE FUNCTION volatility()
	RETURNS pipelinethensum IMMUTABLE PARALLEL SAFE LANGUAGE SQL AS $$
	SELECT ( sort() -> delta() -> abs() -> sum()) ;
	$$;


	SELECT time_bucket('15 min', bucket),
	sum(tv_price -> volatility()) as price_volatility,
	sum(tv_volume -> sum()) as traded_volume
	FROM tv_1m group by 1 order by 1;