題目
Table: Prices
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| product_id | int |
| start_date | date |
| end_date | date |
| price | int |
+---------------+---------+
(product_id，start_date，end_date) 是 Prices 表的主鍵暖释。
Prices 表的每一行表示的是某個產(chǎn)品在一段時期內(nèi)的價格抚官。
每個產(chǎn)品的對應(yīng)時間段是不會重疊的，這也意味著同一個產(chǎn)品的價格時段不會出現(xiàn)交叉脆栋。
Table: UnitsSold
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| product_id | int |
| purchase_date | date |
| units | int |
+---------------+---------+
UnitsSold 表沒有主鍵，它可能包含重復(fù)項洒擦。
UnitsSold 表的每一行表示的是每種產(chǎn)品的出售日期椿争，單位和產(chǎn)品 id。
編寫SQL查詢以查找每種產(chǎn)品的平均售價熟嫩。
average_price 應(yīng)該四舍五入到小數(shù)點后兩位秦踪。
查詢結(jié)果格式如下例所示：
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 |
+------------+---------------+-------+
Result table:
+------------+---------------+
| product_id | average_price |
+------------+---------------+
| 1 | 6.96 |
| 2 | 16.96 |
+------------+---------------+
平均售價 = 產(chǎn)品總價 / 銷售的產(chǎn)品數(shù)量。
產(chǎn)品 1 的平均售價 = ((100 * 5)+(15 * 20) )/ 115 = 6.96
產(chǎn)品 2 的平均售價 = ((200 * 15)+(30 * 30) )/ 230 = 16.96
解答
兩表連接
select *
from UnitsSold as U
join Prices as P
on U.product_id = P.product_id and
U. purchase_date >= P.start_date and
U. purchase_date <= P.end_date
按U.product_id進(jìn)行分組 sum(P.Price * U.units)即為銷售總價
sum(U.units)即為銷售產(chǎn)品數(shù)量
select U.product_id, round(sum(P.Price * U.units)/sum(U.units) ,2) as average_price
from UnitsSold as U
join Prices as 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
