【Leecode-专项突破-SQL基础】day1

前段时间闲了几日，刷题还是不能荒废啊
今天的专项为 数值处理函数，共 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'

–今天的分享就到这里咯