这道 SQL 题，你有不一样的解法吗？

题目:

(1).前置条件：

有以下数据集C，表查询结果如下图所示，设置该表为表：test_month_amount。

 

 

 

(2).题目要求：

数据集C 中 Amount 字段是每个人同一月份，不同状态的金额总和，现要求使用 sql 实现对相同月份的数据均分Amount。

预期结果:

 

 

 

解题思路:

(1). 子查询B: 按照NAME和MON进行分组求数量总和

查询结果如下：

NAME 　　MON 　　   cnt
张三 　　  201901 　　 3
李四 　　  201902 　   2
王五 　　  201902 　   2

(2). 使用子查询C left join 子查询 B 的结果，关联条件为: NAME, MON,使用 C.AMOUNT/B.cnt 即可求得平均值

查询结果如预期结果所示

 

解题方式一:

适用于不用创建物理表的情况下

使用数据集C、A替代物理表：test_month_amount，直接复制以下 hive sql 语句，可以在 Apache Hive 环境直接运行，得到以上预期结果数据。

select C.ID
,C.NAME
,C.MON
,CAST(C.AMOUNT/B.cnt AS BIGINT) AS AMOUNT
from (
select 1 ID,'张三' NAME,201901 MON,'A' STATE,9000 AMOUNT
union all
select 2 ID,'张三' NAME,201901 MON,'B' STATE,9000 AMOUNT
union all
select 3 ID,'张三' NAME,201901 MON,'E' STATE,9000 AMOUNT
union all
select 4 ID,'李四' NAME,201902 MON,'A' STATE,1800 AMOUNT
union all
select 5 ID,'李四' NAME,201902 MON,'C' STATE,1800 AMOUNT
union all
select 6 ID,'王五' NAME,201902 MON,'C' STATE,30000 AMOUNT
union all
select 7 ID,'王五' NAME,201902 MON,'F' STATE,30000 AMOUNT
)C
left join (select A.NAME
,A.MON
,count(1) as cnt
from (
select 1 ID,'张三' NAME,201901 MON,'A' STATE,9000 AMOUNT
union all
select 2 ID,'张三' NAME,201901 MON,'B' STATE,9000 AMOUNT
union all
select 3 ID,'张三' NAME,201901 MON,'E' STATE,9000 AMOUNT
union all
select 4 ID,'李四' NAME,201902 MON,'A' STATE,1800 AMOUNT
union all
select 5 ID,'李四' NAME,201902 MON,'C' STATE,1800 AMOUNT
union all
select 6 ID,'王五' NAME,201902 MON,'C' STATE,30000 AMOUNT
union all
select 7 ID,'王五' NAME,201902 MON,'F' STATE,30000 AMOUNT
) A group by NAME, MON
) B ON C.NAME=B.NAME AND C.MON=B.MON;

 

解题方式二：

适用于先创建物理表：test_month_amount的情况下

将测试数据 insert 至 test_month_amount 表。

表数据结果如下：

 

 

 

将解题方式一中的数据集C, A替换成表 test_month_amount即可。

select C.ID
,C.NAME
,C.MON
,CAST(C.AMOUNT/B.cnt AS BIGINT) AS AMOUNT
from test_month_amount C
left join (select A.NAME
,A.MON
,count(1) as cnt
from test_month_amount A group by NAME, MON
) B ON C.NAME=B.NAME AND C.MON=B.MON;

 

知识点归纳：

使用 hive sql 完成这道 Sql 题，所使用到的函数或方法如下：

CAST(C.AMOUNT/B.cnt AS BIGINT) AS AMOUNT

 

C.AMOUNT/B.cnt: 求每个用户相同月份不同状态的平均值

CAST: 类型转换函数，此处将计算出来的平均值结果转换为 BIGINT 类型并将结果取了别名：AMOUNT

 

欢迎关注【无量测试之道】公众号，回复【领取资源】

Python+Unittest框架API自动化、

Python+Unittest框架API自动化、

Python+Pytest框架API自动化、

Python+Pandas+Pyecharts大数据分析、

Python+Selenium框架Web的UI自动化、

Python+Appium框架APP的UI自动化、

Python编程学习资源干货、

资源和代码 免费送啦~
文章下方有公众号二维码，可直接微信扫一扫关注即可。

备注：我的个人公众号已正式开通，致力于IT互联网技术的分享。

包含：数据分析、大数据、机器学习、测试开发、API接口自动化、测试运维、UI自动化、性能测试、代码检测、编程技术等。

微信搜索公众号：“无量测试之道”，或扫描下方二维码：

添加关注，让我们一起共同成长！