窗口函数排序相关

排序函数dense_rank()

题目要求：查询订单明细表（order_detail）中销量（下单件数）排名第二的商品id，如果不存在返回null，如果存在多个排名第二的商品则需要全部返回。

这里我的问题是，忘记了多个排名第一的问题，使用了rank()函数，如果多个第一，就没有2的值出现。

代码如下：

select sku_id
from 
(select sku_id,
dense_rank() over(order by o_num desc) do
FROM
(select sku_id,
sum(sku_num) o_num
from order_detail
group by sku_id) v) d
where do = 2

DENSE_RANK(), RANK(), 和 ROW_NUMBER() 都是窗口函数，不同点如下：

函数	相同值的排名	后续排名是否连续	示例输入	示例输出
ROW_NUMBER()	不同	连续	[100, 100, 90]	1, 2, 3
RANK()	相同	不连续（跳号）	[100, 100, 90]	1, 1, 3
DENSE_RANK()	相同	连续	[100, 100, 90]	1, 1, 2

巧妙使用排序，获取连续多天数据

题目要求查询订单信息表(order_info)中最少连续3天下单的用户id。

我的思路：

使用窗口函数，设定窗口范围，找到去重后前两条和前一条的数据，然后日期与当天相减，前两条的为2，前一条的为1。那么表示连续三天。我的代码如下：

select distinct user_id
from 
(select user_id,
datediff(cast(create_date as date), cast (min(distinct create_date) over (partition by user_id order by create_date rows between 2 preceding and current row) as date)) date_y2,
datediff(cast(create_date as date), cast (min(distinct create_date) over (partition by user_id order by create_date rows between 1 preceding and current row) as date)) date_y1,
create_date
from order_info) v
where date_y2 = 2 and date_y1 = 1

题目答案及思路：

首先去重，使用row_number()排序，订单日期减去这个排名的天数，如果结果是同一天，那么说明他们是连续的。然后同一天的数量大于等于三天，就是超过连续三天的订单。

这里还有一个我没有用过的，就是在hiving当中使用聚合函数，这个确实不太常用，很少使用hiving，有点不熟悉，要巧妙运用啊。

select distinct user_id
from (
         select user_id
         from (
                  select user_id
                       , create_date
                       , date_sub(create_date, row_number() over (partition by user_id order by create_date)) flag
                  from (
                           select user_id
                                , create_date
                           from order_info
                           group by user_id, create_date
                       ) t1 -- 同一天可能多个用户下单，进行去重
              ) t2 -- 判断一串日期是否连续：若连续，用这个日期减去它的排名，会得到一个相同的结果
         group by user_id, flag
         having count(flag) >= 3 -- 连续下单大于等于三天
     ) t3;

查询相同时刻多地登陆的用户

从登录明细表（user_login_detail）中查询在相同时刻，多地登陆（ip_address不同）的用户。

实现

我使用的方式是不等式的自连接，但是会产生笛卡尔积，用户会话数较多时可能导致计算量激增。问了下ai，可以使用以下方式优化，先记录下：

• 分区裁剪：按 user_id 分区处理，避免全表扫描。
• 时间范围过滤：添加条件限制时间范围（如最近7天）。

SELECT DISTINCT a.user_id
FROM user_login_detail a
JOIN user_login_detail b ON 
  a.user_id = b.user_id 
  AND a.login_ts < b.logout_ts    -- 时间重叠条件1：当前登录早于其他会话登出
  AND b.login_ts < a.logout_ts    -- 时间重叠条件2：其他登录早于当前登出
  AND a.ip_address != b.ip_address -- IP不同
WHERE a.login_ts != b.login_ts;

使用开窗只实现查询相同时刻

在查看题目答案的时候，发现他只实现了相同时间，使用了定义窗口范围的语句。

select
  distinct t2.user_id
from
  (
   select
     t1.user_id,
     if(t1.max_logout is null ,2,if(t1.max_logout<t1.login_ts,1,0)) flag
   from
     (
      select
        user_id,
        login_ts,
        logout_ts,
        max(logout_ts)over(partition by user_id order by login_ts rows between unbounded preceding and 1 preceding) max_logout
      from
        user_login_detail
   )t1
)t2
where
  t2.flag=0