本文总结了牛客SQL刷题：
非技术快速入门
的进阶知识，阅读本文可以轻松解决这个系列下的难题。
substring_index()
可以按照给定的分隔符对字符串进行切割。其语法为：
substring_index(str, delim, count)
参数如下：
str：需要拆分的字符串delim：分隔符，通过某字符进行拆分count：当 count为正数，取第n个分隔符之前的所有字符； 当count为负数，取倒数第n个分隔符之后的所有字符。
mysql> select substring_index('www.wikidm.cn', '.', 1);
+
| substring_index('www.wikidm.cn', '.', 1) |
+
| www |
+
1 row in set (0.00 sec)
mysql> select substring_index('www.wikidm.cn', '.', 2);
+
| substring_index('www.wikidm.cn', '.', 2) |
+
| www.wikidm |
+
1 row in set (0.00 sec)
mysql> select substring_index('www.wikidm.cn', '.', 3);
+
| substring_index('www.wikidm.cn', '.', 3) |
+
| www.wikidm.cn |
+
1 row in set (0.00 sec)
若是count为负呢？看下面：
mysql> select substring_index('www.wikidm.cn', '.', -1);
+
| substring_index('www.wikidm.cn', '.', -1) |
+
| cn |
+
1 row in set (0.00 sec)
mysql> select substring_index('www.wikidm.cn', '.', -2);
+
| substring_index('www.wikidm.cn', '.', -2) |
+
| wikidm.cn |
+
1 row in set (0.00 sec)
mysql> select substring_index('www.wikidm.cn', '.', -3);
+
| substring_index('www.wikidm.cn', '.', -3) |
+
| www.wikidm.cn |
+
1 row in set (0.00 sec)
也就是说，如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容；相反，如果是负数，那么就是从右边开始数，第N个分隔符右边的所有内容。
如果要中间的的wikidm怎么办？很简单，从左数第二个分隔符的左边全部，再从右数的第一个分隔符的右边即可。
mysql> select substring_index(substring_index('www.wikidm.cn', '.', 2),'.', -1);
+
| substring_index(substring_index('www.wikidm.cn', '.', 2),'.', -1) |
+
| wikidm |
+
1 row in set (0.00 sec)
现在来一个复杂一点的例子，也就是题目：SQL30 统计每种性别的人数。
题目：现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，现在运营想要统计每个性别的用户分别有多少参赛者，请取出相应结果。
方法一：使用substring_index把性别取出来就行了。
select
substring_index(profile, ',', -1) as gender,
count(*) as number
user_submit
group by
gender;
方法二：模糊匹配(like)
select
when profile like '%female' then 'female'
when profile like '%male' then 'male'
end as gender,
count(*) as number
user_submit
group by
gender;
从左边开始截取字符串，其语法为：
left(str, len)
参数说明：
str：被截取的字符串len：正整数，表示截取字符串从最左边开始到第len位的值。
mysql> select left('4794,3536,3565,7854', 5);
+
| left('4794,3536,3565,7854', 5) |
+
| 4794, |
+
1 row in set (0.00 sec)
right()与left()一样，只是从右边开始截取字符串。例如：
mysql> select right('4794,3536,3565,7854', 5);
+
| right('4794,3536,3565,7854', 5) |
+
| ,7854 |
+
1 row in set (0.00 sec)
语法格式：
substring(str, pos, len)
参数说明：
str：被截取的字符串pos：从第几位开始截取，当pos为正数时，表示从字符串开始第pos位开始取，直到结束；当pos为负数时，表示从字符串倒数第pos位开始取，直到结束。len：表示取几个字符。
例子：从第3个字符开始获取字符串：
mysql> select substring('4794,3536,3565,7854', 3);
+
| substring('4794,3536,3565,7854', 3) |
+
| 94,3536,3565,7854 |
+
1 row in set (0.00 sec)
从倒数第2个字符开始获取字符串：
mysql> select substring('4794,3536,3565,7854', -2);
+
| substring('4794,3536,3565,7854', -2) |
+
| 54 |
+
1 row in set (0.00 sec)
从字符串的第 4 个字符位置开始取，只取 2 个字符。
mysql> select substring('4794,3536,3565,7854', 4, 2);
+
| substring('4794,3536,3565,7854', 4, 2) |
+
| 4, |
+
1 row in set (0.00 sec)
从字符串的倒数第 4 个字符位置开始取，只取 2 个字符。
mysql> select substring('4794,3536,3565,7854', -4, 2);
+
| substring('4794,3536,3565,7854', -4, 2) |
+
| 78 |
+
1 row in set (0.00 sec)
注意：在函数substring()中， pos可以是负值，但len不能取负值。
替换字符串，其语法为：
replace(str, from_str, to_str)
参数说明：
str：需要进行替换的字符串from_str：需要被替换的字符串to_str：需要替换的字符串
例子：将分隔符",“逗号替换为”"。
mysql> select replace('4794,3536,3565,7854', ',', '');
+
| replace('4794,3536,3565,7854', ',', '') |
+
| 4794353635657854 |
+
1 row in set (0.00 sec)
删除字符串中的某个字段，其语法为：
trim('被删除字段' from 列名)
举个例子，下面代码来自：SQL31 提取博客URL中的用户名。原来的数据为：
mysql> select * from user_submit;
+
| id | device_id | profile | blog_url |
+
| 1 | 2138 | 180cm,75kg,27,male | http:/url/bisdgboy777 |
| 1 | 3214 | 165cm,45kg,26,female | http:/url/dkittycc |
| 1 | 6543 | 178cm,65kg,25,male | http:/url/tigaer |
| 1 | 4321 | 171cm,55kg,23,female | http:/url/uhsksd |
| 1 | 2131 | 168cm,45kg,22,female | http:/url/sysdney |
+
5 rows in set (0.00 sec)
下面通过删除每个链接中的公共部分来提取用户名：
mysql> select trim('http:/url/' from blog_url) from user_submit;
+
| trim('http:/url/' from blog_url) |
+
| bisdgboy777 |
| dkittycc |
| tigaer |
| uhsksd |
| sysdney |
+
5 rows in set (0.00 sec)
获取字符串的长度。其语法为：
length(str)
参数说明：
mysql> select length('4794,3536,3565,7854');
+
| length('4794,3536,3565,7854') |
+
| 19 |
+
1 row in set (0.00 sec)
反转字符串，其语法为：
reverse(str)
mysql> select reverse('12345');
+
| reverse('12345') |
+
| 54321 |
+
1 row in set (0.00 sec)
返回子串substr在字符串 str中第一次出现的位置，如果字符substr在字符串str中不存在，则返回0；其语法为：
locate(substr, str)
mysql> select locate('2', '385295502');
+
| locate('2', '385295502') |
+
| 4 |
+
1 row in set (0.00 sec)
窗口函数的作用：在日常工作中，经常会遇到需要在每组内排名，比如下面的业务需求：
- 排名问题：每个部门按业绩来排名
- topN问题：找出每个部门排名前N的员工进行奖励
面对这类需求，就需要使用sql的高级功能窗口函数。
窗口函数也称为OLAP函数，意思是对数据库数据进行实时分析处理。窗口函数就是为了实现OLAP而添加的标准SQL功能。
窗口函数语法：其中[]中的内容可以省略
<窗口函数> over ([partition by <列清单>]
order by <排序用列清单>)
简单来说，窗口函数有以下功能：
- 同时具有分组和排序的功能
- 不减少原表的行数
针对第二点，可以解释为啥有了group by和order by还需要用窗口函数。原因在于group by分组汇总后改变了表的行数，一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。例如下面统计每个班级的人数。
窗口函数大体可以分为以下两种：
- 能够作为窗口函数的聚合函数（sum，avg，count，max，min）
- rank，dense_rank，row_number等专用窗口函数。
下面简单介绍一下这些函数的基本使用方法。
专用窗口函数主要有rank，dense_rank，row_number。下面先来讲讲rank函数。
rank函数是用来计算记录排序的函数。
mysql> select * from user_profile;
+
| id | device_id | gender | age | university | gpa | active_days_within_30 | question_cnt | answer_cnt |
+
| 1 | 2138 | male | 21 | 北京大学 | 3.4 | 7 | 2 | 12 |
| 2 | 3214 | male | NULL | 复旦大学 | 4 | 15 | 5 | 25 |
| 3 | 6543 | female | 20 | 北京大学 | 3.2 | 12 | 3 | 30 |
| 4 | 2315 | female | 23 | 浙江大学 | 3.6 | 5 | 1 | 2 |
| 5 | 5432 | male | 25 | 山东大学 | 3.8 | 20 | 15 | 70 |
| 6 | 2131 | male | 28 | 山东大学 | 3.3 | 15 | 7 | 13 |
| 7 | 4321 | male | 28 | 复旦大学 | 3.6 | 9 | 6 | 52 |
+
7 rows in set (0.00 sec)
mysql> select device_id, university, gpa, rank() over (partition by university order by gpa) as ranking from user_profile;
+
| device_id | university | gpa | ranking |
+
| 6543 | 北京大学 | 3.2 | 1 |
| 2138 | 北京大学 | 3.4 | 2 |
| 4321 | 复旦大学 | 3.6 | 1 |
| 3214 | 复旦大学 | 4 | 2 |
| 2131 | 山东大学 | 3.3 | 1 |
| 5432 | 山东大学 | 3.8 | 2 |
| 2315 | 浙江大学 | 3.6 | 1 |
+
7 rows in set (0.00 sec)
参数解释：
partition by能够设定排序的对象范围，类似于group by语句，这里就是以university划分排序范围。order by能够指定哪一列，何种顺序进行排序。也可以通过asc，desc来指定升序降序。
窗口函数兼具分组和排序两种功能。通过partition by分组后的记录集合称为窗口。
要注意的是partition by不是窗口函数所必须的，比如：
mysql> select device_id, university, gpa, rank() over (order by gpa) as ranking from user_profile;
+
| device_id | university | gpa | ranking |
+
| 6543 | 北京大学 | 3.2 | 1 |
| 2131 | 山东大学 | 3.3 | 2 |
| 2138 | 北京大学 | 3.4 | 3 |
| 2315 | 浙江大学 | 3.6 | 4 |
| 4321 | 复旦大学 | 3.6 | 4 |
| 5432 | 山东大学 | 3.8 | 6 |
| 3214 | 复旦大学 | 4 | 7 |
+
7 rows in set (0.00 sec)
如果省略了partition by，没有进行范围的划分，就会直接对全部的数据进行排序。
但如果这样用的话就失去了窗口函数的功能，所以一般不要这么使用。
专用窗口函数rank, dense_rank, row_number有什么区别呢？
select *,
rank() over (order by 成绩 desc) as ranking,
dense_rank() over (order by 成绩 desc) as dese_rank,
row_number() over (order by 成绩 desc) as row_num
from 班级表
得到如下结果：
从上面的结果可以看出：
rank函数：这个例子中是5位，5位，5位，8位，也就是如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4。dense_rank函数：这个例子中是5位，5位，5位，6位，也就是如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2。row_number函数：这个例子中是5位，6位，7位，8位，也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。
这三个函数的区别如下：
注意：在上述的这三个专用窗口函数中，函数后面的括号不需要任何参数，保持()空着就可以。
聚和窗口函数和上面的专用窗口函数用法完全相同，只需要把聚合函数写在窗口函数的位置即可，但是函数后面括号里面不能为空，需要指定聚合的列名。
还是用上面的数据，来看看下面的结果：
mysql> select device_id, university, gpa, sum(gpa) over (order by device_id) as sum_gpa, max(gpa) over (order by device_id) as max_gpa from user_profile;
+
| device_id | university | gpa | sum_gpa | max_gpa |
+
| 2131 | 山东大学 | 3.3 | 3.299999952316284 | 3.299999952316284 |
| 2138 | 北京大学 | 3.4 | 6.700000047683716 | 3.4000000953674316 |
| 2315 | 浙江大学 | 3.6 | 10.299999952316284 | 3.5999999046325684 |
| 3214 | 复旦大学 | 4 | 14.299999952316284 | 4 |
| 4321 | 复旦大学 | 3.6 | 17.899999856948853 | 4 |
| 5432 | 山东大学 | 3.8 | 21.699999809265137 | 4 |
| 6543 | 北京大学 | 3.2 | 24.899999856948853 | 4 |
+
7 rows in set (0.00 sec)
聚合函数sum在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果。比如device_id为3214的记录，在使用sum窗口函数后的结果，是对2131、2138、2315和3214的gpa求和，若是6543，则结果是所有成绩的求和，以此类推。
不仅是sum求和，平均、计数、最大最小值，也是同理，都是针对自身记录、以及自身记录之上的所有数据进行计算。如果想要知道所有人成绩的总和、平均等聚合结果，看最后一行即可。
聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响。
数据库中num字段值为：'4794,3536,3565,7854'，现在需要将一行数据变成多行，也就是下面的样子：
+
| num |
+
| 7654 |
| 7698 |
| 7782 |
| 7788 |
+
- 首先获取最后需被拆分成多少个字符串，利用 mysql 库的
help_topic表的help_topic_id来模拟遍历第n个字符串，因为help_topic_id是自增的，当然也可以用其他表的自增字段辅助。help_topic_id的使用见下面：
mysql> select help_topic_id from mysql.help_topic where help_topic_id < 5;
+
| help_topic_id |
+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
+
5 rows in set (0.00 sec)
- 通过如下的函数，求得
,的个数：
mysql> select length('7654,7698,7782,7788') - length(replace('7654,7698,7782,7788', ',', '')) as num;
+
| num |
+
| 3 |
+
1 row in set (0.00 sec)
也就是说，现在有三个逗号，那么就有四个字符串需要被分割。
- 根据逗号拆分字符串，此处利用
substring_index()函数提取每部分的数字，最后把结果赋值给num字段。
select substring_index(substring_index('7654,7698,7782,7788', ',', help_topic_id + 1), ',', -1) as num;
- 将第三步的的help_topic_id限制在要提取的字符串个数内，也就是第二步，就可以得到这道题的最终结果了。
mysql> select substring_index(substring_index('7654,7698,7782,7788', ',', help_topic_id + 1), ',', -1) as num from mysql.help_topic where help_topic_id < length('7654,7698,7782,7788') - length(replace('7654,7698,7782,7788', ',', '')) + 1;
+
| num |
+
| 7654 |
| 7698 |
| 7782 |
| 7788 |
+
4 rows in set (0.00 sec)
数据库中num字段值为：'123456'，现在需要将一行数据变成多行，也就是下面的样子：
+
| num |
+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+
这道题和上一道题其实是一个思路，既然现在没有分隔符了，那就一个一个数的取，使用left配合substring函数就可以做到，如下：
mysql> select left(substring('123456', help_topic_id + 1), 1) as num from mysql.help_topic where help_topic_id < length('123456');
+
| num |
+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+
6 rows in set (0.00 sec)
现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，现在运营想要统计每个年龄的用户分别有多少参赛者，请取出相应结果。
select
substring_index(substring_index(profile, ',', -2), ',', 1) as age,
count(*)
user_submit
group by
age;
现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa。
方法一：第一种方式是用group by把学校分组，然后计算得到每个学校最低gpa，再去找这个学校里和这个gpa相等的同学ID。
注意这样如果最低gpa对应多个同学，都会输出。
select
a.device_id,
a.university,
a.gpa
user_profile a
where
gpa in (
select
min(b.gpa)
user_profile b
where
a.university = b.university
order by
a.university;
方法二：用内连接将单表分为两个表，再将两个表进行连接进行查询。
select
a.device_id,
a.university,
a.gpa
user_profile a
join (
select
university,
min(gpa) as gpa
user_profile
group by
university
) as u
on a.university = u.university and a.gpa = u.gpa
order by a.university;
方法三：借助all函数。
select
a.device_id,
a.university,
a.gpa
user_profile a
where
gpa <= all(
select
b.gpa
user_profile b
where
a.university = b.university
order by
a.university;
方法四：窗口函数
select
device_id,
university,
select
row_number() over (partition by university order by gpa) as rn
user_profile
) as univ_min
where
rn = 1
order by
university;
现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据。
方法一：表里的数据可以看作是全部第一天来刷题了的，那么我们需要构造出第二天来了的字段，因此可以考虑用left join把第二天来了的拼起来，限定第二天来了的可以用date_add(date1, interval 1 day)=date2筛选，并用device_id限定是同一个用户。
可以count(date1)得到左表全部的date记录数作为分母，count(date2)得到右表关联上了的date记录数作为分子，相除即可得到平均概率。
select
count(date2) / count(date1) as avg_ret
select
distinct q.device_id,
q.date as date1,
p.date as date2
question_practice_detail as q
left join(
select
distinct device_id,
question_practice_detail
) as p
on q.device_id = p.device_id
and date_add(q.date, interval 1 day) = p.date
)as id_last_next_date;
方法二：用lead函数将同一用户连续两天的记录拼接起来。先按用户分组partition by device_id，再按日期升序排序order by date，再两两拼接（最后一个默认和null拼接），即lead(date) over (partition by device_id order by date)
检查date2和date1的日期差是不是为1，是则为1（次日留存了），否则为0（次日未留存），取avg即可得平均概率。
select
avg(if(datediff(date2, date1) = 1, 1, 0)) as avg_ret
select
distinct device_id,
date as date1,
lead(date) over (
partition by device_id
order by
) as date2
select
distinct device_id,
question_practice_detail
) as uniq_id_date
) as id_last_next_date;
MySql进阶内容，主要涉及字符串的切割、截取、删除和替换，以及窗口函数，给出了一些对应知识的题目。可以快速解决如下题目：SQL30 统计每种性别的人数，SQL31 提取博客URL中的用户名，字符串拆分（有分隔符），字符串拆分（无分隔符），SQL32 截取出年龄，SQL33 找出每个学校GPA最低的同学，SQL29 计算用户的平均次日留存率。
DECLARE @id VARCHAR(200)
DECLARE @idlist VARCHAR(500) = '1,2,3,5,6,8,9,10,11,12,14,15,16,17,18,19,20'
WHILE LEN(@idlist) > 0
BEGIN
IF CHARINDEX(',',@idlist) > 0
SET @id = SUBSTRING(@idlist,0,CHARINDEX(',',@idlist))
BEGIN
代码如下:eclare @str nvarchar(50);set @str=’462,464,2′;select @str as ‘字符串’select len(@str) as ‘字符长度’select charindex(‘,’,@str,1) as ‘第一个逗号的索引值’select LEFT(@str,charindex(‘,’,@str,1)-1) as ‘第一个值’select SUBSTRING(@str,charindex(‘,’,@str,1)+1,len(@str)) as ‘从第一逗号开始截取出后面的字符串’select LEFT(SUBSTRING(@str,char
SELECT
SingleName = CAST(LEFT(REPLACE(Name, ' ', ''),CHARINDEX(';',REPLACE(Name,' ', '') + ';')-1) AS NVARCHAR(MAX)) ,
Split = CAST(STUFF(REPLACE(Name, ' ', '') + ';',1,CHARINDEX(';',REPLACE(Name, ' '
str：需要拆分的字符串；
delim：分隔符，根据此字符来拆分字符串；
count：当 count 为正数，取第 n 个分隔符之前的所有字符； 当 count 为负数，取倒数第 n 个分隔符之后的所有字符
– 》str=’ 金融业企业-银行业存款类金融机构-国家开发银行及政策性银行’
–> 1 substring_index(str,’-’,1)
–>得到 金融业企业 由于是取第一个 - 之前的所有字符
牛客字符串的展开指的是将字符串中的重复分展开，并且去除展开后的复部分。例如，给定字符串"ab[c2[d]e]f", 展开后结果为"abcddceef"。
你可以使用栈来解决这个问题。遍历字符串，当遇字符和数字时，将它们分存入两个栈中。当到']'时，开始展开字符串从字符栈中弹出字符直到遇到'['为止，并将弹出的字符连接在一起。再从数字栈中弹出数字，表示需要展开的次数。将展开后的字符串再次入栈，重复这个过程直到遍历完整个字符串。
下面是一个示例代码：
```python
def expand_string(s):
char_stack = []
num_stack = []
for c in s:
if c.isalpha():
char_stack.append(c)
elif c.isdigit():
num_stack.append(int(c))
elif c == '[':
char_stack.append(c)
elif c == ']':
temp_str = ''
while char_stack[-1] != '[':
temp_str = char_stack.pop() + temp_str
char_stack.pop() # 弹出'['
repeat_times = num_stack.pop()
temp_str *= repeat_times
char_stack.append(temp_str)
return ''.join(char_stack)
# 示例测试
s = "ab2[c2[d]e]f"
result = expand_string(s)
print(result) # 输出: abcddceef
在上述示例中，我们使用两个栈 `char_stack` 和 `num_stack` 来存储字符和数字。通过遍历字符串并根据字符类型的不同进行相应的操作，最后将 `char_stack` 中的字符连接起来即可得到展开后的字符串。
subprocess.CalledProcessError_ Command ‘[‘where‘, ‘cl‘]‘ returned non-zero exit status 1.
【避坑】paddlepaddle-gpu安装报错：The GPU architecture in your current machine is Pascal, which is not
