2.2 分析
如果要实现以上需求，首先要统计出每个用户每个月的消费总金额，分组实现集合，但是需要按照用户ID，将该用户这个月之前的所有月份的消费总金额进行累加实现。该需求可以通过两种方案来实现：
方案一：分组统计每个用户每个月的消费金额，然后构建自连接，根据条件分组聚合
方案二：分组统计每个用户每个月的消费金额，然后使用窗口聚合函数实现
2.3 建表
➢ 创建表
--切换数据库 use db_function; --建表 create table tb_money( userid string, mth string, money int ) row format delimited fields terminated by '\t';
➢ 创建数据：vim /export/data/money.tsv
A 2021-01 5 A 2021-01 15 B 2021-01 5 A 2021-01 8 B 2021-01 25 A 2021-01 5 A 2021-02 4 A 2021-02 6 B 2021-02 10 B 2021-02 5 A 2021-03 7 B 2021-03 9 A 2021-03 11 B 2021-03 6
➢加载数据
load data local inpath ‘/export/data/money.tsv’ into table tb_money;
➢ 查询数据
select * from tb_money;
➢ 统计得到每个用户每个月的消费总金额
create table tb_money_mtn as select userid, mth, sum(money) as m_money from tb_money group by userid,mth;
2.4 方案一：自连接分组聚合
➢ 基于每个用户每个月的消费总金额进行自连接
select a.userid as auserid, a.mth as amth, a.m_money as am_money, b.userid as buserid, b.mth as bmth, b.m_money as bm_money from tb_money_mtn a join tb_money_mtn b on a.userid = b.userid;
➢ 将每个月之前月份的数据过滤出来
select a.userid as auserid, a.mth as amth, a.m_money as am_money, b.userid as buserid, b.mth as bmth, b.m_money as bm_money from tb_money_mtn a join tb_money_mtn b on a.userid = b.userid where a.mth >= b.mth;
➢ 对每个用户每个月的金额进行分组，聚合之前月份的消费金额
select a.userid as auserid, a.mth as amth, a.m_money as am_money, sum(b.m_money) as t_money from tb_money_mtn a join tb_money_mtn b on a.userid = b.userid where a.mth >= b.mth group by a.userid,a.mth,a.m_money;
2.5 方案二：窗口函数实现
➢ 窗口函数sum
➢ 功能：用于实现基于窗口的数据求和
➢ 语法：sum(colName) over (partition by col order by col)
➢colName：对某一列的值进行求和
➢分析
基于每个用户每个月的消费金额，可以通过窗口函数对用户进行分区，按照月份排序，然后基于聚合窗口，从每个分区的第一行累加到当前和，即可得到累计消费金额。
➢ 统计每个用户每个月消费金额及累计总金额
select userid, mth, m_money, sum(m_money) over (partition by userid order by mth) as t_money from tb_money_mtn;
3 分组TopN
3.1 需求
工作中经常需要实现TopN的需求，例如热门商品Top10、热门话题Top20、热门搜索Top10、地区用户Top10等等，TopN是大数据业务分析中最常见的需求。
普通的TopN只要基于数据进行排序，然后基于排序后的结果取前N个即可，相对简单，但是在TopN中有一种特殊的TopN计算，叫做分组TopN。
分组TopN指的是基于数据进行分组，从每个组内取TopN，不再基于全局取TopN。如果要实现分组取TopN就相对麻烦。
例如：现在有一份数据如下，记录这所有员工的信息：
如果现在有一个需求：查询每个部门薪资最高的员工的薪水，这个可以直接基于表中数据分组查询得到
select deptno,max(salary) from tb_emp group by deptno;
但是如果现在需求修改为：统计查询每个部门薪资最高的前两名员工的薪水，这时候应该如何实现呢？
3.2 分析
根据上述需求，这种情况下是无法根据group by分组聚合实现的，因为分组聚合只能实现返回一条聚合的结果，但是需求中需要每个部门返回薪资最高的前两名，有两条结果，这时候就需要用到窗口函数中的分区来实现了。
3.3 建表
➢ 创建表
--切换数据库 use db_function; --建表 create table tb_emp( empno string, ename string, job string, managerid string, hiredate string, salary double, bonus double, deptno string ) row format delimited fields terminated by '\t';
➢创建数据：vim /export/data/emp.txt
7369 SMITH CLERK 7902 1980-12-17 800.00 20 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 7839 KING PRESIDENT 1981-11-17 5000.00 10 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 7900 JAMES CLERK 7698 1981-12-3 950.00 30 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 7934 MILLER CLERK 7782 1982-1-23 1300.00 10
➢ 加载数据
load data local inpath ‘/export/data/emp.txt’ into table tb_emp;
➢查询数据
select empno,ename,salary,deptno from tb_emp;
3.4 实现
➢ TopN函数：row_number、rank、dense_rank
➢ row_number：对每个分区的数据进行编号，如果值相同，继续编号
➢ rank：对每个分区的数据进行编号，如果值相同，编号相同，但留下空位
➢ dense_rank：对每个分区的数据进行编号，如果值相同，编号相同，不留下空位
➢ 基于row_number实现，按照部门分区，每个部门内部按照薪水降序排序
select empno, ename, salary, deptno, row_number() over (partition by deptno order by salary desc) as rn from tb_emp;
➢ 过滤每个部门的薪资最高的前两名
with t1 as ( select empno, ename, salary, deptno, row_number() over (partition by deptno order by salary desc) as rn from tb_emp ) select * from t1 where rn < 3;
