第八章_函数【窗口函数-案例分析】

案例1

-- 考查 :开窗聚合函数、开窗分区累加(order by)、获取当前记录的前一条数据(lag)、根据row_number将数据划分x等分(ntile)

--1. 数据准备

--1. 数据准备

-- business.txt
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94

-- DDL
create table business(
username string comment '用户名称',
orderdate string comment '订单创建时间',
cost int comment '订单金额'
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

-- local2hive
load data local inpath "/root/business.txt" overwrite into table business;

(1)查询在 2017 年 4 月份购买过的顾客及总人数

-- 执行sql
select
username
,count(username) over() as cnt
from business
where substr(orderdate,1,7) = '2017-04';
-- 结果
username        cnt
mart            5
mart            5
mart            5
mart            5
jack            5

(2)查询顾客的购买明细、月购买总额

-- 执行sql
select
*
,sum(cost) over(partition by substr(orderdate,1,7)) as total
from business;
-- 结果
username  orderdate     cost   total
jack    2017-01-01      10      205
jack    2017-01-08      55      205
tony    2017-01-07      50      205
jack    2017-01-05      46      205
tony    2017-01-04      29      205
tony    2017-01-02      15      205
jack    2017-02-03      23      23
mart    2017-04-13      94      341
jack    2017-04-06      42      341
mart    2017-04-11      75      341
mart    2017-04-09      68      341
mart    2017-04-08      62      341
neil    2017-05-10      12      12
neil    2017-06-12      80      80

(3)上述的场景, 将每个顾客的 cost 按照日期进行累加

-- 执行sql
select
*
,sum(cost) over(partition by username order by orderdate) as total
from business;
-- 结果
username  orderdate     cost   total
jack    2017-01-01      10      10
jack    2017-01-05      46      56
jack    2017-01-08      55      111
jack    2017-02-03      23      134
jack    2017-04-06      42      176
mart    2017-04-08      62      62
mart    2017-04-09      68      130
mart    2017-04-11      75      205
mart    2017-04-13      94      299
neil    2017-05-10      12      12
neil    2017-06-12      80      92
tony    2017-01-02      15      15
tony    2017-01-04      29      44
tony    2017-01-07      50      94

(4)查询每个顾客上次的购买时间,(未找到记录时默认为9999-99-99)

-- 执行sql
select
*
,lag(orderdate,1,'9999-99-99') over(partition by username order by orderdate) as pre_orderdate
from business;
-- 结果
username  orderdate     cost   pre_orderdate
jack    2017-01-01      10      9999-99-99
jack    2017-01-05      46      2017-01-01
jack    2017-01-08      55      2017-01-05
jack    2017-02-03      23      2017-01-08
jack    2017-04-06      42      2017-02-03
mart    2017-04-08      62      9999-99-99
mart    2017-04-09      68      2017-04-08
mart    2017-04-11      75      2017-04-09
mart    2017-04-13      94      2017-04-11
neil    2017-05-10      12      9999-99-99
neil    2017-06-12      80      2017-05-10
tony    2017-01-02      15      9999-99-99
tony    2017-01-04      29      2017-01-02
tony    2017-01-07      50      2017-01-04

(5)查询前 20%时间的订单信息 (按orderdate排序,去前20%的记录)

-- 根据row_number,将数据划分为x等份,x为ntile(x)
-- 当切片不均时,默认增加第一个切片的分布
-- 公差 : max(row_number)/x
-- 当 row_number=14,x=5,时 公差为2.8 取整为3
-- 1 : [1,3]
-- 2 : [4,6]
-- 3 : [7,9]
-- 4 : [10,12]
-- 5 : [13,14]

select
*
,ntile(5) over(order by orderdate) as partitionNub
,row_number() over(order by orderdate) as rank
from business;

username orderdate      cost   partitionnub    rank
jack    2017-01-01      10      1       1
tony    2017-01-02      15      1       2
tony    2017-01-04      29      1       3

jack    2017-01-05      46      2       4
tony    2017-01-07      50      2       5
jack    2017-01-08      55      2       6

jack    2017-02-03      23      3       7
jack    2017-04-06      42      3       8
mart    2017-04-08      62      3       9

mart    2017-04-09      68      4       10
mart    2017-04-11      75      4       11
mart    2017-04-13      94      4       12

neil    2017-05-10      12      5       13
neil    2017-06-12      80      5       14

案例2
-- 考查 :开窗函数、order by 累积求值

--1. 数据准备

--1. 数据准备

-- click_log_tab.txt
20211020,11111,85
20211020,22222,83
20211020,33333,86
20211021,11111,87
20211021,22222,65
20211021,33333,98
20211022,11111,67
20211022,22222,34
20211022,33333,88
20211023,11111,99
20211023,22222,33

-- DDL
create table click_log_tab(
creditdate string comment 'log创建时间',
userid string comment '用户id',
sorce int comment '分数'
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

-- local2hive
load data local inpath "/root/click_log_tab.txt" overwrite into table click_log_tab;

(1) 使用 over() 函数进行数据统计, 统计每个用户及表中数据的总数

(1) 使用 over() 函数进行数据统计, 统计每个用户及表中数据的总数
-- 执行sql
select *
,count(*) over(partition by userid) as cnt -- 每个用户在表中的记录数
from click_log_tab;

-- 结果
creditdate      userid  sorce   cnt
20211023        11111   99      4
20211022        11111   67      4
20211021        11111   87      4
20211020        11111   85      4
20211023        22222   33      4
20211022        22222   34      4
20211021        22222   65      4
20211020        22222   83      4
20211022        33333   88      3
20211021        33333   98      3
20211020        33333   86      3

(2) 求用户明细并统计每天的用户总数

(2) 求用户明细并统计每天的用户总数
-- 执行sql
select *
,count(userid) over(partition by creditdate) as cnt -- 每天的用户数量
from click_log_tab;

-- 结果
creditdate      userid  sorce   cnt
20211020        33333   86      3
20211020        22222   83      3
20211020        11111   85      3
20211021        33333   98      3
20211021        22222   65      3
20211021        11111   87      3
20211022        33333   88      3
20211022        22222   34      3
20211022        11111   67      3
20211023        22222   33      2
20211023        11111   99      2

(3)计算从第一天到现在的(记录行所处的creditdate)所有 score 大于80分的用户总数

(3)计算从第一天到现在的(记录行所处的creditdate)所有 score 大于80分的用户总数
-- 执行sql
select *
,count(userid) over(order by sorce
    rows between unbounded preceding and current row -- （表示从起点到当前行）,不添加也行，此参数为默认值
    ) as cnt -- 第一天到当前时间累积值
from click_log_tab
where sorce >80
;

-- 结果
creditdate      userid  sorce   cnt
20211020        22222   83      1
20211020        11111   85      2
20211020        33333   86      3
20211021        11111   87      4
20211022        33333   88      5
20211021        33333   98      6
20211023        11111   99      7

(4)计算每个用户到当前日期分数大于80的天数

(4)计算每个用户到当前日期分数大于80的天数
-- 执行sql
select *
,count(creditdate) over(
    partition by userid
    order by sorce
    rows between unbounded preceding and current row -- （表示从起点到当前行）,不添加也行，此参数为默认值
    ) as cnt -- 第一天到当前时间累积值
from click_log_tab
where sorce >80
;

-- 结果
20211020        11111   85      1
20211021        11111   87      2
20211023        11111   99      3
20211020        22222   83      1
20211020        33333   86      1
20211022        33333   88      2
20211021        33333   98      3