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
posted @ 2021-08-21 20:36  罗亚雄  阅读(96)  评论(0)  编辑  收藏  举报