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?

NuLo
– NuLo

2021-09-21 15:08:19 +00:00
Commented Sep 21, 2021 at 15:08 — NuLo
– NuLo, Commented Sep 21, 2021 at 15:08

Gordon Linoff · Accepted Answer · 2021-09-21 15:25:19Z

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, 2021 at 15:25

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

takotsubo Over a year ago

How much does it affect performance if I have not 3 such columns (installs, content_view and unique_purchase), but 20?

Gordon Linoff Over a year ago

@hsadik . . . With three columns, this should be faster than union all. With 20 columns, much, much faster.

Rajat · Accepted Answer · 2021-09-21 15:11:14Z

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;

Collectives™ on Stack Overflow

Write columns as rows (PostgreSQL)

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related