mysql其他常用操作技巧

# 导包
import numpy as np
import pandas as pd
import pymysql 
# 创建连接对象
mydb = pymysql.connect(  
                   host='localhost',          # 数据库服务器所在ip地址，如果是本机，就使用loacalhost, 或者127.0.0.1
                   user="root",               # 数据库用户名
                   password="1234"  ,         # 数据库密码
                   database=None,             # 可以指定连接某个数据库
                   port=3306,                 # 端口号, 默认是3306
                   charset='utf8'             # 使用的编码
                   )  
# 创建游标对象, 这里创建的游标是  可以返回带字段名的字典， 比较好用
mycursor = mydb.cursor(cursor=pymysql.cursors.DictCursor)

# #mycursor = mydb.cursor()
# # 直接这样写也可以， 但是这样查出来的数据没有列名
# # 还要通过mycursor.description获取列名

def q(sql):
    mycursor.execute(sql)
    return pd.DataFrame(mycursor)   

q('''
show databases;
''')

	Database
0	information_schema
1	mysql
2	performance_schema
3	python
4	sys

# 如果存在先删除
q('''
drop  database  if EXISTS skill 
''')

q('''
create database skill 
''')

q('''
show databases;
''')

	Database
0	information_schema
1	mysql
2	performance_schema
3	python
4	skill
5	sys

q('''
use skill
''')

np.random.seed(2)
df = pd.DataFrame(np.random.randint(40, 100, (30, 3)))
df.columns = ["语文", "数学", "英语"]
df.index = ['A'+str(i) for i in range(1, 31)]
df.insert(0, "班级", np.random.choice(["一班", "三班", "二班"],30))
df.insert(0, "性别", np.random.choice(["男", "女"],30))
df = df.reset_index().rename(columns={"index":"姓名"})
df.iloc[9,4] = df.iloc[9,4]+5
df.head()

	姓名	性别	班级	语文	数学	英语
0	A1	男	二班	80	55	85
1	A2	女	一班	48	62	83
2	A3	女	二班	58	51	80
3	A4	女	三班	47	74	89
4	A5	女	一班	71	51	61

q('''
create table test(
                  name varchar(255), 
                  sex varchar(255),
                  class varchar(255),
                  cn float,
                  ma float,
                  en float

)
''')

q('''
select * from test
'''
 )

sql = '''
insert into test values(%s,%s,%s,%s,%s,%s)
'''

val = df.values.tolist()

mycursor.executemany(sql, val)

30

mydb.commit()

df = q('''
select * from test
''')
df.head()

	name	sex	class	cn	ma	en
0	A1	男	二班	80.0	55.0	85.0
1	A2	女	一班	48.0	62.0	83.0
2	A3	女	二班	58.0	51.0	80.0
3	A4	女	三班	47.0	74.0	89.0
4	A5	女	一班	71.0	51.0	61.0

case when实现map映射

• 1 可以实现单个单个的映射
• 2 可以连续变量的分箱

把男变成1，女变成0

q('''
select *, 
case 
    when sex='男' then 1 else 0
end pp
from test
''').head()

	name	sex	class	cn	ma	en	pp
0	A1	男	二班	80.0	55.0	85.0	1
1	A2	女	一班	48.0	62.0	83.0	0
2	A3	女	二班	58.0	51.0	80.0	0
3	A4	女	三班	47.0	74.0	89.0	0
4	A5	女	一班	71.0	51.0	61.0	0

case when+group by实现数据透视表

• 就是所谓的行转列

select 
       -- 透视完后结果表的行，就是分组的字段，类似pandas的pivot函数的index参数
       city,county,
		-- 类似columns参数
		-- 透视完后结果表的列，不能像pandas那样直接把列中的各个取值转变成一个新字段需要自己进行case when,同时聚合
		-- 聚合方式   要转变成列的字段      需要聚合的字段
		sum(case when hotel_type='其他' then region_acti_index end) 其他求和,
        avg(case when hotel_type='快捷' then region_acti_index end) 快捷平均,
		max(case when hotel_type='星级' then region_acti_index end) 星级最大
from 
-- 这里是需要透视的表
(SELECT * FROM "ele_trav_hot_2" where ds='2020-10-01') t
-- 这里是行透视，类似pandas里面的index参数
GROUP BY city,county

# index = "sex"
# columns = "class"
# 求每个组的总分平均分和各科平均分

q('''
select 
        sex,
        avg(case when class='二班' then cn+ma+en end) as 二班总成绩平均分,
        avg(case when class='二班' then cn end) as       二班语文平均分,
        avg(case when class='二班' then ma end) as       二班数学平均分,
        avg(case when class='三班' then cn+ma+en end) as 三班总成绩平均分,
        avg(case when class='三班' then cn end) as       三班语文平均分,
        avg(case when class='三班' then ma end) as       三班数学平均分,
        avg(case when class='一班' then cn+ma+en end) as 一班总成绩平均分,
        avg(case when class='一班' then cn end) as       一班语文平均分,
        avg(case when class='一班' then ma end) as       一班数学平均分        
        
from test
group by sex
order by sex

''')

	sex	二班总成绩平均分	二班语文平均分	二班数学平均分	三班总成绩平均分	三班语文平均分	三班数学平均分	一班总成绩平均分	一班语文平均分	一班数学平均分
0	女	211.142857	73.857143	66.571429	220.666667	65.833333	75.166667	201.750000	68.500000	61.75
1	男	217.000000	87.500000	56.000000	203.000000	72.200000	66.600000	218.666667	68.333333	74.50

使用union实现数据逆透视

• 所谓的列转行

q('''
select 
      sex 性别,
      '二班' as 班级,
      二班总成绩平均分 as 总成绩平均分,
      二班语文平均分 as 语文平均分,
      二班数学平均分 as 数学平均分   
from 


(select 
        sex,
        avg(case when class='二班' then cn+ma+en end) as 二班总成绩平均分,
        avg(case when class='二班' then cn end) as       二班语文平均分,
        avg(case when class='二班' then ma end) as       二班数学平均分,
        avg(case when class='三班' then cn+ma+en end) as 三班总成绩平均分,
        avg(case when class='三班' then cn end) as       三班语文平均分,
        avg(case when class='三班' then ma end) as       三班数学平均分,
        avg(case when class='一班' then cn+ma+en end) as 一班总成绩平均分,
        avg(case when class='一班' then cn end) as       一班语文平均分,
        avg(case when class='一班' then ma end) as       一班数学平均分        
        
from test
group by sex
order by sex) t
union
select 
      sex 性别,
      '一班' as 班级,
      一班总成绩平均分 as 总成绩平均分,
      一班语文平均分 as 语文平均分,
      一班数学平均分 as 数学平均分  
from 


(select 
        sex,
        avg(case when class='二班' then cn+ma+en end) as 二班总成绩平均分,
        avg(case when class='二班' then cn end) as       二班语文平均分,
        avg(case when class='二班' then ma end) as       二班数学平均分,
        avg(case when class='三班' then cn+ma+en end) as 三班总成绩平均分,
        avg(case when class='三班' then cn end) as       三班语文平均分,
        avg(case when class='三班' then ma end) as       三班数学平均分,
        avg(case when class='一班' then cn+ma+en end) as 一班总成绩平均分,
        avg(case when class='一班' then cn end) as       一班语文平均分,
        avg(case when class='一班' then ma end) as       一班数学平均分        
        
from test
group by sex
order by sex) t
''')

	性别	班级	总成绩平均分	语文平均分	数学平均分
0	女	二班	211.142857	73.857143	66.571429
1	男	二班	217.000000	87.500000	56.000000
2	女	一班	201.750000	68.500000	61.750000
3	男	一班	218.666667	68.333333	74.500000

# 直接分组肯定更快
q('''
select sex, class, avg(cn+ma+en),avg(cn), avg(ma) from test
group by sex, class
order by sex, class
''')

	sex	class	avg(cn+ma+en)	avg(cn)	avg(ma)
0	女	一班	201.750000	68.500000	61.750000
1	女	三班	220.666667	65.833333	75.166667
2	女	二班	211.142857	73.857143	66.571429
3	男	一班	218.666667	68.333333	74.500000
4	男	三班	203.000000	72.200000	66.600000
5	男	二班	217.000000	87.500000	56.000000

if函数实现map映射

q('''
select *,if(sex='男', 1, 0) from test
''').head()

	name	sex	class	cn	ma	en	if(sex='男', 1, 0)
0	A1	男	二班	80.0	55.0	85.0	1
1	A2	女	一班	48.0	62.0	83.0	0
2	A3	女	二班	58.0	51.0	80.0	0
3	A4	女	三班	47.0	74.0	89.0	0
4	A5	女	一班	71.0	51.0	61.0	0

ifnull缺失值填充

q('''
select class, sex, avg(cn) 语文平均分, avg(ma) 数学平均分, avg(en) 英语平均分 from test
group by class, sex with rollup
''')

	class	sex	语文平均分	数学平均分	英语平均分
0	一班	女	68.500000	61.750000	71.500000
1	一班	男	68.333333	74.500000	75.833333
2	一班	None	68.400000	69.400000	74.100000
3	三班	女	65.833333	75.166667	79.666667
4	三班	男	72.200000	66.600000	64.200000
5	三班	None	68.727273	71.272727	72.636364
6	二班	女	73.857143	66.571429	70.714286
7	二班	男	87.500000	56.000000	73.500000
8	二班	None	76.888889	64.222222	71.333333
9	None	None	71.066667	68.533333	72.733333

q('''
--     class如果为空，则替换成total
select ifnull(class, 'total') class, 
       ifnull(sex,'不分组') sex,
       语文平均分, 数学平均分,英语平均分
from 

(select class, sex, avg(cn) 语文平均分, avg(ma) 数学平均分, avg(en) 英语平均分 from test
group by class, sex with rollup) t
''')

	class	sex	语文平均分	数学平均分	英语平均分
0	一班	女	68.500000	61.750000	71.500000
1	一班	男	68.333333	74.500000	75.833333
2	一班	不分组	68.400000	69.400000	74.100000
3	三班	女	65.833333	75.166667	79.666667
4	三班	男	72.200000	66.600000	64.200000
5	三班	不分组	68.727273	71.272727	72.636364
6	二班	女	73.857143	66.571429	70.714286
7	二班	男	87.500000	56.000000	73.500000
8	二班	不分组	76.888889	64.222222	71.333333
9	total	不分组	71.066667	68.533333	72.733333

全表排名,逐行加一,12345

• 排序后按照行逐渐+1

变量实现

# 排除总成绩排名

# 注意赋值一定要写成  :=
q('''
select *,
      cn+ma+en score,
      -- 这里利用变量args逐行+1
      @args:=@args+1 as rank1
from test ,(select @args:=0 as xxx) t
order by score desc
''').head(5)

	name	sex	class	cn	ma	en	xxx	score	rank1
0	A23	男	一班	96.0	98.0	93.0	0	287.0	1.0
1	A10	女	三班	91.0	84.0	78.0	0	253.0	2.0
2	A11	女	三班	82.0	73.0	98.0	0	253.0	3.0
3	A20	女	二班	85.0	98.0	55.0	0	238.0	4.0
4	A16	女	二班	92.0	86.0	52.0	0	230.0	5.0

# 可以先定义一个变量， 注意select后面给变量赋值只能用 :=
q('''
set @m = 0;

''')

q('''
select *,
       cn+ma+en score,
       @m := @m+1 as score_rank
from test
order by score desc
''').head()

	name	sex	class	cn	ma	en	score	score_rank
0	A23	男	一班	96.0	98.0	93.0	287.0	1
1	A10	女	三班	91.0	84.0	78.0	253.0	2
2	A11	女	三班	82.0	73.0	98.0	253.0	3
3	A20	女	二班	85.0	98.0	55.0	238.0	4
4	A16	女	二班	92.0	86.0	52.0	230.0	5

窗口函数实现row_number()

• 1 row_number()是返回当前行号的函数，不可重复，每次加1
• 2 over([partition by ], [order by])是窗口函数必须加上的over子句
• 3 partition by是按字段分区，对每个区执行前面的窗口函数，如果没有则就是对全表执行前面的窗口函数，也就是row_number()
• 4 order by 是决定每个区如何排序，如果没有则不排序。

q('''
select *,cn+ma+en score,
       row_number() over(order by cn+ma+en desc) as 排名

from test
''').head()

	name	sex	class	cn	ma	en	score	排名
0	A23	男	一班	96.0	98.0	93.0	287.0	1
1	A10	女	三班	91.0	84.0	78.0	253.0	2
2	A11	女	三班	82.0	73.0	98.0	253.0	3
3	A20	女	二班	85.0	98.0	55.0	238.0	4
4	A16	女	二班	92.0	86.0	52.0	230.0	5

全表排名,并列连续排名,12234

变量实现

q('''
select *,
       cn+ma+en score,
case 
--     -- 判断是否和前一个一样,如果一样，就用之前的rank1
       -- 这里case when里面还不能直接使用score
     when @rank2=cn+ma+en then @rank1
--     -- 这里是肯定不一样，@rank2:=score本身是赋值语句， 但是返回的是score的值，始终为真
--     --                    -- 返回@rank1+1，并把返回值赋给@rank1
     when @rank2:=cn+ma+en then @rank1:=@rank1+1
end as rank1
from
--  第一个变量做排名      -- 第二个变量用来记录上一次排序字段的值
test,(select @rank1:=0  as a,@rank2:=null as b) t
order by score desc
''').head()

	name	sex	class	cn	ma	en	a	b	score	rank1
0	A23	男	一班	96.0	98.0	93.0	0	None	287.0	1.0
1	A10	女	三班	91.0	84.0	78.0	0	None	253.0	2.0
2	A11	女	三班	82.0	73.0	98.0	0	None	253.0	2.0
3	A20	女	二班	85.0	98.0	55.0	0	None	238.0	3.0
4	A16	女	二班	92.0	86.0	52.0	0	None	230.0	4.0

窗口函数实现dense_rank()

• 1 dense_rank()也是返回当前行号的函数
• 2 over()子句中order by必须存在，否则排名全是1
• 3 对于order by排序的字段，如果值一样，则排名一样
• 4 排名是连续不间断的

q('''
select *,
       cn+ma+en score,
       dense_rank() over(order by cn+ma+en desc) 排名
       
from test
''').head()

	name	sex	class	cn	ma	en	score	排名
0	A23	男	一班	96.0	98.0	93.0	287.0	1
1	A10	女	三班	91.0	84.0	78.0	253.0	2
2	A11	女	三班	82.0	73.0	98.0	253.0	2
3	A20	女	二班	85.0	98.0	55.0	238.0	3
4	A16	女	二班	92.0	86.0	52.0	230.0	4

全表排名,并列间隔排名,12245

变量实现

q('''
select *,
       cn+ma+en score,
-- 1 不能用score , 首先判断cn+ma+en是等于上一次的分数@b
-- 2 如果是，则排名不变，所以返回@a
-- 3 如果不是，则返回@c
-- 4 最终返回的结果输出给score_rank, 并且返回给@a
       @a:=if(@b=cn+ma+en, @a, @c) as score_rank,
-- 5 无论怎么样，@c是记录行数的
       @c:=@c+1,
-- 6 记录成绩这一次的成绩
       @b:=cn+ma+en 
from 
test,(select @a:=0 a, @b:=null b, @c:=1 c) t
order by score desc
''').head()

	name	sex	class	cn	ma	en	a	b	c	score	score_rank	@c:=@c+1	@b:=cn+ma+en
0	A23	男	一班	96.0	98.0	93.0	0	None	1	287.0	1	2.0	287.0
1	A10	女	三班	91.0	84.0	78.0	0	None	1	253.0	2	3.0	253.0
2	A11	女	三班	82.0	73.0	98.0	0	None	1	253.0	2	4.0	253.0
3	A20	女	二班	85.0	98.0	55.0	0	None	1	238.0	4	5.0	238.0
4	A16	女	二班	92.0	86.0	52.0	0	None	1	230.0	5	6.0	230.0

窗口函数rank()实现

• 1 dense_rank()也是返回当前行号的函数
• 2 over()子句中order by必须存在，否则排名全是1
• 3 对于order by排序的字段，如果值一样，则排名一样
• 4 排名是连续不间断的,注意和dense_rank()的区别

q('''
select *, cn+ma+en score,
       rank() over(order by cn+ma+en desc) 排名
from test
''').head()

	name	sex	class	cn	ma	en	score	排名
0	A23	男	一班	96.0	98.0	93.0	287.0	1
1	A10	女	三班	91.0	84.0	78.0	253.0	2
2	A11	女	三班	82.0	73.0	98.0	253.0	2
3	A20	女	二班	85.0	98.0	55.0	238.0	4
4	A16	女	二班	92.0	86.0	52.0	230.0	5

组内排名(窗口函数实现)

# 自连接思路思路，不用管
# 1 需要使用左自连接，连接的字段就是组内排序分组的那个字段， and 筛选第一个表里面的值比第二个小的
# 2 这样就相当于让第一张表的每一行记录， 匹配上了自身所在组中的哪些记录， 并且第一张表的排序字段的值都小与所匹配的记录
# 3 对得到的结果，按照第一张表的主键、分组字段、排序字段进行分组，筛选having count(分组字段)<n的组
# having count(分组字段)其实就是在计算每个记录的匹配数量，这些所匹配的都是比t1大的，那如果说  count(分组字段)>n
# 那就是说在本组内，有超过n个值比本记录大， 自然不可能是前top N， 反之就是topN

# q('''
# select  new.*, cn+ma+en score,count(class) as score_rank from 
# (select t1.* from test t1
# left join test t2 on t1.class=t2.class and t1.cn+t1.ma+t1.en<t2.cn+t2.ma+t2.en ) new
# group by name, class, cn, ma, en  having count(1)<3  -- 每组只显示三个
# order by class, cn+ma+en desc
# ''')
# 注意看结果
# 最高的和第二高的排名都是1
# 原因在哪最高，找不到匹配更高的 ，但连接用的是 left join on and ,则第一张表都会保留
# 也就是说最好的，比如A23，会保留， 但是实际上是没有匹配到数据的，但是依然后一条记录， 所以count出来是1
# 排名第二高的， 只会匹配到最高的，也就只有最高的比他高，所以只会匹配到最高的这一条数据，count出来还是1





#**以下解决最高和最低排名相同的问题**

# q('''
# select  name, class, ma,count(name2)+1 as ma_rank from 
# -- 如果要加排名，筛选的时候，加上第二表的主键，并重新命名成name2
# -- 对于最高的排序字段的值来说，它没有匹配到第二张表，所以每个组的最高值所在行的name2是缺失的
# -- 这样就处理组合统计count(name2), 对于每个组的最高值来说，其实为0，第二高为1， 则整体加1即可
# (select t1.*, t2.name name2 from test t1
# left join test t2 on t1.class=t2.class and t1.ma<t2.ma ) new
# group by name, class, ma  having count(1)<3
# order by class, ma desc
# ''')

# **如果你需要求topN，而不需要具体的排名，则可以如下简化**

# # 组内排序，添加排名(自连接)

# # 比如求每个班数学成绩的前三名的信息

# df = q('''
# select * from test
# ''' )

# # 使用pandas
# df.groupby("class").apply(lambda x:x.sort_values("ma", ascending=False)[0:3])

# # 思路
# # 1 需要使用左自连接，连接的字段就是组内排序分组的那个字段， and 筛选第一个表里面的值比第二个大的
# # 2 这样就相当于让第一张表的每一行记录， 匹配上了自身所在组中的哪些记录， 并且第一张表的排序字段的值都小与所匹配的记录
# # 3 对得到的结果，按照第一张表的主键、分组字段、排序字段进行分组，筛选having count(分组字段)<n的组
# # having count(分组字段)其实就是在计算每个记录的匹配数量，这些所匹配的都是比t1大的，那如果说  count(分组字段)>n
# # 那就是说在本组内，有超过n个值比本记录大， 自然可能是前top N， 反之就是topN
# q('''
# select new.name,new.class,new.ma
# from 
#     --  只要第一张t1.*
# (select t1.* from test t1
# -- 是在分组的字段上进行匹配class       -- 筛选排序资字段上，ti表小的数据
# left join test t2 on t1.class=t2.class and t1.ma<t2.ma ) new
# group by t1.name, t1.class, t1.ma having count(class)<3
# order by class, ma desc -- desc改为asc就是组内升序
# ''')

组内排名就使用窗口函数即可，比如求每个班的同学的班级排名和年级排名。
记住:对于非聚合窗口函数，对于每一行都会返回自己行的计算结果。

q('''
select *,cn+ma+en score,
       row_number() over(partition by class order by cn+ma+en desc) 班级排名row_number,
       dense_rank() over(partition by class order by cn+ma+en desc) 班级排名dense_rank,
       rank()       over(partition by class order by cn+ma+en desc) 班级排名rank,
       dense_rank() over(order by cn+ma+en desc)                    年级排名_dense_rank
       
from test
''').head(10)

	name	sex	class	cn	ma	en	score	班级排名row_number	班级排名dense_rank	班级排名rank	年级排名_dense_rank
0	A23	男	一班	96.0	98.0	93.0	287.0	1	1	1	1
1	A10	女	三班	91.0	84.0	78.0	253.0	1	1	1	2
2	A11	女	三班	82.0	73.0	98.0	253.0	2	1	1	2
3	A20	女	二班	85.0	98.0	55.0	238.0	1	1	1	3
4	A16	女	二班	92.0	86.0	52.0	230.0	2	2	2	4
5	A7	女	三班	60.0	92.0	77.0	229.0	3	2	3	5
6	A24	男	一班	49.0	97.0	81.0	227.0	2	2	2	6
7	A26	女	三班	72.0	83.0	72.0	227.0	4	3	4	6
8	A14	女	二班	71.0	59.0	97.0	227.0	3	3	3	6
9	A6	男	一班	87.0	71.0	66.0	224.0	3	3	3	7

组内求某个数值字段最高的topN(窗口函数实现)

• 比如求解每个班级里面的总分前三名同学的各科成绩、总成绩、班级排名、年级排

要想求每组里面的topN的对象，必须要先汇总一张这样的表

对象	组	度量值
对象1	组1	值1

select new.* from 
(select * ,
       rank() over(partition by 组 order by 度量值) 排名 from table) new
where 排名<=N

# 首先你要先算出每个同学的班级排名和年级排名
q('''
select *,
       cn+ma+en score,
       dense_rank() over(partition by class order by cn+ma+en desc) 班级排名_dense_rank,
       dense_rank() over(order by cn+ma+en desc)                    学校排名_dense_rank
from test
''').head(10)

	name	sex	class	cn	ma	en	score	班级排名_dense_rank	学校排名_dense_rank
0	A23	男	一班	96.0	98.0	93.0	287.0	1	1
1	A10	女	三班	91.0	84.0	78.0	253.0	1	2
2	A11	女	三班	82.0	73.0	98.0	253.0	1	2
3	A20	女	二班	85.0	98.0	55.0	238.0	1	3
4	A16	女	二班	92.0	86.0	52.0	230.0	2	4
5	A7	女	三班	60.0	92.0	77.0	229.0	2	5
6	A24	男	一班	49.0	97.0	81.0	227.0	2	6
7	A26	女	三班	72.0	83.0	72.0	227.0	3	6
8	A14	女	二班	71.0	59.0	97.0	227.0	3	6
9	A6	男	一班	87.0	71.0	66.0	224.0	3	7

# 在对上面那张表，筛选班级排名<=3的同学
q('''
select * from 
(
select *,
       cn+ma+en score,
       dense_rank() over(partition by class order by cn+ma+en desc) 班级排名_dense_rank,
       dense_rank() over(order by cn+ma+en desc)                    学校排名_dense_rank
from test
) new
where new.班级排名_dense_rank<=3
order by class,score desc

''')

	name	sex	class	cn	ma	en	score	班级排名_dense_rank	学校排名_dense_rank
0	A23	男	一班	96.0	98.0	93.0	287.0	1	1
1	A24	男	一班	49.0	97.0	81.0	227.0	2	6
2	A6	男	一班	87.0	71.0	66.0	224.0	3	7
3	A10	女	三班	91.0	84.0	78.0	253.0	1	2
4	A11	女	三班	82.0	73.0	98.0	253.0	1	2
5	A7	女	三班	60.0	92.0	77.0	229.0	2	5
6	A26	女	三班	72.0	83.0	72.0	227.0	3	6
7	A20	女	二班	85.0	98.0	55.0	238.0	1	3
8	A16	女	二班	92.0	86.0	52.0	230.0	2	4
9	A14	女	二班	71.0	59.0	97.0	227.0	3	6

查找在A表里但不在B表里面的问题

• 思路：其实就是用A去左连接B, 这样保障A表的所有信息都保留。不在B中的自然缺失。

select * from 
A left join B on A.key=B.key
where B.key is null

全表单列累积和计算sum+over

# 1 注意观察，sum() over()基本上能实现累积求和。如果是是聚合函数+over()都变成了一个累积函数
# 2 也就是说求得的当前行与之前行的聚合值
# 3 但是， 明显en相同的地方，累计值也相同，不符合我们的需求。因为如果在在order by相同，那么是同一级别，值是样的。
# 3 一般而言，sum(累积字段) over(order by 排序字段)不应该是同一个，且排序字段是唯一的，如日期这种
# 4 逻辑上，我们是希望在 排序字段的顺序上逐渐累积 
q('''
select *,
       sum(en) over(order by en)
from test
''').head(10)

	name	sex	class	cn	ma	en	sum(en) over(order by en)
0	A13	男	三班	44.0	86.0	46.0	46.0
1	A21	女	一班	81.0	85.0	48.0	142.0
2	A27	男	三班	66.0	90.0	48.0	142.0
3	A16	女	二班	92.0	86.0	52.0	194.0
4	A20	女	二班	85.0	98.0	55.0	249.0
5	A15	男	一班	71.0	42.0	56.0	305.0
6	A25	男	三班	86.0	66.0	59.0	364.0
7	A5	女	一班	71.0	51.0	61.0	425.0
8	A22	男	二班	95.0	57.0	62.0	549.0
9	A30	女	二班	77.0	46.0	62.0	549.0

# 那么如果非要实现，en从小到大逐渐累积
# 那么order by的时候处理en, 还需要加上一个主键， 这样在排序上，因为主键的存在不可能一样
# 就能实现累积
q('''
select *,
       sum(en) over(order by en,name)
from test
''').head(10)

	name	sex	class	cn	ma	en	sum(en) over(order by en,name)
0	A13	男	三班	44.0	86.0	46.0	46.0
1	A21	女	一班	81.0	85.0	48.0	94.0
2	A27	男	三班	66.0	90.0	48.0	142.0
3	A16	女	二班	92.0	86.0	52.0	194.0
4	A20	女	二班	85.0	98.0	55.0	249.0
5	A15	男	一班	71.0	42.0	56.0	305.0
6	A25	男	三班	86.0	66.0	59.0	364.0
7	A5	女	一班	71.0	51.0	61.0	425.0
8	A22	男	二班	95.0	57.0	62.0	487.0
9	A30	女	二班	77.0	46.0	62.0	549.0

分组累积计算sum+over

比如求每个班，数学成绩从小达到的累计值

q('''
select *,
       sum(ma) over(partition by class order by ma,name) ma_sum
from test
''').head()

	name	sex	class	cn	ma	en	ma_sum
0	A15	男	一班	71.0	42.0	56.0	42.0
1	A29	女	一班	74.0	49.0	94.0	91.0
2	A28	男	一班	52.0	50.0	80.0	141.0
3	A5	女	一班	71.0	51.0	61.0	192.0
4	A2	女	一班	48.0	62.0	83.0	254.0

数值字段上下行之差

• 1 lead()函数是向上偏移列。
• 2 lead(要偏移的列，向上偏移及格单位，最后数据不足的默认值)
• 3 通过某字段向上偏移实现后项-前项
• 4 lag()是向下面移动，其他与lead()一样
• 5 需要分组就加over()

q('''
select *, 
       lead(en,1,0) over() en_上移1,
       lead(en,2,0) over() en_上移2,
       en-lead(en,1,0) over() enup_endown,
       lead(en,1,0) over()-en endown_enup,
       
       lag(en,1,0) over()     en_下移1
from test
''').head(10)

	name	sex	class	cn	ma	en	en_上移1	en_上移2	enup_endown	endown_enup	en_下移1
0	A1	男	二班	80.0	55.0	85.0	83.0	80.0	2.0	-2.0	0.0
1	A2	女	一班	48.0	62.0	83.0	80.0	89.0	3.0	-3.0	85.0
2	A3	女	二班	58.0	51.0	80.0	89.0	61.0	-9.0	9.0	83.0
3	A4	女	三班	47.0	74.0	89.0	61.0	66.0	28.0	-28.0	80.0
4	A5	女	一班	71.0	51.0	61.0	66.0	77.0	-5.0	5.0	89.0
5	A6	男	一班	87.0	71.0	66.0	77.0	78.0	-11.0	11.0	61.0
6	A7	女	三班	60.0	92.0	77.0	78.0	83.0	-1.0	1.0	66.0
7	A8	男	三班	79.0	43.0	78.0	83.0	78.0	-5.0	5.0	77.0
8	A9	女	二班	44.0	82.0	83.0	78.0	98.0	5.0	-5.0	78.0
9	A10	女	三班	91.0	84.0	78.0	98.0	64.0	-20.0	20.0	83.0

日期字段上下行之差

见16.2.2

连续问题

• 表形如:
  用户-时间-值

建表

q('''
drop table if EXISTS test2
''')

q('''
create table test2(
             users varchar(50),
             dates date,
             val float
)
''')

q('''
show tables
''')

	Tables_in_skill
0	test
1	test2

q('''
select * from test2
''')

sql = "insert into test2 values(%s,%s,%s)"
print(sql)

insert into test2 values(%s,%s,%s)

val = [['u_001', '2017/1/1', 10],
 ['u_001', '2017/1/2', 270],
 ['u_001', '2017/1/4', 60],
 ['u_001', '2017/1/6', 135],
 ['u_002', '2017/1/1', 10],
 ['u_002', '2017/1/2', 220],
 ['u_002', '2017/1/3', 110],
 ['u_002', '2017/1/4', 150],
 ['u_002', '2017/1/5', 101],
 ['u_002', '2017/1/6', 68],
 ['u_003', '2017/1/1', 20],
 ['u_003', '2017/1/2', 160],
 ['u_003', '2017/1/3', 160],
 ['u_003', '2017/1/4', 20],
 ['u_003', '2017/1/5', 120],
 ['u_003', '2017/1/6', 20],
 ['u_003', '2017/1/7', 120],
 ['u_004', '2017/1/1', 110],
 ['u_004', '2017/1/2', 70],
 ['u_004', '2017/1/3', 120],
 ['u_004', '2017/1/4', 30],
 ['u_004', '2017/1/5', 60],
 ['u_004', '2017/1/6', 120],
 ['u_004', '2017/1/7', 130],
 ['u_005', '2017/1/1', 80],
 ['u_005', '2017/1/2', 130],
 ['u_005', '2017/1/3', 180],
 ['u_005', '2017/1/4', 190],
 ['u_005', '2017/1/5', 80],
 ['u_005', '2017/1/6', 280],
 ['u_005', '2017/1/7', 160],
 ['u_006', '2017/1/1', 40],
 ['u_006', '2017/1/2', 180],
 ['u_006', '2017/1/3', 220],
 ['u_006', '2017/1/4', 40],
 ['u_006', '2017/1/5', 40],
 ['u_006', '2017/1/6', 20],
 ['u_006', '2017/1/7', 290],
 ['u_007', '2017/1/1', 130],
 ['u_007', '2017/1/2', 360],
 ['u_007', '2017/1/3', 30],
 ['u_007', '2017/1/4', 530],
 ['u_007', '2017/1/5', 30],
 ['u_007', '2017/1/6', 230],
 ['u_007', '2017/1/7', 160],
 ['u_008', '2017/1/1', 160],
 ['u_008', '2017/1/2', 120],
 ['u_008', '2017/1/3', 60],
 ['u_008', '2017/1/4', 260],
 ['u_008', '2017/1/5', 360],
 ['u_008', '2017/1/6', 160],
 ['u_008', '2017/1/7', 120],
 ['u_009', '2017/1/1', 70],
 ['u_009', '2017/1/2', 140],
 ['u_009', '2017/1/3', 170],
 ['u_009', '2017/1/4', 270],
 ['u_009', '2017/1/5', 70],
 ['u_009', '2017/1/6', 70],
 ['u_009', '2017/1/7', 140],
 ['u_010', '2017/1/1', 90],
 ['u_010', '2017/1/2', 180],
 ['u_010', '2017/1/3', 90],
 ['u_010', '2017/1/4', 170],
 ['u_010', '2017/1/5', 180],
 ['u_010', '2017/1/6', 190],
 ['u_010', '2017/1/7', 180],
 ['u_011', '2017/1/1', 110],
 ['u_011', '2017/1/2', 200],
 ['u_011', '2017/1/3', 120],
 ['u_011', '2017/1/4', 100],
 ['u_011', '2017/1/5', 100],
 ['u_011', '2017/1/6', 100],
 ['u_011', '2017/1/7', 230],
 ['u_012', '2017/1/1', 10],
 ['u_012', '2017/1/2', 130],
 ['u_012', '2017/1/3', 10],
 ['u_012', '2017/1/4', 50],
 ['u_012', '2017/1/5', 10],
 ['u_012', '2017/1/6', 20],
 ['u_012', '2017/1/7', 20],
 ['u_013', '2017/1/1', 50],
 ['u_013', '2017/1/2', 200],
 ['u_013', '2017/1/3', 150],
 ['u_013', '2017/1/4', 550],
 ['u_013', '2017/1/5', 350],
 ['u_013', '2017/1/6', 50],
 ['u_013', '2017/1/7', 80],
 ['u_014', '2017/1/1', 220],
 ['u_014', '2017/1/2', 140],
 ['u_014', '2017/1/3', 20],
 ['u_014', '2017/1/4', 20],
 ['u_014', '2017/1/5', 250],
 ['u_014', '2017/1/6', 120],
 ['u_014', '2017/1/7', 290],
 ['u_015', '2017/1/1', 10],
 ['u_015', '2017/1/2', 30],
 ['u_015', '2017/1/3', 10],
 ['u_015', '2017/1/4', 20],
 ['u_015', '2017/1/5', 70],
 ['u_015', '2017/1/6', 10],
 ['u_015', '2017/1/7', 140]]

mycursor.executemany(sql, val)

101

mydb.commit()

q('''
select * from test2
''')

	users	dates	val
0	u_001	2017-01-01	10.0
1	u_001	2017-01-02	270.0
2	u_001	2017-01-04	60.0
3	u_001	2017-01-06	135.0
4	u_002	2017-01-01	10.0
...	...	...	...
96	u_015	2017-01-03	10.0
97	u_015	2017-01-04	20.0
98	u_015	2017-01-05	70.0
99	u_015	2017-01-06	10.0
100	u_015	2017-01-07	140.0

101 rows × 3 columns

连续问题

• 连续问题通常都是一段时间内， 比如一个星期，一个月，半年等等

求每个用户的最大连续登录次数

# 先通过组内排序给每个用户的日期加上排名
q('''
select *,
       row_number() over(partition by users order by dates) 排名
from test2
''')

	users	dates	val	排名
0	u_001	2017-01-01	10.0	1
1	u_001	2017-01-02	270.0	2
2	u_001	2017-01-04	60.0	3
3	u_001	2017-01-06	135.0	4
4	u_002	2017-01-01	10.0	1
...	...	...	...	...
96	u_015	2017-01-03	10.0	3
97	u_015	2017-01-04	20.0	4
98	u_015	2017-01-05	70.0	5
99	u_015	2017-01-06	10.0	6
100	u_015	2017-01-07	140.0	7

101 rows × 4 columns

# 用dates-排名的天数，得到daydiff,如果当前行和上一行的daydiff相同，则说明当天是连续的
# 并且要注意，同一个组里面，daydiff是不减的，如果不变说明连续，变，也只可能慢慢增加，不会减少
# 因为日期的增加是大于等于排名的，所以daydiff不会减少
q('''
select new1.*, 
       DATE_SUB(dates,INTERVAL 排名 Day) daydiff
from 
(select *,
       row_number() over(partition by users order by dates) 排名
from test2) new1
''')

	users	dates	val	排名	daydiff
0	u_001	2017-01-01	10.0	1	2016-12-31
1	u_001	2017-01-02	270.0	2	2016-12-31
2	u_001	2017-01-04	60.0	3	2017-01-01
3	u_001	2017-01-06	135.0	4	2017-01-02
4	u_002	2017-01-01	10.0	1	2016-12-31
...	...	...	...	...	...
96	u_015	2017-01-03	10.0	3	2016-12-31
97	u_015	2017-01-04	20.0	4	2016-12-31
98	u_015	2017-01-05	70.0	5	2016-12-31
99	u_015	2017-01-06	10.0	6	2016-12-31
100	u_015	2017-01-07	140.0	7	2016-12-31

101 rows × 5 columns

# 对组 和 daydiff进行分组，进行count(1),就可以得到连续登录的分布情况
q('''
-- 4 按照用户分组，求每个用户的最大连续登录天数
select users, max(num)  from 
(
-- 3 按照用户，daydiff进行分组，然后count(1) num，统计每个用户的连续登录分布
	select users, daydiff, count(1) num from 
			(
					-- 2 用日期减去排名， 得到daydiff
					select new1.*, DATE_SUB(dates,INTERVAL 排名 Day) daydiff
					from 
					-- 1 先对每个组进行日期排序， 添加行号
							(select *, row_number() over(partition by users order by dates) 排名 from test2) new1
			) new2 
	group by users, daydiff 
) new3
group by users
''')

	users	max(num)
0	u_001	2
1	u_002	6
2	u_003	7
3	u_004	7
4	u_005	7
5	u_006	7
6	u_007	7
7	u_008	7
8	u_009	7
9	u_010	7
10	u_011	7
11	u_012	7
12	u_013	7
13	u_014	7
14	u_015	7

df[df.users=="u_001"]  # 最大连续登录天数2

	users	dates	val
0	u_001	2017-01-01	10.0
1	u_001	2017-01-02	270.0
2	u_001	2017-01-04	60.0
3	u_001	2017-01-06	135.0

df[df.users=="u_002"]  # 最大连续登录天数6

	users	dates	val
4	u_002	2017-01-01	10.0
5	u_002	2017-01-02	220.0
6	u_002	2017-01-03	110.0
7	u_002	2017-01-04	150.0
8	u_002	2017-01-05	101.0
9	u_002	2017-01-06	68.0

总结：连续登录问题，往往只需要使用user, log_date就行，只要构造这两列数据，就能像上面那样求出一段时间内每个用户的最大连续登录次数。有了这些信息后就可以筛选满足条件的用户，计算一些指标。

上下行日期之差

• 求每个用户相邻两次登录之间的时间间隔
• datediff(结束日期-开始日期), 返回相隔的天数

q('''
select *,
       -- 1 把dates向下移动一个单位
       lag(dates,1,0) over(partition by users order by dates) dates_down1,
       -- 2 用dates-dates_down1
       datediff(dates, lag(dates,1,0) over(partition by users order by dates)) 两次登录之间的间隔1,
       -- 3 填充缺失值
       ifnull(datediff(dates,lag(dates,1,0) over(partition by users order by dates)), 0) 两次登录之间的间隔1
       
from test2
''').head(10)

	users	dates	val	dates_down1	两次登录之间的间隔1	.两次登录之间的间隔1
0	u_001	2017-01-01	10.0	0	NaN	0
1	u_001	2017-01-02	270.0	2017-01-01	1.0	1
2	u_001	2017-01-04	60.0	2017-01-02	2.0	2
3	u_001	2017-01-06	135.0	2017-01-04	2.0	2
4	u_002	2017-01-01	10.0	0	NaN	0
5	u_002	2017-01-02	220.0	2017-01-01	1.0	1
6	u_002	2017-01-03	110.0	2017-01-02	1.0	1
7	u_002	2017-01-04	150.0	2017-01-03	1.0	1
8	u_002	2017-01-05	101.0	2017-01-04	1.0	1
9	u_002	2017-01-06	68.0	2017-01-05	1.0	1

取出连续N天满足xxx条件的数据

取出连续三天以上，val值都大于50的记录

# 先把这张表创建成视图
q('''
select new1.*, date_sub(dates, interval 排名 day) daydiff from 
    -- 2 和原来不同，排名的同时，先筛选val>50,在进行排名
    (select *,
           row_number() over(partition by users order by dates) 排名
    -- 1  先把满足条件的筛选出来
    from test2 where val>50
    ) new1
''')

	users	dates	val	排名	daydiff
0	u_001	2017-01-02	270.0	1	2017-01-01
1	u_001	2017-01-04	60.0	2	2017-01-02
2	u_001	2017-01-06	135.0	3	2017-01-03
3	u_002	2017-01-02	220.0	1	2017-01-01
4	u_002	2017-01-03	110.0	2	2017-01-01
...	...	...	...	...	...
69	u_014	2017-01-05	250.0	3	2017-01-02
70	u_014	2017-01-06	120.0	4	2017-01-02
71	u_014	2017-01-07	290.0	5	2017-01-02
72	u_015	2017-01-05	70.0	1	2017-01-04
73	u_015	2017-01-07	140.0	2	2017-01-05

74 rows × 5 columns

q('''
create view hhh as
select new1.*, date_sub(dates, interval 排名 day) daydiff from 
    -- 1 和原来不同，排名的同时，先筛选val>50,在进行排名
    (select *,
           row_number() over(partition by users order by dates) 排名
    from test2 where val>50
    ) new1
''')

# 连续登录三天以上的组
q('''
select users,daydiff,count(1) num from hhh
group by users,daydiff having count(1)>=3
''')

	users	daydiff	num
0	u_002	2017-01-01	5
1	u_004	2016-12-31	3
2	u_004	2017-01-01	3
3	u_005	2016-12-31	7
4	u_008	2016-12-31	7
5	u_009	2016-12-31	7
6	u_010	2016-12-31	7
7	u_011	2016-12-31	7
8	u_013	2017-01-01	4
9	u_014	2017-01-02	3

# 在把上面满足条件的组与hhh 内连接，连接字段为 users,daydiff
q('''
select * from hhh inner join 
(
select users,daydiff,count(1) num from hhh
group by users,daydiff having count(1)>=3
) ttt on hhh.users=ttt.users and hhh.daydiff=ttt.daydiff
''')

	users	dates	val	排名	daydiff	ttt.users	ttt.daydiff	num
0	u_002	2017-01-02	220.0	1	2017-01-01	u_002	2017-01-01	5
1	u_002	2017-01-03	110.0	2	2017-01-01	u_002	2017-01-01	5
2	u_002	2017-01-04	150.0	3	2017-01-01	u_002	2017-01-01	5
3	u_002	2017-01-05	101.0	4	2017-01-01	u_002	2017-01-01	5
4	u_002	2017-01-06	68.0	5	2017-01-01	u_002	2017-01-01	5
5	u_004	2017-01-01	110.0	1	2016-12-31	u_004	2016-12-31	3
6	u_004	2017-01-02	70.0	2	2016-12-31	u_004	2016-12-31	3
7	u_004	2017-01-03	120.0	3	2016-12-31	u_004	2016-12-31	3
8	u_004	2017-01-05	60.0	4	2017-01-01	u_004	2017-01-01	3
9	u_004	2017-01-06	120.0	5	2017-01-01	u_004	2017-01-01	3
10	u_004	2017-01-07	130.0	6	2017-01-01	u_004	2017-01-01	3
11	u_005	2017-01-01	80.0	1	2016-12-31	u_005	2016-12-31	7
12	u_005	2017-01-02	130.0	2	2016-12-31	u_005	2016-12-31	7
13	u_005	2017-01-03	180.0	3	2016-12-31	u_005	2016-12-31	7
14	u_005	2017-01-04	190.0	4	2016-12-31	u_005	2016-12-31	7
15	u_005	2017-01-05	80.0	5	2016-12-31	u_005	2016-12-31	7
16	u_005	2017-01-06	280.0	6	2016-12-31	u_005	2016-12-31	7
17	u_005	2017-01-07	160.0	7	2016-12-31	u_005	2016-12-31	7
18	u_008	2017-01-01	160.0	1	2016-12-31	u_008	2016-12-31	7
19	u_008	2017-01-02	120.0	2	2016-12-31	u_008	2016-12-31	7
20	u_008	2017-01-03	60.0	3	2016-12-31	u_008	2016-12-31	7
21	u_008	2017-01-04	260.0	4	2016-12-31	u_008	2016-12-31	7
22	u_008	2017-01-05	360.0	5	2016-12-31	u_008	2016-12-31	7
23	u_008	2017-01-06	160.0	6	2016-12-31	u_008	2016-12-31	7
24	u_008	2017-01-07	120.0	7	2016-12-31	u_008	2016-12-31	7
25	u_009	2017-01-01	70.0	1	2016-12-31	u_009	2016-12-31	7
26	u_009	2017-01-02	140.0	2	2016-12-31	u_009	2016-12-31	7
27	u_009	2017-01-03	170.0	3	2016-12-31	u_009	2016-12-31	7
28	u_009	2017-01-04	270.0	4	2016-12-31	u_009	2016-12-31	7
29	u_009	2017-01-05	70.0	5	2016-12-31	u_009	2016-12-31	7
30	u_009	2017-01-06	70.0	6	2016-12-31	u_009	2016-12-31	7
31	u_009	2017-01-07	140.0	7	2016-12-31	u_009	2016-12-31	7
32	u_010	2017-01-01	90.0	1	2016-12-31	u_010	2016-12-31	7
33	u_010	2017-01-02	180.0	2	2016-12-31	u_010	2016-12-31	7
34	u_010	2017-01-03	90.0	3	2016-12-31	u_010	2016-12-31	7
35	u_010	2017-01-04	170.0	4	2016-12-31	u_010	2016-12-31	7
36	u_010	2017-01-05	180.0	5	2016-12-31	u_010	2016-12-31	7
37	u_010	2017-01-06	190.0	6	2016-12-31	u_010	2016-12-31	7
38	u_010	2017-01-07	180.0	7	2016-12-31	u_010	2016-12-31	7
39	u_011	2017-01-01	110.0	1	2016-12-31	u_011	2016-12-31	7
40	u_011	2017-01-02	200.0	2	2016-12-31	u_011	2016-12-31	7
41	u_011	2017-01-03	120.0	3	2016-12-31	u_011	2016-12-31	7
42	u_011	2017-01-04	100.0	4	2016-12-31	u_011	2016-12-31	7
43	u_011	2017-01-05	100.0	5	2016-12-31	u_011	2016-12-31	7
44	u_011	2017-01-06	100.0	6	2016-12-31	u_011	2016-12-31	7
45	u_011	2017-01-07	230.0	7	2016-12-31	u_011	2016-12-31	7
46	u_013	2017-01-02	200.0	1	2017-01-01	u_013	2017-01-01	4
47	u_013	2017-01-03	150.0	2	2017-01-01	u_013	2017-01-01	4
48	u_013	2017-01-04	550.0	3	2017-01-01	u_013	2017-01-01	4
49	u_013	2017-01-05	350.0	4	2017-01-01	u_013	2017-01-01	4
50	u_014	2017-01-05	250.0	3	2017-01-02	u_014	2017-01-02	3
51	u_014	2017-01-06	120.0	4	2017-01-02	u_014	2017-01-02	3
52	u_014	2017-01-07	290.0	5	2017-01-02	u_014	2017-01-02	3