How to make pivot_wider (r) / pivot (pandas) in clickhouse by mutating part of the rows into columns?

user_9236601 0
Viewed 134

I have a table like:

event_id date event_name key value
1 '2021-01-01' 'session_start' 'session_id' '12345'
1 '2021-01-01' 'session_start' 'network' 'organic'
1 '2021-01-01' 'session_start' 'screen_id' '22'
1 '2021-01-01' 'session_start' 'any_var' 'True'
2 '2021-01-02' 'app_deleted' 'session_id' '23456'
2 '2021-01-02' 'app_deleted' 'network' 'organic'
2 '2021-01-02' 'app_deleted' 'screen_id' '33'

I would like to turn it into a table with more columns and reduce the number of rows, so that the 'key' values become columns, and 'value' - the values of these columns, setting NULL where the values will be empty. There will be ~ 100 columns in total.

event_id date event_name session_id network screen_id any_var
1 '2021-01-01' 'session_start' '12345' 'organic' '22' 'True'
1 '2021-01-02' 'app_deleted' '23456' 'organic' '33' NULL

Thanks!

P.S. I can’t bring the solution with array to mind on my own

P.P.S Unfortunately the keys can be different and they differ from month to month

clickhouse sql
2 Answers
SELECT
    event_id,
    date,
    event_name,
    anyIf(toNullable(value), key = 'session_id') AS session_id,
    anyIf(toNullable(value), key = 'network') AS network,
    anyIf(toNullable(value), key = 'screen_id') AS screen_id,
    anyIf(toNullable(value), key = 'any_var') AS any_var
FROM b
GROUP BY
    event_id,
    date,
    event_name;

┌─event_id─┬───────date─┬─event_name────┬─session_id─┬─network─┬─screen_id─┬─any_var─┐
│        1 │ 2021-01-01 │ session_start │ 12345      │ organic │ 22        │ True    │
│        2 │ 2021-01-02 │ app_deleted   │ 23456      │ organic │ 33        │ ᴺᵁᴸᴸ    │
└──────────┴────────────┴───────────────┴────────────┴─────────┴───────────┴─────────┘


create table b (event_id int, date date, event_name String, key String, value String) Engine=Memory;

insert into b values
(1 ,'2021-01-01','session_start','session_id','12345')
(1,'2021-01-01','session_start','network','organic')
(1,'2021-01-01','session_start','screen_id','22')
(1,'2021-01-01','session_start','any_var','True')
(2,'2021-01-02','app_deleted','session_id','23456')
(2,'2021-01-02','app_deleted','network','organic')
(2,'2021-01-02','app_deleted','screen_id','33')
user_116443080

SQL does not allow variable number of columns. Each row have the same number of columns. Number of columns, their names and types are known before a query execution.

SELECT
    event_id,
    date,
    event_name,
    groupArray((key, value))
FROM b
GROUP BY
    event_id,
    date,
    event_name
┌─event_id─┬───────date─┬─event_name────┬─groupArray(tuple(key, value))────────────────────────────────────────────────────────┐
│        1 │ 2021-01-01 │ session_start │ [('session_id','12345'),('network','organic'),('screen_id','22'),('any_var','True')] │
│        2 │ 2021-01-02 │ app_deleted   │ [('session_id','23456'),('network','organic'),('screen_id','33')]                    │
└──────────┴────────────┴───────────────┴──────────────────────────────────────────────────────────────────────────────────────┘
user_116443080
Related