clickhouse_sum_01.sql

clickhouse_sum_01.sql

CREATE TABLE test_summing_tree
(
    `brand` String,
    `sold_at` DateTime,
    `qty` UInt32,
    `price` Float
)
ENGINE = SummingMergeTree((qty, price))
PARTITION BY toYYYYMM(sold_at)
ORDER BY (sold_at, brand);

# insert some data
insert into test_summing_tree values 
('MadBook', now(), 800, 12.99), 
('MadBook', now(), 1000, 129.99), 
('ThoughtPad', now(), 210, 100.5),
('ThoughtPad', date_add(DAY, -3, now()), 100, 100.5);

# validate
# expect MadBook on a given-date, qty = 1800, price = 142.98 (summing)
# expect ThoughtPad on a given-date, qty = 210, price = 100.5
# expect ThoughtPad on a given-date - 3, qty = 100, price = 100.5
SELECT *
FROM test_summing_tree
ORDER BY
    sold_at ASC,
    brand ASC;

┌─brand──────┬─────────────sold_at─┬──qty─┬─────price─┐
│ ThoughtPad │ 2022-01-03 06:15:32 │  100 │     100.5 │
│ MadBook    │ 2022-01-06 06:15:32 │ 1800 │ 142.98001 │
│ ThoughtPad │ 2022-01-06 06:15:32 │  210 │     100.5 │
└────────────┴─────────────────────┴──────┴───────────┘

# OR (based on recommendation)
SELECT
    brand,
    sold_at,
    sum(qty) AS qty,
    sum(price) AS price
FROM test_summing_tree
GROUP BY
    brand,
    sold_at
ORDER BY
    sold_at ASC,
    brand ASC;

┌─brand──────┬─────────────sold_at─┬──qty─┬──────────────price─┐
│ ThoughtPad │ 2022-01-03 06:15:32 │  100 │              100.5 │
│ MadBook    │ 2022-01-06 06:15:32 │ 1800 │ 142.98001098632812 │
│ ThoughtPad │ 2022-01-06 06:15:32 │  210 │              100.5 │
└────────────┴─────────────────────┴──────┴────────────────────┘