每个产品在不同商店的价格

1795. 每个产品在不同商店的价格

表：Products

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| store1      | int     |
| store2      | int     |
| store3      | int     |
+-------------+---------+
这张表的主键是product_id（产品Id）。
每行存储了这一产品在不同商店store1, store2, store3的价格。
如果这一产品在商店里没有出售，则值将为null。

请你重构 Products 表，查询每个产品在不同商店的价格，使得输出的格式变为(product_id, store, price) 。如果这一产品在商店里没有出售，则不输出这一行。

输出结果表中的 顺序不作要求 。

查询输出格式请参考下面示例。

示例 1：

输入：
Products table:
+------------+--------+--------+--------+
| product_id | store1 | store2 | store3 |
+------------+--------+--------+--------+
| 0          | 95     | 100    | 105    |
| 1          | 70     | null   | 80     |
+------------+--------+--------+--------+
输出：
+------------+--------+-------+
| product_id | store  | price |
+------------+--------+-------+
| 0          | store1 | 95    |
| 0          | store2 | 100   |
| 0          | store3 | 105   |
| 1          | store1 | 70    |
| 1          | store3 | 80    |
+------------+--------+-------+
解释：
产品0在store1，store2,store3的价格分别为95,100,105。
产品1在store1，store3的价格分别为70,80。在store2无法买到。

原站题解

去查看

上次编辑到这里，代码来自缓存点击恢复默认模板

# Write your MySQL query statement below

pythondata 解法, 执行用时: 412 ms, 内存消耗: 60.2 MB, 提交时间: 2023-08-09 17:17:46

import pandas as pd

def rearrange_products_table(products: pd.DataFrame) -> pd.DataFrame:
    store_columns = ['store1', 'store2', 'store3']
    result = pd.DataFrame(columns=['product_id', 'store', 'price'])
    
    for col in store_columns:
        temp_df = products[['product_id', col]].copy()
        temp_df.rename(columns={col: 'price',}, inplace=True)
        temp_df['store'] = col
        temp_df.dropna(subset=['price'], inplace=True)
        result = result._append(temp_df)
    
    result.reset_index(drop=True, inplace=True)
    return result

mysql 解法, 执行用时: 537 ms, 内存消耗: 0 B, 提交时间: 2022-06-01 10:12:13

# Write your MySQL query statement below
select product_id, 'store1' as store, store1 as price
from Products where store1 is not null
union all
select product_id, 'store2' as store, store2 as price
from Products where store2 is not null
union all
select product_id, 'store3' as store, store3 as price
from Products where store3 is not null;

mysql 解法, 执行用时: 536 ms, 内存消耗: 0 B, 提交时间: 2022-05-26 17:40:06

# Write your MySQL query statement below
select product_id, 'store1' as store, store1 as price
from Products where store1 is not null
union all
select product_id, 'store2' as store, store2 as price
from Products where store2 is not null
union all
select product_id, 'store3' as store, store3 as price
from Products where store3 is not null;

上一题

下一题

详情