真的好好看哇,我决定了,以后就这么写
--模块整体数据
select t1.ds
,t1.identify_uv_1d
,t2.identify_order_cnt_1d
,t2.identify_is_true_cnt_1d
,t2.identify_is_false_cnt_1d
,t2.identify_is_true_cnt_1d / t2.identify_order_cnt_1d as t2.identify_is_true_rate_1d
from (
select ds
,count(distinct user_id) as identify_uv_1d
from dwd_flow_identify_click_di
where ds = {yesterday}
group by ds
) t1 left
join (
select ds
,count(1) as identify_order_cnt_1d
,sum(if(identift_status = 8,1,0)) as identify_is_true_cnt_1d
,sum(if(identift_status = 9,1,0)) as identify_is_false_cnt_1d
from dwd_idf_identify_order_di
where ds = {yesterday}
and order_status = 1
group by ds
) t2
on t1.ds = t2.ds
;
--昨日鉴定来源数据
select t1.ds
,source_block_chi_name
,t1.identify_uv_1d
,t2.identify_order_cnt_1d
,t2.identify_is_true_cnt_1d
,t2.identify_is_false_cnt_1d
,t2.identify_is_true_cnt_1d / t2.identify_order_cnt_1d as t2.identify_is_true_rate_1d
from (
select ds
,source_block_chi_name
,count(distinct user_id) as identify_uv_1d
from dwd_flow_identify_click_di
where ds = {yesterday}
group by ds
,source_block_chi_name
) t1 left
join (
select ds
,source_block_chi_name
,count(1) as identify_order_cnt_1d
,sum(if(identift_status = 8,1,0)) as identify_is_true_cnt_1d
,sum(if(identift_status = 9,1,0)) as identify_is_false_cnt_1d
from dwd_idf_identify_order_di
where ds = {yesterday}
and order_status = 1
group by ds
,source_block_chi_name
) t2
on t1.ds = t2.ds
and t1.source_block_chi_name = t2.source_block_chi_name
;
--昨日鉴定品牌数据
select ds
,first_brand_name
,second_brand_name
,count(1) as identify_order_cnt_1d
,sum(if(identift_status = 8,1,0)) as identify_is_true_cnt_1d
,sum(if(identift_status = 9,1,0)) as identify_is_false_cnt_1d
from dwd_idf_identify_order_di
where ds = {yesterday}
and order_status = 1
group by ds
,first_brand_name
,second_brand_name
;
--昨日鉴定类目数据
select ds
,first_category_name
,second_category_name
,count(1) as identify_order_cnt_1d
,sum(if(identift_status = 8,1,0)) as identify_is_true_cnt_1d
,sum(if(identift_status = 9,1,0)) as identify_is_false_cnt_1d
from dwd_idf_identify_order_di
where ds = {yesterday}
and order_status = 1
group by ds
,first_category_name
,second_category_name
;
SELECT c.name AS 'Customers'
FROM Customers AS c
LEFT
JOIN Orders AS o
ON c.id = o.customerId
WHERE o.customerId IS NULL;
SELECT `name` AS 'Customers'
FROM Customers AS c
WHERE c.id
NOT IN (
SELECT customerId
FROM Orders AS o
);