Write columns as rows (PostgreSQL)

Question

I have a table:

campaign_id	media	installs	content_view	date	unique_purchase
7564	google	613	45	2021-09-01	1
7564	facebook	21	33	2021-09-02	2
3451	google	83	12	2021-09-01	0

I want to create view:

write installs column values as metric_key=installs, metric_name=installs, metric_value=...
write content_view and unique_purchase as metric_key=event, metric_name=column_name, metric_value=...

id	media_source	metric_key	metric_name	metric_value	date
7564	google	installs	installs	613	2021-09-01
7564	facebook	installs	installs	21	2021-09-02
3451	google	installs	installs	83	2021-09-01
7564	google	event	unique_purchase	1	2021-09-01
7564	facebook	event	unique_purchase	2	2021-09-02
3451	google	event	unique_purchase	0	2021-09-01
7564	google	event	content_view	45	2021-09-01
7564	facebook	event	content_view	33	2021-09-02
3451	google	event	content_view	12	2021-09-01

Also it should be groupped by campaign_id, media, date.

How to achieve it with Postgres? I dont understand crosstab clearly. Thanks!

As for crosstab, have you read this [question](https://stackoverflow.com/questions/3002499/postgresql-crosstab-query)? — NuLo, Sep 21 '21 at 15:08

score 4 · Accepted Answer · answered Sep 21 '21 at 15:25

4

Use a lateral join:

select t.id, t.media_source, v.*, t.date
from t cross join lateral
     (values ('installs', t.installs, 'installs'),
             ('event', t.unique_purchase, 'unique_purchase'),
             ('event', t.content_view, 'content_view')
     ) v(metric_key, metric_name, metric_value);

answered Sep 21 '21 at 15:25

Gordon Linoff

1,242,037
58
646
786

How much does it affect performance if I have not 3 such columns (installs, content_view and unique_purchase), but 20? – takotsubo Sep 21 '21 at 16:21
2

@hsadik . . . With three columns, this should be faster than `union all`. With 20 columns, much, much faster. – Gordon Linoff Sep 21 '21 at 19:50

score 1 · Answer 2 · answered Sep 21 '21 at 15:11

You can select specific subset of data and stack them on top of each other. One can assign column names directly in the select queries instead of using a CTE. It's just a personal preference.

create view my_metric_view as

with cte (id, media_source, metric_key, metric_name, metric_value, date) as

(select campaign_id,
        media,
        'installs',
        'installs',
        installs,
        date
 from t
 where metric_name='installs'

 union all

 select campaign_id,
        media,
        'event',
        'unique_purchase',
        unique_purchase,
        date
 from t
 where metric_name='unique_purchase'

 union all

 select campaign_id,
        media,
        'event',
        'content_view',
        content_view,
        date
 from t
 where metric_name='content_view')

select * 
from cte;

Write columns as rows (PostgreSQL)

2 Answers2