QUANTA: aggregate_prices

aggregate_prices.sql

# --- !Ups


CREATE FUNCTION first_agg(anyelement, anyelement) RETURNS anyelement
    LANGUAGE SQL IMMUTABLE STRICT
    AS $_$ SELECT $1 $_$;


CREATE FUNCTION last_agg(anyelement, anyelement) RETURNS anyelement
    LANGUAGE SQL IMMUTABLE STRICT
    AS $_$ SELECT $2 $_$;



CREATE AGGREGATE first(anyelement) (
    SFUNC = first_agg,
    STYPE = anyelement
);


CREATE AGGREGATE last(anyelement) (
    SFUNC = last_agg,
    STYPE = anyelement
);


CREATE OR REPLACE FUNCTION get_candlestick(resolution int,base text,counter text, startTime bigint, endTime bigInt)
RETURNS TABLE("timestamp" numeric, COUNT bigint, volume bigint, "avg" bigint, "open" bigint, high bigint, low bigint, "close" bigint)
AS $BODY$
SELECT t.timestamp,
        count(*) AS orders,
        sum(amount)::BIGINT AS volume,
        AVG(price)::BIGINT AS avg,
        first(price)::BIGINT AS OPEN,
        MAX(price)::BIGINT AS high,
        min(price)::BIGINT AS low,
        last(price)::BIGINT AS CLOSE
FROM (
    SELECT div(ledger_closed_at, resolution)*resolution AS timestamp,
        base_asset_code,
        counter_asset_code,
        amount,
        price AS price FROM trades
        WHERE base_asset_code=base AND counter_asset_code=counter
        AND ledger_closed_at BETWEEN COALESCE(startTime,0) AND COALESCE(endTime,9999999999)
) t GROUP BY t.timestamp;;

$BODY$ LANGUAGE SQL stable;;


# --- !Downs

DROP FUNCTION IF EXISTS get_candlestick(int, text, text);
DROP AGGREGATE  IF EXISTS first(anyelement);
DROP AGGREGATE  IF EXISTS last(anyelement);
DROP FUNCTION IF EXISTS first_agg(anyelement, anyelement);
DROP FUNCTION IF EXISTS last_agg(anyelement, anyelement);