前段时间闲了几日,刷题还是不能荒废啊
今天的专项为 数值处理函数,共 4 题:2 简单 2 中等
1699.两人之间的通话次数
输入:
Calls 表:
+---------+-------+----------+
| from_id | to_id | duration |
+---------+-------+----------+
| 1 | 2 | 59 |
| 2 | 1 | 11 |
| 1 | 3 | 20 |
| 3 | 4 | 100 |
| 3 | 4 | 200 |
| 3 | 4 | 200 |
| 4 | 3 | 499 |
+---------+-------+----------+
#该表没有主键,可能存在重复项。
#该表包含 from_id 与 to_id 间的一次电话的时长。
#from_id != to_id
输出:
+---------+---------+------------+----------------+
| person1 | person2 | call_count | total_duration |
+---------+---------+------------+----------------+
| 1 | 2 | 2 | 70 |
| 1 | 3 | 1 | 20 |
| 3 | 4 | 4 | 999 |
+---------+---------+------------+----------------+
编写 SQL 语句,查询每一对用户 (person1, person2) 之间的通话次数和通话总时长,其中 person1 < person2 。以任意顺序返回结果表。
初看好像没有思路,按照以往习惯对表 group by 后即可进行聚合操作,但这道题中对 c1.from_id = c2.to_id 或 c1.to_id = c2.from_id ,均视为同一类通话,需要一点技巧将此类情况合并。
方法(1)
select t.person1, t.person2
,count(t.person1) as call_count
,sum(t.duration) as total_duration from (
select c1.from_id as person1, c1.to_id as person2, c1.duration
from Calls c1 where c1.from_id < c1.to_id
union all
select c2.to_id as person1, c2.from_id as person2, c2.duration
from Calls c2 where c2.to_id < c2.from_id
) t group by t.person1,t.person2 ;
方法(2)
与方法1类似,找到较小的ID值,并分组聚合
select
case when from_id < to_id then from_id else to_id end as person1
,case when from_id > to_id then from_id else to_id end as person2
,count(from_id) as call_count
,sum(duration) as total_duration
from Calls group by person1, person2 ;
1251.平均售价
Prices table:
+------------+------------+------------+--------+
| product_id | start_date | end_date | price |
+------------+------------+------------+--------+
| 1 | 2019-02-17 | 2019-02-28 | 5 |
| 1 | 2019-03-01 | 2019-03-22 | 20 |
| 2 | 2019-02-01 | 2019-02-20 | 15 |
| 2 | 2019-02-21 | 2019-03-31 | 30 |
+------------+------------+------------+--------+
#(product_id,start_date,end_date) 是 Prices 表的主键。
#Prices 表的每一行表示的是某个产品在一段时期内的价格。
#每个产品的对应时间段是不会重叠的,这也意味着同一个产品的价格时段不会出现交叉。
UnitsSold table:
+------------+---------------+-------+
| product_id | purchase_date | units |
+------------+---------------+-------+
| 1 | 2019-02-25 | 100 |
| 1 | 2019-03-01 | 15 |
| 2 | 2019-02-10 | 200 |
| 2 | 2019-03-22 | 30 |
+------------+---------------+-------+
#UnitsSold 表没有主键,它可能包含重复项
Result table:
+------------+---------------+
| product_id | average_price |
+------------+---------------+
| 1 | 6.96 |
| 2 | 16.96 |
+------------+---------------+
编写SQL查询以查找每种产品的平均售价。average_price 应该四舍五入到小数点后两位。
select product_id,round(sum(t.sub_sum)/sum(t.units),2) as average_price from (
select u.product_id, u.units, (u.units * p.price) as sub_sum
from UnitsSold u left join Prices p
on u.product_id = p.product_id
and u.purchase_date >= p.start_date and u.purchase_date <= p.end_date
group by u.product_id,u.purchase_date
) t group by t.product_id
1571.仓库经理
输入:
Warehouse 表:
+------------+--------------+-------------+
| name | product_id | units |
+------------+--------------+-------------+
| LCHouse1 | 1 | 1 |
| LCHouse1 | 2 | 10 |
| LCHouse1 | 3 | 5 |
| LCHouse2 | 1 | 2 |
| LCHouse2 | 2 | 2 |
| LCHouse3 | 4 | 1 |
+------------+--------------+-------------+
Products 表:
+------------+--------------+------------+----------+-----------+
| product_id | product_name | Width | Length | Height |
+------------+--------------+------------+----------+-----------+
| 1 | LC-TV | 5 | 50 | 40 |
| 2 | LC-KeyChain | 5 | 5 | 5 |
| 3 | LC-Phone | 2 | 10 | 10 |
| 4 | LC-T-Shirt | 4 | 10 | 20 |
+------------+--------------+------------+----------+-----------+
输出:
+----------------+------------+
| WAREHOUSE_NAME | VOLUME |
+----------------+------------+
| LCHouse1 | 12250 |
| LCHouse2 | 20250 |
| LCHouse3 | 800 |
+----------------+------------+
写一个 SQL 查询来报告, 每个仓库的存货量是多少立方英尺。返回结果没有顺序要求。
select t.name as WAREHOUSE_NAME, sum(t.unit_vol) as VOLUME from (
select w.name, (w.units * p.Width * p.Length * p.Height) as unit_vol
from Warehouse w left join Products p
on w.product_id = p.product_id
) t group by t.name
1445.苹果和桔子
Sales 表:
+------------+------------+-------------+
| sale_date | fruit | sold_num |
+------------+------------+-------------+
| 2020-05-01 | apples | 10 |
| 2020-05-01 | oranges | 8 |
| 2020-05-02 | apples | 15 |
| 2020-05-02 | oranges | 15 |
| 2020-05-03 | apples | 20 |
| 2020-05-03 | oranges | 0 |
| 2020-05-04 | apples | 15 |
| 2020-05-04 | oranges | 16 |
+------------+------------+-------------+
Result 表:
+------------+--------------+
| sale_date | diff |
+------------+--------------+
| 2020-05-01 | 2 |
| 2020-05-02 | 0 |
| 2020-05-03 | 20 |
| 2020-05-04 | -1 |
+------------+--------------+
写一个 SQL 查询, 报告每一天 苹果 和 桔子 销售的数目的差异。返回的结果表, 按照格式为 (‘YYYY-MM-DD’) 的 sale_date 排序。
比较好的方法(1)
select sale_date
,sum(case when fruit='apples' THEN sold_num ELSE -sold_num end) as diff
from sales
group by sale_date order by sale_date
方法(2)
select t.sale_date, (t.sold_num - t.org_num) as diff from (
select *
,lead(sold_num) over(order by sale_date,fruit) as org_num
from Sales
) t where t.fruit = 'apples'
–今天的分享就到这里咯