百分比问题,SQL给你答案

SQL语句
150
0
0
2024-01-14

【题目】

下面有两张表, “交易表”和“产品信息表”。

假如交易金额大于等于10万,认定为大单。统计产品ID,产品名称,大单数量,大单数量占比。

【解题思路】

思路:先通过分解步骤一步步求出必要字段,再通过分解步骤,最后解决题目要求,即统计产品ID,产品名称,大单数量,大单数量占比。

1.多表联结

题目要求的字段信息在两张表中,涉及到多张表,要想到《猴子 从零学会SQL》里讲过的用多表查询。两张表的关系如下:

两张表通过“产品ID”关联

使用哪种联结呢?

看以下两表可知,交易表和产品信息表中均含有字段【产品ID】,因交易表中含有需求出的未知字段【大单】和【大单数量】的关键字段【交易金额】,

所有,选择将交易表作为左表,进行左联结(left join)。

select 
       t1.产品ID,
       t1.用户ID,
       t2.产品ID,
       t2.产品名称
from 交易表 as t1
left join 交易信息表 as t2
on t1.产品ID = t2.产品ID;

2.如何得到大单、大单数量?

业务指标定位为“交易金额大于等于10万”被认定为【大单】。找到“交易金额”筛选出对应的大单。然后通过分组汇总,得到大单数量。

(todo需要画图说明 sum(case sum t1.交易金额 >= 100000 then 1 end) as 大单数量,不然小白读者看不懂怎么来的)

select 
  t1.产品ID,t2.产品名称,count(t1.产品名称) as 单数汇总,
  sum(case sum t1.交易金额 >= 100000 then 1 end) as 大单数量
from 交易表 as t1
left join 交易信息表 as t2
on t1.产品ID = t2.产品ID
group by t1.产品ID,t2.产品名称;

3.大单数量的占比?

大单数量占比=大单数量/总数

前面获得“大单”相关字段数据,就可以进行统计各字段和“大单数量的占比”(即汇总百分比的计算)。

(todo需要画图说明百分比怎么计算,还有下面怎么来的看不懂

 count(1)as '单数汇总,
  sum( casewhen t1.交易金额>= 100000 then 1 end ) as '大单数量',
  sum(case when t1.交易金额<100000 then 1 end ) as '小单数量',
  concat( left(sum ( case when t1.交易金额 >= 100000 THEN 1 END) / count(1)*100,5),'%') as '大单数量占比'

select
  t2.产品ID,
  t2.产品名称,
  count(1)as '单数汇总,
  sum( casewhen t1.交易金额>= 100000 then 1 end ) as '大单数量',
  sum(case when t1.交易金额<100000 then 1 end ) as '小单数量',
  concat( left(sum ( case when t1.交易金额 >= 100000 THEN 1 END) / count(1)*100,5),'%') as '大单数量占比'
from 交易表 as t1
left join 交易信息表 as t2
on t1.产品ID = t2.产品ID
group by t2.产品ID,t2.产品名称'小单数量';

【本题考点】

本题着重考察如何掌握计算百分比,以及如何分步拆解最后解决问题的思维。

(1)考查对百分比的计算。注意掌握MySQL计算百分比计算的语法;

(2)考查分步拆解的思维能力。传统百分比计算大家都会,难点在于如何使用SQL计算百分比。可以通过分步拆解题意解决最后的百分比问题。

【举一反三】 “举一反三”题目来源:

不同数据库,对于用SQL计算百分比的语法有所不同。

(todo给出计算百分比这类问题的万能模板,参考这个:https://mp.weixin.qq.com/s/8Ax7eHb02cxc30HrKFJi9g)

参考答案:

(1)MySQL的写法:

select
  t2.产品ID,
  t2.产品名称,
  count(1)as '单数汇总',
  sum( casewhen t1.交易金额>= 100000 then 1 end ) as '大单数量',
  sum(case when t1.交易金额<100000 then 1 end ) as '小单数量',
  concat( left( sum ( case when t1.交易金额 >= 100000 THEN 1 END) / count(1)*100,5),'%') as '大单数量占比'
from 交易表 as t1 join 交易信息表 as t2
where t1.产品ID = t2.产品ID
group by t2.产品ID,t2.产品名称;

(2)PostgreSQL的写法:

select
  t2.产品ID,
  t2.产品名称,
  count(1)as '单数汇总',
  sum( casewhen t1.交易金额>= 100000 then 1 end ) as '大单数量',
  sum(case when t1.交易金额<100000 then 1 end ) as '小单数量',
  round sum(case when t1.交易金额>=100000 then 1 end) / count(1)::numeric((10,2)*100,2) ||'%' as '大单占比'
from 交易表 as t1 join 交易信息表 as t2
where t1.产品ID = t2.产品ID
group by t2.产品ID,t2.产品名称;