SQL笔记（三） - LC1795 / 透视表

发表于 | 阅读次数：

Question

表：Products 

1
2
3
4
5
6
7
8
9
10
11
+-------------+---------+
| 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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
输入：
+------------+--------+--------+--------+
| 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    |
+------------+--------+-------+

Solution

这是一个典型的行转列题目，在 pandas 中可以很方便地通过函数实现，但是 SQL 似乎没有提供这类函数，直接就卡住了，完全无从下手。看了题解发现 SQL 的行转列居然是通过分别 SELECT 每一列再 UNION 起来实现的：

1
2
3
4
5
6
7
8
9
10
11
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

在列数较多的情况下，这种写法感觉还是挺麻烦的。

Keypoint

如果将题目的要求倒过来，改为列转行，可以用 GROUP BY 配合 IF 函数实现：

1
2
3
4
5
6
SELECT product_id, 
       SUM(IF(store='store1', price, NULL)) AS store1, 
       SUM(IF(store='store2', price, NULL)) AS store2, 
       SUM(IF(store='store3', price, NULL)) AS store3
FROM Products 
GROUP BY product_id