Search code examples
sqlclickhouse

Unpivot operation in SQL for clickhouse

I'm working with SQL for a clickhouse database.

I have a table with the following data:

┌─report_date──┬─platform─┬─viewers─┬─effectivePlaytime─┬─revenue─┐
│ Date         │ String   │ UInt32  | Float32           │ Float32 |
└──────────────┴──────────┴─────────┴───────────────────┴─────────┘

I'd like to perform an operation over this table that could be described as an unpivot operation. This is extracting one row per metric, date and platform, instead of the usual one row per platform and data that the regular select * from data_table returns.

This is what I need to have in the response.

┌─report_date──┬─platform─┬─metric ─┬─Value───┐
│ Date         │ String   │ String  | Float32 │
└──────────────┴──────────┴─────────┴─────────┘

My current attempt is doing multiple UNION ALL and it produces the result I want, however, I'd like to have a better (more compact and presumably faster) solution.

SELECT report_date, platform, 'viewers' AS metric, viewers AS value
FROM platform_table
UNION ALL
SELECT report_date, platform, 'effectivePlaytime' AS metric, effectivePlaytime AS value
FROM platform_table
UNION ALL
SELECT report_date, platform, 'revenue' AS metric, revenue AS value
FROM platform_table
Solution

  • You want ARRAY JOIN. Try this:

    SELECT
    report_date,
    platform,
    metric,
    value
FROM platform_table
ARRAY JOIN
    ['viewers', 'effectivePlaytime', 'revenue'] AS metric,
    [viewers, effectivePlaytime, revenue] AS value