sql - Presto SQL 全外连接
问题描述
我有一个完全加入,它不像我预期的那样工作。
我加入了 4 列。但是,其中一列有一些空白值,导致出现一些数字[unjoint. 当我删除此列时,查询有效。但是,我必须为报告设置此特定列,因为这是我们报告数字的方式。
有问题的查询:(不起作用的列是 OM.line_item_id)
SELECT DBM.dated, DBM.line_item, DBM.line_item_id, DBM.insertion_order, DBM.insertion_order_id, DBM.device_type, DBM.market, DBM.impressions, DBM.clicks, DBM.amount_spent_EUR, OM.orders, OM.revenue, OM.device
FROM
(
SELECT
DATE_FORMAT(DATE_PARSE(date,'%Y/%m/%d'),'%Y-%m-%d') AS dated, line_item, line_item_id, insertion_order, insertion_order_id, device_type, trim(SPLIT_PART(insertion_order,'|',3)) AS market, cast(impressions as double) as impressions, cast(clicks as double) as clicks, CAST(media_cost_advertiser_currency AS DOUBLE)*1.15 AS amount_spent_EUR
FROM ralph_lauren_google_sheet_dbm_data
WHERE dated >= '2019-03-31'
AND dated <= {{days_ago 1}}
GROUP BY 1,2,3,4,5,6,7,8,9,10
)DBM
FULL outer JOIN
(
SELECT dated, line_item_id, device, market, sum(orders) as orders, sum(revenue)+sum(shipping_revenue)-sum(coupon_discount) as revenue
FROM
(
select
dated,
utm_content_v21 as line_item_id,
order_currency_code_v33_evar33,
case lower(mobile_device_type)
when 'other' then 'Desktop'
when 'tablet' then 'Tablet'
when 'mobile phone' then 'Smart Phone'
else 'Other'
End as device,
case geosegmentation_countries
when 'united kingdom' then 'UK'
when 'germany' then 'DE'
when 'france' then 'FR'
when 'italy' then 'IT'
when 'spain' then 'ES'
else 'other'
end as market,
sum(cast(orders as bigint))as orders,
case
WHEN lower(order_currency_code_v33_evar33) LIKE '%gbp%' THEN sum(TRY_CAST(revenue AS DOUBLE)*1.15)
ELSE Sum(TRY_CAST(revenue AS DOUBLE)*1)
END as revenue,
CASE
WHEN lower(order_currency_code_v33_evar33) LIKE '%gbp%' THEN sum(TRY_CAST(order_level_shipping_revenue_e62_event62 AS DOUBLE)*1.15)
ELSE sum(TRY_CAST(order_level_shipping_revenue_e62_event62 AS DOUBLE)*1)
END as shipping_revenue,
CASE
WHEN lower(order_currency_code_v33_evar33) LIKE '%gbp%' THEN sum(TRY_CAST(order_level_coupon_discount_e77_event77 AS DOUBLE)*1.15)
ELSE sum(TRY_CAST(order_level_coupon_discount_e77_event77 AS DOUBLE)*1)
END as coupon_discount
from ralph_lauren_ftp_all_eu_markets_ltc
WHERE dated >= '2019-03-31'
AND dated <= {{days_ago 1}}
and last_touch_channel like 'Retargeting'
and lower(utm_medium_v21) not like '%fbig%'
and cast(orders as bigint) > 0
group by
1,2,3,4,5
)
GROUP BY
1,2,3 ,4-- revenue numbers are getting duplicated for some reason
)OM
ON
DBM.dated = OM.dated AND DBM.line_item_id = OM.line_item_id and DBM.device_type = OM.device AND DBM.market = OM.market
如果将其他三列连接起来,完全外部连接不会允许我连接数字吗?
谢谢
解决方案
推荐阅读
- javascript - R闪亮的DT悬停显示详细表格
- javascript - 如何更改 React 中的路由以响应窗口消息事件?
- mysql - SQL 获得最高票数的一方
- python - 计算我要活多少天才能活到 25,000 天,那个日期是多少天
- c++ - 无法在 webbots::PositionSensor 对象中获取位置传感器的值
- r - 如何在不使用 R 中的日期的情况下创建多个主题的时间线?
- php - 如何使用 Postgres jsonb '?Laravel 中具有索引支持的运算符?
- c# - 注册 .Net Standard 2.0 DLL 时忽略连接的 WCF 服务?
- c# - Where(predicate).FirstOrDefault() 与 FirstOrDefault(predicate) 的令人惊讶或错误的基准?
- sql - 如何将外键添加到现有表?