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;