I have following SQL statement:

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;

What I want.. If conversion_type = re-engagement then first row in CROSS JOIN LATERAL should be ('conversions'::text, report.conversions,'conversions'::text),.

If conversion_type = re-atribution then it should be ('installs'::text, report.conversions,'installs'::text). So I need smth like CASE - WHEN, but as far as i know, i can't use it inside cross join lateral values? I couldn't apply it. So I need to dynamically set metric_key in first row inside CROSS JOIN LATERAL. Is it possible?

CodePudding user response：

The first entry in your VALUES list could simply be

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), ...