本文主要是介绍[每日一练]平均售价(MySQL中对null值的处理,和连接表时多条件的判断),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
改题目来源于力扣:
1251. 平均售价 - 力扣(LeetCode)
题目要求:
表: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
思路流程:
如何连接两个表:
SELECT ****
#A和B进行连接
FROM A (LEFT[OUTER]/RIGHT[OUTER]/UNION) B
#连接条件
ON 条件1
AND 条件2
- 首先,我们需要将两个表进行连接,连接的条件是多个,有:1)U表的日期必须在P表的开始日期和结束日期后。2)U表的id和P表的id相等:
#实例代码块,不可测试
Prices P
LEFT JOIN UnitsSold U
ON P.product_id = U.product_id
AND purchase_date between P.start_date and P.end_date
2.返回特定的数据:在按照id分组后:SUM(U表的units*P表的price)/SUM(U表的units),小数保留两位,可以表示为:
# 最终代码, 可执行
SELECT P.product_id,ROUND(SUM(U.units*p.price)/SUM(U.units),2) AS average_price
FROM Prices P
LEFT JOIN UnitsSold U
ON P.product_id = U.product_id
AND purchase_date between P.start_date and P.end_date
group by product_id
特殊情况(null异常):
输入
Prices =
| 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 |
| 3 | 2019-02-21 | 2019-03-31 | 30 |
UnitsSold =
| 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 |
| 3 | null |
预期结果
| product_id | average_price |
| ---------- | ------------- |
| 1 | 6.96 |
| 2 | 16.96 |
| 3 | 0 |
可见问题出现的原因就是P表多出了一个id:3,导致了输出数据的null异常。为了保证P表的完整性,我们利用左连接(P表在左边) 。在输出的表达式中出现nul的话会输出null值,在合并数据的时候会造成不便。这里可以利用代码:
#如果value1不为空则返回value1,否则返回value2
IFNULL(value1,value2)
来将输出的null值进行替换:
所以最终代码实现:
SELECT P.product_id,IFNULL(ROUND(SUM(U.units*p.price)/SUM(U.units),2),0) AS average_price
FROM Prices P
LEFT JOIN UnitsSold U
ON P.product_id = U.product_id
AND purchase_date between P.start_date and P.end_date
group by product_id
这篇关于[每日一练]平均售价(MySQL中对null值的处理,和连接表时多条件的判断)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!