目录
数据表示例数据分别如下:
data_learning.product_order(商品销量表):
data_learning.product(商品信息表):
data_learning.product_category(商品二级分类信息表):
01 行转列
1.1 使用CASE WHEN语句+聚合函数
问题:请查询女裙和儿童家具的销量,输出结果形式为女裙销量作为一列,儿童家具作为一列。
SQL:
SELECT
SUM(CASE WHEN c.category_name = '女裙' THEN a.sales_volume ELSE 0 END) AS 女裙销量
,SUM(CASE WHEN c.category_name = '儿童家具' THEN a.sales_volume ELSE 0 END) AS 儿童家具销量
FROM
data_learning.product_order a
LEFT JOIN
(
SELECT
*
FROM
data_learning.product_category
)c
on a.category_id = c.category_id
;
结果如下:
1.2 使用if函数+聚合函数
同样是上面那个问题,用第二种方式如何实现呢?
SQL:
SELECT
SUM(IF(c.category_name = '女裙',sales_volume,0 )) AS 女裙销量
,SUM(IF(c.category_name = '儿童家具',sales_volume,0)) AS 儿童家具销量
FROM
data_learning.product_order a
LEFT JOIN
(
SELECT
*
FROM
data_learning.product_category
)c
on a.category_id = c.category_id
;
结果如下:
在实际工作中,使用场景可能比这个复杂。
比如“请查询每个月女裙和儿童家具地销量,输出结果形式为女裙销量作为一列,儿童家具作为一列”。这个时候,可以结合GROUP BY语句去实现。
同时,聚合函数可能也不一定是SUM,有可能是MAX,这个都要根据具体的业务定义,数据表内容等去灵活运用。
02 列转行
2.1 使用 UNION ALL或UNION语句
我们将上面行转列的数据结果插入到一张临时表data_learning.tmp_test,然后用这个把这个表的结果做列转行操作,也就是女裙和儿童家具变成行。
step1 : 上面的查询结果存入临时表。(实际工作中不用,这里仅仅是想用上面的数据另建一张表作为操作示例)
CREATE TABLE data_learning.tmp_test AS
SELECT
SUM(IF(c.category_name = '女裙',sales_volume,0 )) AS 女裙销量
,SUM(IF(c.category_name = '儿童家具',sales_volume,0)) AS 儿童家具销量
FROM
data_learning.product_order a
LEFT JOIN
(
SELECT
*
FROM
data_learning.product_category
)c
on a.category_id = c.category_id
;
step2:做列转行操作
SQL:
SELECT
'女裙' AS 类别
,女裙销量 AS 销量
FROM
data_learning.tmp_test
UNION ALL
SELECT
'儿童家具' AS 类别
,儿童家具销量 AS 销量
FROM
data_learning.tmp_test
;
结果如下:
行转列与列转行是SQL中非常重要的数据转换技巧。通过掌握这些技巧,你可以更加灵活地处理和分析数据,提高数据处理效率。希望本文能对你有所帮助!