hive中如何取日期的第几周的计算周期

关于SQL，市面上有太多的学习资料，包括各种教材和各路大神分享，对于想要学习且时间充裕的童鞋来说，其实可以找本书或者网上的资料慢慢学，一步步入手。

但对于SQL基础不太好，同时想在短期内应对数据分析师面试的童鞋来说，快速了解企业面试常考内容是第一诉求，这时面对市面上“琳琅满目”的分享资料，反而会束手无策，一般会有下面几个问题：

• 知识点太细，时间不够，无法针对性学习；
• 尤其刚入门/转行的童鞋 ，自学了一堆内容，面试时换个场景就懵圈，本文列举的题目都是建立在企业实际场景之上，有实际代入感，同时给出了可能会考察此类题目的公司类型，以便提前有心理准备；
• 自学的知识点与面试内容吻合度低，比如学了较长时间的建表语句却在面试时一个没考（一般企业很少需要数据分析师去建表，多数可能会用到建中间表、但也是为查询做辅助）；
• 有些分享资料，只给出了题目，并没有给出答案或者答案有错误，导致“错学错用”；

这里总结几道面试题目，主要是一些互联网公司数据分析师面试的常见题目，请注意这里的三个修饰：

• 互联网公司：本文题目更多的是建立在互联网公司业务场景之上；
• 数据分析师面试：题目主要针对数据分析师常规工作所需的SQL能力考察，难度易中难均有涵盖，注意这里的易中难是对数据分析师来说的、对其他工种可能不匹配；
• 常见题目：高频/常用考点，比如：各join、case when、group by+聚合函数、having筛选、子查询、窗口排序、窗口偏移分析…等，但不能涵盖所有，同时不同公司/部门岗位考察的内容也可能不一样，有的可能很easy，有的可能很“变态” 〒▽〒……；

这里的题目基于hive/hql，不同查询平台可能语法会略有不同；本文参考答案是自己实际测试运行过的，争取做到简洁、严谨，但水平有限、也难免有疏忽的地方，有问题请及时指出，感谢！

同时，参考答案注释里面会给出题目的核心考点，核心的解释和避免踩坑提示都在注释里，建议一定要仔细看。大家可以通过这几道题目去针对性得学习，提高效率。下面开始题目：

1、电商题目：查询输出每个月消费≥3笔且消费总金额≥100的用户

• 考点：group by+聚合函数，datetime时间处理，子查询/having筛选
• 难度：易
• 适用公司：电商（某宝、某东、某多）、O2O平台（某饿、某团）等，下同！
• 参考答案（下面给出两种答案，注意体会不同）：

-- 参考答案①：having筛选
select substring(order_time,1,7) as month , customer_id , 
count(order_id) as cnt , sum(cost) as amt  -- 聚合结果
from order_detail 
group by substring(order_time,1,7) , customer_id  -- 同时按月份和顾客分组,同时注意datetime时间取月份的用法
having cnt >= 3 and amt >= 100      -- 注意having用法
order by month asc , cnt desc ;     -- 排序

-- 参考答案②：子查询 + where条件判断
select tmp.* from 
(select substring(order_time,1,7) as month , customer_id , 
count(order_id) as cnt , sum(cost) as amt  -- 聚合结果
from order_detail 
group by substring(order_time,1,7) , customer_id  -- 同时按月份和顾客分组,同时注意datetime时间取月份的用法
)tmp where tmp.cnt >= 3 and tmp.amt >= 100        -- where条件判断
order by tmp.month asc , tmp.cnt desc ;     -- 排序

2、电商题目：表字段同上，查询输出每个月消费总金额最多的用户

• 考点：窗口排序函数，group by+聚合函数，datetime时间处理
• 难度：中
• 参考答案：

select tab.* from (
select tmp.* , rank() over (partition by tmp.month order by tmp.amt desc) as rk   -- row_number()也可以,但重复数据只输出一条
from (select substring(order_time,1,7) as month , customer_id , sum(cost) as amt  -- 聚合结果
from order_detail 
group by substring(order_time,1,7) , customer_id  -- 同时按月份和顾客分组,同时注意datetime时间取月份的用法
)tmp )tab where tab.rk = 1   -- 降序排名取等于1的
order by tab.month asc ;     -- 排序

3、社交APP留存率题目：查询2020年2月每日登录用户数及对应7日留存率----- 此题建议认真看，中大厂产品分析岗基本必考的题目！！！

• 考点：表连接，case when，datediff(end_date,start_date)计算不同日期间隔天数
• 难度：中
• 适用公司：社交、资讯（某条）、电商、O2O、UGC平台（某音、某手、某红书）等，下同！
• 参考答案：

select a.login_date as login_dt , count(distinct a.user_id) as login_users ,   -- 注意左表日期为登录日期,用来分组
count(distinct(case when datediff(b.login_date,a.login_date)>=1 and datediff(b.login_date,a.login_date)<=7 then b.user_id else NULL end)) as retention7_users,
count(distinct(case when datediff(b.login_date,a.login_date)>=1 and datediff(b.login_date,a.login_date)<=7 then b.user_id else NULL end)) / count(distinct a.user_id) as retention7_rate  -- 7日留存率=7日留存用户/登录用户
from
(select user_id , substring(login_time,1,10) as login_date from login_table  -- login_date 登录日期
where substring(login_time,1,7) = '2020-02'       -- 注意左表限定2020-02月份登录用户,这里容易出错!
group by user_id , substring(login_time,1,10))a   -- 同一用户同一天内登录只保留一条记录,提高计算效率
left join 
(select user_id , substring(login_time,1,10) as login_date from login_table 
where substring(login_time,1,7) >= '2020-02'      -- 注意右表限定2020-02月份及以后登录用户
group by user_id , substring(login_time,1,10))b on b.user_id = a.user_id
group by a.login_date order by login_dt asc;

4、社交APP连续登录题目：表字段同上，查询输出连续7天都登录的用户

• 考点：窗口偏移分析函数lead()/lag()
• 难度：难
• 参考答案：

select tab.user_id from 
(select tmp.user_id , tmp.login_date , 
lead(tmp.login_date,6) over (partition by tmp.user_id order by tmp.login_date asc) as lead_next6_date ,  -- 偏移取往后第6行日期
date_add(tmp.login_date,6) as date add_next6_date    -- 登录日期后6天对应的日期
from (select user_id , substring(login_time,1,10) as login_date from login_table 
group by user_id , substring(login_time,1,10) as login_date)tmp  -- 务必:同一用户同一天内登录只保留一条记录,否则结果是错误的
)tab where tab.add_next6_date = tab.lead_next6_date
group by tab.user_id ;  -- 去除重复用户,非常有必要,否则会很多重复用户

5、短视频题目：查询输出 用户发布完一个作品后，平均多久(分钟)发布下一个作品，只发布过一次作品的不需输出

• 考点：表连接，时间差计算/时间戳转换，窗口偏移分析函数lead()/lag()
• 难度：难
• 参考答案（下面给出两种答案，注意体会不同，举一反三）：

-- 参考答案①：表连接 + 窗口排序 (相对复杂)
Select tmp1.user_id , 
avg((unix_timestamp(tmp2.create_time)-unix_timestamp(tmp1.create_time))/60) as avg_mins  -- 注意转换为时间戳计算时差(秒),再除以60,取平均
from (select user_id , create_time , 
row_number() over (partition by user_id order by create_time asc) as rnum1   -- 排序,后面用于join on
from create_detail)tmp1 
inner join   -- 注意这里请用inner join , left join会把只有一次作品发布的用户计算进来
(select user_id , create_time , 
row_number() over (partition by user_id order by create_time asc) as rnum2 
from create_detail)tmp2 on (tmp1.user_id = tmp2.user_id and (tmp1.rnum1 + 1) = tmp2.row_num2)   -- 注意这里join on两个字段
group by tmp1.user_id ;

-- 参考答案②：利用偏移分析lead()函数
Select tmp.user_id , avg(unix_timestamp(tmp.next_create_tm) – unix_timestamp(tmp.create_time)) / 60 as avg_mins 
from (select user_id , create_time , 
lead(create_time,1) over (partition by user_id order by create_time asc) as next_create_tm
from create_detail)tmp  where tmp.next_create_tm is not null  -- 必须判断不为空,否则会把只有一次作品发布的用户计算进来
group by tmp.user_id ;

最后祝大家顺利。欢迎交流~