SQL表格形式转化问题技巧汇总

表格转化

表格转化大体上可以分分为两类，一种是原始表格的转化问题，另一种是设计序列构建新表格的问题。而在原始表格转化问题中，又可以分成行转列和列转行；在设计序列构建新表格的问题中，又可以分成数列构建和字符串构建。

原始表格转化

1. 行转列

第一种情况：原表格有基准id

group by + sum / max / min + case / if / where

eg.1777.每家商店的产品价格（简单）

表：Products

Column Name Type

product_id int

store enum

price int

(product_id,store) 是这个表的主键。store 字段是枚举类型，它的取值为以下三种 ('store1', 'store2', 'store3') 。price 是该商品在这家商店中的价格。写出一个 SQL 查询语句，查找每种产品在各个商店中的价格。可以以任何顺序输出结果。

查询结果格式如下例所示：

Products 表：

product_id store price

0 store1 95

0 store3 105

0 store2 100

1 store1 70

1 store3 80

Result 表：

product_id store1 store2 store3

0 95 100 105

1 70 null 80

产品 0 的价格在商店 1 为 95 ，商店 2 为 100 ，商店 3 为 105 。产品 1 的价格在商店 1 为 70 ，商店 3 的产品 1 价格为 80 ，但在商店 2 中没有销售。

Column Name	Type
product_id	int
store	enum
price	int

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

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

题解：

select
    product_id, 
    max(if(store='store1', price, NULL)) as store1,
    max(if(store='store2', price, NULL)) as store2,
    max(if(store='store3', price, NULL)) as store3
from Products
group by product_id

首先group by进行分组，然后通过if过滤分别计算不同指标

第二种情况：原表格没有基准id，需要我们自己构造

row_number() + group by + sum / max / min + case / if / where

eg.618.学生地理信息报告

一所美国大学有来自亚洲、欧洲和美洲的学生，他们的地理信息存放在如下 student 表中。

name continent

Jack America

Pascal Europe

Xi Asia

Jane America

写一个查询语句实现对大洲（continent）列的透视表操作，使得每个学生按照姓名的字母顺序依次排列在对应的大洲下面。输出的标题应依次为美洲（America）、亚洲（Asia）和欧洲（Europe）。

对于样例输入，它的对应输出是：

America Asia Europe

Jack Xi Pascal

Jane

name	continent
Jack	America
Pascal	Europe
Xi	Asia
Jane	America

America	Asia	Europe
Jack	Xi	Pascal
Jane

题解：

select
    max(if(continent='America', name, NULL)) as America,
    max(if(continent='Asia', name, NULL)) as Asia,
    max(if(continent='Europe', name, NULL)) as Europe
from(
    select # 按照每个州划分，每个州按照name进行排序
        name, 
        continent,
        row_number() over(partition by continent order by name) as rk
    from student
) as temp
group by rk

首先构造基准id，在这里是rk
之后与情况一类似

总结

观察题中是否有基准id,若题中无基准id，需要先使用row_number()进行构造
使用的聚合函数sum/max/min是根据列中的数据格式决定的

2. 列转行

列转行通用技巧是使用union all

eg.1435.制作会话柱状图

表：Sessions | Column Name | Type | | ----------- | ---- | | session_id | int | | duration | int |

session_id 是该表主键，duration 是用户访问应用的时间, 以秒为单位

你想知道用户在你的 app 上的访问时长情况。因此决定统计访问时长区间分别为 "[0-5>", "[5-10>", "[10-15>" 和 "15 or more" （单位：分钟）的会话数量，并以此绘制柱状图。写一个SQL查询来报告（访问时长区间，会话总数）。结果可用任何顺序呈现。

下方为查询的输出格式：

Sessions 表： | session_id | duration | | ---------- | -------- | | 1 | 30 | | 2 | 199 | | 3 | 299 | | 4 | 580 | | 5 | 1000 |

Result 表： | bin | total | | ---- | ----- | | [0-5> | 3 | | [5-10> | 1 | | [10-15> | 0 | | 15 or more | 1 |

对于 session_id 1，2 和 3 ，它们的访问时间大于等于 0 分钟且小于 5 分钟。

对于 session_id 4，它的访问时间大于等于 5 分钟且小于 10 分钟.

没有会话的访问时间大于等于 10 分钟且小于 15 分钟。

对于 session_id 5, 它的访问时间大于等于 15 分钟。

题解：

select
    '[0-5>' as bin,
    sum(if(duration >=0 and duration <300, 1, 0)) as total
from Sessions
union all
select
    '[5-10>' as bin,
    sum(if(duration >=300 and duration <600, 1, 0)) as total
from Sessions
union all
select
    '[10-15>' as bin,
    sum(if(duration >=600 and duration <900, 1, 0)) as total
from Sessions
union all
select
    '15 or more' as bin,
    sum(if(duration >=900, 1, 0)) as total
from Sessions

利用union all完成不同情况下行的拼接

设计序列构建新表格

1. 数列构建

result中出现了一个无中生有的列，且列中的数据为一个逐渐递增的数列，考虑使用with的递归方法构建

with recursive + left join

eg.1336.每次访问的交易次数

表: Visits | Column Name | Type | | ----------- | ---- | | user_id | int | | visit_date | date |

(user_id, visit_date) 是该表的主键，该表的每行表示 user_id 在 visit_date 访问了银行

表: Transactions | Column Name | Type | | ----------- | ---- | | user_id | int | | transaction_date | date | | amount | int |

该表没有主键，所以可能有重复行。该表的每一行表示 user_id 在 transaction_date 完成了一笔 amount 数额的交易，可以保证用户 (user) 在 transaction_date 访问了银行 (也就是说 Visits 表包含 (user_id, transaction_date) 行)，银行想要得到银行客户在一次访问时的交易次数和相应的在一次访问时该交易次数的客户数量的图表。

写一条 SQL 查询多少客户访问了银行但没有进行任何交易，多少客户访问了银行进行了一次交易等等

结果包含两列：

transactions_count：客户在一次访问中的交易次数

visits_count：在 transactions_count 交易次数下相应的一次访问时的客户数量transactions_count 的值从 0 到所有用户一次访问中的max(transactions_count) 按 transactions_count 排序

下面是查询结果格式的例子：

Visits 表: | user_id | visit_date | | ------- | ---------- | | 1 | 2020-01-01 | | 2 | 2020-01-02 | | 12 | 2020-01-01 | | 19 | 2020-01-03 | | 1 | 2020-01-02 | | 2 | 2020-01-03 | | 1 | 2020-01-04 | | 7 | 2020-01-11 | | 9 | 2020-01-25 | | 8 | 2020-01-28 |

Transactions 表: | user_id | transaction_date | amount | | ------- | ---------------- | ------ | | 1 | 2020-01-02 | 120 | | 2 | 2020-01-03 | 22 | | 7 | 2020-01-11 | 232 | | 1 | 2020-01-04 | 7 | | 9 | 2020-01-25 | 33 | | 9 | 2020-01-25 | 66 | | 8 | 2020-01-28 | 1 | | 9 | 2020-01-25 | 99 |

结果表: | transactions_count | visits_count | | ------------------ | ------------ | | 0 | 4 | | 1 | 5 | | 2 | 0 | | 3 | 1 |

对于 transactions_count = 0, visits 中 (1, "2020-01-01"), (2, "2020-01-02"), (12, "2020-01-01") 和 (19, "2020-01-03") 没有进行交易，所以 visits_count = 4 。

对于 transactions_count = 1, visits 中 (2, "2020-01-03"), (7, "2020-01-11"), (8, "2020-01-28"), (1, "2020-01-02") 和 (1, "2020-01-04") 进行了一次交易，所以 visits_count = 5 。

对于 transactions_count = 2, 没有客户访问银行进行了两次交易，所以 visits_count = 0 。

对于 transactions_count = 3, visits 中 (9, "2020-01-25") 进行了三次交易，所以 visits_count = 1 。

对于 transactions_count >= 4, 没有客户访问银行进行了超过3次交易，所以我们停止在 transactions_count = 3 。

题解：

with recursive temp(transactions_count) as
(
    select 0
    union all
    select transactions_count+1 
    from temp
    where transactions_count<(
        select max(num) as max_num
        from(
            select
                count(*) over(partition by transaction_date, user_id) as num
            from transactions
        ) as temp
    )
) # 利用with recursive构造出结果表中的第一列
# 后续使用temp表进行left连接
select t.transactions_count, count(t1.id) as visits_count
from temp as t
left join (
    select v.user_id as id, v.visit_date, count(t.amount) as times
    from Visits as v
    left join Transactions as t
    on v.user_id = t.user_id and v.visit_date = t.transaction_date
    group by v.user_id, v.visit_date
) as t1
on t.transactions_count = t1.times
group by t.transactions_count

2. 字符串构建

result中出现了一个无中生有的列，且列中的数据为未曾出现过的字符串，考虑使用union all方法直接构造

eg.1127.用户购买平台

支出表: Spending | Column Name | Type | | ----------- | ---- | | user_id | int | | spend_date | date | | platform | enum | | amount | int |

这张表记录了用户在一个在线购物网站的支出历史，该在线购物平台同时拥有桌面端（'desktop'）和手机端（'mobile'）的应用程序。

这张表的主键是 (user_id, spend_date, platform)。平台列 platform 是一种 ENUM ，类型为（'desktop', 'mobile'）。

写一段 SQL 来查找每天仅使用手机端用户、仅使用桌面端用户和同时使用桌面端和手机端的用户人数和总支出金额。

查询结果格式如下例所示：

Spending table: | user_id | spend_date | platform | amount | | ------- | ---------- | -------- | ------ | | 1 | 2019-07-01 | mobile | 100 | | 1 | 2019-07-01 | desktop | 100 | | 2 | 2019-07-01 | mobile | 100 | | 2 | 2019-07-02 | mobile | 100 | | 3 | 2019-07-01 | desktop | 100 | | 3 | 2019-07-02 | desktop | 100 |

Result table: | spend_date | platform | total_amount | total_users | | ---------- | -------- | ------------ | ----------- | | 2019-07-01 | desktop | 100 | 1 | | 2019-07-01 | mobile | 100 | 1 | | 2019-07-01 | both | 200 | 1 | | 2019-07-02 | desktop | 100 | 1 | | 2019-07-02 | mobile | 100 | 1 | | 2019-07-02 | both | 0 | 0 |

在 2019-07-01, 用户1 同时使用桌面端和手机端购买, 用户2 仅使用了手机端购买，而用户3 仅使用了桌面端购买。

在 2019-07-02, 用户2 仅使用了手机端购买, 用户3 仅使用了桌面端购买，且没有用户同时使用桌面端和手机端购买。

题解：

select
    temp2.spend_date,
    temp1.platform,
    sum(if(temp1.platform = temp2.platform, amount, 0)) as total_amount,
    count(if(temp1.platform = temp2.platform, 1, null)) as total_users
from (
    select 'mobile' as platform
    union
    select 'desktop' as platform
    union
    select 'both' as platform
) as temp1 # 字符串构建
join (
    select
        user_id, spend_date,
        any_value(if(count(platform) = 2, 'both', platform)) as platform,
        sum(amount) as amount
    from Spending
    group by user_id, spend_date
) as temp2
group by temp2.spend_date, temp1.platform

with的递归查询

with recursive语法

with recursive temp(n) as
(
    select 0 #初始条件
    union all
    select n+1 # 每次递归的操作
    from temp # 调用递归
    where n<7 # 递归停止条件
)

参考文章

pipideveloper-总结各类表格格式化问题-leetcode

数据库 > SQL

#SQL #LeetCode #数据库

SQL表格形式转化问题技巧汇总

http://example.com/2022/03/10/SQL表格形式转化问题技巧汇总/

作者

EverNorif

发布于

2022年3月10日

许可协议

SQL表格形式转化例题上一篇

Hadoop学习笔记-Yarn(2)-Yarn命令行操作与参数配置下一篇