PostgreSQL Mastery

continuous-aggregate.md

CREATE TABLE
  transactions (
    id BIGSERIAL NOT NULL,
    datetime TIMESTAMPTZ NOT NULL,
    amount numeric(6, 2) NOT NULL,
    PRIMARY KEY(wallet, datetime)
  );

SELECT
  *
FROM
  create_hypertable(
    'transactions',
    'datetime',
    chunk_time_interval = > INTERVAL '1 hour'
  );

INSERT INTO
  transactions
SELECT
  (random() * 30) ::int,
  datetime,
  round(CAST(random() * 20 as numeric), 2)
FROM
  generate_series(
    TIMESTAMP '2020-07-02 00:00:00',
    TIMESTAMP '2020-08-05 00:00:00',
    INTERVAL '10 min'
  ) AS datetime;

CREATE MATERIALIZED VIEW transaction_hourly
WITH
  (timescaledb.continuous) AS
SELECT
  wallet,
  time_bucket(INTERVAL '1 hour', datetime) AS bucket,
  SUM(amount)
FROM
  transactions
GROUP BY
  wallet,
  bucket;

SELECT
  sum
from
  transaction_hourly
where
  bucket >= '2020-08-04 00:00:00+00'
  AND bucket < '2020-08-05 00:00:00+00'
  AND wallet = 6;

----
-- trx
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE
  trx (
    id uuid DEFAULT uuid_generate_v4(),
    datetime TIMESTAMPTZ NOT NULL,
    amount numeric(6, 2) NOT NULL,
    PRIMARY KEY(id, datetime)
  );

SELECT
  *
FROM
  create_hypertable(
    'trx',
    'datetime',
    chunk_time_interval = > INTERVAL '1 hour'
  );

CREATE MATERIALIZED VIEW trx_hourly
WITH
  (timescaledb.continuous) AS
SELECT
  time_bucket(INTERVAL '1 hour', datetime) AS bucket,
  SUM(amount)
FROM
  trx
GROUP BY
  bucket;

INSERT INTO
  trx
SELECT
  uuid_generate_v4(),
  datetime,
  round(CAST(random() * 2 as numeric), 2)
FROM
  generate_series(
    TIMESTAMP '2020-07-01 00:00:00',
    TIMESTAMP '2020-09-20 00:00:00',
    INTERVAL '1 min'
  ) AS datetime;

SELECT
  sum(amount) as s
from
  trx
where
  DATE_TRUNC('hour', datetime) = '2020-07-02 00:00:00+00';

WITH history as (
 select
      bucket,
      sum(sum) over (
        order by
          bucket asc rows between unbounded preceding
          and current row
      )
    from
      trx_hourly
)

select
  *
from
  history
WHERE
  bucket >= '2020-08-04 00:00:00+00'
  AND bucket < '2020-08-05 00:00:00+00';

migration

atlas migrate --dev-url "postgres://timescaledb:secret@localhost:5432/anylogi?sslmode=disable&search_path=public" diff create_all
--dir "file://migrations"
--to "file://schema.hcl"

apply

atlas schema apply
-u "postgres://timescaledb:secret@localhost:5432/anylogi?sslmode=disable&search_path=public"
-f schema.hcl