clickhouse_sum_02.sql

clickhouse_sum_02.sql

# add another column... to test the confusing
ALTER TABLE test_summing_tree
    ADD COLUMN `country` String;

# empty the table
ALTER TABLE test_summing_tree
    DELETE WHERE 1 = 1;

# add records again with some confusions of the country value
insert into test_summing_tree values 
('MadBook', toDate(now()), 800, 12.99, 'Brazil'), 
('MadBook', toDate(now()), 1000, 129.99, 'India'), 
('ThoughtPad', toDate(now()), 210, 100.5, 'China'),
('ThoughtPad', toDate(date_add(DAY, -3, now())), 100, 100.5, 'China');

SELECT *
FROM test_summing_tree;

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

# PS. MadBook would be produced in "Brazil" <- the 1st record available in the table 
# (other rows with other country values would be ignored for replacement)

insert into test_summing_tree values 
('MadBook', toDate(now()), 200, 100, 'Africa');

SELECT *
FROM test_summing_tree;

┌─brand──────┬─────────────sold_at─┬──qty─┬─────price─┬─country─┐
│ ThoughtPad │ 2022-01-03 00:00:00 │  100 │     100.5 │ China   │
│ MadBook    │ 2022-01-06 00:00:00 │ 1800 │ 142.98001 │ Brazil  │
│ ThoughtPad │ 2022-01-06 00:00:00 │  210 │     100.5 │ China   │
└────────────┴─────────────────────┴──────┴───────────┴─────────┘
┌─brand───┬─────────────sold_at─┬─qty─┬─price─┬─country─┐
│ MadBook │ 2022-01-06 00:00:00 │ 200 │   100 │ Africa  │
└─────────┴─────────────────────┴─────┴───────┴─────────┘

# PS. data partially summed~ so either you run an OPTIMIZE query... or use sum() by yourself

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 00:00:00 │  100 │              100.5 │
│ MadBook    │ 2022-01-06 00:00:00 │ 2000 │ 242.98001098632812 │
│ ThoughtPad │ 2022-01-06 00:00:00 │  210 │              100.5 │
└────────────┴─────────────────────┴──────┴────────────────────┘

# PS. but this will not merge the multi-value "country"

OPTIMIZE TABLE test_summing_tree DEDUPLICATE

┌─brand──────┬─country─┬─────────────sold_at─┬──qty─┬──────────────price─┐
│ ThoughtPad │ China   │ 2022-01-03 00:00:00 │  100 │              100.5 │
│ MadBook    │ Brazil  │ 2022-01-06 00:00:00 │ 2000 │ 242.98001098632812 │
│ ThoughtPad │ China   │ 2022-01-06 00:00:00 │  210 │              100.5 │
└────────────┴─────────┴─────────────────────┴──────┴────────────────────┘

# PS. this will~ again, the first row under the primary-key (sorting-key) would be the skeleton 
# for non numeric fields; hence for MadBook, the country is "Brazil" as usual.