1.考察日期函数的应用

select Order_date, count(distinct user_id) as uv from 
(select user_id, Order_date, row_number() over(partition by user_id order by Order_date) as new_tagfrom ord where date_diff(current_date(), Order_date)<=30
) t where new_tag=1
group by Order_date-备注 这是用row_number的方取出每一个用户在最近一个月内第一次购买的对应的时间,然后用new_tag 给这一天打上标识r

2.考察case when

SELECT Gender,
SUM(CASE WHEN '年龄'<20 THEN 1 ELSE 0 END) "20以下",
SUM(CASE WHEN '年龄'>=20 AND '年龄'<=40 THEN 1 ELSE 0 END) "20~40",
SUM(CASE WHEN '年龄'>40 THEN 1 ELSE 0 END) "40以上"
from user
GROUP BY Gender;

3.考察窗口函数

SELECT category,product,sum_sale
(SELECT *,row_number() over(PARTITION by category ORDER BY sum_sale DESC) rankFROM(SELECT category,product,sum(sale_num) sum_saleFROM ordGROUP BY category,product)t)t WHERE rank=1;

4.考察窗口函数和日期函数

解题思路，如果一个用户是连登录的，那么他的数据具有这样的特点, 
user_id, log_date 
1111      2019-01-01
1111      2019-01-02
1111      2019-01-03
如果这个用按他的登录时间排序，就可以成成下面的数据，user_id, log_date    rank
1111      2019-01-01   1
1111      2019-01-02   2
1111      2019-01-03   3
你会惊人的发现，如果一个用户是连续登录的，那么他的log_date和rank
的差值是相等的，也就是2019-01-01减去1，等于2019-01-02减去2SELECT user_id,sub_date,count(*) log_timesFROM
(SELECT user_id, date_sub(log_date, rank) sub_date_diffFROM(SELECT user_id,log_date,row_number()over(PARTITION by user_id order by log_date) rankFROM(SELECT  user_id,substr(request_tm,1,10) log_date--时截为日期FROM user_userlog)t)t )tGROUP BY user_id,sub_dateHAVING log_times=2