当前位置:   article > 正文

【SQL】平均售价

【SQL】平均售价

目录

题目

分析

代码


题目

表:Prices

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| start_date    | date    |
| end_date      | date    |
| price         | int     |
+---------------+---------+
(product_id,start_date,end_date) 是 prices表的主键(具有唯一值的列的组合)。prices表的每一行表示的是某个产品在一段时期内的价格。
每个产品的对应时间段是不会重叠的,这也意味着同一个产品的价格时段不会出现交叉。

表:UnitsSold

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| purchase_date | date    |
| units         | int     |
+---------------+---------+
该表可能包含重复数据。
该表的每一行表示的是每种产品的出售日期,数量和产品 id。

编写解决方案以查找每种产品的平均售价。average_price 应该 四舍五入到小数点后两位

返回结果表 无顺序要求 。

结果格式如下例所示。

示例 1:

输入:
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     |
+------------+------------+------------+--------+
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    |
+------------+---------------+-------+
输出:
+------------+---------------+
| product_id | average_price |
+------------+---------------+
| 1          | 6.96          |
| 2          | 16.96         |
+------------+---------------+
解释:
平均售价 = 产品总价 / 销售的产品数量。
产品 1 的平均售价 = ((100 * 5)+(15 * 20) )/ 115 = 6.96
产品 2 的平均售价 = ((200 * 15)+(30 * 30) )/ 230 = 16.96

分析

产品价格表和出售时间表

左连接,from Prices p left join UnitsSold u on p.product_id = u.product_id

价格对应时间段,出售日期对应时间点

使用between找到对应价格,where u.purchase_date between p.start_date and p.end_date

考虑产品尚未出售

或关系,or u.product_id is null

查找每种产品的平均售价

按照产品分组,group by p.product_id

平均售价,sum(units*price)/sum(units

存在null情况,ifnull(,0)

average_price 应该 四舍五入到小数点后两位

round函数,round(ifnull(sum(units*price)/sum(units),0),2)

代码

  1. select p.product_id, round(ifnull(sum(units*price)/sum(units),0),2) average_price
  2. from Prices p left join UnitsSold u
  3. on p.product_id = u.product_id
  4. where u.purchase_date between p.start_date and p.end_date or u.product_id is null
  5. group by p.product_id

 

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/爱喝兽奶帝天荒/article/detail/999102
推荐阅读
相关标签
  

闽ICP备14008679号