-- 统计近10天每日行为数量
select 
    pt_dt, count(1)
from
(
    select 
        to_date(timestamp) as pt_dt,  --  to_date()返回日期时间字段中的日期部分
        username,
        user_action
    from table
    where to_date(timestamp) between '$now.date.delta(10)'  and  '$now.date'
)t1
group by t1.pt_dt

1.1.2 注意事项：

1）近10天如何表示。

? ? -- sql数据库中使用getDate()函数作用是获取系统当前时间。

? datediff() :? 日期比较函数。

? ? ? ? ? ? ?-- 语法：datediff(string? ?enddate,? string? ?startdate)，返回值为int。

? date_add(string? ? startdate, int days)? ?返回开始日期startdate增加days后的日期。

??date_sub(): 减法

 select? ? *? ? 
 from? ? table? ? 
 where? ? datediff(dd,? ? datetime类型字段, getdate() ) <= 7

2)? 理解题目意思。

? ? ? 题目要求是求近10天每日行为总数，而不是近10天每个用户的行为总数。

3）to_date() 和unit_timestamp（）异同点。

? ? ?to_date('2018-12-08 10:03"01')? ? -- 2018-12-08? 返回日期字段中的日期部分

? ? ?注意：sql中获取当前时间可以使用getdate()来获取当前时间，那么hive中获取

接下来总结一下：

to_date():返回日期字段中的日期部分

current_timestamp() : 获取到当前时间。

? ? ? ? ?eg: select? ? current_timestamp(); 2019-07-04 10:22:09.4334

? ? ? ? hive中unix_timestamp()函数获取到当前的时间戳为10位的bigint类型数值，该数值只精确到秒级。

unit_timestamp()函数：得到当前时间戳。

? ? ? ? ?eg: select? unix_timestamp(current_timestamp()) ;? ?-- 138278423623

from_unixtime（）：将时间戳转换为日期类型。

? ? ? ? ?eg: select? from_unixtime(126437826583,'yyyy-MM-dd HH:mm:ss');

1.2?行为第三多的用户及其数量

1.2.1 答案

-- 行为第三多的用户及其数量
-- 思路：先找到每个用户的行为数量，再找到行为第三多的用户

select 
    username,
    action_cnt
from
(
  select 
     username, 
     action_cnt,
     dense_rank() over(order by action_cnt desc) as rc
  from
  (  select 
         username, 
         count(1)  as action_cnt
     from table
     group by username
  )t1
)t2
where t2.rc = 3;

1.2.2 注意事项

开窗函数的使用

开窗函数语法：partition by可以省略，但是order by不能省。

? ? 此处不能加partition by 否则运行出来的答案不对。因为已经group by了，每个组里面就只有聚合的数量这一条记录。此时如果再开窗的话，就是对每个用户的一条记录进行order by这样没有任何意义，我们不加partition by的话，这样是对所有的用户进行开窗，结果就符合我们的要求了。

1.3 连续3天有行为的用户

1.3.1? 思路

1）因为每天用户登陆次数不止一次，所以需要先将用户每天的登陆日期去重。

2）再使用row_number()? over(partition by _? order by _)函数将用户id分组，按照登陆时间进行排序，得到rc。

3）计算登陆日期减去排序rc指得到间距gap,核心思想：用户连续登陆的情况下，每次相减的结果都相同。

4）按照用户，间距进行分组并且求和，筛选大于等于3天登陆的用户。

1.3.2 答案

select
  username
from 
(
     select 
        username, 
        (pt_dt - rc) as gap
     from 
     (
        select 
            username,
            pt_dt,
            row_number(partition by username order by pt_dt desc) as rc
        from
        （   select 
               username, 
               distinct to_date(timestamp) as pt_dt
             from table 
        ）t1
     )t2
)t3
group by username , gap
having gap>= 3