第5题

有日志如下，请写出代码求得所有用户和活跃用户的总数及平均年龄。（活跃用户指连续两天都有访问记录的用户）日期 用户 年龄

数据集

2019-02-11,test_1,23 2019-02-11,test_2,19 2019-02-11,test_3,39 2019-02-11,test_1,23 2019-02-11,test_3,39 2019-02-11,test_1,23 2019-02-12,test_2,19 2019-02-13,test_1,23 2019-02-15,test_2,19 2019-02-16,test_2,19

1） 建表

create table log_info( dt string, user string, age int ) row format delimited fields terminated by ',';

需求：所有用户和活跃用户的总数及平均年龄

2） 按照日期以及用户进行分组，按照日期排序并给出排名

select dt, user, min(age) age, rank() over(partition by user order by dt) rk from log_tab group by dt, user;t1

3) 计算日期及排名的差值

select user, age, data_sub(dt,rk) flag from t1;t2

4） 过滤出差值大于等于2的，即是连续两天活跃的用户

select user, min(age) age, from t2 group by user_id,flag having count(*)>=2;t3

5) 对数据进行去重处理（一个用户可以在两个不同的时间点连续登录），例如：a用户在1月10号1月11号以及1月20号和1月21号4天登录。

select user, min(age) age, from t3 group by user;t4

6) 计算活跃用户的人数以及平均年龄

select count(*) ct, cast(sum(age)/count(*) as decimal(10,2)) from t4;

7) 对全量数据集进行按照用户去重

select user, min(age) age, from log_tab group by user;t5

8) 计算所有用户的数量以及平均年龄

select count(*) user_count, cast((sum(age)/count(*)) as decimal(10,1)) from t5;

9）将五步以及第七步两个数据集进行union操作

略

10) 拼接成最终的SQL

略

第6题

请用sql写出所有用户中在今年10月份第一次购买商品的金额，表ordertable字段（购买用户：userid，金额：money，购买时间：paymenttime(格式：2017-10-01)，订单id：orderid）

1） 建表

create table ordertable( userid string, money int, paymenttime string, orderid string) row format delimited fields terminated by '\t';

2）查询出

需求：所有用户中在今年10月份第一次购买商品的金额 分析： 按照用户分组 条件是今年十月份、第一次购买 select userid, min(paymenttime) paymenttime from ordertable where date_format(paymenttime,'yyyy-MM')='2017-10' group by userid;t1 select t1.userid, t1.paymenttime, od.money, from t1 join ordertable od on t1.userid = od.userid and t1.paymenttime=od.paymenttime;

3） SQL

所有用户中在今年10月份第一次购买商品的金额 select t1.userid, ti.paymenttime, od.money from (select userid, min(paymenttime) paymenttime from ordertable where date_format(paymenttime,'yyyy-MM')='2017-10' group by userid)t1 join ordertable od on t1.userid=od.userid and t1.paymenttime=od.paymenttime;

---

1.本站遵循行业规范，任何转载的稿件都会明确标注作者和来源；2.本站的原创文章，会注明原创字样,如未注明都非原创，如有侵权请联系删除！；3.作者投稿可能会经我们编辑修改或补充；4.本站不提供任何储存功能只提供收集或者投稿人的网盘链接。