create database if not exists gdm;
create table if not exists gdm.itcast_gdm_user_consume_order(
user_id string,
first_order_time timestamp,
last_order_time timestamp,
first_order_ago bigint,
last_order_ago bigint,
month1_hg_order_cnt bigint,
month1_hg_order_amt double,
month2_hg_order_cnt bigint,
month2_hg_order_amt double,
month3_hg_order_cnt bigint,
month3_hg_order_amt double,
month1_order_cnt bigint,
month1_order_amt double,
month2_order_cnt bigint,
month2_order_amt double,
month3_order_cnt bigint,
month3_order_amt double,
max_order_amt double,
min_order_amt double,
total_order_cnt bigint,
total_order_amt double,
user_avg_amt double,
month3_user_avg_amt double,
common_address string,
common_paytype string,
month1_cart_cnt bigint,
month1_cart_goods_cnt bigint,
month1_cart_submit_cnt bigint,
month1_cart_rate double,
month1_cart_cancle_cnt double,
return_cnt bigint,
return_amt double,
reject_cnt bigint,
reject_amt double,
last_return_time timestamp,
school_order_cnt bigint,
company_order_cnt bigint,
home_order_cnt bigint,
forenoon_order_cnt bigint,
afternoon_order_cnt bigint,
night_order_cnt bigint,
morning_order_cnt bigint,
dw_date timestamp
) partitioned by (dt string);
drop table if exists gdm.itcast_gdm_user_consume_order_temp_01;
CREATE TABLE gdm.itcast_gdm_user_consume_order_temp_01 AS
SELECT
t.user_id,
MIN(order_date) first_order_time,
MAX(order_date) last_order_time,
DATEDIFF(MIN(order_date), '2017-01-01') first_order_ago,
DATEDIFF(MAX(order_date), '2017-01-01') last_order_ago,
SUM(
CASE
WHEN t.dat_30 = 1
AND t.order_flag = 0
THEN 1
END
) month1_hg_order_cnt,
SUM(
CASE
WHEN t.dat_30 = 1
AND t.order_flag = 0
THEN t.order_money
END
) month1_hg_order_amt,
SUM(
CASE
WHEN t.dat_60 = 1
AND t.order_flag = 0
THEN 1
END
) month2_hg_order_cnt,
SUM(
CASE
WHEN t.dat_60 = 1
AND t.order_flag = 0
THEN t.order_money
END
) month2_hg_order_amt,
SUM(
CASE
WHEN t.dat_90 = 1
AND t.order_flag = 0
THEN 1
END
) month3_hg_order_cnt,
SUM(
CASE
WHEN t.dat_90 = 1
AND t.order_flag = 0
THEN t.order_money
END
) month3_hg_order_amt,
SUM(dat_30) month1_order_cnt,
SUM(
CASE
WHEN t.dat_30 = 1
THEN t.order_money
END
) month1_order_amt,
SUM(dat_60) month2_order_cnt,
SUM(
CASE
WHEN t.dat_60 = 1
THEN t.order_money
END
) month2_order_amt,
SUM(dat_90) month3_order_cnt,
SUM(
CASE
WHEN t.dat_90 = 1
THEN t.order_money
END
) month3_order_amt,
MAX(t.order_money) max_order_amt,
MIN(t.order_money) min_order_amt,
SUM(
CASE
WHEN t.order_flag = 0
THEN 1
END
) total_order_cnt,
SUM(
CASE
WHEN t.order_flag = 0
THEN t.order_money
END
) total_order_amt,
SUM(coupon_money) total_coupon_amt,
SUM(t.order_money) / COUNT(1) user_avg_amt,
0 month3_user_avg_amt,
0 common_address,
0 common_paytype,
0 month1_cart_cnt,
0 month1_cart_goods_cnt,
0 month1_cart_submit_cnt,
0 month1_order_rate,
0 month1_cart_cancle_cnt,
SUM(
CASE
WHEN t.order_status = 3
THEN t1.goods_amount
END
) return_cnt,
SUM(
CASE
WHEN t.order_status = 3
THEN t.order_money
END
) return_amt,
SUM(
CASE
WHEN t.order_status = 4
THEN t1.goods_amount
END
) reject_cnt,
SUM(
CASE
WHEN t.order_status = 4
THEN t.order_money
END
) reject_amt,
MAX(
CASE
WHEN t.order_status = 3
THEN t.order_date
END
) last_return_time,
SUM(
CASE
WHEN t2.order_addr = 1
THEN 1
END
) school_order_cnt,
SUM(
CASE
WHEN t2.order_addr = 2
THEN 1
END
) company_order_cnt,
SUM(
CASE
WHEN t2.order_addr = 3
THEN 1
END
) home_order_cnt,
SUM(
CASE
WHEN t.order_hour >= 8
AND t.order_hour <= 11
THEN 1
END
) forenoon_order_cnt,
SUM(
CASE
WHEN t.order_hour >= 12
AND t.order_hour <= 18
THEN 1
END
) afternoon_order_cnt,
SUM(
CASE
WHEN t.order_hour >= 19
AND t.order_hour <= 22
THEN 1
END
) night_order_cnt,
SUM(
CASE
WHEN t.order_hour >= 23
AND t.order_hour <= 7
THEN 1
END
) morning_order_cnt
FROM
(SELECT
a.*,
(
CASE
WHEN order_date >= DATE_SUB('2017-01-01', 29)
AND order_date <= '2017-01-01'
THEN 1
END
) dat_30,
(
CASE
WHEN order_date >= DATE_SUB('2017-01-01', 59)
AND order_date <= '2017-01-01'
THEN 1
END
) dat_60,
(
CASE
WHEN order_date >= DATE_SUB('2017-01-01', 89)
AND order_date <= '2017-01-01'
THEN 1
END
) dat_90,
(
CASE
WHEN a.order_status IN (3, 4)
THEN 1
ELSE 0
END
) order_flag,
HOUR(order_date) order_hour
FROM
gdm.itcast_gdm_order a
WHERE dt = '2017-01-01') t
LEFT JOIN
(SELECT
order_id,
goods_amount
FROM
fdm.itcast_fdm_order_goods) t1
ON (t.order_id = t1.order_id)
LEFT JOIN
(SELECT
user_id,
order_addr
FROM
gdm.itcast_user_order_addr_model) t2
ON (t.user_id = t2.user_id)
GROUP BY t.user_id ;
drop table if exists gdm.itcast_gdm_user_consume_order_temp_01;
CREATE TABLE gdm.itcast_gdm_user_consume_order_temp_01 AS
SELECT
t.user_id,
MIN(order_date) first_order_time,
MAX(order_date) last_order_time,
DATEDIFF(MIN(order_date), '2017-01-01') first_order_ago,
DATEDIFF(MAX(order_date), '2017-01-01') last_order_ago,
SUM(
CASE
WHEN t.dat_30 = 1
AND t.order_flag = 0
THEN 1
END
) month1_hg_order_cnt,
SUM(
CASE
WHEN t.dat_30 = 1
AND t.order_flag = 0
THEN t.order_money
END
) month1_hg_order_amt,
SUM(
CASE
WHEN t.dat_60 = 1
AND t.order_flag = 0
THEN 1
END
) month2_hg_order_cnt,
SUM(
CASE
WHEN t.dat_60 = 1
AND t.order_flag = 0
THEN t.order_money
END
) month2_hg_order_amt,
SUM(
CASE
WHEN t.dat_90 = 1
AND t.order_flag = 0
THEN 1
END
) month3_hg_order_cnt,
SUM(
CASE
WHEN t.dat_90 = 1
AND t.order_flag = 0
THEN t.order_money
END
) month3_hg_order_amt,
SUM(dat_30) month1_order_cnt,
SUM(
CASE
WHEN t.dat_30 = 1
THEN t.order_money
END
) month1_order_amt,
SUM(dat_60) month2_order_cnt,
SUM(
CASE
WHEN t.dat_60 = 1
THEN t.order_money
END
) month2_order_amt,
SUM(dat_90) month3_order_cnt,
SUM(
CASE
WHEN t.dat_90 = 1
THEN t.order_money
END
) month3_order_amt,
MAX(t.order_money) max_order_amt,
MIN(t.order_money) min_order_amt,
SUM(
CASE
WHEN t.order_flag = 0
THEN 1
END
) total_order_cnt,
SUM(
CASE
WHEN t.order_flag = 0
THEN t.order_money
END
) total_order_amt,
SUM(coupon_money) total_coupon_amt,
SUM(t.order_money) / COUNT(1) user_avg_amt,
0 month3_user_avg_amt,
0 common_address,
0 common_paytype,
0 month1_cart_cnt,
0 month1_cart_goods_cnt,
0 month1_cart_submit_cnt,
0 month1_order_rate,
0 month1_cart_cancle_cnt,
SUM(
CASE
WHEN t.order_status = 3
THEN t1.goods_amount
END
) return_cnt,
SUM(
CASE
WHEN t.order_status = 3
THEN t.order_money
END
) return_amt,
SUM(
CASE
WHEN t.order_status = 4
THEN t1.goods_amount
END
) reject_cnt,
SUM(
CASE
WHEN t.order_status = 4
THEN t.order_money
END
) reject_amt,
MAX(
CASE
WHEN t.order_status = 3
THEN t.order_date
END
) last_return_time,
SUM(
CASE
WHEN t2.order_addr = 1
THEN 1
END
) school_order_cnt,
SUM(
CASE
WHEN t2.order_addr = 2
THEN 1
END
) company_order_cnt,
SUM(
CASE
WHEN t2.order_addr = 3
THEN 1
END
) home_order_cnt,
SUM(
CASE
WHEN t.order_hour >= 8
AND t.order_hour <= 11
THEN 1
END
) forenoon_order_cnt,
SUM(
CASE
WHEN t.order_hour >= 12
AND t.order_hour <= 18
THEN 1
END
) afternoon_order_cnt,
SUM(
CASE
WHEN t.order_hour >= 19
AND t.order_hour <= 22
THEN 1
END
) night_order_cnt,
SUM(
CASE
WHEN t.order_hour >= 23
AND t.order_hour <= 7
THEN 1
END
) morning_order_cnt
FROM
(SELECT
a.*,
(
CASE
WHEN order_date >= DATE_SUB('2017-01-01', 29)
AND order_date <= '2017-01-01'
THEN 1
END
) dat_30,
(
CASE
WHEN order_date >= DATE_SUB('2017-01-01', 59)
AND order_date <= '2017-01-01'
THEN 1
END
) dat_60,
(
CASE
WHEN order_date >= DATE_SUB('2017-01-01', 89)
AND order_date <= '2017-01-01'
THEN 1
END
) dat_90,
(
CASE
WHEN a.order_status IN (3, 4)
THEN 1
ELSE 0
END
) order_flag,
HOUR(order_date) order_hour
FROM
gdm.itcast_gdm_order a
WHERE dt = '2017-01-01') t
LEFT JOIN
(SELECT
order_id,
goods_amount
FROM
fdm.itcast_fdm_order_goods) t1
ON (t.order_id = t1.order_id)
LEFT JOIN
(SELECT
user_id,
order_addr
FROM
gdm.itcast_user_order_addr_model) t2
ON (t.user_id = t2.user_id)
GROUP BY t.user_id ;
DROP TABLE IF EXISTS gdm.itcast_gdm_user_consume_order_temp_02;
CREATE TABLE gdm.itcast_gdm_user_consume_order_temp_02 AS
SELECT
user_id,
COUNT(1) month1_cart_cnt,
SUM(goods_num) month1_cart_goods_cnt,
SUM(
CASE
WHEN sumbit_time <> ''
THEN goods_num
ELSE 0
END
) month1_cart_submit_cnt,
'' month1_cart_rate,
SUM(
CASE
WHEN cancle_time <> ''
THEN goods_num
ELSE 0
END
) month1_cart_cancle_cnt
FROM
fdm.itcast_fdm_order_cart
WHERE dt = '2017-01-01'
AND to_date (add_time) >= DATE_SUB('2017-01-01', 29)
AND to_date (add_time) <= '2017-01-01'
GROUP BY user_id ;
drop table if exists gdm.itcast_gdm_user_consume_order_temp_03;
create table gdm.gdm_user_consume_order_temp_03 as
select
t.user_id,
t.con,
t.type,
t.cnt
from
(select
b.user_id,
b.con,
b.type,
b.cnt,
row_number() over(distribute by b.user_id,
b.type sort by b.cnt,
b.type desc) rn
from
(select
a.user_id,concat(
coalesce(area_name, ''),
coalesce(address, '')) con,
'address' type,
count(1) cnt
from
gdm.itcast_gdm_order a where dt = '2017-01-01'
group by a.user_id,
concat(
coalesce(area_name, ''),
coalesce(address, '')
)
union
all
select
a.user_id,
a.pay_type con,
'pay_type' type,
count(1) cnt
from
gdm.itcast_gdm_order a
where dt = '2017-01-01'
group by a.user_id,
a.pay_type) b) t
where t.rn = 1 ;
drop table if exists gdm.itcast_gdm_user_consume_order_temp_100;
create table gdm.gdm_user_consume_order_temp_100 as
select
a.user_id
from
(select
user_id
from
gdm.itcast_gdm_user_consume_order_temp_01
union
all
select
user_id
from
gdm.itcast_gdm_user_consume_order_temp_02) a
group by a.user_id ;
文章源自懂站帝-http://www.sfdkj.com/12919.html
