数据库

光殿
253 阅读58分钟

SQL的导入和导出

  1. 导出-cmd管理员运行-mysqldump -h[主机所在ip] -u[用户名] -p[要导出的数据库名] >导出的路径//自定义的文件(后缀.sql)

    mysqldump -h localhost -u root -p test > D://th.sql

  2. 导入-cmd-进入mysql服务-创建数据库或进入数据库

    source 文件所在路径:// xxx.sql

  3. sqlyog从外部导入数据报错 SHOW GLOBAL VARIABLES LIKE 'local_infile'; SET GLOBAL local_infile=1;

显示数据库:show databases;

创建数据库:create database 数据库名;

进入数据库:use 数据库名;

删除数据库:drop database 数据库名;

修改表名:alter database 旧表名 rename to 新表名;

查看表结构：desc 表名;

查看建表语句:show create table 表名;

修改字段名字（或数据类型或约束）:alter table 表名 change 旧字段名 新字段名 新数据类型 新约束;

增加字段：alter table 表名 add column 字段名 数据类型 可选约束;

在表的首列增加字段：alter table 表名 add column 字段名 数据类型 可选约束 first;

在某个字段后添加列名:alter table 表名 add column 字段名 数据类型 可选约束 after 某个字段

删除字段：alter table 表名 drop 字段名;

删除表： drop table 表名

复制表：create table 表名 as(select * from 表名)

将查询的临时表保存成一张数据表，方便复用，as不能省略: create table 表名 as 查询语句

创建视图：create view 表名 as 查询语句

round(x,d)--x指要处理的数，d是指保留几位小数（将x保留d位小数）

注意，有拥有外键约束的关系中的父表是无法直接删除的

删除关联表的方式：

方式一：直接先删除子表，再删除父表

方式二：先删除外键关系，再删除父表

查看外键名show create table 表名

删除外键：alter table 表名 drop foreign key 外键名

新表的语法：

create  table 表名(
    [列名]字段1 字段类型 约束,
    [列名]字段2 字段类型 约束,
    [列名]字段3 字段类型 约束,
    ...
    [列名]字段n 字段类型 约束,
);

常用的字段类型

  1. 数据值
  • 整数型：int/integer
  • 浮点数：float/double/real

image.png

  1. 字符串类型
  • 一般英文使用char(长度)字符串
  • 一般中文使用varchar(长度)字符串

image.png

  1. 日期类型
  • date:年月日类型
  • datetime:年月日小时分钟秒数类型
  • timestamp:时间戳
  • time:小时分钟秒数形式
  • year:年

image.png

  1. 特殊值:null值。

删除数据

delete from 表名 where 字段名=[值];
多条件
delete from 表名 where 条件1 and,or 条件2;
一键清除表中所有数据(速度比delete快)
truncate table 表名

更新数据

update 表名 set 字段名=新值 where 条件;

查询数据

范围查询 :字段 between a and b,在mysql8中是闭区间
具体查询 :字段 in (a,b,c,d...)或者满足
模糊查询 :通配符(_表示一位长度,%表示0位或多位字符长度) 字段 like '_a%'，[]匹配[]中任意一个字符，
[^]不匹配[]中的任意一个字符

排序查询

select [distinct] 字段列 from 表
where 条件
order by 排序依据字段 desc|asc,排序依据字段 desc|asc
多条件排序逗号隔开

分页查询

查询排序后的前n行数据
limit n
select [distinct] 字段列 from 表
where 条件
order by 排序依据字段 desc|asc
limit n
limit分页
limit skip,pagesize(第几行开始,一页行数)
skip(想知道的正常页码-1)*pagesize(想输出的数据条量)
想知道第10页6条数据，即skip=(10-1)*6=54
6行为页
第一页：limit 0,6--skip=(1-1)*6
第二页：limit 6,6--skip=(2-1)*6
第三页：limit 12,6--skip=(3-1)*6

聚合函数查询

count():统计记录数，非空单元格计数
非重复计数--count(distinct 字段)
avg():平均值
sum():求和
max():最大值
min():最小值

分组查询

group by 配合聚合函数使用。
select [distinct] 字段列 from 表
where 条件
group by
order by 排序依据字段 desc|asc,排序依据字段 desc|asc
limit n
having 条件

MySQL日期时间操作函数

  1. 获取时间
#获取当前日期时间
SELECT NOW();
#获取当前日期
SELECT CURDATE();
#获取当前时间
SELECT CURTIME();
#获取当前时间的年
SELECT YEAR(NOW());
#获取当前时间的月
SELECT MONTH(NOW());
#获取当前时间的日
SELECT DAY(NOW());
#获取当前时间的时
SELECT HOUR(NOW());
#获取当前时间的分
SELECT MINUTE(NOW());
#获取当前时间的秒
SELECT SECOND(NOW());
#获取该日期是当年的第几个季度
SELECT QUARTER(NOW());
#获取长日期
SELECT NOW(),CURRENT_TIMESTAMP(),LOCALTIME(),LOCALTIMESTAMP();
#获取年月日
SELECT CURDATE(),CURRENT_DATE();
#获取时分秒
SELECT CURTIME(),CURRENT_TIME();

2. 日期转换相关

- SELECT DAYOFWEEK(NOW());-- 返回一周的第几天，周日为1
- SELECT DAYNAME(NOW()); -- 返回周几，英文
- SELECT WEEK(NOW()); -- 当前日期属于当年的第几周
- SELECT DAYOFYEAR(NOW()); -- 返回该日期为这个年的第几天
- SELECT DAYOFMONTH(NOW()); -- 返回该日期为这个月的第几天
- SELECT EXTRACT(YEAR FROM NOW()); -- 2024 从日期中获取指定的单位值。   
- SELECT extract(type from 日期) type:指定返回的单位,year,month,day,hour等
- SELECT TIME_TO_SEC(NOW()); -- 将日期中的时间转换为当天的纯秒数
- SELECT DATE(NOW()); -- 直接将字段转换为年月日格式的日期类型 效果等同于 cast
- datediff(结束时间,起始时间)-- 计算日期之间的相隔天数(结束时间-起始时间)
- timestampdiff(单位,d1,d2)--计算日期d1-d2之间的相隔单位差(year,month,day...)
- adddate(d,n)--计算日期d加上n天的日期
- adddate(d,interval expr type)/date_add(d,interval expr type):计算日期d加上一段之后的日期.  expr>0往后推，expr<0往前推，type:year,month,hour,second...
- adddate(NOW(),interval 3 year)
- subdate(d,n)--计算日期d减去n天的日期
- subdate(d,interval expr type)/date_sub(d,interval expr type):计算日期d减去一段之后的日期.  expr>0往前推，expr<0往后推，type:year,month,hour,second...
- 计算一个月最后一天：SELECT SUBDATE(/*下个月第一天*/ADDDATE(/*本月第一天*/SUBDATE(NOW(),DAY(NOW())-1),INTERVAL 1 MONTH),1) AS 本月最后一天;
- 计算一个月最后一天：select last_day(now())有版本限制
- str_to_date('1998-05-08','%Y-%m-%d') --文本转换成日期
- from_unixtime(时间戳,格式) 时间戳转日期函数

新建条件列

if(条件,满足条件返回值,不满足条件的返回值)
若条件和返回值很多case when end
1. 精确匹配效果()
case 字段
    when 值1 then 结果1
    when 值2 then 结果2
    when 值3 then 结果3
    when 值4 then 结果4
...
else 默认结果
end
2. 模糊匹配效果
case
    when 字段 条件1 then 结果1
    when 字段 条件2 then 结果2
    when 字段 条件3 then 结果3
    when 字段 条件4 then 结果4
...
else 默认结果
end

单列多行的子查询
- any--任一个
- all-所有

-- 配合逻辑运算符>,<,<>,==进行子查询

关联子查询--exists

select 字段列 from where exists(子查询)

转置

  1. 行转列（一维转二维）

数据：

CREATE  TABLE [StudentScores]
(
   [UserName]         NVARCHAR(20),        --学生姓名
   [Subject]          NVARCHAR(30),        --科目
   [Score]            FLOAT,               --成绩
)

INSERT INTO [StudentScores] SELECT '张三', '语文', 80
INSERT INTO [StudentScores] SELECT '张三', '数学', 90
INSERT INTO [StudentScores] SELECT '张三', '英语', 70
INSERT INTO [StudentScores] SELECT '张三', '生物', 85
INSERT INTO [StudentScores] SELECT '李四', '语文', 80
INSERT INTO [StudentScores] SELECT '李四', '数学', 92
INSERT INTO [StudentScores] SELECT '李四', '英语', 76
INSERT INTO [StudentScores] SELECT '李四', '生物', 88
INSERT INTO [StudentScores] SELECT '码农', '语文', 60
INSERT INTO [StudentScores] SELECT '码农', '数学', 82
INSERT INTO [StudentScores] SELECT '码农', '英语', 96
INSERT INTO [StudentScores] SELECT '码农', '生物', 78

image.png

SELECT * FROM [StudentScores] /*数据源*/
AS P
PIVOT 
(
    SUM(Score/*行转列后 列的值*/) FOR 
    p.Subject/*需要行转列的列*/ IN ([语文],[数学],[英语],[生物]/*列的值*/)
) AS T

执行结果：

image.png

  1. 列转行（二维转一维）

数据：

CREATE TABLE ProgrectDetail
(
    ProgrectName         NVARCHAR(20), --工程名称
    OverseaSupply        INT,          --海外供应商供给数量
    NativeSupply         INT,          --国内供应商供给数量
    SouthSupply          INT,          --南方供应商供给数量
    NorthSupply          INT           --北方供应商供给数量
)

INSERT INTO ProgrectDetail
SELECT 'A', 100, 200, 50, 50
UNION ALL
SELECT 'B', 200, 300, 150, 150
UNION ALL
SELECT 'C', 159, 400, 20, 320
UNION ALL

image.png

SELECT P.ProgrectName,P.Supplier,P.SupplyNum
FROM 
(
    SELECT ProgrectName, OverseaSupply, NativeSupply,
           SouthSupply, NorthSupply
     FROM ProgrectDetail
)T
UNPIVOT 
(
    SupplyNum FOR Supplier IN
    (OverseaSupply, NativeSupply, SouthSupply, NorthSupply )
) P

执行结果：

image.png

常见的几种约束形式如下

  1. 主键约束

    主键用来保证表中每条记录的唯一性，因此在设计数据库表时，建议为所有的数据库表都定义一个主键，用于保证数据库表中记录的唯一性。

    主键约束写法：

    1. 唯一主键写法：id int primary key
    2. 联合主键写法：多个字段为一个主键,放到字段后面,primary key(字段1,字段2)

alter table<表名> add constraint <主键名>用法介绍

格式为：

alter table 表格名称 add constraint 约束名称 增加的约束类型 （列名）

alter table emp add constraint ppp primary key(id)

  1. 外键约束

    外键约束主要用于定义表与表之间的某种关系。

    外键写于子表内，其语法为:

    1. CASCADE
      在父表上update/delete记录时，同步update/delete掉子表的匹配记录

    2. SET NULL
      在父表上update/delete记录时，将子表上匹配记录的列设为null (要注意子表的外键列不能为not null)  

    3. NO ACTION
      如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作  

    4. RESTRICT
      同no action, 都是立即检查外键约束

    constraint 外键名 foreign key(引用字段) references 被引用的表(被引用的字段)

格式：

alter table 表名 add constraint 约束名称 约束类型（列名） references 被引用的表名称（列名）

  1. 非空约束

    如果在一个字段中允许不输入数据，可以将该字段定义为null,如果一个字段中必须输入数据，则应当将该字段定义为not null。如果设置某个字段的非空约束，直接在该字段的数据类型后面加上"not null"关键字即可。

  2. 唯一性约束

    如果一个字段值不允许重复，则应当对该字段添加唯一性(unique)约束。与主键约束不同，一张表中可以存在多个唯一性约束，满足唯一性约束的字段可以取null。如果设置某个字段为唯一性约束，直接在该字段的数据类型后面加上"unique"关键字即可。

格式：

alter table emp add constraint 约束名称 增加的约束类型（列名）

alter table emp add constraint xxx unique(ename)

  1. 默认约束

    默认值字段用于指定一个字段的默认值，当尚未在该字段中输入数据时，该字段将自动填入这个默认值。

alter table emp add constraint 约束名称 约束类型默认值 for 列名

比方说：emp表中的gongzi列默认10000

alter table emp add constraint jfsd default 10000 for gongzi

  1. 检查约束

    检查约束用于检查字段的输入值是否满足指定的条件，在表中输入或修改记录时，如果不符合检查约束指定的条件，则数据不能写入该字段。

    年龄列的数据都要大于20

    表名(emp) 列名(age)

格式

alter table <表名> add constraint 约束名称 增加的约束类型 (列名)

alter table emp add constraint xxx check(age>20)

多表删除

  1. 从数据表t1中把那些id值在数据表t2里有匹配的记录全删除

delete from t1 using t1 left join t2 on t1.id=t2.id where [...]

  1. 从数据表t1里在数据表t2里没有匹配的记录查找并删除

delete from t1 using t1 left join t2 on t1.id=t2.id where t2.id is null;

  1. 从两个表中找出相同的记录的数据并把两个表中的数据都删除掉

delete t1,t2 from t1 left join t2 on t1.id=t2.id where t1.id=25;

多表查询（join连接）

  1. 内连接：join,inner join
  2. 外连接：left join,left outer join,right join,right outer join,union
  3. 交叉连接：cross join

两张表格如下：

tablea

image.png

tableb

image.png

内连接

select * from tab_a,tab_b where ...

等价于

select * from tab_a inner join tab_b

推荐使用inner join这种写法

inner join等同于join

select a.*,b.* from tablea a 
inner join[join] tableb b 
on a.id=b.id
也可以使用where连接
select a.*,b.* from tablea a,tableb b where a.id=b.id

查询结果

image.png

应用场景

image.png

外连接（六种场景）

1. left join或者left outer join(等同于left join)

select a.*,b.* from tablea a
left join[left outer join] tableb b
on a.id=b.id

多张表内连接
select 字段列
from a inner join b inner join c
on a.id=b.id and a.id=c.id

select 字段列
from a inner join b on a.id=b.id
inner join c.id on b.id=c.id

使用where
select 字段列
from a,b,c
where a.id=b.id and b.id=c.id

where进行内连接效果写法上，在三张表或以上的表中进行会更加灵活和便捷，用inner join就要多次编写

tablea

image.png

tableb

image.png

结果如下，tableb中不存在的记录填充为null;

image.png

应用场景

image.png

这种场景下得到的是A的所有数据，和满足某一条件的B的数据；

2. left join 或者left outer join(等同于left join) + where b.column is null

select a.id aid,a.age,b.id bid,b.name from tablea a left join tableb b on a.id=b.id where b.id is null [where a.id=xxx]

tablea

image.png

tableb

image.png

left join 表a的数据全部显示，匹配表b的数据也显示，而b.id再次过滤掉表b的id为空的。

image.png

应用场景

image.png

这种场景下得到的是a中的所有数据减去与b满足同一条件的数据，然后得到的a剩余数据；

3. right join 或者 right outer join (等同于right join)

select a.id aid,a.age,b.id bid,b.name from tablea a right join tableb b on a.id=b.id

tablea

image.png

tableb

image.png

image.png

查询结果，tableb中更不存在的记录填充null;

image.png

a right join b :表b的数据全部显示，表a匹配的数据显示，不匹配的为空。

应用场景

image.png

这种场景下得到的是b的所有数据，和满足某一条件的a的数据；

4. right join或者right outer join(等同于right join) + where a.column is null

tablea

image.png

tableb

image.png

SELECT a.id aid,a.age,b.id bid,b.name FROM tablea a RIGHT JOIN tableb b ON a.id=b.id WHERE a.id IS NULL [where a.id=xxx]

查询结果

image.png

应用场景

image.png

这种场景下得到的是b中的所有数据减去与a满足同一条件的数据，然后得到的b剩余数据；

5. full join(mysql 不支持，但是可以用left join union right join代替)

tablea

image.png

tableb

image.png

SELECT a.id aid,a.age,b.id bid,b.name FROM tablea a LEFT JOIN tableb b ON a.id=b.id UNION SELECT a.id aid,a.age,b.id bid,b.name FROM tablea a RIGHT JOIN tableb b ON a.id=b.id

union过后，重复的记录会合并

union all就是不去重

image.png

应用场景

image.png

这种场景下得到的是满足某一条件的公共记录，和独有的记录。

6. full join + is null

tablea

image.png

tableb

image.png

SELECT a.id aid,a.age,b.id bid,b.name FROM tablea a LEFT JOIN tableb b ON a.id=b.id WHERE b.id IS NULL UNION SELECT a.id aid,a.age,b.id bid,b.name FROM tablea a RIGHT JOIN tableb b ON a.id=b.id WHERE a.id IS NULL

结果如下

image.png

应用场景

image.png

这种场景下得到的是a,b中不满足某一条件的记录之和。

**交叉连接（cross join）

想得到a,b记录的排列组合，即笛卡尔积。

tablea

image.png

tableb

image.png

SELECT a.id aid,a.age,b.id bid,b.name FROM tablea a CROSS JOIN tableb b

还可以为cross join指定条件(where)

select a.id aid,a.age,b.id bid,b.name from tablea a cross join tableb b where a.id=b.id

image.png

这种情况实现了内连接的效果。

7.自连接(self join) 一张表有两个字段

  • 员工id
  • 主管id 查询出主管及主管的员工

表自己连接自己

select 表名.字段名,表名.字段名
from 表名 inner join 表名
去重：让字段名不对待判断 on 表名.字段名<>表名.字段名
求组合：利用大小判断 on 表名.字段名>表名.字段名

8.三张表连接

SELECT  A1,A2,C1,C2      --展示A表中的A1\A2字段和C表中的C1\C2
FROM  B                         --中间表
INNER JOIN A ON A.A1 = B.B1   --A表中的与B表中相同的字段
INNER JOIN C ON C.C1 = B.B1    --C表中的与B表中相同的字段
where    xxxxx     ---条件你自己按照需求来加，没有条件就不写where了

内聚函数

扩展阅读 MySQL函数 - 逆心 - 博客园 (cnblogs.com)

1. 数学相关

  • 绝对值--abs(x)
  • 四舍五入round(x):直接返回x的四舍五入后的整数，round(x,n):返回保留n位小数位的四舍五入的数
  • truncate(x,n):返回x，保留n位小数位，不四舍五入
  • 随机数--rand():不给参数，返回0-1之间的随机数但当其多次输出时，随机值不
  • 一样;rand(x):给参数，但当其多次输出的时候，随机值一样
  • 取余mod(x,y)--mod(8,3)=2
  • variance(expr):返回一组数据的方差，std(expr):返回一组数据的标准差

  • 变异系数（Coefficient of Variation, CV）  是衡量波动性的常用指标：

    CV=标准差均值CV=均值标准差​

    • 如果 CV > 1：说明波动较大，数据分散程度高。
    • 如果 CV < 0.5：说明波动较小，数据较为集中。
    • 如果 0.5 ≤ CV ≤ 1：波动性中等。

常见函数汇总 image.png

2. 字符串函数

  • concat(x,y,...)--x,y拼接，如果参数中有null,则返回null
  • concat_ws(分隔符,str1,str2...)按照字符串并给定一个分隔符，分隔符是null则直接返回null，若分割符后的参数是null,则会忽略
  • char_length(str):字符串长度
  • length(str);返回字符串字节长度
  • group_concat(....)：和group by一起使用，group_concat内的字段是除了group by的字段。
GROUP_CONCAT([DISTINCT] column_name [,column_name ...]
             [ORDER BY {unsigned_integer | col_name | expr}             [ASC | DESC] [SEPARATOR 'separator_string']])
  • DISTINCT: 可选参数，用于去除重复值。
  • column_name: 要连接的列名，可以是多个。
  • ORDER BY: 可选参数，用于指定结果排序的方式。
  • SEPARATOR: 可选参数，用于指定分隔符，默认为逗号 ,。

将group by产生的同一个分组中的值连接起来，返回一个字符串结果。

group_concat函数首先根据group by指定的列进行分组，将同一组的列显示出来，并且用分隔符分隔。由函数参数（字段名）决定要返回的列。

#和group by一起使用，将同一组的字段1，按字段1排序，分隔符为';'
SELECT 分类字段
       GROUP_CONCAT(字段1 ORDER BY 字段1 ASC SEPARATOR ';') AS 字段
FROM 表
GROUP BY 分类字段;

完整语法：

GROUP_CONCAT([DISTINCT] expression
    [ORDER BY expression desc]
    [SEPARATOR sep]);
方括号内的语法是可省略
[DISTINCT] ：对该字段下的内容值 进行去重
[ORDER BY expression desc] : 表示将会根据指定字段排序后，再进行连接；默认不写则其链接是没有顺序
[SEPARATOR sep] ： 指定值之间的 连接符号，默认不写为逗号拼接

select group_concat(distinct 字段名 order by 字段名 asc/desc separator '分割符')
新字段名 from 表名

3. 查找函数

  • locate(找谁,从哪里找,[指定位置])：默认返回参数1在参数2中第一次出现的位置

4. 替换函数

  • insert(字符串,位置,字符的长度,新的内容):将新的内容替换到字符串的位置
  • replace(字符串,旧内容,新内容);返回旧字符串替换成新字符串

5. 提取函数

  • left(str,n):字符串左侧提取n个
  • right(str,n):字符串右侧提取n个
  • mid(str,x,len):字符串x的位置开始提取len个
  • substring(str,x,len):字符串x的位置开始提取len个,功能和mid一样

6.空格处理

  • trim(str);删除前后空格
  • Ltrim(str);删除前导空格
  • Rtrim(str);删除后导空格

7. 重复输出

  • repeat(str,n);str重复输出n次

8. 倒序输出

  • reverse(str);

常见字符串函数汇总

image.png

9. 数据类型转换

  • cast(xxx as 类型);按照ascii规格进行
  • convert(xxx,类型);按照odbc规格进行

类型

整数:signed

浮点数:decimal,double,float

字符串，可带参数 :char()

日期:date

时间:time

日期时间行:datetime

10. 格式化函数

  • date_format(date,格式化方式):日期格式化

%Y，%m，%d，%H，%i，%s

大小写 有意义：

Y：2024 ; y：24；

m/d： 数字的月 和 日 ； M/D : 英文形式的 月 和 日

H：24 小时制；h：12小时制

SELECT DATE_FORMAT(NOW(),"%y");
SELECT DATE_FORMAT(NOW(),"%M");

具体参考：MySQL DATE_FORMAT() 函数

  • format(x,n)将数据进行小数位n长度控制，不够则用0补全

11. 逻辑判断

  • case when .. then ..else ..end
  • ifnull(expr1,expr2)如果expr1是null，则显示expr2,否则显示expr1
  • ISNULL函数是一种用于处理空白（null）值的常见函数，它可以将空白值替换为指定的值。在本示例中，我们将使用ISNULL函数将所有空白值替换为0。select col_name,isnull(col_name,0) as replaced_col from tableName;在上面的示例中，column_name是要替换值的列名，table_name是要查询的表名。通过ISNULL函数，我们将column_name列中的空白值替换为0，并将替换后的值显示为replaced_value。
  • COALESCE函数是另一种处理空白（null）值的常见函数，它也可以将空白值替换为 指定的值。与ISNULL函数不同的是，COALESCE函数可以同时替换多个列的值。下面是一个示例： SELECT COALESCE(column1, 0) AS replaced_column1, COALESCE(column2, 0) AS replaced_column2 FROM table_name;在上面的示例中，column1和column2是要替换值的列名，table_name是要查询的表名。通过COALESCE函数，我们将column1和column2列中的空白值分别替换为0，并将替换后的值分别显示为replaced_column1和replaced_column2
  • 除了使用函数外，我们还可以使用CASE语句来替换空白（null）值为0。CASE语句允许我们根据条件来执行不同的操作。在此示例中，我们将使用CASE语句将所有空白值替换为0。SELECT column_name, CASE WHEN column_name IS NULL THEN 0 ELSE column_name END AS replaced_value FROM table_name;在上面的示例中，column_name是要替换值的列名，table_name是要查询的表名。通过CASE语句，我们检查column_name列的每个值是否为空白（null），如果是，则将其替换为0；如果不是空白值，则返回原始值。

12. 数据分列

substring_index(str,delim,计数)

  • str:要处理的字符串
  • dellim:分割符
  • 计数：正数，则就是从左往右，第n个分隔符左侧全部内容，负数，则就是从右往左，第n个分隔符右侧全部内容，
将2008-10-1分成年，月，日列
select  *,
	substring_index(birthday,'-',1) 年,
	substring_index(substring_index(birthday,'-',2),'-',-1) 月,
	substring_index(birthday,'-',-1) 日
from stu

正则查询

select 字段列 from 表
where 字段 regexp [正则表达式]

窗口函数

窗口函数是类似于可以返回聚合值的函数，例如sum(),count(),max()。但是窗口函数与普通聚合函数不同，不会对结果进行分组，能使输出中的行数与输入中的行数相同。 新建自定义升序列

select sum() over(partition by order by) from 表名 1：聚合功能 2：partition by:即分组依据或计算的依据，类似于group by 3:order by:排序

  • 方式一：新建一个带主键及自增长的约束字段，且数据类型为int

若表中已经存在主键，要先删除主键

alter table 表名 add 辅助列 int primary key auto_increment;
  • 方式二：窗口函数 row_number() over() 按排序依据进行序号增加
select *,row_number() over() 编号列 from 表名;

得到不同部门的平均年龄

select *,avg(age) over(partition by dept) 部门平均年龄 from tableName;

原理：利用partition by语句通过dept字段对数据表进行了分组划分，并计算每个分组范围中的平均值avg(age)，同时创建一个字段名为 "部门平均年龄" 的新列。

image.png

简单来说，其区别：

  • 聚合函数就是基于表或 分组依据进行统计，得到一个总值
  • 窗口函数，先根据分组依据进行聚合统计后，将该值做为新建列赋予给依据所在的行进行展示

窗口函数可以分为静态窗口函数和动态窗口函数

  • 静态窗口函数的窗口大小是固定的，不会因为记录的不同而不同；
  • 动态窗口函数的窗口大小会随着记录的不同而变化。

image.png

求不同类别的最大时间和第二大时间

#distinct去重的作用
SELECT DISTINCT *,DATEDIFF(最大时间,第二大时间) 相隔天数 FROM(
SELECT 类别,
        #按类别分类，时间降序，nth_value(时间,1/2)得到前二的时间
	NTH_VALUE(时间,1) OVER(PARTITION BY 类别 ORDER BY 时间 DESC)  AS 最大时间,
	NTH_VALUE(时间,2) OVER(PARTITION BY 类别 ORDER BY 时间 DESC)  AS 第二大时间
FROM 表) AS temp
WHERE 第二大时间 IS NOT NULL;

计算累计求和

有一张产品销售表，求累计销售额，80/20法则。

SELECT *,ROUND(累计指标求和/分组指标求和,2) AS 累计占比,
	CASE WHEN ROUND(累计指标求和/分组指标求和,2)>=0.8 THEN '大于0.8'
	ELSE '小于0.8' END AS '是否大于0.8'
FROM(
	SELECT *,SUM(指标：销量/销售额) OVER(PARTITION BY 分组类别：产品/地区
				ORDER BY 指标：销量/销售额 DESC
				ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 累计指标求和,
		SUM(指标：销量/销售额) OVER(PARTITION BY 分组类别：产品/地区) AS 分组指标求和
	FROM 表)AS temp;

窗口函数名(字段或表达式) over(
    partition by 字段 #分组/分区
    order by 字段# 排序
    frame_clause#手动设定其窗口大小

对于窗口范围的指定，通常使用between 窗口开始的位置 and 窗口结束的位置 来表示

关键字

  • current row:当前行
  • unbounded:表示无限的范围，例如在排序中表示从最前面或最后面的记录开始
  • unbounded preceding:表示区间的第一行，通常与聚合函数一起使用，如计算和或平均值
  • unbounded following:表示区间的最后一行，常用于分组的结尾部分
  • unbounded preceding and unbounded following:针对当前所有记录的前一条，后一条记录，分组中的所有记录
  • preceding:在之前的行， n preceding:当前行之前的n行
  • following:在之后的行， n following:当前行之后的n行
  • rows between:用于定义一个明确的行数范围，如"rows between unbounded preceding and current row"表示从第一行到当前行的数据。
  • range between:基于列值的范围，如"range between current row and 350 following"表示从当前行到当前行数据+350的范围内的数据。
  • range between preceding and following:定义一个基于列值的动态范围，如"range between 5 preceding and 5 following" 表示当前行数据幅度减5加5后的范围数据。
  • rows between unbounded following:窗口范围是当前行到分区中的最后一行
  • rows between unbounded preceding and unbounded following:窗口范围是当前分区中的所有行。

序号函数

  • row_number()序号函数运用--（行编号添加）
  • rank():序号函数--并列排名跳过重复 1、2、2、4
  • dense_rank():序号函数--并列排名不跳过重复1、2、2、3、4

前后函数

  • lag(expr,n) 返回当前行的前n行，lag(字段,n)
  • lead(expr,n)返回当前行的后n行，lag(字段,n)

首尾函数

  • first_value(expr)
  • last_value(expr)

last_value()窗口范围是当前行与当前行之上，需指定范围窗口

返回（排序后）第一个或最后一个expr(字段)的值

分布函数

percent_rand()用于计算分区或结果集中行的百分位数，返回0-1的数字

计算方式：(rank-1)/(rows-1)；rank就是使用rank()函数产生的排名值,rows值为当前窗口的总行数

cume_dist()主要用于查询小于或等于某个值的比例。

计算方式：

  • 若是按升序排序，则统计小于或等于当前值的行数/总行数
  • 若是按降序排序，则统计大于或等于当前值的行数/总行数
  • nth_value(expr,N);返回窗口中第N个expr(字段)的值

sql数据清洗

01 删除指定列、重命名列

场景：多数情况并不是底表的所有特征（列）都对分析有用，这个时候就只需要抽取部分列，对于不用的那些列，可以删除。 重命名列可以避免有些列的命名过于冗长（比如Case When 语句），且有时候会根据不同的业务指标需求来命名。

删除列Python版：
df.drop(col_names, axis=1, inplace=True)
 
删除列SQL版：
1、select col_names from Table_Name
 
2、alter table tableName drop column columnName
 
重命名列Python版：
df.rename(index={'row1':'A'},columns ={'col1':'B'})
 
重命名列SQL版：
select col_names as col_name_B from Table_Name

02 重复值、缺失值处理

场景：比如某网站今天来了1000个人访问，但一个人一天中可以访问多次，那数据库中会记录用户访问的多条记录，而这时候如果想要找到今天访问这个网站的1000个人的ID并根据此做用户调研，需要去掉重复值给业务方去回访。

缺失值：NULL做运算逻辑时，返回的结果还是NULL，这可能就会出现一些脚本运行正确，但结果不对的BUG，此时需要将NULL值填充为指定值。

重复值处理Python版：
df.drop_duplicates()
 
重复值处理SQL版：
1、select distinct col_name from Table_Name
 
2、select col_name from Table_Name group by col_name
 
缺失值处理Python版：
df.fillna(value = 0)
 
df1.combine_first(df2)
 
缺失值处理SQL版：
1、select ifnull(col_name,0) value from Table_Name
 
2、select coalesce(col_name,col_name_A,0) as value from Table_Name
 
3、select case when col_name is null then 0 else col_name end from Table_Name

03 替换字符串空格、清洗%@等垃圾字符、字符串拼接、分隔等字符串处理*

场景：理解用户行为的重要一项是去假设用户的心理，这会用到用户的反馈意见或一些用研的文本数据，这些文本数据一般会以字符串的形式存储在数据库中，但用户反馈的这些文本一般都会很乱，所以需要从这些脏乱的字符串中提取有用信息，就会需要用到文字符串处理函数。

字符串处理Python版：
## 1、空格处理
df[col_name] = df[col_name].str.lstrip() 
 
## 2、*%d等垃圾符处理
df[col_name].replace(' &#.*', '', regex=True, inplace=True)
 
## 3、字符串分割
df[col_name].str.split('分割符')
 
## 4、字符串拼接
df[col_name].str.cat()
 
字符串处理SQL版：
## 1、空格处理
select ltrim(col_name) from Table_name 
 
## 2、*%d等垃圾符处理
select regexp_replace(col_name,正则表达式) from Table_name 
 
## 3、字符串分割
select split(col_name,'分割符') from Table_name 
 
## 4、字符串拼接
select concat_ws(col_name,'拼接符') from Table_name 

04  合并处理

场景：有时候你需要的特征存储在不同的表里，为便于清洗理解和操作，需要按照某些字段对这些表的数据进行合并组合成一张新的表，这样就会用到连接等方法。

合并处理Python版：
 
左右合并
1、pd.merge(left, right, how='inner', on=None, left_on=None, right_on=None,
         left_index=False, right_index=False, sort=True,
         suffixes=('_x', '_y'), copy=True, indicator=False,
         validate=None)
2、pd.concat([df1,df2])
 
上下合并
df1.append(df2, ignore_index=True, sort=False)
 
合并处理SQL版：
 
左右合并
select A.*,B.* from Table_a A join Table_b B on A.id = B.id
 
select A.* from Table_a A left join Table_b B on A.id = B.id
 
上下合并
## Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
## Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
 
select A.* from Table_a A 
union
select B.* from Table_b B 
 
# Union 因为会将各查询子集的记录做比较，故比起Union All ，通常速度都会慢上许多。一般来说，如果使用Union All能满足要求的话，务必使用Union All。

05、窗口函数的分组排序

场景：假如现在你是某宝的分析师，要分析今年不同店的不同品类销售量情况，需要找到那些销量较好的品类，并在第二年中加大曝光，这个时候你就需要将不同店里不同品类进行分组，并且按销量进行排序，以便查找到每家店销售较好的品类。

Demo数据如上，一共a,b,c三家店铺，卖了不同品类商品，销量对应如上，要找到每家店卖的最多的商品。

Sale_store:店铺（a,b,c）
Sale_Kind:商品类别
Sale_Num:销售数量

窗口分组Python版：
 
df['Rank'] = df.groupby(by=['Sale_store'])
['Sale_Num'].transform(lambda x: x.rank(ascending=False))
 
窗口分组SQL版：
 
select * from(
    Select *，row_number() over(partition by Sale_store order by Sale_Num desc) rk
    from table_name) b 
where b.rk = 1

常用sql

日期字段查找周末和节假日

SELECT your_date_column, 
    CASE WHEN DAYOFWEEK(your_date_column) IN (1, 7) 
    THEN '周末' 
    ELSE '工作日' 
    END AS day_type 
FROM your_table;

为了判断一个日期是否为节假日，你需要有一个包含具体节假日日期的表。假设你有一个名为holidays的表，其中包含一个名为holiday_date的列，你可以这样查询：

SELECT your_date_column,
       CASE
           WHEN EXISTS (SELECT 1 FROM holidays WHERE holiday_date = your_date_column) THEN '节假日'
           WHEN (CASE
                     WHEN DATEPART(DW, your_date_column) IN (1, 7) THEN '周末'
                     ELSE '工作日'
                 END = '周末') THEN '周末'
           ELSE '工作日'
       END AS day_type
FROM your_table;

这段代码首先检查给定的日期是否存在于holidays表中，如果存在，则认为是节假日；如果不是节假日，再检查是否为周末。

计算百分比

计算销量表中，每类产品的销量/销售额占总销量/销售额的百分比

要计算百分比，我们需要先计算出每个类别的销售总额，然后再计算每类产品的销售额占总销售额的比例。以下是使用GROUP BY查询计算百分比的示例代码：

SELECT type,#类别 
       SUM(Amount) AS TotalAmount, #每类产品销售额求和
       #计算每类产品销售额和占总销售额的百分比
       CAST(ROUND(SUM(Amount) / 
           (SELECT SUM(Amount) FROM Sales) * 100, 2) AS CHAR,'%') 
       AS Percentage
FROM Sales
GROUP BY Salesperson;

计算同环比

有两张表，销售表(产品id,销售数量,销售时间)；商品表(产品id,产品名称,产品单价)

#计算同比环比
/*
1获得每月数据，首先获得每个月的销售额，要有销售额，两个表肯定是要连接起来的
获取时间：year(字段名)--获取年份，month(字段名)--获取月份
*/
SELECT YEAR(s.`销售时间`) 年,MONTH(s.`销售时间`) 月,SUM(s.`销售数量`*p.`产品单价`) 销售额
FROM sales s
LEFT JOIN product p
ON s.`产品ID`=p.`产品ID`
GROUP BY YEAR(s.`销售时间`),MONTH(s.`销售时间`)


/*
2单步操作获得同比数据
同比：一般情况下是今年第n月与去年第n月比。
计算方式：同比增长率=（本期数据-去年同期数据）/去年同期数据*100%
所以要有个本期数据与去年同期数据一一对应的表，那就要本期数据与去年同期数据分离
有了第一步之后容易获得数据
*/
#本期数据
SELECT YEAR(s.`销售时间`) 年,MONTH(s.`销售时间`) 月,SUM(s.`销售数量`*p.`产品单价`) 销售额
FROM sales s
LEFT JOIN product p
ON s.`产品ID`=p.`产品ID`
WHERE YEAR(s.`销售时间`)=2020
GROUP BY YEAR(s.`销售时间`),MONTH(s.`销售时间`)
#去年同期数据
SELECT YEAR(s.`销售时间`) 年,MONTH(s.`销售时间`) 月,SUM(s.`销售数量`*p.`产品单价`) 销售额
FROM sales s
LEFT JOIN product p
ON s.`产品ID`=p.`产品ID`
WHERE YEAR(s.`销售时间`)=2019
GROUP BY YEAR(s.`销售时间`),MONTH(s.`销售时间`)
/*
下面就要进行表的关联了
本期、去年同期区别就是年份不一样，月份一样，那就用月份作为连接点进行连接，同比就完成了
计算的是本期，本期数据是关键，所以本期数据在右边，用的是右连接
*/
SELECT CONCAT(本期年,'-',往期年) 本期年月,本期销售额,往期销售额,
	CASE WHEN 往期销售额>0 THEN CONCAT((本期销售额-往期销售额)/往期销售额*100,'%')
	ELSE '同期没有数据' END 同比
FROM(
SELECT YEAR(s.`销售时间`) 往期年,MONTH(s.`销售时间`) 往期月,SUM(s.`销售数量`*p.`产品单价`) 往期销售额
FROM sales s LEFT JOIN product p ON s.`产品ID`=p.`产品ID` WHERE YEAR(s.`销售时间`)=2019
GROUP BY YEAR(s.`销售时间`),MONTH(s.`销售时间`)
) AS s_pre
RIGHT JOIN
(SELECT YEAR(s.`销售时间`) 本期年,MONTH(s.`销售时间`) 本期月,SUM(s.`销售数量`*p.`产品单价`) 本期销售额
FROM sales s LEFT JOIN product p ON s.`产品ID`=p.`产品ID` WHERE YEAR(s.`销售时间`)=2020
GROUP BY YEAR(s.`销售时间`),MONTH(s.`销售时间`)
) AS s_now
ON s_now.本期月=s_pre.往期月

计算环比

环比：一般是指本期数据与前一时期数据之比，此处指本月数据与上月数据 计算方式：环比增长速度=(本月数据-上月数据)/上月数据*100%

SELECT 本期数据.年,本期数据.月,本期销售额,往期销售额,
        #先判断往期数据是否大于0，小于0则可以计算环比
	CASE WHEN 往期销售额>0 THEN CONCAT(ROUND((本期销售额-往期销售额)/往期销售额*100,2),'%')
	ELSE '同期没有数据' END 同比
FROM
(SELECT YEAR(s.`销售时间`) 年,MONTH(s.`销售时间`) 月,SUM(s.`销售数量`*p.`产品单价`) 本期销售额
FROM sales s LEFT JOIN product p ON s.`产品ID`=p.`产品ID`
GROUP BY YEAR(s.`销售时间`),MONTH(s.`销售时间`)
) AS 本期数据 #按照年/月进行分组对销售额求和
INNER JOIN 
(
SELECT YEAR(s.`销售时间`) 年,MONTH(s.`销售时间`) 月,SUM(s.`销售数量`*p.`产品单价`) 往期销售额
FROM sales s LEFT JOIN product p ON s.`产品ID`=p.`产品ID`
GROUP BY YEAR(s.`销售时间`),MONTH(s.`销售时间`)
) AS 往期数据 #与自己内连接
ON (本期数据.年=往期数据.年 AND 本期数据.月=往期数据.月+1) #连接条件本年的这个月和上个月匹配
OR(本期数据.年=往期数据.年+1 AND 本期数据.月=1 AND 往期数据.月=12)#还要匹配本年1月份与去年12月份

数据清洗

字段是否有空
SELECT * FROM 表名 WHERE 字段1 IS NULL OR 字段2 IS NULL OR 字段3 IS NULL;
统计各字段非null行
SELECT COUNT(*),COUNT(字段1),COUNT(字段2),COUNT(字段3) FROM 表名;
时间戳转换成日期时间
UPDATE 表名 SET 时间=FROM_UNIXTIME(时间戳,"%H:%i:%s");
按字段1，字段2分类，再查看是否有重复值
SELECT * FROM 表名 WHERE(字段1,字段2) IN(
	SELECT 字段1,字段2 FROM 表名 GROUP BY 字段1,字段2
	HAVING COUNT(*)>1
);

淘宝数据分析项目


/*
在数据中发现用户的购物习惯，以及用户在购物时，电商平台各个环节存在的问题，提出解决办法以提高服务，让消费者更加乐意在本平台消费
【最终目的永远都是为了提高用户体验】本项目针对于淘宝APP的运营数据，常见的指标对用户行为分析，包括UV、PV、新增用户分析、漏斗流失分析、
留存分析、用户价值分析、复购分析等内容【运营方向常见的数据指标和分析方向】

----分析方法：漏斗分析，用户路径分析，RFM用户价值分析，活跃/存留分析，帕累托分析，假设验证分析


需求
1、基于漏斗模型分析，找到影响新增用户数量的因素，从中找到需要改进的转化环节，发现留存现存的问题--漏斗分析
2、研究用户在不同的时间下的行为规律，找到用户在不同时间周期下的活跃规律--活跃度
3、找出最具价值的核心付费用户群，对该组用户行为进行分析--用户价值分组分析，用户路径分析
4、找到用户对不同种类的商品偏好，指定针对不同商品的营销策略--偏好分析
5、通过RFM模型分析客户价值--价格分组


指定需求指标（初步）
纵向（时间）：
	这个数据集中用户的日活跃和周活跃时间有什么规律
	在当日活跃的用户次日、三日、四日还有多少活跃
深向（细节维度）：
	用户从浏览到购买的整体转化率怎么样
	用户从浏览到购买的路径是怎么样的
	平台主要会给用户推送什么商品
	用户喜欢什么类目？喜欢什么商品
	怎么判断哪些是高价值用户
数据库：mytaobao
*/


#缺失值处理
#1、字段是否有空
SELECT * FROM userbehavior WHERE 用户id IS NULL OR 商品id IS NULL OR 
	商品类目id IS NULL OR 用户行为 IS NULL OR 时间戳 IS NULL;
#2、统计检查，通过行量的统计和单独字段的非空计算
SELECT COUNT(*),COUNT(用户id),COUNT(商品id),COUNT(商品类目id),COUNT(用户行为),COUNT(时间戳)
FROM userbehavior;
/*
若有空值，处理思路：
1）通过前后字段的内容进行优先的自我判断，所在行是否有分析的意义和营销
若通过统计发现空值的量较大（可以先做一个百分比，30%为节点进行判断），则直接
找相关对接人重新获取
2）若有，则填充内容，update更新数据 ，通过where找到指定行
3）若无，则直接delete，通过where找到指定行
*/

#格式处理
#新建日期时间列
ALTER TABLE userbehavior ADD 日期 DATE;
ALTER TABLE userbehavior ADD 时间 TIME;
#为该更添加数据内容，其内容从时间戳中转换出来 的日期（年/月/日），时间（时/分/秒）
UPDATE userbehavior SET 日期= FROM_UNIXTIME(时间戳,"%Y-%m-%d");
UPDATE userbehavior SET 时间= FROM_UNIXTIME(时间戳,"%H-%i-%s");

SELECT * FROM userbehavior;

#重复值处理
/*
使用关键字段联合查询：用户id、时间戳、商品id
检查思路：利用count 和 group by 进行关键字段的分组行统计，若结果行超过1，则说明该行有
重复，再从源数据中查询找到这几个包含值的整行进行删除
*/

SELECT * FROM userbehavior WHERE (用户id,时间戳,商品id) IN (
SELECT 用户id,时间戳,商品id FROM userbehavior_min
GROUP BY 用户id,时间戳,商品id
HAVING COUNT(*) >1
)
/*
若有重复值需要处理，处理思路：
1）纯走SQL处理，给原表一个编号列【要么走自增长主键int约束和类型，要么窗口函数
row_number（）】，筛选每个分组中 编号最大或最小值即可【为的就是获取保留分组中的
数据 行上的唯一一行】 --- 再通过这些编号值+原本的唯一数据行的编号，做delete删除
（利用where 编号 not in(唯一值)
2）利用distinct 进行非重复查询，得到一张唯一的数据表，做另存为。。。后续其余操作均
从该表中进行
3）配合BI工具实现
*/

#异常值处理
/*
通过日期查询，得到数据集中最近一周的数据即可（时间越远，对于需求中的活跃度分析来说月
没有参考价值），同时考虑到电商消费平台，周末的用户量及使用量相对更高一点，其数据也更
具意义--- 故 此数据集中选取最近一周 + 上周末 共9天数据

查询思路：
1、查询目前数据中的最大日期和最小日期
顺道做个检查，目前的数据集是否有满足一周的数据量
2、最大的日期值 手动往前推9天，或 利用 subdate() 函数基于最大的日起至 往前推9天
3、删除小于9天的日期数据
*/

#检查时间
SELECT MAX(日期),MIN(日期) FROM userbehavior;
#使用subdate往前推8天
SELECT MAX(日期),SUBDATE(MAX(日期),8) FROM userbehavior;
#删除小于2017-11-25的数据
DELETE FROM userbehavior WHERE 日期<'2017-11-25';


/*
描述性分析
1、 这个数据集中用户的日活跃和周活跃时间有什么规律吗？
思路：从“时间戳“字段中抽取出“日期”和“小时”的数据，创建一个“活跃时间”字段，并从“行为类
型”中用分组方式把用户的“浏览”“收藏”“加购物车”“购买”行为抽离出来，组成一个视图表【其实就
是按每天的每小时 作为 分组依据维度 统计不同行为的量】，导出到Excel中用透视表分析用户的
日活跃规律和周活跃规律
虽然原表中有日期和时间列，但其均不合适做为 行行为汇总的分组依据维度，一个太广，一
个太细，导致去作为统计其人数形成对比判断 不合适 ，故这种，选择该日期下的小时进行
即 2017-11-25 11 ，方式
1、时间和日期 拼接
2、时间戳直接提炼
*/

#1、新建活跃时间列
ALTER TABLE userbehavior ADD 活跃时间 VARCHAR(255);
#为该列添加数据内容，其内容从时间戳中转换出来
UPDATE userbehavior SET 活跃时间 = FROM_UNIXTIME(时间戳,"%Y-%m-%d %H");
#2、将数据集中的数据按活跃时间进行分组统计，同时按用户行为分类汇总，获取用户行为的漏斗时间分布数据表
#pv ---浏览 ；cart --- 加购物车； fav --- 收藏；buy ---购买
CREATE TABLE 用户活跃时间表 AS(
SELECT 活跃时间,
SUM(IF(用户行为='pv',1,0)) 浏览,SUM(IF(用户行为='cart',1,0)) 加购物车,
SUM(IF(用户行为='fav',1,0)) 收藏,SUM(IF(用户行为='buy',1,0)) 购买
FROM userbehavior GROUP BY 活跃时间
)

/*
结论：
四个行为活跃度曲线整体为上升状态，同为周末，最近的周末比上周末要高，同时在周六的活跃
度达到了最高点
分析结论是否合理【假设验证】
按主观常识来说：周末的使用人数比工作日要高很多 --- 正常，毕竟周末；但同为周末，这周末
比上周末的浏览量大很多
这时候就要考虑其他因素【假设验证】：假设 公司最近是否在做一波 引流活动，若是则可能由该
活动带来的，具体验证：询问下相关部门，再同时配合 新老用户的量比 ，若确实有进行一些活动
的话，其实若单纯以周末作为基础判断来说，此活动有效
*/

/*
2、在当日活跃的用户次日，三日，四日……还有多少活跃？
思路：用户存留的分析可以分为“新用户存留”和“活跃用户存留”
新用户存留一般指：新注册用户在一定时间周期内还会不会再登录 。 --- 前提需要有新用户
的判断依据
活跃用户存留需要根据产品类型和用户场景选择“关键行为”和选择“时间周期”
关键行为：淘宝作为购物网站，用户浏览，收藏，加购，购买商品与交易行为高度相关
都可作为关键行为。
时间周期：淘宝拥有海量的SKU，基本可以满足用户各方面的需求，理论上用户每天都
有购买需求，时间周期可以按天。
实际上这个问题就是在求，数据集第一日在APP有关键行为的用户在第二天，第三天……还会
继续在APP中有关键行为的用户占比，我们需要先列出每用户每天及当天后面又活跃的日
期，用于后面求次日存留，三日存留…… 之后按日期对用户进行分组，并抽取之后9天依然活
跃的用户数量，最后用活跃用户表中后续活跃用户除首日活跃数量乘100加%号


1）创建活跃时间间隔表
思路：列出每用户每天及当天后面又活跃的日期，用于后面求次日存留，三日存留
用自连接行为，左表为 a 【主】，右表为b【辅】，获取每行的主日期【a】 后 该用户 依然活跃
的日期值【即列出了每个用户当前行上的日期(a) 及 该日期之后又出现的日期值（b） --- a.日期
<= b.日期 and a.用户id = b.用户id

*/
CREATE TABLE 活跃时间间隔表 AS(
SELECT a.用户id,a.日期,b.用户id AS id,b.日期 活跃日期
FROM userbehavior a LEFT JOIN userbehavior b 
ON a.用户id=b.用户id AND a.日期<=b.日期
ORDER BY a.用户id,b.用户id
)

/*
2）创建活跃用户留存表
思路：按日期对用户进行分组统计，并计算抽取得到9天内依然活跃的用户数（即相隔2天他还
在，相隔3天他还在。。。）
datediff：计算时间天数间隔
distinct：去重
统计+判断进行不同时间间隔内的人数统计
*/

#2）创建活跃用户留存表
CREATE TABLE 用户活跃时间留存表 AS
SELECT 日期,
COUNT(DISTINCT 用户id) AS 首日用户,
COUNT(DISTINCT IF(DATEDIFF(活跃日期,日期)=1,用户id,NULL)) AS 第二日的用户数,
COUNT(DISTINCT IF(DATEDIFF(活跃日期,日期)=2,用户id,NULL)) AS 第三日的用户数,
COUNT(DISTINCT IF(DATEDIFF(活跃日期,日期)=3,用户id,NULL)) AS 第四日的用户数,
COUNT(DISTINCT IF(DATEDIFF(活跃日期,日期)=4,用户id,NULL)) AS 第五日的用户数,
COUNT(DISTINCT IF(DATEDIFF(活跃日期,日期)=5,用户id,NULL)) AS 第六日的用户数,
COUNT(DISTINCT IF(DATEDIFF(活跃日期,日期)=6,用户id,NULL)) AS 第七日的用户数,
COUNT(DISTINCT IF(DATEDIFF(活跃日期,日期)=7,用户id,NULL)) AS 第八日的用户数,
COUNT(DISTINCT IF(DATEDIFF(活跃日期,日期)=8,用户id,NULL)) AS 第九日的用户数
FROM 活跃时间间隔表
GROUP BY 日期

/*
3）对留存人数表进行统计计算，统计活跃用户留存率
思路：由于sql中没有专门的百分比转换操作，故若想得到比例值（百分比形式），只能通过文本
拼接进行，即用户活跃表中后续的 间隔用户数据/首日用户数 * 100 加 % 号
concat（）：拼接
round(值，小数位)：四舍五入 可固定小数位
*/
CREATE TABLE 统计活跃用户留存率表 AS
SELECT 日期,首日用户,
CONCAT(ROUND((第二日的用户数/首日用户)*100,2),"%") AS 次日留存率 ,
CONCAT(ROUND((第三日的用户数/首日用户)*100,2),"%") AS 二日留存率 ,
CONCAT(ROUND((第四日的用户数/首日用户)*100,2),"%") AS 三日留存率 ,
CONCAT(ROUND((第五日的用户数/首日用户)*100,2),"%") AS 四日留存率 ,
CONCAT(ROUND((第六日的用户数/首日用户)*100,2),"%") AS 五日留存率 ,
CONCAT(ROUND((第七日的用户数/首日用户)*100,2),"%") AS 六日留存率 ,
CONCAT(ROUND((第八日的用户数/首日用户)*100,2),"%") AS 七日留存率 ,
CONCAT(ROUND((第九日的用户数/首日用户)*100,2),"%") AS 八日留存率
FROM 用户活跃时间留存表

/*
结论：
1、首日用户整体增长率（第一天的最后一天的首日用户增长率）：从11-25 到 12-3 日，首日的
活跃用户新增了 （965-695）/ 695 = 39%
2、留存率增长：从2017-11-25 到12-3日直接，基于 当日用户的次日留存比较【即 12-2日的那天
次日留存 与 11-25的次日留存对比 98.23-79.57=18.67%】；以11-25为基准，当日的用户留存 也
在快速增长，最后一天的留存比次日留存要高【11-25的八日留存- 11-25的次日留存】 98.57-
79.57=18%
--- 即整体有上升
分析结论是否合理：
假设随着时间的增长 其留存率的提升 是来源于 策略的优化（比如老会员的召回、或服务优化 或
迎流活动 或 正好是在做一些大型促销活动等），那么若这个增长率是基于该活动背景来说 ，其
合理；但若没有这些活动策略的话，只是常规的营销，其增长率过高，数据有问题
*/


/*
3、用户从浏览到购买的整体转化率怎么样？
思路：将数据集中按不同用户，不同商品维度进行分组获得某一用户行为对某一商品不同行为的数据，
然后对“用户行为漏斗表”中的浏览，加购物车，收藏，购买行为进行分组统计。
1）SQL获取用户行为漏斗数据（即得到每个用户针对不同类目下不同商品的行为次数统计）
pv ---浏览 ；cart --- 加购物车； fav --- 收藏；buy ---购买
*/

CREATE TABLE 用户行为漏斗表 AS(
SELECT 用户id,商品类目id,商品id,
	SUM(IF(用户行为="pv",1,0)) 浏览,SUM(IF(用户行为="cart",1,0)) 加购物车,
	SUM(IF(用户行为="fav",1,0)) 收藏,SUM(IF(用户行为="buy",1,0)) 购买
FROM userbehavior_min GROUP BY 用户id,商品类目id,商品id
)

/*
2）基于上方数据表，统计下整体的漏斗数据（不同行为的总量）
导出总表
*/
CREATE TABLE 用户行为漏斗总表 AS(
SELECT SUM(浏览)浏览,SUM(加购物车) 加购物车,SUM(收藏) 收藏,SUM(购买) 购买
FROM 用户行为漏斗表)
/*
小结：
用户从浏览到购买的整体转化率为 2.34%，具体主要是在哪个环节流失以及 本app上 永久花群体更加习
惯的购买的行为是怎么样的，需要再具体的细化分析了
*/


/*
4、用户从浏览到购买的具体路径是怎么样子的？
思路：穷举所有可能的用户路径，引用“用户行为漏斗表”视图，统计在数据中点击行为大于0，购买行为
大于0，其他两项为0，则判定本用户购买路径为：点击—购买，其他路径同理，多次查询并用Excel表记
录查询数据，用户到PowerBI使用桑基图做可视化。
*/

CREATE TABLE 用户行为路径人数统计 AS(
SELECT '点击->流失' AS 用户行为路径,COUNT(用户id) 人数
FROM 用户行为漏斗表 WHERE 浏览>0 AND 加购物车=0 AND 收藏=0 AND 购买=0
UNION
SELECT '点击->购买' AS 用户行为路径,COUNT(用户id) 人数
FROM 用户行为漏斗表 WHERE 浏览>0 AND 加购物车=0 AND 收藏=0 AND 购买>0
UNION
SELECT '点击->收藏->流失' AS 用户行为路径,COUNT(用户id) 人数
FROM 用户行为漏斗表 WHERE 浏览>0 AND 加购物车=0 AND 收藏>0 AND 购买=0
UNION
SELECT '点击->收藏->购买' AS 用户行为路径,COUNT(用户id) 人数
FROM 用户行为漏斗表 WHERE 浏览>0 AND 加购物车=0 AND 收藏>0 AND 购买>0
UNION
SELECT '点击->收藏->加购物车->购买' AS 用户行为路径,COUNT(用户id) 人数
FROM 用户行为漏斗表 WHERE 浏览>0 AND 加购物车>0 AND 收藏>0 AND 购买>0
UNION
SELECT '点击->收藏->加购物车->流失' AS 用户行为路径,COUNT(用户id) 人数
FROM 用户行为漏斗表 WHERE 浏览>0 AND 加购物车>0 AND 收藏>0 AND 购买=0
UNION
SELECT '点击->加购物车->购买' AS 用户行为路径,COUNT(用户id) 人数
FROM 用户行为漏斗表 WHERE 浏览>0 AND 加购物车>0 AND 收藏=0 AND 购买>0
UNION
SELECT '点击->加购物车->流失' AS 用户行为路径,COUNT(用户id) 人数
FROM 用户行为漏斗表 WHERE 浏览>0 AND 加购物车>0 AND 收藏=0 AND 购买=0)

/*
2）可视化分析 --- 用户行为路径分析
本项目后续会使用桑基图 进行路径的流向可视化，但其需要有固定的数据格式内容与字段，故需要上方
得到的表进行操作，方便流程的制作和理解，先通过excel制作表格 --- 流量分流报表
1）先通过SQL统计下目前的浏览总人数情况【分流的操作需要有个总量，从总量中进行分配出去，本项
目基于浏览开始往后分流的】
*/


/*
5、平台主要会给用户推送什么商品？
思路：
虽然我们没法直接从数据中找到平台推送的数据，但作为平台流量倾斜的商品，浏览量一般都会比其他
商品的浏览量高一些，我们可以引用“用户行为漏斗表”视图统计浏览量前100的商品及其类目
*/
#1、）统计浏览量前100的商品及其类目id
CREATE TABLE 浏览量_top100 AS(
SELECT 商品类目id,商品id,SUM(浏览) AS 浏览量
FROM 用户行为漏斗表 GROUP BY 商品类目id,商品id
ORDER BY 浏览量 DESC LIMIT 100);

SELECT *,SUM(浏览量) OVER(ORDER BY 浏览量 DESC) AS 浏览量累计 FROM 浏览量_top100;


/*
用户喜欢什么类目？喜欢什么商品？
思路：找高转换率的商品
1）得到 浏览 -- 购买 的转换率最高的 前100的商品和类目id，以及其购买量和浏览量
注意：分母有零的情况，算时防止分母为0，对于创建table表会有影响
1）if判断
2）ifnull/iferror 进行
3）where 先做筛选 浏览>0
4）先创建view 视图表（分母为零不影响）
*/
CREATE TABLE 高转换率top100 AS(
SELECT 商品类目id,商品id,SUM(浏览)浏览,SUM(购买)购买,IF(SUM(浏览)>0,SUM(购买)/SUM(浏览),0) 转换率
FROM 用户行为漏斗表 GROUP BY 商品类目id,商品id ORDER BY 转换率 DESC LIMIT 100)

/*
本次分析中的R,F,M具体定义（仅用于演示分析方法，无实际业务参考价值）：
R：根据用户最近一次的购买时间 与 2017-12-3 之间的差值，判断该用户最近一次消费时间间隔
F：将此数据集中该用户在2017-11-25 到 2017-12-3 之间 9天内的购买总次数作为消费频率
M：由于本数据集中未包含金额字段，暂时排除

业务积累：
对于RFM的模型来说，可以有两种计算方式：
1）传统的单纯以 R、F、M对比整张表的R、F、M各自均值作为标准进行的
2）打分机制因为不同的业务背景的用户 消费频率、消费金额、对于他们精细化运营策略与成
本。。。均不同，即其消费周期不能以天来具体固定，故就出现了打分机制行为：

按价值打分	     R			F
	    最近一次消费时间间隔      消费频率
1		7-8天			1-5次
2		5-6天			6-10次
3		3-4天			11-15次
4		小于2天			>=15次
最后再以总分的平均值来作为判断标准
打分标准由 公司制定
*/

#1）从min原表中查询 不同用户ID 的R值【2017年12月3日与最大日期的间隔】 和 F值【行为为buy的次数统计】
CREATE TABLE RF数值表 AS(
SELECT 用户id,DATEDIFF('2017-12-03',MAX(日期)) AS R,SUM(IF(用户行为='buy',1,0)) F 
FROM userbehavior_min WHERE 用户行为="buy" GROUP BY 用户id)
#2）结合打分标准进行打分
CREATE TABLE 用户RF数值分数表 AS
SELECT 用户id,
SUM( CASE
WHEN R BETWEEN 0 AND 2 THEN 4
WHEN R BETWEEN 3 AND 4 THEN 3
WHEN R BETWEEN 5 AND 6 THEN 2
WHEN R BETWEEN 7 AND 8 THEN 1
END) R分数值,
SUM(
CASE
WHEN F BETWEEN 1 AND 5 THEN 1
WHEN F BETWEEN 6 AND 10 THEN 2
WHEN F BETWEEN 11 AND 15 THEN 3
WHEN F >=15 THEN 4
ELSE 0
END
) F价值分数
FROM RF数值表
GROUP BY 用户id
#计算分数的平均分 --- 不考虑更新情况
SELECT AVG(R价值分数),AVG(F价值分数) FROM 用户rf数值分数表;
#或使用窗口函数进行统计该表的平均值后直接新建列与每个用户
SELECT *,
CASE 
WHEN R价值分数>R平均值 AND F价值分数>F平均值 THEN '重要价值客户'
WHEN R价值分数>R平均值 AND F价值分数<F平均值 THEN '重要发展客户'
WHEN R价值分数<R平均值 AND F价值分数>F平均值 THEN '重要保持客户'
WHEN R价值分数<R平均值 AND F价值分数<F平均值 THEN '重要挽留客户'
END AS '客户类型'
FROM(
SELECT *,AVG(R价值分数) OVER() R平均值,AVG(F价值分数) OVER() F平均值
FROM 用户rf数值分数表) AS temp;



/*
一、LTV1~LTVn的定义
LTV1是新用户在注册后一天内产生的平均用户价值，其计算公式为：
LTV1 = 新增用户在新增当天的总付费金额/新增用户数
例如2月1日新增用户100人，其中有6人共付费1000元，那么LTV1为：1000/100 = 10。
LTV2是新用户在注册后2天内产生的平均用户价值，其计算公式为：
LTV2 = 新增用户在新增2天内的累计付费金额/新增用户数
例如2月1日新增用户100人，其中截止到2月2日结束共有10人共付费金额2000元，那么LTV2为：2000/100 = 20


计算这个LTV，一共分为n步：

1、先把每个注册用户从注册日开始，后续每天的付费日期取出来；
2、计算付费日期-注册日期(这个值我管它叫付费日期差)，如果付费日期差=1天，就代表这个注册用户在次日有付费，
	如果付费日期差=2，就代表这个注册用户在第3日有付费，以此类推。
3、计算某一天新增用户在第二天付费过的人数
4、计算各日LTV
*/



#第一步：先把每个注册用户从注册日开始，后续每天的付费日期取出来
SELECT * FROM register a LEFT JOIN pay b ON a.`userid`=b.`userid`;
#第二步：计算付费日期-注册日期（付费日期差）
SELECT a.userid,a.register_date,b.pay_date,DATEDIFF(b.pay_date,a.`register_date`) days
FROM register a LEFT JOIN pay b ON a.`userid`=b.`userid`;
#第三步：计算某一天新增用户在第二天累计付费的金额
SELECT a.`register_date`,
SUM(CASE WHEN DATEDIFF(b.`pay_date`,a.`register_date`)<1 THEN b.`pay_money` ELSE NULL END) sumpay_1,
SUM(CASE WHEN DATEDIFF(b.`pay_date`,a.`register_date`)<2 THEN b.pay_money ELSE NULL END) sumpay_2,
SUM(CASE WHEN DATEDIFF(b.`pay_date`,a.`register_date`)<3 THEN b.pay_money ELSE NULL END) sumpay_3,
SUM(CASE WHEN DATEDIFF(b.`pay_date`,a.`register_date`)<4 THEN b.pay_money ELSE NULL END) sumpay_4,
SUM(CASE WHEN DATEDIFF(b.`pay_date`,a.`register_date`)<5 THEN b.pay_money ELSE NULL END) sumpay_5,
SUM(CASE WHEN DATEDIFF(b.`pay_date`,a.`register_date`)<6 THEN b.pay_money ELSE NULL END) sumpay_6,
SUM(CASE WHEN DATEDIFF(b.`pay_date`,a.`register_date`)<7 THEN b.pay_money ELSE NULL END) sumpay_7,
SUM(CASE WHEN DATEDIFF(b.`pay_date`,a.`register_date`)<14 THEN b.pay_money ELSE NULL END) sumpay_14,
SUM(CASE WHEN DATEDIFF(b.`pay_date`,a.`register_date`)<30 THEN b.pay_money ELSE NULL END) sumpay_30
FROM register a LEFT JOIN pay b ON a.`userid`=b.`userid`
GROUP BY a.`register_date` ORDER BY a.`register_date`;
#第四步：计算各日LTV
/*
第三步只是把累计付费算出来，但是LTV还没有，根据上面公式，LTV1 = 新增用户在新增当天的累计付费金额/新增用户数，、
LTV2 = 新增用户在2天内的累计付费金额/新增用户数，所以还需要把新增用户数给取出来，然后连到第三步结果上，
再进行除法计算就可以啦。
*/
#计算每日注册用户数代码
SELECT register_date,COUNT(DISTINCT userid) cnt1
FROM register GROUP BY register_date;
#根据日期，和第三步结果连起来
SELECT reg.register_date,cnt1,
	ROUND(CAST(sumpay_1 AS FLOAT)/CAST(cnt1 AS FLOAT),2) AS "LTV1",
	ROUND(CAST(sumpay_2 AS FLOAT)/CAST(cnt1 AS FLOAT),2) AS "LTV2",
	ROUND(CAST(sumpay_3 AS FLOAT)/CAST(cnt1 AS FLOAT),2) AS "LTV3",
	ROUND(CAST(sumpay_4 AS FLOAT)/CAST(cnt1 AS FLOAT),2) AS "LTV4",
	ROUND(CAST(sumpay_5 AS FLOAT)/CAST(cnt1 AS FLOAT),2) AS "LTV5",
	ROUND(CAST(sumpay_6 AS FLOAT)/CAST(cnt1 AS FLOAT),2) AS "LTV6",
	ROUND(CAST(sumpay_7 AS FLOAT)/CAST(cnt1 AS FLOAT),2) AS "LTV7",
	ROUND(CAST(sumpay_14 AS FLOAT)/CAST(cnt1 AS FLOAT),2) AS "LTV14",
	ROUND(CAST(sumpay_30 AS FLOAT)/CAST(cnt1 AS FLOAT),2) AS "LTV30"
FROM(SELECT register_date,COUNT(DISTINCT userid) cnt1 FROM register GROUP BY register_date) reg
LEFT JOIN(
SELECT a.`register_date`,
ROUND(SUM(CASE WHEN DATEDIFF(b.`pay_date`,a.`register_date`)<1 THEN b.`pay_money` ELSE NULL END),2) sumpay_1,
ROUND(SUM(CASE WHEN DATEDIFF(b.`pay_date`,a.`register_date`)<2 THEN b.pay_money ELSE NULL END),2) sumpay_2,
ROUND(SUM(CASE WHEN DATEDIFF(b.`pay_date`,a.`register_date`)<3 THEN b.pay_money ELSE NULL END),2) sumpay_3,
ROUND(SUM(CASE WHEN DATEDIFF(b.`pay_date`,a.`register_date`)<4 THEN b.pay_money ELSE NULL END),2) sumpay_4,
ROUND(SUM(CASE WHEN DATEDIFF(b.`pay_date`,a.`register_date`)<5 THEN b.pay_money ELSE NULL END),2) sumpay_5,
ROUND(SUM(CASE WHEN DATEDIFF(b.`pay_date`,a.`register_date`)<6 THEN b.pay_money ELSE NULL END),2) sumpay_6,
ROUND(SUM(CASE WHEN DATEDIFF(b.`pay_date`,a.`register_date`)<7 THEN b.pay_money ELSE NULL END),2) sumpay_7,
ROUND(SUM(CASE WHEN DATEDIFF(b.`pay_date`,a.`register_date`)<14 THEN b.pay_money ELSE NULL END),2) sumpay_14,
ROUND(SUM(CASE WHEN DATEDIFF(b.`pay_date`,a.`register_date`)<30 THEN b.pay_money ELSE NULL END),2) sumpay_30
FROM register a LEFT JOIN pay b ON a.`userid`=b.`userid`
GROUP BY a.`register_date` ORDER BY a.`register_date`) LOG
ON reg.register_date=log.register_date;

用户生命周期

  1. 这个是按月维度计算留存率，首先是将所有用户按首次下单时间的年月分组，得到每个月在这个月第一次下单的客户。如果按下单时间的年月分组，后面的用户数量会包含前辈的用户数量。
  2. 然后计算活跃用户留存表，先按首次下单年月分组，得到首次下单月份不同的用户，计算这些用户在之后的每个月的留存用户数
  3. 然后计算活跃留存率表，也是按首次下单年月进行分组
with 活跃时间间隔表 as(
select a.下单人手机号,a.下单时间,b.下单人手机号 as 手机号,b.下单时间 as 活跃日期
,min(date_format(a.下单时间,"%Y-%m")) over(partition by a.下单人手机号) as 首次下单年月
from 商城客户订单 a left join 商城客户订单 b
on a.下单人手机号=b.下单人手机号 and a.下单时间<=b.下单时间
order by a.下单人手机号,a.下单时间,b.下单人手机号,b.下单时间
)#select * from 活跃时间间隔表
,活跃用户留存表 as(
select 首次下单年月,#date_format(下单时间,"%Y-%m") as 下单年月,
count(distinct 下单人手机号) as 首月用户,
count(distinct if(timestampdiff(month,date_format(下单时间,"%Y-%m-01"),
    date_format(活跃日期,"%Y-%m-01"))=1,下单人手机号,null)) as 第二月的用户数,
count(distinct if(timestampdiff(month,date_format(下单时间,"%Y-%m-01"),
    date_format(活跃日期,"%Y-%m-01"))=2,下单人手机号,null)) as 第三月的用户数,
count(distinct if(timestampdiff(month,date_format(下单时间,"%Y-%m-01"),
    date_format(活跃日期,"%Y-%m-01"))=3,下单人手机号,null)) as 第四月的用户数,
count(distinct if(timestampdiff(month,date_format(下单时间,"%Y-%m-01"),
    date_format(活跃日期,"%Y-%m-01"))=4,下单人手机号,null)) as 第五月的用户数,
)#select * from 活跃用户留存表
,活跃留存率表 as(
select 首次下单年月,首月用户,#下单年月
concat(round((第二月的用户数/首月用户)*100,2),"%") as 次月留存率,
concat(round((第三月的用户数/首月用户)*100,2),"%") as 二月留存率,
concat(round((第四月的用户数/首月用户)*100,2),"%") as 三月留存率,
concat(round((第五月的用户数/首月用户)*100,2),"%") as 四月留存率,
concat(round((第六月的用户数/首月用户)*100,2),"%") as 五月留存率,
from 活跃用户留存表
)
select * from 活跃留存率表
avatar
文章
阅读
粉丝