|
1
|
SELECT t1.special_date
|
|
2
|
,t1.order_id --订单id
|
|
3
|
,t1.user_id --用户ID
|
|
4
|
,t1.rec_id --订单详情id
|
|
5
|
,t1.goods_no --商品货号
|
|
6
|
,t1.goods_id --商品id
|
|
7
|
,t1.goods_kh --款号
|
|
8
|
,t1.special_id --专场id
|
|
9
|
,t1.special_name --专场名
|
|
10
|
,t1.special_start_time --专场开始时间
|
|
11
|
,CASE WHEN t3.special_style_type IN (1,7,17,19) THEN '风格新款'
|
|
12
|
WHEN t3.special_style_type IN (15,22,23,24) THEN '明星档口'
|
|
13
|
WHEN t3.special_style_type IN (2,5,10,16,18,20,25) THEN '畅销返场'
|
|
14
|
ELSE "其他"
|
|
15
|
END AS special_type ---专场类型
|
|
16
|
,t1.supply_id --供应商id
|
|
17
|
,t1.supplier_id --档口id
|
|
18
|
,t1.brand_id --品牌id
|
|
19
|
,t1.market_id --市场id
|
|
20
|
,t1.picker_group --买手组id
|
|
21
|
,t1.picker_group_code --买助代号
|
|
22
|
,t1.cat_id --类目id
|
|
23
|
,t5.is_new --是否新款
|
|
24
|
,t1.shop_price --售价
|
|
25
|
,t1.buy_num --购买数量
|
|
26
|
,t1.sa_add_cart_id --加购id
|
|
27
|
,t1.sa_source --加购来源
|
|
28
|
,t1.is_100_no_reason --是否支持100%无理由
|
|
29
|
,t2.route add_cart_route --加购路径
|
|
30
|
,t2.special_date AS add_cart_special_date --加购日期
|
|
31
|
,CASE WHEN t2.route LIKE '首页_专场详情%' AND t2.route NOT LIKE '%搜索%' AND t2.route NOT LIKE '%以图搜图%' AND t2.route NOT LIKE '%进货车%' THEN '专场'
|
|
32
|
WHEN t2.route LIKE '%分类%' AND t2.route NOT LIKE '%搜索%' AND t2.route NOT LIKE '%以图搜图%' AND t2.route NOT LIKE '%进货车%' THEN '分类'
|
|
33
|
WHEN t2.route LIKE '%_搜索结果_%' AND t2.route NOT LIKE '%进货车%' THEN '搜索'
|
|
34
|
ELSE '其他'
|
|
35
|
END AS add_cart_channel --加购渠道
|
|
36
|
,CASE WHEN t4.supply_id IS NOT NULL THEN 1
|
|
37
|
ELSE 0
|
|
38
|
END AS is_platform --是否平台化
|
|
39
|
,t1.dt --专场日期
|
|
40
|
FROM yishou_data.dwd_ys_sale_sp_order_info_final_dt t1 --订单表
|
|
41
|
LEFT JOIN (
|
|
42
|
---路径表中同一个加购id，取其中一个路径
|
|
43
|
SELECT add_cart_id
|
|
44
|
,MIN(route) route
|
|
45
|
,MIN(special_date) special_date
|
|
46
|
FROM yishou_data.dwd_log_app_route_dt --路径表
|
|
47
|
WHERE dt BETWEEN to_char(dateadd(TO_DATE('${bdp.system.bizdate}','yyyymmdd'), - 30,'dd'),'yyyymmdd') AND '${bdp.system.bizdate}'
|
|
48
|
AND add_cart_id IS NOT NULL
|
|
49
|
AND add_cart_id <> ''
|
|
50
|
AND add_cart_id > '0'
|
|
51
|
AND length(add_cart_id) > 0
|
|
52
|
GROUP BY add_cart_id
|
|
53
|
) t2
|
|
54
|
ON t1.sa_add_cart_id = t2.add_cart_id LEFT
|
|
55
|
JOIN yishou_data.dim_special_info t3 --专场信息表
|
|
56
|
ON t1.special_id = t3.special_id
|
|
57
|
LEFT JOIN yishou_data.dwd_ys_mall_sp_supply_permissions_dt t4 --平台化商家表
|
|
58
|
ON t1.supply_id = t4.supply_id
|
|
59
|
AND t1.dt = t4.dt
|
|
60
|
AND t4.is_platform = 1
|
|
61
|
AND to_char(t4.no_reason_begin_time,'yyyy') >= '2016'
|
|
62
|
JOIN yishou_data.dim_goods_id_info t5 --商品信息表
|
|
63
|
ON t1.goods_id = t5.goods_id
|
|
64
|
WHERE t1.dt = '${bdp.system.bizdate}'
|
|
65
|
AND t1.pay_status = 1
|
|
66
|
AND t1.sa_source = 11 -- 档口渠道
|
|
67
|
AND t1.user_id NOT IN (2,10,17,387836)
|
|
68
|
;
|
