Hive 常用窗口函数

1 SELECT b.user_name,
2        b.goods_kind
3 FROM
4   (SELECT user_name,
5           goods_kind,
6           row_number() over(partition BY user_name ORDER BY count(goods_kind) DESC) AS rank
7    FROM user_goods_table) b
8 WHERE b.rank =1

SELECT b.user_name
FROM
  (SELECT user_name,
          ntile(5) over(ORDER BY sum(pay_amount) DESC) AS LEVEL
   FROM user_sales_table
   GROUP BY user_name) b
WHERE b.LEVEL = 1

SELECT b.user_name
  (SELECT user_name, date,lead(date,7) over(partition BY user_name ORDER BY date DESC) AS date_7
   FROM user_login_table) b
WHERE b.date IS NOT NULL
  AND date_sub(cast(b.date AS date,7)) = cast(b.date_7 AS date)