Chapter four (index calculation) of hundreds of billions of warehouses_ User order indicator business development)

User order indicator business development
3.1 demand analysis
E-commerce platforms often need to analyze users' behaviors according to their purchase data. We make some statistical analysis based on the user's order situation for future user behavior analysis. According to the consumer behavior of users, provide user analysis data indicators to the operation Department. Table is order table!!
The following are the order indicators based on users that need to be counted for this demand:


3.2 create dw layer table
1. Create itcast_dw.dim_user table

drop table if exists itcast_dw.dim_user;
create table itcast_dw.dim_user(
userId bigint,
loginName string,
userSex bigint
)
partitioned by (dt string)
STORED AS PARQUET;

2. Create order temporary table tmp_order_wide
Create TMP in dw layer_ order_ wide

– Order temporary order form
drop table if exists itcast_dw.tmp_order_wide;
create table itcast_dw.tmp_order_wide(
orderId bigint,
orderStatus bigint,
payType bigint,
userId bigint,
userAddressId bigint,
payTime string,
payMoney double,
createtime string
)
partitioned by(dt string)
STORED AS PARQUET;

3. Create order time flag wide table tmp_order_datetag_wide

drop table itcast_dw.tmp_order_datetag_wide;
create table itcast_dw.tmp_order_datetag_wide(
orderId bigint,
orderStatus bigint,
payType bigint,
userId bigint,
userAddressId bigint,
payTime string,
payMoney double,
createtime string,
flag30 bigint,
flag60 bigint,
flag90 bigint,
flag180 bigint,
flagTimeBucket string
)
partitioned by(dt string)
STORED AS PARQUET;

4. Create order time mark and address mark wide table fact_order_wide

– Address widening
drop table itcast_dw.fact_order_wide;
create table itcast_dw.fact_order_wide(
orderId bigint,
orderStatus bigint,
payType bigint,
userId bigint,
userAddressId bigint,
payTime string,
payMoney double,
createtime string,
flag30 bigint,
flag60 bigint,
flag90 bigint,
flag180 bigint,
flagTimeBucket string,
othername string
)
partitioned by(dt string)
STORED AS PARQUET;

3.3 order wide table ETL processing
1. Load user dimension data:

insert overwrite table itcast_dw.dim_user partition(dt='20190908')
select
userId,
loginName,
userSex
from
itcast_ods.itcast_users ;

– verification
select * from itcast_dw.dim_user limit 10;

2. Import order data:

insert overwrite table itcast_dw.tmp_order_wide partition (dt='20190908')
select
orderid,
orderstatus,
paytype,
userid,
useraddressid,
paytime,
totalmoney,
createtime
from itcast_ods.itcast_orders where dt='20190908' ;
– test
select * from itcast_dw.tmp_order_wide limit 10;

3. The time is nearly 30 days, 90 days, 180 days, and the order is widened in the morning and afternoon

insert overwrite table itcast_dw.tmp_order_datetag_wide partition(dt='20190908')
select
orderId,
orderStatus,
payType,
userId,
userAddressId,
payTime,
paymoney,
createtime,
case when datediff(current_timestamp, createtime) <= 30
then 1
else 0
end as flag_30,
case when datediff(current_timestamp, createtime) <= 60
then 1
else 0
end as flag_60,
case when datediff(current_timestamp, createtime) <= 90
then 1
else 0
end as flag_90,
case when datediff(current_timestamp, createtime) <= 180
then 1
else 0
end as flag_180,
case when hour(createtime) >= 0 and hour(createtime) < 6
then 'before dawn'
when hour(createtime) >= 6 and hour(createtime) < 12
then 'morning'
when hour(createtime) >= 12 and hour(createtime) < 14
then 'noon'
when hour(createtime) >= 14 and hour(createtime) < 18
then 'afternoon'
else 'night'
end as flag_time_bucket
from
itcast_dw.tmp_order_wide
where dt='20190908';

– test statement

select * from itcast_dw.tmp_order_datetag_wide limit 5;

4. Merge with address table to add receiving address information
– create dw layer dim_user_address table

drop table if exists itcast_dw.dim_user_address;
create table itcast_dw.dim_user_address(
addressId bigint,
userId bigint,
userName string,
otherName string,
userPhone string,
areaIdPath string,
areaId bigint,
userAddress string,
isDefault bigint,
dataFlag bigint,
createTime string
)
partitioned by (dt string)
STORED AS PARQUET;

–from ods layer itcast_user_address Export data to dim_user_address surface
insert overwrite table itcast_dw.dim_user_address partition(dt="20190908")
select
addressId,
userId,
userName,
otherName,
userPhone,
areaIdPath,
areaId,
userAddress,
isDefault,
dataFlag,
createTime
from itcast_ods.itcast_user_address where dt="20190908";

– address table merge to add receiving address information

insert overwrite table itcast_dw.fact_order_wide partition(dt='20190908')
select
t1.orderId,
t1.orderStatus,
t1.payType,
t1.userId,
t1.userAddressId,
t1.payTime,
t1.paymoney,
t1.createtime,
t1.flag30,
t1.flag60,
t1.flag90,
t1.flag180,
t1.flagTimeBucket,
t2.othername
from
(select * from itcast_dw.tmp_order_datetag_wide where dt='20190908') t1
left join
(select * from itcast_dw.dim_user_address where dt='20190908') t2
on t1.userAddressId = t2.addressId;

– testing

select * from itcast_dw.fact_order_wide limit 10;

3.4 index development
1 index development I

Reference code:

select
t1.userid,
t1.loginname,
MIN(t2.payTime) as first_paytime, --First order time
MAX(t2.payTime) as lastest_paytime, --Last order time
DATEDIFF(CURRENT_TIMESTAMP, MIN(t2.payTime)) as first_day_during_days,–Date of the first single
DATEDIFF(CURRENT_TIMESTAMP, MAX(t2.payTime)) as lastest_day_durning_days, --The history of Tailan
MIN(t2.paymoney) as min_paymoney,
MAX(t2.paymoney) as max_paymoney
from
(select * from itcast_dw.fact_order_wide where dt='20190908') as t2
left join
(select * from itcast_dw.dim_user where dt='20190908') as t1
on t1.userId = t2.userId
group by t1.userid,t1.loginname
limit 5;

2 index development II

Reference code:

select
t2.userid,
t2.loginname,
MIN(t1.payTime) as first_paytime,
MAX(t1.payTime) as lastest_paytime,
DATEDIFF(CURRENT_TIMESTAMP, MIN(t1.payTime)) as first_day_during_days,
DATEDIFF(CURRENT_TIMESTAMP, MAX(t1.payTime)) as lastest_day_durning_days,
MIN(t1.paymoney) as min_paymoney,
MAX(t1.paymoney) as max_paymoney,
sum(
case when t1.orderstatus !=-3 --Order status -3:User Reject -2:Unpaid orders -1: User cancels 0:To be shipped 1:In distribution 2:Receipt confirmed by user
then 1
else 0
end
) as total_count_without_back,–The cumulative number of consumption does not include rejection,
sum(case when t1.orderstatus != -3
then t1.paymoney
else 0
end
) as total_money_without_back, --Accumulated consumption amount excluding rejection
–Accumulative consumption times in the past 30 days excluding rejection
sum(case when t1.flag30 =1 and t1.orderstatus != -3
then 1
else 0
end
) as total_count_without_back_30,
–Total consumption amount in the past 30 days excluding rejection
sum(case when t1.flag30 =1 and t1.orderstatus != -3
then t1.paymoney
else 0
end
) as total_money_without_back_30,
–Cumulative consumption times in the past 30 days including rejection
sum(case when t1.flag30 =1
then 1
else 0
end
) as total_count_without_30,
–Total consumption amount in recent 30 days including refund and rejection
sum(case when t1.flag30 =1
then t1.paymoney
else 0
end
) as total_money_with_back_30

from
(select * from itcast_dw.fact_order_wide where dt="20190908") t1
left join
(select * from itcast_dw.dim_user where dt="20190908") t2 on
t1.userid=t2.userid
group by t2.userid,t2.loginname limit 5;

3 index development 3

Reference code:

– Indicator development III
– 1. Customer unit price (including rejection)
– 2. Customer unit price (excluding rejection)
– 3. Unit price of customers in recent 60 days (including rejection)-----Analysis of user consumption level
– 4. Unit price of customers in recent 60 days (excluding rejection)

select
t2.userid,
t2.loginname,
MIN(t1.payTime) as first_paytime,
MAX(t1.payTime) as lastest_paytime,
DATEDIFF(CURRENT_TIMESTAMP, MIN(t1.payTime)) as first_day_during_days,
DATEDIFF(CURRENT_TIMESTAMP, MAX(t1.payTime)) as lastest_day_durning_days,
MIN(t1.paymoney) as min_paymoney,
MAX(t1.paymoney) as max_paymoney,
sum(
case when t1.orderstatus != -3
then 1
else 0
end
) as total_count_without_back,–The cumulative number of consumption does not include rejection,
sum(case when t1.orderstatus != -3
then t1.paymoney
else 0
end
) as total_money_without_back, --Accumulated consumption amount excluding rejection
–Accumulative consumption times in the past 30 days excluding rejection
sum(case when t1.flag30 =1 and t1.orderstatus != -3
then 1
else 0
end
) as total_count_without_back_30,
–Total consumption amount in the past 30 days excluding rejection
sum(case when t1.flag30 =1 and t1.orderstatus != -3
then t1.paymoney
else 0
end
) as total_money_without_back_30,
–Cumulative consumption times in the past 30 days including rejection
sum(case when t1.flag30 =1
then 1
else 0
end
) as total_count_without_30,
–Total consumption amount in recent 30 days including refund and rejection
sum(case when t1.flag30 =1
then t1.paymoney
else 0
end
) as total_money_with_back_30,
–Customer unit price including rejection
SUM(t1.paymoney) / SUM(1) AS atv,
–Customer unit price excluding rejection
SUM(case when t1.orderStatus !=-3 then t1.paymoney else 0 end) /
SUM(case when t1.orderStatus !=-3 then 1 else 0 end) AS atv_withoutback,
–Unit price of customers in recent 60 days including rejection
SUM(case when t1.flag60 = 1 then t1.paymoney else 0 end) /
SUM(case when t1.flag60 = 1
then 1
else 0
end) AS atv_60,
–No rejection in the past 60 days
SUM(case when t1.orderStatus !=-3 and t1.flag60 = 1
then t1.paymoney
else 0
end) / SUM(case when (t1.orderStatus !=-3 ) and t1.flag60 = 1
then 1
else 0
end) AS atv_60_withoutback
from
(select * from itcast_dw.fact_order_wide where dt="20190909") t1
left join
(select * from itcast_dw.dim_user where dt="20190909") t2 on
t1.userid=t2.userid
group by t2.userid,t2.loginname limit 5;

4 index development 4

1. Load order address analysis table
Reference code:

– Create order address analysis table
drop table if exists itcast_ads.tmp_order_address;
create table itcast_ads.tmp_order_address(
userId bigint, – user Id
otherName string, – Address type (home, School)
totalCount bigint, – Lower singular
rn bigint – Order ranking
)
partitioned by (dt string)
STORED AS PARQUET;

–from tmp_order_datetag_wide Count the most commonly used addresses
insert overwrite table itcast_ads.tmp_order_address partition(dt='20190908')
select
t3.userid,
t3.othername,
t3.ordercount,
row_number() over( partition by t3.userid order by ordercount desc ) rn --partiton by userid:Group by user, order by ordercount :Sort by order quantity in descending order, rn:Sorting within groups
from
(select
t1.userId as userid,
t1.othername as othername,
count(t1.orderid) as ordercount -->Quantity per order per user
from
(select * from itcast_dw.fact_order_wide where dt='20190908') t1

group by t1.userid,t1.otherName order by t1.userid ) t3 ;

– testing

select * from itcast_ads.tmp_order_address order by userId, rn limit 10;

2. Statistics of common receiving address indicators
Reference code:

select
t2.userid,
t2.loginname,
MIN(t1.payTime) as first_paytime,
MAX(t1.payTime) as lastest_paytime,
DATEDIFF(CURRENT_TIMESTAMP, MIN(t1.payTime)) as first_day_during_days,
DATEDIFF(CURRENT_TIMESTAMP, MAX(t1.payTime)) as lastest_day_durning_days,
MIN(t1.paymoney) as min_paymoney,
MAX(t1.paymoney) as max_paymoney,
sum(
case when t1.orderstatus !=10 and t1.orderstatus !=11
then 1
else 0
end
) as total_count_without_back,–The cumulative number of consumption does not include rejection,
sum(case when t1.orderstatus !=10 and t1.orderstatus !=11
then t1.paymoney
else 0
end
) as total_money_without_back, --Accumulated consumption amount excluding rejection
–Accumulative consumption times in the past 30 days excluding rejection
sum(case when t1.flag30 =1 and t1.orderstatus !=10 and t1.orderstatus !=11
then 1
else 0
end
) as total_count_without_back_30,
–Total consumption amount in the past 30 days excluding rejection
sum(case when t1.flag30 =1 and t1.orderstatus !=10 and t1.orderstatus !=11
then t1.paymoney
else 0
end
) as total_money_without_back_30,
–Cumulative consumption times in the past 30 days including rejection
sum(case when t1.flag30 =1
then 1
else 0
end
) as total_count_without_30,
–Total consumption amount in recent 30 days including refund and rejection
sum(case when t1.flag30 =1
then t1.paymoney
else 0
end
) as total_money_with_back_30,
SUM(t1.paymoney) / SUM(1) AS atv,
SUM(case when t1.orderStatus !=-3
then t1.paymoney else 0 end) /
SUM(case when t1.orderStatus !=-3 then 1 else 0
end) AS atv_withoutback,
SUM(case when t1.flag60 = 1
then t1.paymoney else 0 end) / SUM(case when t1.flag60 = 1
then 1
else 0
end) AS atv_60,
SUM(case when t1.orderStatus !=-3 and t1.flag60 = 1
then t1.paymoney
else 0
end) / SUM(case when (t1.orderStatus !=-3 ) and t1.flag60 = 1
then 1
else 0
end) AS atv_60_withoutback,
–Most commonly used address
max(case when t3.rn =1
then t3.othername
else ''
end) as most_usual_address
from
(select * from itcast_dw.fact_order_wide where dt="20190908") t1
left join
(select * from itcast_dw.dim_user where dt="20190908") t2 on
t1.userid=t2.userid
left join
(select * from itcast_ads.tmp_order_address where dt='20190908') as t3
on t1.userId = t3.userId
group by t2.userid,t2.loginname limit 5;

5 index development 5

Update the simulation data in mysql table:

SET FOREIGN_KEY_CHECKS=0;​

– Table structure for itcast_payments

DROP TABLE IF EXISTS itcast_payments;
CREATE TABLE itcast_payments (
id int(11) NOT NULL AUTO_INCREMENT,
payCode varchar(20) DEFAULT NULL,
payName varchar(255) DEFAULT NULL,
payDesc text,
payOrder int(11) DEFAULT '0',
payConfig text,
enabled tinyint(4) DEFAULT '0',
isOnline tinyint(4) DEFAULT '0',
payFor varchar(100) DEFAULT NULL,
PRIMARY KEY (id),
KEY payCode (payCode,enabled,isOnline)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

– Records of itcast_payments

INSERT INTO itcast_payments VALUES ('0 ',' unkown ',' unknown method ',' unknown ',' 0 ', null,' 0 ',' 0 ', null);
INSERT INTO itcast_payments VALUES ("1", "alipays", "Alipay (timely arrival)", "Alipay (timely arrival)", "4", "0", "1", "1,2,4".
INSERT INTO itcast_payments VALUES ('2 ',' weixinpay ',' wechat payment ',' 0 ',' 0 ',' 1 ',' 1,2,3 ');
INSERT INTO itcast_payments VALUES ('3 ',' wallets', 'balance payment', 'balance payment', '5', '1', '1', '1,2,3,4');
INSERT INTO itcast_payments VALUES ('4 ',' cod ',' cash on delivery ',' open city ',' 1 ',' 1 ',' 0 ',' 1,2,3,4 ');

– update ods layer data

establish dw layer dim_payments surface
drop table if exists itcast_dw.dim_payments;
create table itcast_dw.dim_payments(
id bigint,
payCode string,
payName string,
payDesc string,
payOrder bigint,
payConfig string,
enabled bigint,
isOnline bigint,
payFor string
)
partitioned by (dt string)
STORED AS PARQUET;

– itcast from ods layer_ Payments export data to dim of dw layer_ In the payments table

insert overwrite table itcast_dw.dim_payments partition(dt="20190908")
select
id ,
payCode ,
payName ,
payDesc ,
payOrder ,
payConfig,
enabled ,
isOnline ,
payFor
from itcast_ods.itcast_payments where dt="20190908";

1. Load payment method ranking

– Create payment method analysis table
drop table if exists itcast_ads.tmp_order_paytype;
create table itcast_ads.tmp_order_paytype(
userid bigint, – user id
payType bigint, – Payment type id
payCode string, – Payment code
totalCount bigint, – Total orders
rn bigint – Grade
)
partitioned by (dt string)
STORED AS PARQUET;

– load payment method analysis

insert overwrite table itcast_ads.tmp_order_paytype partition(dt='20190908')
select
t3.*,
row_number() over(partition by userId order by totalCount desc) rn
from
(select
t1.userId,
t1.payType,
t2.payCode,
sum(1) as totalCount --sum(1)Equivalent to count effect
from
itcast_dw.dim_payments t2
right join
itcast_dw.tmp_order_datetag_wide t1
on t2.id = t1.payType
group by t1.userId, t1.payType, t2.payCode) t3;

– testing

select * from itcast_ads.tmp_order_paytype limit 5;

2. Statistics of common payment methods and indicators
Reference code:

select
t2.userid,
t2.loginname,
MIN(t1.payTime) as first_paytime,
MAX(t1.payTime) as lastest_paytime,
DATEDIFF(CURRENT_TIMESTAMP, MIN(t1.payTime)) as first_day_during_days,
DATEDIFF(CURRENT_TIMESTAMP, MAX(t1.payTime)) as lastest_day_durning_days,
MIN(t1.paymoney) as min_paymoney,
MAX(t1.paymoney) as max_paymoney,
sum(
case when t1.orderstatus !=10 and t1.orderstatus !=11
then 1
else 0
end
) as total_count_without_back,–The cumulative number of consumption does not include rejection,
sum(case when t1.orderstatus !=10 and t1.orderstatus !=11
then t1.paymoney
else 0
end
) as total_money_without_back, --Accumulated consumption amount excluding rejection
–Accumulative consumption times in the past 30 days excluding rejection
sum(case when t1.flag30 =1 and t1.orderstatus !=10 and t1.orderstatus !=11
then 1
else 0
end
) as total_count_without_back_30,
–Total consumption amount in the past 30 days excluding rejection
sum(case when t1.flag30 =1 and t1.orderstatus !=10 and t1.orderstatus !=11
then t1.paymoney
else 0
end
) as total_money_without_back_30,
–Cumulative consumption times in the past 30 days including rejection
sum(case when t1.flag30 =1
then 1
else 0
end
) as total_count_without_30,
–Total consumption amount in recent 30 days including refund and rejection
sum(case when t1.flag30 =1
then t1.paymoney
else 0
end
) as total_money_with_back_30,
SUM(t1.paymoney) / SUM(1) AS atv,
SUM(case when t1.orderStatus !=-3
then t1.paymoney else 0 end) /
SUM(case when t1.orderStatus !=-3 then 1 else 0
end) AS atv_withoutback,
SUM(case when t1.flag60 = 1
then t1.paymoney else 0 end) / SUM(case when t1.flag60 = 1
then 1
else 0
end) AS atv_60,
SUM(case when t1.orderStatus !=-3 and t1.flag60 = 1
then t1.paymoney
else 0
end) / SUM(case when (t1.orderStatus !=-3 ) and t1.flag60 = 1
then 1
else 0
end) AS atv_60_withoutback,
–Most commonly used address
max(case when t3.rn =1
then t3.othername
else ''
end) as most_usual_address,
–Common payment methods
MAX(case when t4.rn = 1
then t4.payCode
else ''
end) as most_usual_paytype
from
(select * from itcast_dw.fact_order_wide where dt="20190908") t1
left join
(select * from itcast_dw.dim_user where dt="20190908") t2 on
t1.userid=t2.userid
left join
(select * from itcast_ads.tmp_order_address where dt='20190908') as t3
on t1.userId = t3.userId
left join (select * from itcast_ads.tmp_order_paytype where dt='20190908') as t4
on t1.userId = t4.userId
group by t2.userid,t2.loginname limit 5;

6 index development 6

Reference code:

select
t2.userid,
t2.loginname,
MIN(t1.payTime) as first_paytime,
MAX(t1.payTime) as lastest_paytime,
DATEDIFF(CURRENT_TIMESTAMP, MIN(t1.payTime)) as first_day_during_days,
DATEDIFF(CURRENT_TIMESTAMP, MAX(t1.payTime)) as lastest_day_durning_days,
MIN(t1.paymoney) as min_paymoney,
MAX(t1.paymoney) as max_paymoney,
sum(
case when t1.orderstatus !=10 and t1.orderstatus !=11
then 1
else 0
end
) as total_count_without_back,–The cumulative number of consumption does not include rejection,
sum(case when t1.orderstatus !=10 and t1.orderstatus !=11
then t1.paymoney
else 0
end
) as total_money_without_back, --Accumulated consumption amount excluding rejection
–Accumulative consumption times in the past 30 days excluding rejection
sum(case when t1.flag30 =1 and t1.orderstatus !=10 and t1.orderstatus !=11
then 1
else 0
end
) as total_count_without_back_30,
–Total consumption amount in the past 30 days excluding rejection
sum(case when t1.flag30 =1 and t1.orderstatus !=10 and t1.orderstatus !=11
then t1.paymoney
else 0
end
) as total_money_without_back_30,
–Cumulative consumption times in the past 30 days including rejection
sum(case when t1.flag30 =1
then 1
else 0
end
) as total_count_without_30,
–Total consumption amount in recent 30 days including refund and rejection
sum(case when t1.flag30 =1
then t1.paymoney
else 0
end
) as total_money_with_back_30,
SUM(t1.paymoney) / SUM(1) AS atv,
SUM(case when t1.orderStatus !=-3
then t1.paymoney else 0 end) /
SUM(case when t1.orderStatus !=-3 then 1 else 0
end) AS atv_withoutback,
SUM(case when t1.flag60 = 1
then t1.paymoney else 0 end) / SUM(case when t1.flag60 = 1
then 1
else 0
end) AS atv_60,
SUM(case when t1.orderStatus !=-3 and t1.flag60 = 1
then t1.paymoney
else 0
end) / SUM(case when (t1.orderStatus !=-3 ) and t1.flag60 = 1
then 1
else 0
end) AS atv_60_withoutback,
–Most commonly used address
max(case when t3.rn =1
then t3.othername
else ''
end) as most_usual_address,
–Most commonly used payment method
max(case when t4.rn = 1
then t4.payCode
else ''
end) as most_usual_paytype,
SUM(case when t1.otherName = 'school'
then 1
else 0
end) as school_order_count, – Total number of school orders
SUM(case when t1.otherName = 'Company'
then 1
else 0
end) as company_order_count, – Total number of unit orders
SUM(case when t1.otherName = 'of one's own unit'
then 1
else 0
end) as home_order_count, – Total orders placed at home
SUM(case when t1.flagTimeBucket = 'morning'
then 1
else 0
end) as am_order_count, – Total orders placed in the morning
SUM(case when t1.flagTimeBucket = 'afternoon'
then 1
else 0
end) as pm_order_count, – Total orders placed in the afternoon
SUM(case when t1.flagTimeBucket = 'night'
then 1
else 0
end) as night_order_count-- Total orders placed in the evening
from
(select * from itcast_dw.fact_order_wide where dt="20190908") t1
left join
(select * from itcast_dw.dim_user where dt="20190908") t2 on
t1.userid=t2.userid
left join
(select * from itcast_ads.tmp_order_address where dt='20190908') as t3
on t1.userId = t3.userId
left join (select * from itcast_ads.tmp_order_paytype where dt='20190908') as t4
on t1.userId = t4.userId
group by t2.userid,t2.loginname limit 5;

7 create ads layer table and load data

drop table if exists itcast_ads.tmp_user_order_measure;
create table itcast_ads.tmp_user_order_measure(
userid string, – user id
username string, – User name
first_paytime string, – First consumption time
lastest_paytime string, – Last consumption time
first_day_during_days bigint, – Date of the first order
lastest_day_durning_days bigint, – Time since the last single
min_paymoney double, – Minimum consumption amount
max_paymoney double, – Maximum consumption amount
total_count_without_back bigint, – Cumulative consumption times (excluding rejection)
total_money_without_back double, – Accumulated consumption amount (excluding rejection)
total_count_without_back_30 bigint, – Cumulative consumption times in the past 30 days (excluding rejection)
total_money_without_back_30 double, – Accumulated consumption amount in recent 30 days (excluding rejection)
total_count_30 bigint, – Cumulative consumption times in the past 30 days (including rejection)
total_money_30 double, – Accumulated consumption amount in recent 30 days (including rejection)
atv double, – Customer unit price (including rejection)
atv_withoutback double, – Customer unit price (excluding rejection)
atv_60 double, – Unit price of customers in recent 60 days (including rejection)
atv_60_withoutback double, – Unit price of customers in recent 60 days (excluding rejection)
most_usual_address string, – Common receiving address
most_usual_paytype string, – Common payment method
school_order_count bigint, – Total number of school orders
company_order_count bigint, – Total number of unit orders
home_order_count bigint, – Total orders placed at home
am_order_count bigint, – Total orders placed in the morning
pm_order_count bigint, – Total orders placed in the afternoon
night_order_count bigint – Total orders placed in the evening
)
partitioned by (dt string)
STORED AS PARQUET;

insert overwrite table itcast_ads.tmp_user_order_measure partition (dt='20190908')
select
t2.userid,
t2.loginname,
MIN(t1.payTime) as first_paytime,
MAX(t1.payTime) as lastest_paytime,
DATEDIFF(CURRENT_TIMESTAMP, MIN(t1.payTime)) as first_day_during_days,
DATEDIFF(CURRENT_TIMESTAMP, MAX(t1.payTime)) as lastest_day_durning_days,
MIN(t1.paymoney) as min_paymoney,
MAX(t1.paymoney) as max_paymoney,
sum(
case when t1.orderstatus !=10 and t1.orderstatus !=11
then 1
else 0
end
) as total_count_without_back,–The cumulative number of consumption does not include rejection,
sum(case when t1.orderstatus !=10 and t1.orderstatus !=11
then t1.paymoney
else 0
end
) as total_money_without_back, --Accumulated consumption amount excluding rejection
–Accumulative consumption times in the past 30 days excluding rejection
sum(case when t1.flag30 =1 and t1.orderstatus !=10 and t1.orderstatus !=11
then 1
else 0
end
) as total_count_without_back_30,
–Total consumption amount in the past 30 days excluding rejection
sum(case when t1.flag30 =1 and t1.orderstatus !=10 and t1.orderstatus !=11
then t1.paymoney
else 0
end
) as total_money_without_back_30,
–Cumulative consumption times in the past 30 days including rejection
sum(case when t1.flag30 =1
then 1
else 0
end
) as total_count_without_30,
–Total consumption amount in recent 30 days including refund and rejection
sum(case when t1.flag30 =1
then t1.paymoney
else 0
end
) as total_money_with_back_30,
SUM(t1.paymoney) / SUM(1) AS atv,
SUM(case when t1.orderStatus !=-3
then t1.paymoney else 0 end) /
SUM(case when t1.orderStatus !=-3 then 1 else 0
end) AS atv_withoutback,
SUM(case when t1.flag60 = 1
then t1.paymoney else 0 end) / SUM(case when t1.flag60 = 1
then 1
else 0
end) AS atv_60,
SUM(case when t1.orderStatus !=-3 and t1.flag60 = 1
then t1.paymoney
else 0
end) / SUM(case when (t1.orderStatus !=-3 ) and t1.flag60 = 1
then 1
else 0
end) AS atv_60_withoutback,
–Most commonly used address
max(case when t3.rn =1
then t3.othername
else ''
end) as most_usual_address,
max(case when t4.rn = 1
then t4.payCode
else ''
end) as most_usual_paytype,
SUM(case when t1.otherName = 'school'
then 1
else 0
end) as school_order_count, – Total number of school orders
SUM(case when t1.otherName = 'Company'
then 1
else 0
end) as company_order_count, – Total number of unit orders
SUM(case when t1.otherName = 'of one's own unit'
then 1
else 0
end) as home_order_count, – Total orders placed at home
SUM(case when t1.flagTimeBucket = 'morning'
then 1
else 0
end) as am_order_count, – Total orders placed in the morning
SUM(case when t1.flagTimeBucket = 'afternoon'
then 1
else 0
end) as pm_order_count, – Total orders placed in the afternoon
SUM(case when t1.flagTimeBucket = 'night'
then 1
else 0
end) as night_order_count-- Total orders placed in the evening
from
(select * from itcast_dw.fact_order_wide where dt="20190908") t1
left join
(select * from itcast_dw.dim_user where dt="20190908") t2 on
t1.userid=t2.userid
left join
(select * from itcast_ads.tmp_order_address where dt='20190908') as t3
on t1.userId = t3.userId
left join (select * from itcast_ads.tmp_order_paytype where dt='20190908') as t4
on t1.userId = t4.userId
group by t2.userid,t2.loginname ;

– testing

select * from itcast_ads.tmp_user_order_measure limit 10;

Tags: MySQL

Posted on Fri, 05 Jun 2020 01:04:27 -0700 by bakaneko