SQL窗口函数/插入数据的两种方式/正则表达式例题-2021年12月5日

正则表达式：判断字符串中有几个中文字符

import re

def getnum_of_cn(inputdata):
	'''计算字符串中 中文字符 数量'''
	chi = re.findall(r'[\u4E00-\u9FFF]', inputdata)
	return len(chi)
	
def test():
	n = getnum_of_cn('你好，lajfldkjaklda123')
	print(n)
	
if __name__ == '__main__':
	test()

\u4E00-\u9FFF
是unicode编码，一种全世界语言都包括的一种编码。
\u4e00-\u9fa5是用来判断是不是中文的一个条件。

sql全称是结构化查询语言,即Structured Query Language

SQL窗口函数

一、窗口函数的作用
日常工作中，经常需要组内排名
排名问题：按照业绩对每个部门排名
topN问题：找出每个部门排名前N的员工进行奖励
面对排名需求，就需要使用SQL的高级功能窗口函数了

二、什么是窗口函数
窗口函数也叫OLAP函数(Online Analytical Processing，在线分析处理），可以对数据库数据进行实时分析处理。

三、窗口函数的用法
窗口函数的基本语法：

<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>

<窗口函数>的位置可以放置：
1.专用窗口函数rank,dense_rank,row_number
2.聚合函数
sum,avg,count,max,min
窗口函数是对where或者group by子句处理后的结果
所以原则上，窗口函数只能写在select子句中
为什么要用窗口函数？
group by子句可以实现分组，order by子句可以实现排序，但是group by分组汇总后会改变表的行数，一行只有一个类别。
而partition by和rank函数不会减少原表中的行数。

rank,dense_rank,row_number的区别
row_number正常排序，1、2、3、…
dense_rank有并列名次的话，并列名次排名一致，下一个排名在当前排名情况下加1，1、1、2、…
rank有并列名次的话，并列名次一致，下一个排名在当前排名情况下加并列行数，1、1、2、…

注意：在三个专用窗口函数中，函数后面的括号不需要任何参数，rank(),dense_rank(),row_number()就可以

聚合函数作为窗口函数
和专用窗口函数不同的是函数后面的括号不能为空，需要指定聚合的列名
特别之处：聚合函数是对自身记录之上的所有数据进行计算，可以直观地看到截止到本行数据的情况

例题

题目1：找出每个学校GPA最低的同学
题解1:

select device_id,university,gpa
from (select *,
			row_number() over(partition by university order by gpa ) rn
 			from user_profile) a
 where a.rn=1

题目2:截取出年龄
题解2:

select SUBSTRING(profile,12,2) age,count(profile) number
from user_submit
group by age

题目3：
计算用户的平均次日留存率，运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。
题解3:
用户的平均次日留存率：第二天刷题情况/第一天刷题情况
即 去重的数据表中符合次日留存的条目数目/去重的数据表中的所有条目数目
限定条件：是用户第一天刷题后，第二天还会再来刷题。把表内数据当作第一天，次日是第二天。
去重：因为同一用户可能会多次来刷题
方法：
1.构造一个第二天刷题情况的临时表
限定第二天来了的可以用date_add(date1, interval 1 day)=date2筛选

select count(date2) / count(date1) as avg_ret
from (
    select
        distinct(qpd.device_id),
        qpd.date as date1,
        uniq_id_date.date as date2
    from question_practice_detail as qpd
    left join(
        select distinct device_id, date
        from question_practice_detail
    ) as uniq_id_date
    on qpd.device_id=uniq_id_date.device_id
        and date_add(qpd.date, interval 1 day)=uniq_id_date.date
) as id_last_next_date

2.用窗口函数 lead()

lead()可以从同一结果集中的当前行访问后续行的数据。
与LAG()函数类似，LEAD()函数对于计算同一结果集中当前行和后续行之间的差异非常有用。
LEAD()函数的语法：

LEAD(<expression>[,offset[, default_value]]) OVER (
    PARTITION BY (expr)
    ORDER BY (expr)
)

offset:是从当前行向前行的行数。
offset必须是一个非负整数。如果offset为零，则LEAD()函数计算expression当前行的值。
如果省略offset，则LEAD()函数默认使用一个，offset=1。
default_value:如果没有后续行，则LEAD()函数返回default_value。
例如，如果offset是1，则最后一行的返回值为default_value。
如果您未指定default_value，则函数返回NULL。

PARTITION BY子句
PARTITION BY子句将结果集中的行划分LEAD()为应用函数的分区。
如果PARTITION BY未指定子句，则结果集中的所有行都将被视为单个分区。

ORDER BY子句
ORDER BY子句确定LEAD()应用函数之前分区中行的顺序。

select avg(if(datediff(date2, date1)=1, 1, 0)) as avg_ret
from (
    select
        distinct(device_id),
        date as date1,
        lead(date) over (partition by device_id order by date) as date2
    from (
        select distinct(device_id), date
        from question_practice_detail
    ) as uniq_id_date
) as id_last_next_date

字符串截取函数

1、left（name,4）截取左边的4个字符

SELECT LEFT(201809,4) 年
结果：2018

2、right（name,2）截取右边的2个字符

SELECT RIGHT(201809,2) 月份
结果：09

3、SUBSTRING(name,5,3) 截取name这个字段，从第五个字符开始，只截取之后的3个字符

SELECT SUBSTRING('成都融资事业部',5,3)
结果：事业部

4、SUBSTRING(name,3) 截取name这个字段从第三个字符开始，之后的所有个字符

SELECT SUBSTRING('成都融资事业部',3)
结果：融资事业部

5、SUBSTRING(name, -4) 截取name这个字段的倒数第 4 个字符位置开始取，直到结束

SELECT SUBSTRING('成都融资事业部',-4)
结果：资事业部

6、SUBSTRING(name, -4，2) 截取name这个字段的倒数第 4 个字符位置开始取，只截取之后的2个字符

SELECT SUBSTRING('成都融资事业部',-4,2)
结果：资事

注意：我们注意到在函数 substring(str,pos, len)中， pos 可以是负值，但 len 不能取负值。

7、substring_index(‘www.baidu.com’, ‘.’, 2) 截取第二个 ‘.’ 之前的所有字符

SELECT substring_index('www.baidu.com', '.', 2)
结果：www.baidu

8、substring_index(‘www.baidu.com’, ‘.’, -2) 截取倒数第二个 ‘.’ 之后的所有字符

SELECT substring_index('www.baidu.com', '.', -2)
结果：baidu.com

substring_index效率更高
9、SUBSTR(name, 1, CHAR_LENGTH(name)-3) 截取name字段，取除了name字段后三位的所有字符

SELECT SUBSTR('成都融资事业部', 1, CHAR_LENGTH('成都融资事业部')-3)
结果：成都融资

SQL数据操纵语言DML

插入：insert
修改：update
删除：delete

一、插入语句
插入哪个表，插入哪一列
方式一：

insert into 表名(列名1，列名2，...) values(值1，值2，...);

可以一次性插入多行

insert into 表名(列名1，列名2，...) values(值1，值2，...),values(值1，值2，...),values(值1，值2，...),...

支持子查询
将子查询结果插入表中

insert into 表名(列名1，列名2，...)
select '值1','值2',...;

1.插入的值的类型需要和列的类型一致或者兼容
2.不可以为null值的列必须插入值
可以为null值的列可以插入null值，不能直接为空，或者那一列的列名和值直接都不写
3.列的位置可以和原表中的位置不同，但是列和值的顺序要一一对应好
4.列的个数和值的个数必须一致
5.可以省略列名，默认是所有列，默认列的顺序和表的顺序一致

例：
insert into exam_record (uid,exam_id,start_time,submit_time,score)
values (1001,9001,'2021-09-01 22:11:12','2021-09-01 22:11:12'
+INTERVAL 50 minute,90),
(1002,9002,'2021-09-04 07:01:02',null,null)

方式二：

insert into 表名 set 列名1=值1,列名2=值2,...;