你的位置:首页 > 信息动态 > 新闻中心
信息动态
联系我们

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

2021-12-5 18:47:52

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

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,...;