京东订单分析项目

2015购买总人数：3379535 需求1 SET @index=0; CREATE TABLE dw.tmp_cust_amt_rank AS SELECT t3.cust_id,t3.mobile,t3.payable_amount,t3.real_amount,@index:=(@index+1) AS rank FROM ( SELECT t1.cust_id,t2.mobile,t1.payable_amount,t1.real_amount FROM ( SELECT cust_id,SUM(PAYABLE_AMOUNT) AS payable_amount, SUM(REAL_AMOUNT) AS real_amount FROM dw.`t_order` WHERE order_date >= '2015-01-01' AND order_date<='2015-12-31' AND ORDER_STATUS=5 GROUP BY cust_id ) t1 INNER JOIN (SELECT t.`CUST_ID`,t.`MOBILE` FROM dw.`t_customer` t WHERE t.`MOBILE` IS NOT NULL) t2 ON t1.cust_id=t2.cust_id ) t3 ORDER BY t3.payable_amount DESC; --产出表 CREATE TABLE dw.t_cust_amt_rank AS SELECT cust_id,mobile,payable_amount AS payable_amt,real_amount AS real_amt,rank, ROUND((3379535-rank)/3379535,10) AS beat_people_rate FROM dw.tmp_cust_amt_rank; select * from dw.t_cust_amt_rank; ---------------------------------------------------------------------------------------------------------- 需求2 CREATE TABLE dw.tmp_cust_goods_amt SELECT t1.CUST_ID,t2.GOODS_ID,SUM(SELL_PRICE*SELL_NUM) AS goods_sell_amt FROM ( SELECT * FROM dw.`t_order` WHERE ORDER_STATUS=5 AND order_date >= '2015-01-01' AND order_date<='2015-12-31' ) t1 INNER JOIN dw.`t_order_goods` t2 ON t1.`ORDER_ID`=t2.`ORDER_ID` AND t2.GOODS_STATUS<>1 GROUP BY t1.`CUST_ID`,t2.`GOODS_ID`; CREATE TABLE dw.tmp_cust_goods_amt_rank SELECT cust_id, goods_id, goods_sell_amt, rank #排名 FROM ( SELECT cust_id, goods_id, goods_sell_amt, IF (@cust_id = oe.cust_id , @rank :=@rank + 1 ,@rank := 1) AS rank, #top @cust_id := oe.cust_id FROM ( SELECT cust_id,goods_id,ROUND(goods_sell_amt,2) AS goods_sell_amt FROM dw.tmp_cust_goods_amt ORDER BY cust_id,goods_sell_amt DESC )oe, ( SELECT @cust_id := NULL , @rank := 0 ) rk )m WHERE m.rank <= 10; --产出表 CREATE TABLE dw.t_cust_goods_amt_rank AS SELECT t1.`cust_id`,t2.`mobile`,t1.`goods_id`,t3.`goods_name`,t1.`goods_sell_amt`,t1.`rank` FROM dw.tmp_cust_goods_amt_rank t1 INNER JOIN dw.`t_customer` t2 ON t1.`cust_id`=t2.`CUST_ID` AND t2.mobile IS NOT NULL INNER JOIN dw.`t_bs_goods` t3 ON t1.`goods_id`=t3.`GOODS_ID`; ------------------------------------------------------------------------------------------ 需求3 CREATE TABLE dw.tmp_cust_city_goods_amt SELECT t1.CUST_ID,t1.CITY_ID,t2.GOODS_ID,SUM(SELL_PRICE*SELL_NUM) AS goods_sell_amt FROM ( SELECT * FROM dw.`t_order` WHERE ORDER_STATUS=5 AND order_date >= '2015-01-01' AND order_date<='2015-12-31' ) t1 INNER JOIN dw.`t_order_goods` t2 ON t1.`ORDER_ID`=t2.`ORDER_ID` AND t2.GOODS_STATUS<>1 GROUP BY t1.`CUST_ID`,t1.CITY_ID,t2.`GOODS_ID`; CREATE TABLE dw.tmp_cust_category_amt AS SELECT t1.cust_id,category_name,SUM(goods_sell_amt) AS goods_sell_amt FROM dw.tmp_cust_city_goods_amt t1 INNER JOIN dw.`t_bs_city_goods` t2 ON t1.city_id=t2.city_id AND t1.goods_id=t2.goods_id LEFT JOIN ( SELECT tree_id AS city_id,category_id,category_name FROM dw.`v_goods_foreground_category` WHERE category_name IN ('优选水果','乳制品','卤味熟食','休闲零食','饮料酒水') ORDER BY category_name ) v3 ON t1.city_id=v3.city_id AND t2.category_id=v3.category_id GROUP BY t1.cust_id,category_name; --产出表 CREATE TABLE dw.t_cust_category_rate AS SELECT t1.cust_id,t2.mobile, '优选水果' AS fruits_category, ROUND(t1.fruits_category/goods_sell_amt_tot,2) AS fruits_rate, '乳制品' AS dairy_category, ROUND(t1.dairy_category/goods_sell_amt_tot,2) AS dairy_rate, '卤味熟食' AS food_category, ROUND(t1.food_category/goods_sell_amt_tot,2) AS food_rate, '休闲零食' AS snack_category, ROUND(t1.snack_category/goods_sell_amt_tot,2) AS snack_rate, '饮料酒水' AS drink_category, ROUND(t1.snack_category/goods_sell_amt_tot,2) AS drink_rate, '其他' AS other_category, ROUND(t1.other_category/goods_sell_amt_tot,2) AS other_rate FROM ( SELECT cust_id, MAX(CASE WHEN category_name='优选水果' THEN IFNULL(goods_sell_amt,0) END) AS fruits_category, MAX(CASE WHEN category_name='乳制品' THEN IFNULL(goods_sell_amt,0) END) AS dairy_category, MAX(CASE WHEN category_name='卤味熟食' THEN IFNULL(goods_sell_amt,0) END) AS food_category, MAX(CASE WHEN category_name='休闲零食' THEN IFNULL(goods_sell_amt,0) END) AS snack_category, MAX(CASE WHEN category_name='饮料酒水' THEN IFNULL(goods_sell_amt,0) END) AS drink_category, MAX(CASE WHEN category_name IS NULL THEN IFNULL(goods_sell_amt,0) END) AS other_category, SUM(goods_sell_amt) AS goods_sell_amt_tot FROM dw.tmp_cust_category_amt GROUP BY cust_id ) t1 INNER JOIN dw.`t_customer` t2 ON t1.cust_id=t2.cust_id AND t2.mobile IS NOT NULL;