SQL:量表关联取舍时, is null 判断只能单独存在,不能与其他判断同时进行。
- 错误的写法:
select
a.user_id
,a.uid
,a.page_type
,a.d
from
(
select
o.user_id
,f.uid
,f.page_type
,f.d
from dw_busdb.for_flow_conversion_report_distinguish_regular_fresh_customers f
right join dw_busdb.tbl_order o
on o.d = f.d and o.user_id = f.uid
where f.d = '2019-08-05' ---这边的写法也不好,最好写嵌套20190807
and f.page_type = 'list'
and to_date(o.create_dttm) = '2019-08-05'
and f.uid is not null
and f.uid <> '' --''不考虑20190806
and (o.user_id is not null and o.user_id <> '') -- 订单表本身不为null
)a
left join dw_busdb.for_flow_conversion_report_distinguish_regular_fresh_customers f_pkg
on a.user_id = f_pkg.uid and a.d = f_pkg.d
where f_pkg.page_type = 'servicePkg' -- 这样的写法是错误的
and f_pkg.uid is null
- 正确的写法:
select
distinct a.user_id
from
(
select
o.user_id
from dw_busdb.for_flow_conversion_report_distinguish_regular_fresh_customers f
join dw_busdb.tbl_order o -- join会默认跳过null数据
on o.d = f.d and o.user_id = f.uid
where f.d = '2019-08-06' -- 这样写效率不高,最好先写子查询,也能有效避免出错
and f.page_type = 'list'
and to_date(o.create_dttm) = '2019-08-06'
and f.uid <> '' --''不考虑20190806
and order_business_type = 'bus'
)a
left join
(select f_pkg.uid
from
dw_busdb.for_flow_conversion_report_distinguish_regular_fresh_customers f_pkg
where f_pkg.d = '2019-08-06'
and f_pkg.page_type = 'servicePkg'
)b
on a.user_id = b.uid
where b.uid is null -- 此处判断没问题,null判断不能与其他判断同时存在。