I have a table:

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=...

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

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

CodePudding user response：

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);

CodePudding user response：

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;