|
/电商项目高价值用户排名
温馨提示：本次任务使用的数据在电商项目云实验平台中,数据库 kkb_da
先使用 desc 表名；语句查看字段，在进行查询。
1.查询 2020 年 GMV，按照月累计进行分析（使用 kkb_order 表）
2.计算 2020 年 6 月份，每三天进行移动求平均注册用户数（使用 kkb_order 表）
3.计算 2020 年 6 月份注册用户数排名前 3 名的城市（使用 kkb_order 表）/
– 1.查询 2020 年 GMV，按照月累计进行分析（使用 kkb_order 表）GMV(Gross Merchandise Volume商品交易总额
/*
1.商品交易总额为已完成订单的实际总额
2.所以限定条件为2020年和订单完成或退款完成
3.按月份和每月的销售总额进行分段,在用sum窗口函数进行统计
*/
select a.mon,a.gmv,
sum(a.gmv) over(order by a.mon)as re
from
(select month(to_date(order_date))as mon,
sum(case when order_status='订单完成’or order_status=‘退款完成’ then succ_amount end)as gmv
from kkb_order
where order_status in(‘订单完成’,‘退款完成’) and year(to_date(order_date))=‘2020’
group by month(to_date(order_date)))a;
– 2.计算 2020 年 6 月份，每三天进行移动求平均注册用户数（使用 kkb_user 表）
/*
hive中的时间戳转日期的函数为：
from_unixtime(BIGINT unixtime,STRING format)首先这里我要说一下，这里的unixtime的输入单位十秒，也就是十位的BIGINT。
但是呢，在我们实际中用的时间戳一般都是十三位的时间戳，精确到毫秒了，如果将精确到毫秒的时间戳输入方法中会有错误，出现错误的日期。
解决办法：如果是13位的时间戳，可使用下面的方法from_unixtime(cast(timestamp/1000 as bigint)) as time
注意：timestamp/1000 是一个double的数值类型，所以直接使用cast函数变成bigint就可以了
*/
– 第三步 按时间进行分段,将用户注册数和用户注册平均数查询出来
select
b.event_day,
b.user,
avg(b.user)over(order by b.event_day rows between 2 preceding and current row )as re
from
(select
– 第二步 按时间进行分段,计算用户注册数
a.event_day,
count(distinct a.user_id)as user
from(
– 第一步 先将时间戳转换为日期格式,提取六月份时间
select
from_unixtime(cast(createtime/1000 as bigint),‘yyyy-MM-dd’) as event_day,
user_id
from kkb_user
where from_unixtime(cast(createtime/1000 as bigint),‘yyyy-MM-dd’) between ‘2020-06-01’and’2020-06-30’
)as a
where a.event_day between ‘2020-06-01’ and ‘2020-06-30’
group by a.event_day
)as b;
– 3.计算 2020 年 6 月份注册用户数排名前 3 名的城市（使用 kkb_user和kkb_province_city表）
– 1.先将2020年6分的城市和kkb_province_city进行内连接
– 2.然后统计用户数(去重)
– 3.再将统计的用户数进行排名 只取前三名
select
c.city,
c.tot_user,
c.dr
from
(select b.city,
count(distinct a.user_id)as tot_user,
dense_rank() over(order by count(distinct a.user_id) desc)as dr
from
(select from_unixtime(cast(createtime/1000 as bigint),‘yyyy-MM-dd’)as event_day,
user_id,
city_id
from kkb_user
where from_unixtime(cast(createtime/1000 as bigint),‘yyyy-MM-dd’)between ‘2020-06-01’and’2020-06-30’
)a inner join(
select city_id,city
from kkb_province_city) b on a.city_id=b.city_id
group by b.city)c
where c.dr in (1,2,3);
|