I have this weird requirement to generate an output where period can have values 1 to 36. So the output needs to have 36 + 3 columns. Is there any better way to do this. Please help. Thanks in advance
Updated 1st Nov 2024
Sample data is as below:
| bdate | id | period | ct | value |
|---|---|---|---|---|
| 2024-06-28 | 1 | 1 | ct1 | 1.4563 |
| 2024-06-28 | 1 | 1 | ct2 | 2.0023 |
| 2024-06-28 | 1 | 1 | ct3 | 3.4563 |
| 2024-06-28 | 1 | 1 | ct4 | 4.0023 |
| 2024-06-28 | 1 | 2 | ct1 | 1.4563 |
| 2024-06-28 | 1 | 2 | ct2 | 2.0023 |
| 2024-06-28 | 1 | 2 | ct3 | 3.4563 |
| 2024-06-28 | 1 | 2 | ct4 | 4.0023 |
| 2024-06-28 | 1 | 3 | ct1 | 0.4563 |
| 2024-06-28 | 1 | 3 | ct2 | 1.0023 |
| 2024-06-28 | 1 | 3 | ct3 | 2.4563 |
| 2024-06-28 | 1 | 3 | ct4 | 3.0023 |
Expected output is
| bdate | id | period_1 | period_2 | period_3 | period_36 |
|---|---|---|---|---|---|
| 2024-06-28 | 1 | 10.9172 | 10.9172 | 6.9172 | 0 |
Here the list of columns will be from period_1 to period_36.
CREATE or replace TABLE test_8192590.some_table
(
`bdate` Date,
`id` UInt32,
`period` UInt32,
`ct` String,
`value` Float64
)
ENGINE = MergeTree
ORDER BY (bdate, id)
SETTINGS index_granularity = 8192;
insert into test_8192590.some_table VALUES
('2024-06-28',1,1,'ct1',1.4563),
('2024-06-28',1,1,'ct2',2.0023),
('2024-06-28',1,1,'ct3',3.4563),
('2024-06-28',1,1,'ct4',4.0023),
('2024-06-28',1,2,'ct1',1.4563),
('2024-06-28',1,2,'ct2',2.0023),
('2024-06-28',1,2,'ct3',3.4563),
('2024-06-28',1,2,'ct4',4.0023),
('2024-06-28',1,3,'ct1',0.4563),
('2024-06-28',1,3,'ct2',1.0023),
('2024-06-28',1,3,'ct3',2.4563),
('2024-06-28',1,3,'ct4',3.0023);
Select date
, id
, period
, case when period = 1 then sum(value) else 0 end as period_1,
, case when period = 1 then sum(value) else 0 end as period_2,
…
, case when period = 36 then sum(value) else 0 end as period_36
from some_table
group by bdate, id, period
Found the answer, it is easy like this
WITH CAST(sumMap([period], [value]), 'Map(UInt32, Float64)') AS map
select bdate
, id
, period
, map[1] AS period_1
, map[2] AS period_2
, map[3] AS period_3
from test_8192590.some_table
group by bdate, id, period
order by bdate, id, period;