列表

详情

1251. 平均售价

Table: Prices

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

 

Table: UnitsSold

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| purchase_date | date    |
| units         | int     |
+---------------+---------+
UnitsSold 表没有主键，它可能包含重复项。
UnitsSold 表的每一行表示的是每种产品的出售日期，单位和产品 id。

 

编写SQL查询以查找每种产品的平均售价。
average_price 应该四舍五入到小数点后两位。
查询结果格式如下例所示：

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

原站题解

去查看

上次编辑到这里，代码来自缓存 点击恢复默认模板
# Write your MySQL query statement below

pythondata 解法, 执行用时: 459 ms, 内存消耗: 67.4 MB, 提交时间: 2024-05-27 11:38:24 

import pandas as pd

def average_selling_price2(prices: pd.DataFrame, units_sold: pd.DataFrame) -> pd.DataFrame:
    df=prices.merge(units_sold,on='product_id',how='left')
    df=df[(df['purchase_date']>=df['start_date'])&(df['purchase_date']<=df['end_date'])]
    result=df.groupby('product_id').agg(average_price=('units',lambda x:((x*df['price']).sum()/x.sum()).round(2))).reset_index()
    all_products=prices['product_id'].unique()
    res=pd.DataFrame(all_products,columns=['product_id'])
    return res.merge(result,on='product_id',how='left').fillna(0)
    

def average_selling_price(prices: pd.DataFrame, units_sold: pd.DataFrame) -> pd.DataFrame:
    # 合并 Prices 和 UnitsSold 表格，根据 product_id 进行连接
    merged_df = pd.merge(prices, units_sold, on='product_id', how='left')

    # 筛选出符合日期范围的记录
    merged_df = merged_df[(merged_df['purchase_date'] >= merged_df['start_date']) & (merged_df['purchase_date'] <= merged_df['end_date'])]

    merged_df['total_price'] = merged_df['price']*merged_df['units']

    # 计算每种产品的总销售额和总销售数量
    product_sales = merged_df.groupby('product_id').agg({'total_price': 'sum', 'units': 'sum'}).reset_index()

    # 计算平均售价，并四舍五入到小数点后两位
    product_sales['average_price'] = round(product_sales['total_price'] / product_sales['units'], 2)

    # 提取所需的列并返回结果
    result_df = product_sales[['product_id', 'average_price']]

    result_df = prices[['product_id']].merge(result_df, on = 'product_id', how = 'left').fillna(0)

    return result_df.drop_duplicates()

mysql 解法, 执行用时: 450 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 11:47:25 

# Write your MySQL query statement below

SELECT
	p.product_id,
	ROUND(SUM(p.price * u.units) / SUM(u.units), 2) AS average_price 
FROM
	Prices AS p
	INNER JOIN UnitsSold AS u
		ON p.product_id = u.product_id 
		AND u.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY p.product_id;

mysql 解法, 执行用时: 477 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 11:47:12 

# Write your MySQL query statement below
SELECT 
    product_id, ROUND(SUM(total) / SUM(units), 2) average_price
FROM(
SELECT 
    u.product_id, u.purchase_date, u.units, (p.price * u.units) total
FROM (SELECT DISTINCT * FROM unitsSold) u
INNER JOIN  
    prices p
ON u.product_id = p.product_id 
AND u.purchase_date >= p.start_date 
AND u.purchase_date <= p.end_date) a
GROUP BY a.product_id;

mysql 解法, 执行用时: 544 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 11:46:48 

# Write your MySQL query statement below
SELECT
    product_id,
    Round(SUM(sales) / SUM(units), 2) AS average_price
FROM (
    SELECT
        Prices.product_id AS product_id,
        Prices.price * UnitsSold.units AS sales,
        UnitsSold.units AS units
    FROM Prices 
    JOIN UnitsSold ON Prices.product_id = UnitsSold.product_id
    WHERE UnitsSold.purchase_date BETWEEN Prices.start_date AND Prices.end_date
) T
GROUP BY product_id

上一题

© 2024 leetcode.cn