# 导包
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;
‘‘‘)

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

q(‘‘‘
create database skill 
‘‘‘)

q(‘‘‘
show databases;
‘‘‘)

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()

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()

case when实现map映射

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

把男变成1，女变成0

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

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

‘‘‘)

使用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
‘‘‘)

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

if函数实现map映射

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

ifnull缺失值填充

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

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
‘‘‘)

全表排名,逐行加一,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)

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

‘‘‘)

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

窗口函数实现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()

全表排名,并列连续排名,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()

窗口函数实现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()

全表排名,并列间隔排名,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()

窗口函数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()

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

# 自连接思路思路，不用管
# 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)

组内求某个数值字段最高的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)

# 在对上面那张表，筛选班级排名<=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

‘‘‘)

查找在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)

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

分组累积计算sum+over

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

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

数值字段上下行之差

• 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)

日期字段上下行之差

见16.2.2

连续问题

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

建表

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

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

q(‘‘‘
show tables
‘‘‘)

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
‘‘‘)

连续问题

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

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

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

# 用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
‘‘‘)

# 对组 和 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
‘‘‘)

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

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

总结：连续登录问题，往往只需要使用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)

取出连续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
‘‘‘)

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
‘‘‘)

# 在把上面满足条件的组与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
‘‘‘)

原文：https://www.cnblogs.com/LUOyaXIONG/p/15170517.html