9/17 每日一題(MYSQL 創建一個新表格將store1,store2,store3併成store,並將原本的store代的值帶入到price)

Table: Products

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| store1      | int     |
| store2      | int     |
| store3      | int     |
+-------------+---------+
product_id is the primary key (column with unique values) for this table.
Each row in this table indicates the product's price in 3 different stores: store1, store2, and store3.
If the product is not available in a store, the price will be null in that store's column.

 

Write a solution to rearrange the Products table so that each row has (product_id, store, price). If a product is not available in a store, do not include a row with that product_id and store combination in the result table.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Products table:
+------------+--------+--------+--------+
| product_id | store1 | store2 | store3 |
+------------+--------+--------+--------+
| 0          | 95     | 100    | 105    |
| 1          | 70     | null   | 80     |
+------------+--------+--------+--------+
Output: 
+------------+--------+-------+
| product_id | store  | price |
+------------+--------+-------+
| 0          | store1 | 95    |
| 0          | store2 | 100   |
| 0          | store3 | 105   |
| 1          | store1 | 70    |
| 1          | store3 | 80    |
+------------+--------+-------+
Explanation: 
Product 0 is available in all three stores with prices 95, 100, and 105 respectively. 

Product 1 is available in store1 with price 70 and store3 with price 80. The product is not available in store2. 

# Write your MySQL query statement below

select product_id ,'store1' AS store, store1 AS price

from Products

where store1 is not null

union

select product_id ,'store2' AS store, store2 AS price

from Products

where store2 is not null

union

select product_id ,'store3' AS store, store3 AS price

from Products

where store3 is not null

#選擇'store1' 作為 store的值,store1本身改名為price,篩選條件設定在store1不為空時

#選擇'store2' 作為 store的值,store2本身改名為price,篩選條件設定在store2不為空時

#選擇'store3' 作為 store的值,store3本身改名為price,篩選條件設定在store3不為空時

#最後使用union 連接三個部份的結果

--------------------------------pandas

import pandas as pd

def rearrange_products_table(products: pd.DataFrame) -> pd.DataFrame:

    return pd.melt(

        products,id_vars='product_id', var_name='store', value_name='price'

        ).dropna()

   

   

    # pd.melt() 用來將寬格式轉換成長格式的函式

    #id_vars='product_id' 表示這列不進行轉換,保留在長格式中

    #var_name = 'store' 表示轉換後的新列名稱為'store'

    #value_name = 'price' 表示轉換後新值的 列名稱為'price'

Input

Products =

| product_id | store1 | store2 | store3 | | ---------- | ------ | ------ | ------ | | 0 | 95 | 100 | 105 | | 1 | 70 | null | 80 |

Output

| product_id | store | price | | ---------- | ------ | ----- | | 0 | store1 | 95 | | 1 | store1 | 70 | | 0 | store2 | 100 | | 0 | store3 | 105 | | 1 | store3 | 80 |