awesome istore

istore_filter_agg.sql

CREATE EXTENSION istore;
CREATE TABLE data AS
SELECT (i%30)::int as event_id, (random()*100 + 10)::int as events
FROM generate_series(1,1e6) i;

SELECT
SUM(events) FILTER (WHERE event_id = 0) as "0_events",
SUM(events) FILTER (WHERE event_id = 1) as "1_events",
SUM(events) FILTER (WHERE event_id = 2) as "2_events",
SUM(events) FILTER (WHERE event_id = 3) as "3_events",
SUM(events) FILTER (WHERE event_id = 4) as "4_events",
SUM(events) FILTER (WHERE event_id = 5) as "5_events",
SUM(events) FILTER (WHERE event_id = 6) as "6_events",
SUM(events) FILTER (WHERE event_id = 7) as "7_events",
SUM(events) FILTER (WHERE event_id = 8) as "8_events",
SUM(events) FILTER (WHERE event_id = 9) as "9_events",
SUM(events) FILTER (WHERE event_id = 10) as "10_events",
SUM(events) FILTER (WHERE event_id = 11) as "11_events",
SUM(events) FILTER (WHERE event_id = 12) as "12_events",
SUM(events) FILTER (WHERE event_id = 13) as "13_events",
SUM(events) FILTER (WHERE event_id = 14) as "14_events",
SUM(events) FILTER (WHERE event_id = 15) as "15_events",
SUM(events) FILTER (WHERE event_id = 16) as "16_events",
SUM(events) FILTER (WHERE event_id = 17) as "17_events",
SUM(events) FILTER (WHERE event_id = 18) as "18_events",
SUM(events) FILTER (WHERE event_id = 19) as "19_events",
SUM(events) FILTER (WHERE event_id = 20) as "20_events",
SUM(events) FILTER (WHERE event_id = 21) as "21_events",
SUM(events) FILTER (WHERE event_id = 22) as "22_events",
SUM(events) FILTER (WHERE event_id = 23) as "23_events",
SUM(events) FILTER (WHERE event_id = 24) as "24_events",
SUM(events) FILTER (WHERE event_id = 25) as "25_events",
SUM(events) FILTER (WHERE event_id = 26) as "26_events",
SUM(events) FILTER (WHERE event_id = 27) as "27_events",
SUM(events) FILTER (WHERE event_id = 28) as "28_events",
SUM(events) FILTER (WHERE event_id = 29) as "29_events"
FROM data;


SELECT
isagg(event_id, events) -> 0 as "0_events",
isagg(event_id, events) -> 1 as "1_events",
isagg(event_id, events) -> 2 as "2_events",
isagg(event_id, events) -> 3 as "3_events",
isagg(event_id, events) -> 4 as "4_events",
isagg(event_id, events) -> 5 as "5_events",
isagg(event_id, events) -> 6 as "6_events",
isagg(event_id, events) -> 7 as "7_events",
isagg(event_id, events) -> 8 as "8_events",
isagg(event_id, events) -> 9 as "9_events",
isagg(event_id, events) -> 10 as "10_events",
isagg(event_id, events) -> 11 as "11_events",
isagg(event_id, events) -> 12 as "12_events",
isagg(event_id, events) -> 13 as "13_events",
isagg(event_id, events) -> 14 as "14_events",
isagg(event_id, events) -> 15 as "15_events",
isagg(event_id, events) -> 16 as "16_events",
isagg(event_id, events) -> 17 as "17_events",
isagg(event_id, events) -> 18 as "18_events",
isagg(event_id, events) -> 19 as "19_events",
isagg(event_id, events) -> 20 as "20_events",
isagg(event_id, events) -> 21 as "21_events",
isagg(event_id, events) -> 22 as "22_events",
isagg(event_id, events) -> 23 as "23_events",
isagg(event_id, events) -> 24 as "24_events",
isagg(event_id, events) -> 25 as "25_events",
isagg(event_id, events) -> 26 as "26_events",
isagg(event_id, events) -> 27 as "27_events",
isagg(event_id, events) -> 28 as "28_events",
isagg(event_id, events) -> 29 as "29_events"
FROM data;