0

Ive a Clickhouse query question, Im pretty new to Clickhouse so maybe its an easy one for the experts ;)! We have a single table with events in, each event is linked to a product fe product_click, product_view. I want to extract the data grouped by product but in a single line I need all types of events in a separated column so I can sort on it.

I already wrote this query:

SELECT product_id,
       arrayMap((x, y) -> (x, y),
       (arrayReduce('sumMap', [(groupArrayArray([event_type]) as arr)], 
       [arrayResize(CAST([], 'Array(UInt64)'), length(arr), toUInt64(1))]) as s).1, s.2) events
FROM events
GROUP BY product_id

Result:

┌─────────────────────────product_id───┬─events─────────────────────────────────────────────────────────────────────────────────────┐
│ 0071f1e4-a484-448e-8355-64e2fea98fd5 │ [('PRODUCT_CLICK',1341),('PRODUCT_VIEW',11)]                           │
│ 406f4707-6bad-4d3f-9544-c74fdeb1e09d │ [('PRODUCT_CLICK',1),('PRODUCT_VIEW',122),('PRODUCT_BUY',37)]    │
│ 94566b6d-6e23-4264-ad76-697ffcfe60c4 │ [('PRODUCT_CLICK',1027),('PRODUCT_VIEW',7)]                            │
...

Is there any way to convert to arrayMap to columns with a sort key? So we can filter on the most clicked products first, or the most viewed?

Another question, is having this kind of queries a good idea to always execute, or should we create a MATERIALIZED view for it?

Thanks!

Dimi
  • 279
  • 1
  • 3
  • 11

1 Answers1

1

SQL does not allow variable number of columns.

the only way for you

SELECT product_id,
       countIf(event_type = 'PRODUCT_CLICK') PRODUCT_CLICK,
       countIf(event_type = 'PRODUCT_VIEW') PRODUCT_VIEW,
       countIf(event_type = 'PRODUCT_BUY') PRODUCT_BUY
FROM events
GROUP BY product_id
Denny Crane
  • 11,574
  • 2
  • 19
  • 30