Hive--笔试题01--自连接实现

末蓝、 2022-01-07 04:25 394阅读 0赞

有以下一份数据：

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,16
    A,2015-03,22
    B,2015-03,23
    B,2015-03,10
    B,2015-03,11

数据的字段定义是：

**name,month,pv**

数据的字段意义是：

**用户，月份，访问量**

现在来看需求：

## **每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数** ##

结果如下：

用户	月份		当月访问次数	最大访问次数	总访问次数		
    A       2015-01		33		33		33
    A       2015-02		10		33		43
    A       2015-03		38		38		81
    B       2015-01		30		30		30
    B       2015-02		15		30		45
    B       2015-03		44		44		89

具体的最终实现的SQL：

select a.name as aname, a.month as amonth, a.pv as apv, 
    max(b.pv) as maxpv, sum(b.pv) as sumpv 
    from 
    (select a.name, a.month, sum(a.pv) as pv  from exercise01 a  group by a.name, a.month) a 
    join 
    (select a.name, a.month, sum(a.pv) as pv  from exercise01 a  group by a.name, a.month) b 
    on a.name = b.name 
    where a.month >= b.month 
    group by a.name, a.month, a.pv;

实现思路：

第一步：由于每个用户在每个月份有多条数据访问记录，所以根据题意，首先得汇总每个用户在每个月份的总访问次数

SQL实现：

select a.name, a.month, sum(a.pv) as pv from exercise01 a group by a.name, a.month;

结果数据：

A       2015-01 33
    A       2015-02 10
    A       2015-03 38
    B       2015-01 30
    B       2015-02 15
    B       2015-03 44

第二步：由于要求得

用户 月份 当月访问次数 最大访问次数 总访问次数  
A 2015-01 33 33 33  
A 2015-02 10 33 43  
A 2015-03 38 38 81  
B 2015-01 30 30 30  
B 2015-02 15 30 45  
B 2015-03 44 44 89

这种格式的数据，需要如下这种格式的数据才能求出：

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 38 A 2015-01 33  
A 2015-03 38 A 2015-02 10  
A 2015-03 38 A 2015-03 38  
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 44 B 2015-01 30  
B 2015-03 44 B 2015-02 15  
B 2015-03 44 B 2015-03 44

那如何得到这样的数据呢？执行如下的SQL：

select a.name as aname, a.month as amonth, a.pv as apv, 
    b.name as bname, b.month as bmonth, b.pv as bpv 
    from 
    (select a.name, a.month, sum(a.pv) as pv from exercise01 a group by a.name, a.month) a 
    join 
    (select a.name, a.month, sum(a.pv) as pv from exercise01 a group by a.name, a.month) b 
    on a.name = b.name  
    where a.month >= b.month;

第三步：在得到上述数据的基础之上，然后直接进行聚合即可

SQL实现：

select a.aname, a.amonth, a.apv,  max(a.bpv) as maxpv, sum(a.bpv) as sumpv 
    from 
    (
    select a.name as aname, a.month as amonth, a.pv as apv, 
    b.name as bname, b.month as bmonth, b.pv as bpv 
    from 
    (select a.name, a.month, sum(a.pv) as pv from exercise01 a group by a.name, a.month) a 
    join 
    (select a.name, a.month, sum(a.pv) as pv from exercise01 a group by a.name, a.month) b 
    on a.name = b.name  
    where a.month >= b.month
    ) a 
    group by a.aname, a.amonth, a.apv;

第四步：执行结果

A       2015-01 33      33      33
    A       2015-02 10      33      43
    A       2015-03 38      38      81
    B       2015-01 30      30      30
    B       2015-02 15      30      45
    B       2015-03 44      44      89

第五步：对于SQL语句进行一些优化得到最终SQL:

总结：

为了实现需求，我们从结果数据进行推导，有什么样的基础数据写什么样的SQL语句就能得到什么的结果数据。这是指导我么写SQL的最基本的方式。

在本题中，我们使用结果数据，推导需要什么样的基础数据，在构建基础数据的时候。我们使用了自连接。

这里还有一个类似的题目，大家可以尝试一下

数据：

a,01,150
    a,01,200
    b,01,1000
    b,01,800
    c,01,250
    c,01,220
    b,01,6000
    a,02,2000
    a,02,3000
    b,02,1000
    b,02,1500
    c,02,350
    c,02,280
    a,03,350
    a,03,250

数据的字段定义是：

**shop,month,money**

数据的字段意义是：

**店铺，月份，营业额**

**需求：**

**需求：编写Hive的HQL语句求出每个店铺的当月销售额和累计到当月的总销售额**