我有以下 SQL 陳述句：
WITH report AS (
SELECT reattr.app_id,
reattr.campaign_c AS campaign,
reattr.media_source_pid AS media_source,
reattr.date,
reattr.agency_pmd_af_prt AS agency,
sum(reattr.total_cost) AS total_cost,
sum(reattr.total_revenue) AS total_revenue,
sum(reattr.af_purchase_unique_users) AS af_purchase_unique_users,
sum(reattr.af_purchase_event_counter) AS af_purchase_event_counter,
reattr.conversion_type
FROM a.partners_reattr reattr
GROUP BY reattr.app_id, reattr.campaign_c, reattr.media_source_pid, reattr.date, reattr.agency_pmd_af_prt, reattr.conversion_type
)
SELECT report.app_id,
report.campaign,
report.media_source,
report.agency,
report.date,
case
when report.conversion_type = 're-engagement'::text then 're-engagement'::text
ELSE 'retargeting'::text
END AS metric_type,
v.metric_key,
v.metric_value,
v.metric_name
FROM report
CROSS JOIN LATERAL ( VALUES
('installs'::text, report.conversions,'installs'::text),
('total_cost'::text,report.total_cost,'total_cost'::text),
('event'::text,report.af_purchase_unique_users,'af_purchase_unique_users'::text),
('event'::text,report.af_purchase_event_counter,'af_purchase_event_counter'::text),
('total_revenue'::text,report.total_revenue,'total_revenue'::text))
v(metric_key, metric_value, metric_name)
WHERE v.metric_value IS NOT NULL;
我想要什么.. 如果conversion_type=re-engagement那么 CROSS JOIN LATERAL 中的第一行應該是('conversions'::text, report.conversions,'conversions'::text),.
如果conversion_type=re-atribution那么它應該是 ('installs'::text, report.conversions,'installs'::text)。所以我需要類似的東西CASE - WHEN，但據我所知，我不能在交叉連接橫向值中使用它？我無法應用它。所以我需要metric_key在 CROSS JOIN LATERAL 內的第一行中動態設定。是否可以？
uj5u.com熱心網友回復：
您VALUES串列中的第一個條目可能只是
VALUES (CASE WHEN conversion_type = 're-engagement'
THEN 'conversions'::text
ELSE 'installs'::text
END,
report.conversions,
CASE WHEN conversion_type = 're-engagement'
THEN 'conversions'::text
ELSE 'installs'::text
END), ...
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/389907.html
標籤：sql PostgreSQL的 横向连接
上一篇：兩列中UNION的結果