【Hadoop离线基础总结】Hive级联求和
2024-09-07 11:05:30
Hive级联求和
- 建表
CREATE TABLE t_salary_detail(
username string,
month string,
salary INT )
ROW format delimited FIELDS TERMINATED BY ',';
- 导入数据
LOAD DATA LOCAL inpath '/export/servers/hivedatas/click-part-r-00000' INTO TABLE t_salary_detail;
+---------------------------+------------------------+-------------------------+--+
| t_salary_detail.username | t_salary_detail.month | t_salary_detail.salary |
+---------------------------+------------------------+-------------------------+--+
| A | 2015-01 | 5 |
| A | 2015-01 | 15 |
| B | 2015-01 | 5 |
| A | 2015-01 | 8 |
| B | 2015-01 | 25 |
| A | 2015-01 | 5 |
| A | 2015-02 | 4 |
| A | 2015-02 | 6 |
| B | 2015-02 | 10 |
| B | 2015-02 | 5 |
| A | 2015-03 | 7 |
| A | 2015-03 | 9 |
| B | 2015-03 | 11 |
| B | 2015-03 | 6 |
+---------------------------+------------------------+-------------------------+--+
- 思路
累积求和其实就是通过inner join表本身来实现。首先要先明白 统计每个用户每个月总共获得多少小费 的hql语句怎么写SELECT username,month,sum(salary)
FROM t_salary_detail
GROUP BY username,month;
+-----------+----------+------+--+
| username | month | _c2 |
+-----------+----------+------+--+
| A | 2015-01 | 33 |
| A | 2015-02 | 10 |
| A | 2015-03 | 16 |
| B | 2015-01 | 30 |
| B | 2015-02 | 15 |
| B | 2015-03 | 17 |
+-----------+----------+------+--+
现在通过inner join连接自己
SELECT a.*,b.*
FROM (
SELECT username,month,sum(salary)
FROM t_salary_detail
GROUP BY username,month ) a
INNER JOIN (
SELECT username,month,sum(salary)
FROM t_salary_detail
GROUP BY username,month ) b
ON a.username = b.username;
+-------------+----------+--------+-------------+----------+--------+--+
| a.username | a.month | a._c2 | b.username | b.month | b._c2 |
+-------------+----------+--------+-------------+----------+--------+--+
| A | 2015-01 | 33 | A | 2015-01 | 33 |
| A | 2015-01 | 33 | A | 2015-02 | 10 |
| A | 2015-01 | 33 | A | 2015-03 | 16 |
| A | 2015-02 | 10 | A | 2015-01 | 33 |
| A | 2015-02 | 10 | A | 2015-02 | 10 |
| A | 2015-02 | 10 | A | 2015-03 | 16 |
| A | 2015-03 | 16 | A | 2015-01 | 33 |
| A | 2015-03 | 16 | A | 2015-02 | 10 |
| A | 2015-03 | 16 | A | 2015-03 | 16 |
| B | 2015-01 | 30 | B | 2015-01 | 30 |
| B | 2015-01 | 30 | B | 2015-02 | 15 |
| B | 2015-01 | 30 | B | 2015-03 | 17 |
| B | 2015-02 | 15 | B | 2015-01 | 30 |
| B | 2015-02 | 15 | B | 2015-02 | 15 |
| B | 2015-02 | 15 | B | 2015-03 | 17 |
| B | 2015-03 | 17 | B | 2015-01 | 30 |
| B | 2015-03 | 17 | B | 2015-02 | 15 |
| B | 2015-03 | 17 | B | 2015-03 | 17 |
+-------------+----------+--------+-------------+----------+--------+--+
得到以上结果可以看出，我们可以利用b表的salary列来进行累积求和，因为我们要求每个用户每个月总共获得小费，到2月份是要将2月和前一个月的小费累计，也就是表中33,33+10,33+10+16这样
SELECT a.*,b.*
FROM (
SELECT username,month,sum(salary)
FROM t_salary_detail
GROUP BY username,month ) a
INNER JOIN (
SELECT username,month,sum(salary)
FROM t_salary_detail
GROUP BY username,month ) b
ON a.username = b.username
WHERE b.month <= a.month;
+-------------+----------+--------+-------------+----------+--------+--+
| a.username | a.month | a._c2 | b.username | b.month | b._c2 |
+-------------+----------+--------+-------------+----------+--------+--+
| A | 2015-01 | 33 | A | 2015-01 | 33 |
| A | 2015-02 | 10 | A | 2015-01 | 33 |
| A | 2015-02 | 10 | A | 2015-02 | 10 |
| A | 2015-03 | 16 | A | 2015-01 | 33 |
| A | 2015-03 | 16 | A | 2015-02 | 10 |
| A | 2015-03 | 16 | A | 2015-03 | 16 |
| B | 2015-01 | 30 | B | 2015-01 | 30 |
| B | 2015-02 | 15 | B | 2015-01 | 30 |
| B | 2015-02 | 15 | B | 2015-02 | 15 |
| B | 2015-03 | 17 | B | 2015-01 | 30 |
| B | 2015-03 | 17 | B | 2015-02 | 15 |
| B | 2015-03 | 17 | B | 2015-03 | 17 |
+-------------+----------+--------+-------------+----------+--------+--+
得到以上结果后，只需要按照Month分组，对salary列用SUM函数即可
SELECT SUM(bSalSum)
FROM (
SELECT a.month AS aMonth,a.username AS aUser,a.salSum AS aSalSum,
b.month AS bMonth,b.username AS bUser,b.salSum AS bSalSum
FROM (
SELECT month,username,SUM(salary) AS salSum
FROM t_salary_detail
GROUP BY username,month ) a
INNER JOIN (
SELECT month,username,SUM(salary) AS salSum
FROM t_salary_detail
GROUP BY username,month ) b
ON a.username = b.username
WHERE b.month <= a.month ) t
GROUP BY aUser,aMonth;
+------+--+
| _c0 |
+------+--+
| 33 |
| 43 |
| 59 |
| 30 |
| 45 |
| 62 |
+------+--+
最新文章
- .net程序部署（setupFactory）
- svn在linux上的安装
- MARIADB 在 OPENSUSE 的安装。
- BZOJ2851 : 极限满月
- Deep Learning论文笔记之（四）CNN卷积神经网络推导和实现（转）
- 相比于汇编语言的准确性c语言延时精确度如何提升
- Android的那些轮子
- 一个备份MySQL数据库的简单Shell脚本(转)
- mysql float double 类型
- 从用python做zoj1011发生Non-zero Exit Code错误说起
- github上预览Demo网页最简单的方法
- 抓取60000+QQ空间说说做一次数据分析
- Git版本控制之多人协作
- Java-HttpURLConnection详细说明与实例
- 如何在一台机器上部署多个tomcat
- 安装APK时引发INSTALL_PARSE_FAILED_MANIFEST_MALFORMED错误的几种可能(申明：来源于网络)
- BZOJ2721或洛谷1445 [Violet]樱花
- 【LOJ】#2534. 「CQOI2018」异或序列
- CHtmlEditCtrl (3): More HTML Editor Options
- Beginning SDL 2.0(4) YUV加载及渲染