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

Wu_Candy

发布于 2022-07-05 14:10:16

3560

发布于 2022-07-05 14:10:16

文章被收录于专栏：无量测试之道

题目:

(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

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2022-02-23，如有侵权请联系 cloudcommunity@tencent.com 删除

sql

hive

本文分享自无量测试之道微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

sql

hive

登录后参与评论

0 条评论

热度