大数据（3g）HIVE开窗函数应用场景（不定更）

1、回购周期

创建数据

--删库、建库
DROP DATABASE IF EXISTS trade CASCADE;
CREATE DATABASE trade LOCATION '/trade';
USE trade;
--建表
CREATE TABLE sales(
    ymd  STRING COMMENT "日期",
    name STRING COMMENT "顾客",
    sale INT COMMENT "消费"
) COMMENT "日期用户销量汇总表"
LOCATION '/trade/sales';
--插数据
INSERT INTO TABLE sales VALUES
('2020-01-01', '剑圣', 10),('2020-01-02', '守望者', 15),
('2020-01-04', '守望者', 29),('2020-01-05', '剑圣', 46),
('2020-01-07', '守望者', 50),('2020-01-08', '先知', 62),
('2020-01-08', '剑圣', 55),('2020-02-03', '巫妖', 23),
('2020-03-10', '剑圣', 12),('2020-03-10', '巫妖', 12),
('2020-04-05', '先知', 42),('2020-04-06', '先知', 42),
('2020-04-09', '剑圣', 13),('2020-04-09', '守望者', 68);

SELECT
    name,
    LAG(ymd,1) OVER (PARTITION BY name ORDER BY ymd),
    ymd,
    datediff(ymd,LAG(ymd,1) OVER (PARTITION BY name ORDER BY ymd))
FROM sales;

2、TopN系列

创建数据

--删库、建库
DROP DATABASE IF EXISTS fruit CASCADE;
CREATE DATABASE fruit LOCATION '/fruit';
USE fruit;
--建表
CREATE TABLE sales(
name  STRING  COMMENT "产品",
city  STRING  COMMENT "城市",
sale  INT     COMMENT "销量"
)COMMENT "水果销量表"
LOCATION '/fruit/sales';
--插数据
INSERT INTO TABLE sales VALUES
('椰子','佛山',99),
('雪梨','佛山',77),
('苹果','佛山',88),
('椰子','广州',80),
('雪梨','广州',90),
('苹果','广州',70);

2.1、各地销量Top2

第一步，增加排序列：按城市分区，按销量降序【关键字OVER】

SELECT
    city,
    name,
    sale,
    RANK() OVER(PARTITION BY city ORDER BY sale DESC)
FROM sales;

第二步，使用排序列来筛选

SELECT * FROM(
    SELECT
        city,
        name,
        sale,
        RANK() OVER(PARTITION BY city ORDER BY sale DESC)AS r
    FROM sales
)t
WHERE r<=2;

原始数据

第一步结果

第二步结果

2.2、销量前33%

1. 开窗 并 按销量降序
2. ntile给数据按比例分组，ntile(3)就是分3组
3. 前33%可以是分100组取前33，或者分3组取前1

WITH t100 AS (
     SELECT
         city,
         name,
         sale,
         ntile(100) OVER(ORDER BY sale DESC)AS r
     FROM sales
 )
SELECT * FROM t100 WHERE r<=33;

或

WITH t3 AS (
     SELECT
         city,
         name,
         sale,
         ntile(3) OVER(ORDER BY sale DESC)AS r
     FROM sales
 )
SELECT * FROM t3 WHERE r<=1;

中间结果t3

最终结果

3、每个人连续签到的最大天数

1. 开窗，按uid分组，日期排序，排名；
   日期减排名；

SELECT
    uid,
    dt,
    DATE_SUB(dt,RANK() OVER (PARTITION BY uid ORDER BY dt))d
FROM t;

2. 按uid和日期减排名分组并计数；

SELECT uid,count(1)c FROM temp0
GROUP BY uid,d

3. 按uid分组并取最大值。

SELECT uid,max(c) FROM temp1
GROUP BY uid

4、运费分摊

https://yellow520.blog.youkuaiyun.com/article/details/113790083

5、页面单跳转化率

https://yellow520.blog.youkuaiyun.com/article/details/116200798

6、并行计算获取消费金额topN的用户ID

https://yellow520.blog.youkuaiyun.com/article/details/120188295

7、去重

https://yellow520.blog.youkuaiyun.com/article/details/120764731

8、同比 环比

https://yellow520.blog.youkuaiyun.com/article/details/121531348