hive-级联求和

最新推荐文章于 2024-09-22 23:16:50 发布

貂皮-坎肩儿

最新推荐文章于 2024-09-22 23:16:50 发布

阅读量530

点赞数 1

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/mswuxianshen/article/details/94723003

本文介绍了一种级联求和的方法，通过内连接和条件筛选实现了访客访问次数的逐月累计计算。从数据加载、表创建到最终的级联求和实现，详细展示了SQL操作流程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

本人思路，欢迎大佬指出错误

级联求和的思路：

：首先我们针对一个求每月之中每天产品递增的销量，求解；

1.明确目的，针对此需求。要的是一月之中的每天数据，分组应是 day。

2.每天递增，说明只要当前天以及本月当前天之前的day的销量总和。

3.运用inner join连接方式和where b.day<=a.day针对此问题把当前天以及之前的天数销量排查出来，做聚合操作。

4.针对 1 中做分组操作，后做排序操作。

下面针对这个思路做详细的问题解答：

1、基本需求

根据访客的每日访问信息，进行累计访问：

输入数据：

有如下访客访问次数统计表 t_access_times

为了减轻计算复杂度，去掉了天的信息只留下了年月。

输出数据：

需要输出报表：t_access_times_accumulate

2、实现步骤

2.1 原始数据

vi /home/chunsoft/t_access_times.dat

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

2.2 创建表

jdbc:hive2://172.16.208.128:10000>create table t_access_times(username string,month string,access_time int)

row format delimited fields terminated by ',';

2.3 将数据加载到表中

jdbc:hive2://172.16.208.128:10000>

load data local inpath '/home/chunsoft/t_access_times.dat' into table t_access_times;

2.4 计算单个用户的月访问次数

select username,month,sum(access_time) as access_time from t_access_times group by username,month;

+-----------+----------+---------+--+

| username | month | access_time |

+-----------+----------+---------+--+

| A | 2015-01 | 33 |

| A | 2015-02 | 10 |

| B | 2015-01 | 30 |

| B | 2015-02 | 15 |

+-----------+----------+---------+--+

2.4 上面的表自己跟自己进行inner join方便求总访问

select A.*,B.* from

(select username,month,sum(access_time) as access_time from t_access_times group by username,month) A

inner join

(select username,month,sum(access_time) as access_time from t_access_times group by username,month) B

on

A.username=B.username

+-------------+----------+-----------+-------------+----------+-----------+--+

+-------------+----------+-----------+-------------+----------+-----------+--+

| A | 2015-01 | 33 | A | 2015-01 | 33 |

| A | 2015-01 | 33 | A | 2015-02 | 10 |

| A | 2015-02 | 10 | A | 2015-01 | 33 |

| A | 2015-02 | 10 | A | 2015-02 | 10 |

| B | 2015-01 | 30 | B | 2015-01 | 30 |

| B | 2015-01 | 30 | B | 2015-02 | 15 |

| B | 2015-02 | 15 | B | 2015-01 | 30 |

| B | 2015-02 | 15 | B | 2015-02 | 15 |

+-------------+----------+-----------+-------------+----------+-----------+--+

2.5 根据当前月份的大小来判断累计求和并排序

select A.username,A.month,max(A.access_time) access_time,sum(B.access_time) accumulate

from

(select username,month,sum(access_time) as access_time from t_access_times group by username,month) A

inner join

(select username,month,sum(access_time) as access_time from t_access_times group by username,month) B

on

A.username=B.username

where B.month<=A.month

group by A.username,A.month

order by A.username,A.month

+-------------+----------+---------+-------------+--+

+-------------+----------+---------+-------------+--+

| A | 2015-01 | 33 | 33 |

| A | 2015-02 | 10 | 43 |

| B | 2015-01 | 30 | 30 |

| B | 2015-02 | 15 | 45 |

这里面的max(A.access_time) 起到一个聚合作用，保留一条结果。

但是：看着有点麻烦

下面?是简易的级联求和:

select  username,date,access_number,sum(access_number) over
 (partition by username order by date) as sums from table;

有兴趣的小伙伴可以试一下