hive sum函数的顶级应用_qingchun851212

在我们基本的认识中，sum函数都是用来求和的，没错，sum就是用来求和的，但除了对整列求和外，你还知道hive的哪些特殊的应用呢，下面小编就为您扒一扒hive中sum的高级顶级应用。

首先去下载我准备的数据 http://pan.baidu.com/s/1bns3wwJ
部分数据示例如下：

[code lang="text"]

1000001754085 中国工商银行 2014-05-1913:58:00 1.0

1000001754085 中国工商银行 2014-05-1913:57:59 1.0

1000001754085 中国工商银行 2014-05-1913:57:57 1.0

1000001918152 中国建设银行 2014-05-1815:01:59 678.0

1000001882307 中国建设银行 2014-05-1516:48:02 1000.0

1000001882307 中国建设银行 2014-05-1516:52:03 3800.0

1000001882307 中国建设银行 2014-05-1600:15:58 3000.0

1000001967226 中国建设银行 2014-05-2015:51:55 800.0

1000001787148 中国建设银行 2014-05-0818:46:02 800.0

1000001787148 中国建设银行 2014-05-0821:02:01 200.0

[/code]

下载后建表并导入数据，我的数据库为tmp，可根据你的实际情况自行修改

[code lang="sql"]

create table tmp.hive_sum (

id string COMMENT '会员ID',

bank_name string COMMENT '银行名称',

create_time string COMMENT '交易时间',

amount double COMMENT '交易金额'

) COMMENT 'hive_sum顶级应用'

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

LINES TERMINATED BY '\n';



load data local inpath '/data/tmp/tqc/hive_sum.txt' overwrite into table tmp.hive_sum;

[/code]

1、每条记录增加此会员的消费总额，用到开窗函数over

[code lang="sql"]

select id,bank_name,create_time,amount,sum(amount) over(partition by id) amount_all

from tmp.hive_sum

order by id,bank_name,create_time;

[/code]

部分结果如下：

[code lang="text"]

1000001913074 建设银行 2014-05-1901:45:17 300.0 600.0

1000001913074 招商银行 2014-05-1901:48:23 300.0 600.0

1000001918152 中国建设银行 2014-05-1815:01:59 678.0 5034.0

1000001918152 北京银行 2014-05-1813:00:15 1678.0 5034.0

1000001918152 北京银行 2014-05-1813:00:51 1678.0 5034.0

1000001918152 招商银行 2014-05-1813:50:19 1000.0 5034.0

1000001918328 建设银行 2014-05-1813:15:53 100.0 4000.0

1000001918328 建设银行 2014-05-1813:16:19 900.0 4000.0

1000001918328 建设银行 2014-05-1813:17:47 1000.0 4000.0

1000001918328 招商银行 2014-05-1813:41:19 1000.0 4000.0

1000001918328 招商银行 2014-05-1900:50:58 1000.0 4000.0

1000001918482 建设银行 2014-05-1813:21:40 2400.0 4800.0

1000001918482 招商银行 2014-05-1813:47:56 1000.0 4800.0

1000001918482 招商银行 2014-05-1900:52:35 1000.0 4800.0

[/code]

2、求每个会员本次消费时，显示之前历史消费的总和

[code lang="sql"]

select id,bank_name,create_time,amount,

sum(amount) over(partition by id order by create_time asc ) amount_all

from tmp.hive_sum

order by id,create_time asc;

[/code]

部分数据显示如下：

[code lang="text"]

1000002108977   建设银行        2014-05-26 14:50:32     1000.0  1000.0

1000002108977   建设银行        2014-05-26 14:50:57     1000.0  2000.0

1000002108977   建设银行        2014-05-26 14:51:27     1000.0  3000.0

1000002108977   北京银行        2014-05-26 14:54:02     3000.0  6000.0

1000002108977   北京银行        2014-05-26 15:20:57     1500.0  7500.0

1000002108977   北京银行        2014-05-26 16:23:23     1500.0  9000.0

1000002108977   中国建设银行    2014-05-26 16:23:55     1500.0  10500.0

1000002108977   北京银行        2014-05-26 16:47:28     208.0   10708.0

1000002108977   北京银行        2014-05-26 16:58:42     979.0   11687.0

1000002108977   北京银行        2014-05-26 17:02:07     298.0   11985.0

1000001786530   招商银行        2014-05-08 18:38:56     1000.0  1000.0

[/code]

如果想按会员id，银行查询历史消费信息，只需在开窗函数over里的partition部分增加bank_name字段即可，代码如下

[code lang="sql"]

select id,bank_name,create_time,amount,

sum(amount) over(partition by id,bank_name order by create_time asc ) amount_all

from tmp.hive_sum

order by id,bank_name,create_time asc;

[/code]

效果：

[code lang="sql"]

1000002108977   中国建设银行    2014-05-26 16:23:55     1500.0  1500.0

1000002108977   北京银行        2014-05-26 14:54:02     3000.0  3000.0

1000002108977   北京银行        2014-05-26 15:20:57     1500.0  4500.0

1000002108977   北京银行        2014-05-26 16:23:23     1500.0  6000.0

1000002108977   北京银行        2014-05-26 16:47:28     208.0   6208.0

1000002108977   北京银行        2014-05-26 16:58:42     979.0   7187.0

1000002108977   北京银行        2014-05-26 17:02:07     298.0   7485.0

1000002108977   建设银行        2014-05-26 14:50:32     1000.0  1000.0

1000002108977   建设银行        2014-05-26 14:50:57     1000.0  2000.0

1000002108977   建设银行        2014-05-26 14:51:27     1000.0  3000.0

[/code]

3、按时间显示会员本次消费的上两次、下两次消费的和，也就是本次消费的前后两次消费总和，sql如下

[code lang="sql"]



select id,create_time,  amount,

sum(amount) over(partition by id order by create_time asc rows between 2 preceding and 2 following ) amount_all

from tmp.hive_sum

order by id, create_time asc;



[/code]

查询部分结果显示

[code lang="text"]



1000002108977   2014-05-26 14:50:32     1000.0  3000.0

1000002108977   2014-05-26 14:50:57     1000.0  6000.0

1000002108977   2014-05-26 14:51:27     1000.0  7500.0

1000002108977   2014-05-26 14:54:02     3000.0  8000.0

1000002108977   2014-05-26 15:20:57     1500.0  8500.0

1000002108977   2014-05-26 16:23:23     1500.0  7708.0

1000002108977   2014-05-26 16:23:55     1500.0  5687.0

1000002108977   2014-05-26 16:47:28     208.0   4485.0

1000002108977   2014-05-26 16:58:42     979.0   2985.0

1000002108977   2014-05-26 17:02:07     298.0   1485.0

1000001786530   2014-05-08 18:38:56     1000.0  1000.0



[/code]

注意：partition by 是分组用的，按谁进行分组统计，order by 不要省掉，否则会有麻烦， asc或者desc最好写上。between 2 preceding （是前两行） and 2 following （是后两行，没有就不出现）注意是分组后的前后两行哦

4、求每个会员每个银行的消费总额，并单独一行显示此会员的消费总额

[code lang="sql"]

select id,bank_name,

sum(amount)  amount_all

from tmp.hive_sum

group by id,bank_name

with rollup

order by id, bank_name desc ;

[/code]

显示效果如下：

[code lang="text"]

1000002101498        建设银行    200

1000002101498  北京银行    20345

1000002101498        NULL    20545

1000002108977        建设银行    3000

1000002108977 北京银行    7485

1000002108977 中国建设银行      1500

1000002108977 NULL    11985



[/code]

hive sum求和的高级应用

很多时候万恶的分析师在数据出完后，还要一条对每个会员消费总额或者什么什么什么的汇总，这时候你可能用的最多的是union all了吧，现在换个方法吧with rollup，你会有意想不到的惊喜。

如果你喜欢本文，欢迎转载，记得要写上本文链接哦

如有任何问题，欢迎留言，在能力所及的范围内，我会认真解答的！！！

hive sum函数的顶级应用

杂谈