You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@superset.apache.org by GitBox <gi...@apache.org> on 2020/01/10 21:14:53 UTC
[GitHub] [incubator-superset] graceguo-supercat edited a comment on issue
#8930: Revert "Make select_star work with SQL Lab views (#8598)"
graceguo-supercat edited a comment on issue #8930: Revert "Make select_star work with SQL Lab views (#8598)"
URL: https://github.com/apache/incubator-superset/pull/8930#issuecomment-573207215
airbnb is using Presto version 0.188.
this is the query that generates above error (not sure how other ppl can reproduce):
```
SELECT
dms.ds_week_ending
, dms.dim_affiliate_channel
, dms.campaign_strategy
, CONCAT(dms.dim_affiliate_channel, '_', dms.campaign_strategy) AS dim_group_id
, (SUM(dms.bookings) - SUM(ba.predicted_bookings)) / SUM(dms.bookings) AS bias_m_bookings
, (SUM(dms.nights) - SUM(ba.predicted_nights)) / SUM(dms.nights) AS bias_m_nights
, (SUM(dms.incremental_cm) - SUM(ba.predicted_incremental_cm)) / SUM(dms.incremental_cm) AS bias_m_incremental_cm
, SUM(dms.incremental_cm) / SUM(dms.cost) - SUM(ba.predicted_incremental_cm) / SUM(dms.cost) AS bias_efficiency
-- , CASE
-- WHEN dms.account_name in ('DEMAND:FIN','DEMAND:RUS-ROW','DEMAND:SPA-COL','DEMAND:SWE','DEMAND:POL','DEMAND:NOR','DEMAND:GER-AUT','DEMAND:HUN','DEMAND:HRV','DEMAND:CZE','DEMAND:SPA-URY','DEMAND:SPA-PER','DEMAND:THA','DEMAND:HEB','DEMAND:IDN','DEMAND:ARA','DEMAND:MAY','DEMAND:GRE','DEMAND:SRB','DEMAND:EST','DEMAND:UKR','DEMAND:CAT','DEMAND:SPA-MEX','DEMAND:SVN','DEMAND:BGR','DEMAND:ROU','DEMAND:SVK','DEMAND:LVA','DEMAND:VNM','DEMAND:LTU') THEN 'Other'
-- ELSE dms.account_name END AS account_name
-- , 'All Devices' as device
-- , dms.experiment_label
-- , dms.bid_system
-- , dms.campaign_strategy
-- , dms.match_type
-- , dms.domestic_intl
FROM
(
SELECT
ft.ds_week_ending
, dms.calendar_date
, dms.dim_affiliate_channel
, dms.platform_id
, dms.account_name as account_name
, dms.campaign_name
, dms.dim_external_campaign_id
-- , CASE
-- WHEN dms.account_name in ('DEMAND:ENG-ROW:TEST','DEMAND:SPA-ARG:TEST') THEN 'NewEngen Bidding'
-- WHEN c.bidding_strategy_type = 'Target ROAS' THEN 'TROAS'
-- WHEN c.bidding_strategy_type in ('cpc', 'Enhanced CPC') AND c.labels LIKE '%airbidder_eligible%' AND c.labels NOT LIKE '%exclude-auto-bidding%' THEN 'airbidder_core'
-- WHEN c.bidding_strategy_type in ('cpc', 'Enhanced CPC') AND c.labels LIKE '%exclude-auto-bidding%' THEN 'exclude-auto-bidding'
-- WHEN c.labels LIKE '%TEST_Q32018_BMM_AIRBIDDER_CONTROL%' THEN 'BMM_AIRBIDDER_CONTROL'
-- WHEN c.labels LIKE '%TEST_Q32018_BMM_AIRBIDDER_TREATMENT%' THEN 'BMM_AIRBIDDER_TREATMENT'
-- WHEN c.labels LIKE '%TEST_Q32018_ONEMODEL_CONTROL%' THEN 'ONEMODEL_CONTROL'
-- WHEN c.labels LIKE '%TEST_Q32018_ONEMODEL_TREATMENT%' THEN 'ONEMODEL_TREATMENT'
-- WHEN c.labels LIKE '%TEST_Q32018_C2B_V3_CONTROL%' THEN 'C2BV3_CONTROL'
-- WHEN c.labels LIKE '%TEST_Q32018_C2B_V3_TREATMENT%' THEN 'C2BV3_TREATMENT'
-- WHEN c.bidding_strategy_type in ('cpc', 'Enhanced CPC') AND dim_classifications['channel'] != 'SEM Non-brand' THEN 'Unlabeled Manual CPC'
-- ELSE 'Unknown' END AS bid_system
-- , CASE
-- WHEN dms.account_name in ('DEMAND:ENG-ROW:TEST','DEMAND:SPA-ARG:TEST') THEN 'NewEngen'
-- WHEN dms.campaign_name LIKE '%TEST_CM-User-August-2018' THEN 'USER_AUDIENCE'
-- WHEN c.labels LIKE '%TEST_Q32018_BMM_AIRBIDDER_CONTROL%' THEN 'BMM_AIRBIDDER_CONTROL'
-- WHEN c.labels LIKE '%TEST_Q32018_BMM_AIRBIDDER_TREATMENT%' THEN 'BMM_AIRBIDDER_TREATMENT'
-- WHEN c.labels LIKE '%TEST_Q32018_ONEMODEL_CONTROL%' THEN 'ONEMODEL_CONTROL'
-- WHEN c.labels LIKE '%TEST_Q32018_ONEMODEL_TREATMENT%' THEN 'ONEMODEL_TREATMENT'
-- WHEN c.labels LIKE '%TEST_Q32018_C2B_V3_CONTROL%' THEN 'C2BV3_CONTROL'
-- WHEN c.labels LIKE '%TEST_Q32018_C2B_V3_TREATMENT%' THEN 'C2BV3_TREATMENT'
-- ELSE 'not_in_experiments' END AS experiment_label
, dim_classifications['campaign_strategy'] as campaign_strategy
-- , CASE
-- WHEN dms.campaign_name LIKE '%[EXACT]%' THEN 'EXACT'
-- WHEN dms.campaign_name LIKE '%[BMM]%' THEN 'BMM'
-- WHEN dms.campaign_name LIKE '%[PHRASE]%' THEN 'PHRASE'
-- WHEN dms.campaign_name LIKE '%:DSA%' THEN 'DSA'
-- ELSE 'Mixed' END AS match_type
-- , CASE
-- WHEN upper(dms.campaign_name) LIKE '%:COMP%' THEN 'N/A'
-- WHEN upper(dms.campaign_name) LIKE '%:DSA%' THEN 'N/A'
-- WHEN upper(dms.campaign_name) LIKE '%GENERIC%' THEN 'N/A'
-- WHEN upper(dms.campaign_name) LIKE '%KW:%' AND SPLIT(upper(dms.campaign_name),':')[1] = SUBSTR(SPLIT(upper(dms.campaign_name),'KW:')[2],1,3) THEN 'domestic'
-- WHEN upper(dms.campaign_name) LIKE '%DEST:%' AND SPLIT(upper(dms.campaign_name),':')[1] = SUBSTR(SPLIT(upper(dms.campaign_name),'DEST:')[2],1,3) THEN 'domestic'
-- WHEN upper(dms.campaign_name) LIKE '%POI:%' AND SPLIT(upper(dms.campaign_name),':')[1] = SUBSTR(SPLIT(upper(dms.campaign_name),'POI:')[2],1,3) THEN 'domestic'
-- WHEN dms.campaign_name LIKE '%SRC:%' AND SPLIT(dms.campaign_name,':')[1] = SUBSTR(SPLIT(dms.campaign_name,'SRC:')[2],1,3) THEN 'domestic'
-- ELSE 'international' END AS domestic_intl
, SUM(dms.m_cost) AS cost
, SUM(m_bookings_by_guest_stages_cohorted['visitor'] + m_bookings_by_guest_stages_cohorted['user'] + m_bookings_by_guest_stages_cohorted['booker']) AS bookings
, SUM(m_nights_by_guest_stages_cohorted['visitor'] + m_nights_by_guest_stages_cohorted['user'] + m_nights_by_guest_stages_cohorted['booker']) AS nights
, SUM(m_incremental_cm_by_guest_stages_cohorted['visitor'] + m_incremental_cm_by_guest_stages_cohorted['user'] + m_incremental_cm_by_guest_stages_cohorted['booker']) AS incremental_cm
FROM omg.dim_marketing_summary_v2_campaign_cohorted dms
JOIN core_data.dim_time ft
ON dms.calendar_date = ft.ds
JOIN omg.campaign_performance_report_all_today c
ON dms.dim_external_campaign_id = c.campaign_id
AND dms.ds = c.ds
WHERE
-- returns calendar_date for Sunday week_start of 4 complete weeks ago.
dms.calendar_date < CAST(date_add('day', -1,date_trunc('week', date_add('day', -21, CAST(dms.ds AS DATE)))) AS VARCHAR)
AND dms.ds = '{{ presto.latest_partition("omg.dim_marketing_summary_v2_campaign_cohorted") }}'
AND dms.platform_id = 0
AND c.ad_network_type2 = 'Google search'
AND dms.account_name LIKE '%DEMAND:%'
-- AND dms.account_name in ('DEMAND:ENG-ROW:TEST','DEMAND:SPA-ARG:TEST')
AND dms.account_name NOT LIKE '%REMARKETING%'
AND dms.account_name NOT LIKE '%PROSPECTING%'
AND dms.account_name NOT LIKE '%EXPERIENCES%'
AND dms.account_name NOT LIKE '%ALL:XXM:SRC%'
AND dms.dim_classifications['channel'] in ('SEM Non-brand','SEM Brand')
AND dms.dim_classifications['campaign_strategy'] NOT IN ('SEM Brand') -- filter out duplicate dimensions which was causing double-counting of SEM Brand
-- filters for QA
-- AND dms.account_name = 'DEMAND:ENG-ROW:TEST'
-- AND dms.account_name = 'DEMAND:FRE'
-- AND dms.campaign_name = 'USA:DTM:SRC:KW:FRA+Paris[BMM]'
GROUP BY 1,2,3,4,5,6,7,8
) as dms
JOIN (
SELECT
calendar_date
, dim_affiliate_channel
, platform_id
, account_name
, campaign_name
, dim_external_campaign_id
, SUM(m_cost) AS cost
, SUM(predict_m_bookings) AS predicted_bookings
, SUM(predict_m_nights) AS predicted_nights
, SUM(predict_m_incremental_cm) AS predicted_incremental_cm
FROM omg.dim_marketing_summary_baby_ace v2
JOIN core_data.dim_time ft
ON v2.calendar_date = ft.ds
WHERE
v2.ds >='2018-07-01'
-- returns calendar_date for Sunday week_start of 4 complete weeks ago.
AND day_of_week(CAST(v2.ds AS DATE)) = 6
AND calendar_date >='2018-06-25'
AND date_diff('day',CAST(calendar_date AS DATE), CAST(v2.ds AS DATE)) >= 0
AND date_diff('day',CAST(calendar_date AS DATE), CAST(v2.ds AS DATE)) <= 6
AND dim_affiliate_channel in ('sem-non-brand','sem-brand')
AND platform_id = 0
AND dim_group_name = 'channel_platform_account_campaign'
AND dim_group_id LIKE '%0_DEMAND%'
AND dim_group_id NOT LIKE '%:REMARKETING:%'
AND dim_group_id NOT LIKE '%:PROSPECTING:%'
AND dim_group_id NOT LIKE '%EXPERIENCES%'
AND dim_group_id NOT LIKE '%ALL:XXM:SRC%'
GROUP BY 1,2,3,4,5,6
) as ba
ON dms.calendar_date = ba.calendar_date
AND dms.dim_affiliate_channel = ba.dim_affiliate_channel
AND dms.platform_id = ba.platform_id
AND dms.account_name =ba.account_name
AND dms.dim_external_campaign_id = ba.dim_external_campaign_id
--WHERE dms.bid_system not in ('exclude-auto-bidding','Unknown')
GROUP BY 1,2,3
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org