《CDA一级教材》在线电子版正式上线CDA网校,为你提供系统、实用、前沿的学习资源,助你轻松迈入数据分析的大门!
免费加入阅读:https://edu.cda.cn/goods/show/3151?targetId=5147&preview=0
做数据分析的,为什么要写SQL?
没有数据的情况下,我们分析数据就像是巧妇难为无米之炊。因此,为了进行数据分析,我们必须获取数据。而大多数情况下,数据都存放在数据库中,这时候我们就必须要学会SQL取数了。
除了一部分公司专人专岗,有人帮你查好数据发你做分析,大部分情况还是需要你自己取数的。
本次我们沿用之前《极简统计学入门》的“MVP”思路,用三节的内容梳理一下SQL(基于MySQL8.0),整个系列框架如下
我们知道,SQL(结构化查询语言)是一种数据库语言,按照功能分类,有DDL、DQL、DML、DCL、TCL 五大类型,简单了解一下它们分别是做什么用的:
(1)DDL(Data Definition Language)
DDL是数据定义语言,主要用来定义或者改变表的结构。例如:create、alter、drop、truncate
等语句。
(2)DQL(Data Query Language)
DQL是数据查询语言,主要用来从表中检索数据。例如:select
语句。
(3)DML(Data Manipulation Language)
DML是数据操作语言,主要用来对数据库里表中的数据进行操作。例如:insert、delete、update
等语句。
(4)DCL(Data Control Language)
DCL是数据控制语言,主要用来设置或更改数据库用户或角色对数据的访问权限。例如:grant、revoke
等语句。
(5)TCL(Transaction Control Language)
TCL是事务控制语言,主要用来控制事务。例如:COMMIT、ROLLBACK
等语句。
了解了以上分类,我们简单回顾一下其中DDL、DML、DQL的基础语法(有基础的可直接跳过看SQL查询与执行顺序)
定义数据库当中的对象 (库、表) 关键字: create
、delete
、alter
、show
create database 数据库名
create database 数据库名 charset set 字符集编码
create database if not exist数据库名
show database;
show create database 数据库名;
drop database 数据库名;
drop database 数据库名 if exist;
use 数据库名;
create table table_name
(
column_1 int null,
column_2 int null
);
create table 新表名 like 被复制的表名;
执行上述语句后,将创建一个名为table2的新表,其结构与table1完全相同,但不会复制table1中的任何数据。
create table 新表名 as select * from 被复制的表名;
上述语句将创建一个名为table2的新表,其结构和数据与table1完全相同。
show tables;
describe 表名;
show create table 表名;
alter table 表名 add column (字段名 字段类型,字段名 字段类型);
alter table 表名 modify column 字段名 字段类型;
alter table 表名 change column 旧字段名 新字段名 字段类型;
alter table 表名 drop column 字段名;
alter table 表名 rename 新表名;
操作数据 (增、删、改) 关键字: insert
、delete
、update
insert into 表名 (字段名1,字段名2) values(数值1,数值2);
insert into 表名 values (数值1,···,最后一个数值);
insert into 表名 values
(数值1_1,数值2_1···,最后一个数值n_1),
(数值1_2,数值2_2···,最后一个数值n_2),
(数值1_n,数值2_n···,最后一个数值n_n);
delete from 表名 where 字段名 = 字段值;
truncate table 表名;
drop table 表名;
drop
用于删除数据库对象,包括定义和数据。truncate
用于删除表中的所有数据,但保留表的定义。delete
用于删除表中的行,可以根据条件删除特定的数据,并且可以回滚。update 表名 set 字段名 = 数据值 where 字段名 = 数据值;
update 表名 set 字段名1=数据值1,字段名2=数据值2 where 字段名 = 数据值;
select * from 表名;
select 字段名 as 别名 from 表名;
select 字段名 as 自定义名字,临时常量 as 别名 from 表名;
select 字段名 as 自定义名字,(字段 1 + 字段2) as 别名 from 表名;
< # 小于
> # 大于
<= # 小于等于
>= # 大于等于
<> # 不等于
!= # 不等于
between…and… # 在……范围内
in # 包括
like # 模糊查询
is null # 是否为空
and # 且
or # 或
not # 非
select * from 表名 where 字段名 like "关键词";
select * from 表名 where 字段名 like "%hello%";
select * from 表名 where 字段名 like "%_大学%";
select max(字段名) from 表名;
select min(字段名) from 表名;
select avg(字段名) from 表名;
select sum(字段名) from 表名;
select count (字段名) from 表名;
select *
from table_class
order by id desc;
select class_name,count(*)
from table_class
group by class_name;
select class_name,count(*)
from table_class
group by class_name
having count (*)>=2;
两张表交叉后并且过滤后的数据查询 (交集)关键字: inner join
select *
from table_a a
inner join table_b b
on a.aid = b.bid;
左表 (table_a) 的记录将会全部表示出来,而右表 (table_b) 只会显示符合搜索条件的记录,右表记录不足的地方均为 NULL 关键字: left join。
select *
from table_a a
left join table_b b
on a.a_id = b.b_id;
左表 (a_table) 只会显示符合搜索条件的记录,而右表 (b_table) 的记录将会全部表示出来,左表记录不足的地方均为 null 关键字: right join
select *
from table_a a
right join table_b b
on a.a_id = b.b_id;
(select colum_1,colum_2,...,colum_n
from table_a)
union
(select colum_1,colum_2,...,colum_n
from table_b)
两个select语句具有相同的列数和相似的数据类型。如果列数不匹配,可以使用null或者空字符串填充缺失的列
使用 union 时,数据完全相同的记录,将会被合并,由于合并比较耗时,一般不直接使用 union 进行合并,而是采用 union all进行合并。
(select id,name from table_a
order by id)
union all
(select id,name from table_b
order by id);
#没有排序效果
(select id,name from table_a )
union all
(select id,name from table_b )
order by id;
#有排序效果
将一个 SQL 语句的查询结果 (单列数据) 作为另一个 SQL 语句的查询条件。
select *
from table_a
where id_a in
(select id_b
from table_a);
好了,以上内容,我们简单回顾了一下SQL的基本函数,下面我们开始正式内容:
如果你接触过不同编程语言就会发现,任何编程语言的学习,都离不开3个最基本的核心要素,数据类型、流程控制、函数
数据类型是用来描述数据的性质和特征的,它决定了数据在计算和处理过程中的行为和规则。常见的数据类型包括整数、浮点数、字符串、日期等。简而言之,数据类型就是你将要操作的东西具有什么样的特点。
流程控制是指通过条件判断和循环等方式,控制程序按照一定的顺序执行不同的操作步骤。它决定了数据的处理流程,包括判断条件、循环次数、分支选择等。简而言之,流程控制解决的问题就是你要操作这个东西的基本流程是什么。
函数是一段预先定义好的代码,用于执行特定的操作或计算。它接受输入参数,并返回一个结果。函数可以用来对数据进行各种计算、转换、筛选等操作,以满足特定的需求。简而言之,函数解决的问题就是你要怎么样才能可复用地操作这一类东西。
SQL极简教程系列我们重点讨论数据类型与函数,下面我们先来看第一个核心要素:
整数类型 | 用途 | 范围 |
---|---|---|
tinyint | 用于存储小整数值 | -128到127,即()到() |
smallint | 用于存储较小的整数值 | -32768到32767 ,即()到() |
mediumint | 用于存储中等大小的整数值 | -8388608到 8388607 ,即()到() |
int | 用于存储普通大小的整数值 | -2147483648到2147483647,即 ()到() |
bigint | 用于存储大整数值 | -9223372036854775808到9223372036854775807,即 () 到 () |
浮点类型 | 用途 | 范围 |
---|---|---|
float | 用于存储单精度浮点数值 | -3.402823466E+38到-1.175494351E-38,0,1.175494351E-38到3.402823466E+38 |
double | 用于存储双精度浮点数值 | -1.7976931348623157E+308到-2.2250738585072014E-308,0,2.2250738585072014E-308到1.7976931348623157E+308 |
数据类型 | 用途 | 特点 |
---|---|---|
char | 用于存储固定长度的字符串 | 存储的字符串长度固定,最多可以存储255个字符 |
varchar | 用于存储可变长度的字符串 | 存储的字符串长度可变,最多可以存储65535个字符 |
binary | 用于存储二进制数据 | 存储的数据以二进制形式存储,最多可以存储255个字节 |
varbinary | 用于存储可变长度的二进制数据 | 存储的数据以二进制形式存储,长度可变,最多可以存储65535个字节 |
text | 用于存储较长的文本数据 | 存储的文本数据长度可变,最多可以存储65535个字符 |
blob | 用于存储较大的二进制数据 | 存储的二进制数据长度可变,最多可以存储65535个字节 |
数据类型 | 用途 | 范围 |
---|---|---|
date | 用于存储日期值 | '1000-01-01'到'9999-12-31' |
time | 用于存储时间值 | '-838:59:59'到'838:59:59' |
datetime | 用于存储日期和时间值 | '1000-01-01 00:00:00'到'9999-12-31 23:59:59' |
timestamp | 用于存储日期和时间值,自动更新 | '1970-01-01 00:00:01' UTC到'2038-01-19 03:14:07' UTC |
year | 用于存储年份值 | 1901到2155 |
如果上面内容看明白了,恭喜你已经学会了如何描述你要操作的对象的特点了,接着我们看第二个核心问题:函数。一般无外乎针对字符串的函数、针对日期的函数、针对数值运算的函数、以及操作数据转化的函数:
**字符串函数:**返回字符串的长度
select length('learn_mysql_and_find_a_data_analysis_job') str_len;
**字符串连接函数:**返回输入字符串连接后的结果,支持任意个输入字符串
select concat('Certified','Data','Analyst') as str_concat;
带分隔符字符串连接函数: 返回输入字符串连接后的结果,SEP表示各个字符串间的分隔符
select concat_ws('_','Certified','Data','Analyst') as str_concat_ws;
字符串截取函数: 返回字符串从start位置到结尾的字符串
select substr('Certified_Data_Analyst',11);
select substr('Certified_Data_Analyst',-12);
字符串截取函数: 返回字符串从start位置开始,长度为len的字符串
select substr('Certified_Data_Analyst',11,4);
select substring('Certified_Data_Analyst',-7,7);
字符串转大写函数:upper,ucase 返回字符串A的大写格式
select upper('certified_data_analyst');
select ucase('certified_data_analyst');
字符串转小写函数:lower,lcase 返回字符串A的小写格式
select lower('CERTIFIED_DATA_ANALYST');
select lcase('CERTIFIED_DATA_ANALYST');
**字符串反转函数:**返回字符串的反转结果
select reverse('learn_mysql') as str_rev;
去空格函数:trim 去除字符串两边的空格
select trim(' Data ');
左边去空格函数:ltrim去除字符串左边的空格
select ltrim(' Data ');
右边去空格函数:rtrim 去除字符串右边的空格
select rtrim(' Data '); `
空格字符串函数:space 返回长度为n的字符串
select space(10);
select length(space(10));
重复字符串函数:repeat 返回重复n次后的str字符串
select repeat('SQL',5);
左补足函数:lpad 将str进行用pad进行左补足到len位
select lpad('MySQL',11,'go');
右补足函数:rpad 将str进行用pad进行右补足到len位
select rpad('MySQL',11,'go');
分割字符串函数: mysql里面没有直接做字符串分割的函数,substring_index 按照pat字符串分割str,会返回分割后的字符串数组
select substring_index('Certified_Data_Analyst', '_', 1) AS part1,
substring_index(substring_index('Certified_Data_Analyst', '_', 2), '_', -1) AS part2,
substring_index(substring_index('Certified_Data_Analyst', '_', 3), '_', -1) AS part3;
集合查找函数: find_in_set 返回str在strlist第一次出现的位置,strlist是用逗号分割的字符串。如果没有找该str字符,则返回0
select find_in_set('data', 'certified,data,analyst');
select find_in_set('mysql','certified,data,analyst');
正则表达式替换函数:regexp_replace将字符串A中的符合java正则表达式B的部分替换为C。注意,在有些情况下要使用转义字符
select regexp_replace('learn_mysql_and_python', 'mysal|python', 'programming');
正则表达式提取函数:regexp_extract 返回第一个匹配的子字符串
select regexp_substr('mysql8', '[0-9]+') extracted_number;
四舍五入:round
select round(3.14159); # round(a) 返回a的值,并对a四舍五入
select round(3.14159,3); # round(a, n) 返回保留n小数位和四舍五入后的a的值
向上取整:ceil
select ceil(3.14);
select ceiling(3.14);
select ceil(-3.14);
向下取整:floor
select floor(3.14);
select floor(-3.14);
求取随机数:rand
select rand(); # 每行返回一个double型随机数
select rand(100); # 每行返回一个double型随机数,整数seed是随机因子的种子;
其他数学运算函数
# exp(d) #返回e的 d幂次方,返回double型;
select exp(1); # e的1次方
# 2.718281828459045
# ln(d) #以自然数为底d的对数,返回double型;
select ln(exp(1)); # 以e为底e的对数
# 1
# log2(d) #以2为底d的对数,返回double型;
select log2(8);
# 3
# log10(d) #以10为底d的对数,返回double型;
select log10(100);
# 2
# log(a, b) #以a为底b的对数,返回double型;
select log(3,9);
# 2
# pow(x, n) # x 的n次幂,返回double型;
select pow(10,2);
# 100
# sqrt(DOUBLE d) #d的平方根,返回double型;
select sqrt(16);
# 4
# abs(DOUBLE d) #返回d的绝对值,结果为double型;
select abs(-3.14);
# 3.14
# sin() #返回d的正弦值,结果为double型;
select sin(radians(30));
# 0.49999999999999994
# cos() #返回d 的余弦值,结果为double型;
select tan(radians(60));
# 0.5000000000000001
# tan() #返回d的正切值,结果为double型;
select tan(radians(45));
# 0.9999999999999999
# asin() #返回d的反正弦值,结果为double型;
select degrees(asin(0.5));
# 30.000000000000004
# acos() #返回d的反余弦值,结果为double型;
select degrees(acos(0.5));
# 60.00000000000001
# atan() #返回d的反正切值,结果为double型;
select degrees(atan(1));
# 45
# PI() #数学常数Pi,圆周率;
select PI();
# 3.141593
获取日期 date() 返回时间字符串的日期部分
select date('2023-09-21 15:06:51');
获取年月日
year()、month()、day() 从一个日期中取出相应的年、月、日
select
year('2023-09-21 15:06:51'),
month('2023-09-21 15:06:51'),
day('2023-09-21 15:06:51');
获取第几周
weekofyear() 返回输入日期在该年中是第几个星期
select weekofyear('2023-09-21 15:06:51');
获取指定间隔的日期
#请问,2023-09-21起,7天以后的日期是?7天前的日期是?
select date_add('2023-09-21',interval 7 day) as seven_days_after,
date_sub('2023-09-21',interval 7 day ) as seven_days_before;
# 当前日期,7天以后的日期是?7天前的日期是?
select date_add(current_date(),interval 7 day) as seven_days_after, date_sub(current_date(),interval 7 day) as seven_days_before;
获取两个日期之差
返回的是数字
select datediff('2023-09-21 15:06:51','2003-09-21 15:06:51');
# 7305
select current_date();
# 2023-09-21
日期时间格式化
#将“2023-09-21 15:06:51”转化如下格式
select date_format('2023-09-21 15:06:51', '%Y-%m-%d');
select date_format('2023-09-21 15:06:51', '%Y-%M-%D');
select date_format('2023-09-21 15:06:51', '%M-%d-%y');
select date_format('2023-09-21 15:06:51', '%m/%d/%y');
select date_format('2023-09-21 15:06:51', '%m/%d/%Y %H:%i:%s');
select date_format('2023-09-21 15:06:51', '%Y年%m月%d日 %H点%i分%s秒');
附:MySql查询当天、本周、本月、本季度、本年
# 1.今天
select to_days(now());
# 2.昨天
select to_days(now()) - 1 ;
# 3.本周
select yearweek(now());
# 4.上周
select yearweek(now()) -1;
# 5.往回推,7天前的时间
select date_sub(current_date(), interval 7 day);
# 6.往回推,30天前的时间
select date_sub(current_date(), interval 30 day);
# 7.本月
select date_format(current_date(),'%Y%m');
# 8.上月
select date_format(date_sub(current_date(),interval 1 month),'%Y%m') ;
# 9.近6个月
select date_sub(current_date(),interval 6 month);
# 10.本季度
select quarter(current_date());
# 11.上季度
select quarter(date_sub(current_date(),interval 1 quarter));
# 12.今年
select year(now());
13.去年
select year(date_sub(now(),interval 1 year));
类型转换函数 double、date、char
# 将字符'3.14'转换为double数值类型
select cast('3.14' as double);
# 将字符串'2023-09-21'转换为date类型
select cast('2023-09-21' as date);
interval(a,n1,n2,n3,...);
其中,a是要判断的数值,n1,n2,n3,...是分段的间隔。这个函数的返回值是段的位置:如果比n1还小,则返回0,如果在n1和n2中间,则返回1,如果n2<=a<n3,则返回2。
select interval(1, 3, 7, 10);
# 0
select interval(5, 3, 7, 10);
# 1
select interval(9, 3, 7, 10);
# 2
interval关键词
select now()-interval '2' hour;
函数if
select if(80 > 60,'及格','未及格'); 条件表达式为真返回1,为假返回2
非空查找 coalesce
coalesce(v1,v2,…) 返回参数中的第一个非空值;如果所有值都为null,那么返回 null
select coalesce('Certified',null, 'Analyst');
select coalesce(null, 'Data','Analyst');
判断是否为null
isnull() 判断是否为null
select isnull(null);
select isnull('');
条件判断 case...when...
select name,
case
when score >= 80 then '优秀'
when score >= 70 and score < 80 then '良好'
when score >= 60 and score < 70 then '及格'
else '未及格'
end as score_label
from cda_exam;
看完了数据类型与函数,我们来了解一下SQL的执行顺序。了解SQL的执行顺序,不仅有助于深入理解SQL的执行过程,还能在处理异常时快速确定问题所在。
下面看一个包含常用SQL关键词的语句模板:
select distinct column_name,
agg_func(column_name_or_expression)
from table_a a
join table_b b
on a.column_name = b.column_name
where constraint_expression
group by column_name
having constraint_expression
order by column_name asc/desc
limit count offset count;
从指定的表中选择数据
从数据进行过滤。 注意:as 列别名还不能在这个阶段使用,因为这时候select还没执行,别名是一个还没执行的表达式
按指定的列对数据进行分组。
对group by 子句中分组后的数据进行过滤。as 列别名也不能在这个阶段使用。
选择要返回的列,决定输出什么数据。
如果数据行有重复 distinct 将负责排重
对结果做排序。此时可以用 as 别名了,select 中的表达式已经执行完了。
限制结果集的数量。 limmit a,b 等价于 limit b offset a
日拱一卒,功不唐捐。你所有的奋斗都不会白费!
窗口分析函数简介
我们知道,OLAP联机分析处理和OLTP联机事务处理是两种常见的数据库处理方式,通常情况下,分析师更喜爱OLAP(分析),开发者更关注的是OLTP(事务)
窗口分析函数可以计算一定范围内、一定值域内、或者一段时间内的累积和以及移动平均值等,可以方便的实现复杂的数据统计分析需求。
lag, lead, first_value, last_value
学过Python的同学都知道,这个函数与pandas的
shift()
十分相似
lag(col,n,default) 函数的作用是返回某列的值向下平移n行后的结果。
本节数据/SQL下载:回复“SQL3” user_pv表的建表及数据插入SQL如下(也可以选择网盘下载后导入,二选一即可):
例如:对每个用户当天浏览次数与前一天的浏览次数进行比较
select uid,
dt,
pv,
lag(pv, 1, 0) over (partition by uid order by dt) as lag_1_pv
from user_pv
order by uid,dt
lead:函数的作用是返回某列的值向上平移n行后的结果。 第一个参数为列名 第二个参数为当前行后面第n行(可选,默认为1) 第三个参数为缺失时默认值(当前行后面第n行为没有时,返回该默认值,如不指定,则为NULL)。
例如:比较每个用户当天浏览次数和后一天的浏览次数。
select uid,
dt,
pv,
lead(pv, 1, 0) over ( partition by uid order by dt) as lead_1_pv
from user_pv
order by uid,dt;
例如:比较每个用户当天浏览次数与第一天浏览次数。
select uid,
dt,
pv,
first_value(pv) over (partition by uid order by dt) first_value_pv
from user_pv
order by uid,dt;
注:上面例子窗口为第一行到当前行(缺失窗口子句,有order by ,默认为rows between unbounded preceding and current row)。 所以,first_value返回窗口的第一行,即第一天浏览次数。
例如:比较每个用户当天浏览次数与最后一天浏览次数进行比较。
select uid,
dt,
pv,
last_value(pv) over (partition by uid order by dt rows between current row and unbounded following) last_value_pv
from user_pv
order by uid,dt;
注:上面例子的窗口为当前行到最后一行(rows between current row and unbounded following)。 last_value返回的是窗口最后一行,即最新一天的浏览次数。
分析函数 row_number, rank, dense_rank、cume_dist, percent_rank, ntile
按顺序排序,排序的值不会重复,总数不变;
select uid,
dt,
pv,
row_number() over (partition by uid order by pv desc) as row_number_pv
from user_pv
order by uid, pv desc;
大小一样排序的值一样,但会占用排名的位置,总数不变; 下面对用户每天浏览量进行一个排名。
select uid,
dt,
pv,
rank() over (partition by uid order by pv desc) as rank_pv
from user_pv
order by uid, pv desc;
排序值相同时重复,排名并列,排名依次增加,排序相同时总数会减少; 例如,如果两行排名为3,则下一个排名为4,不同于RANK()函数返回5。 下面对用户每天浏览量进行一个排名:
select uid,
dt,
pv,
dense_rank() over (partition by uid order by pv desc) dense_rank_pv
from user_pv
order by uid, pv desc;
对比看下,row_number, rank, dense_rank的运行效果:
select uid,
dt,
pv,
row_number() over (partition by uid order by pv desc) as row_number_pv,
rank() over (partition by uid order by pv desc) as rank_pv,
dense_rank() over (partition by uid order by pv desc) dense_rank_pv
from user_pv
order by uid, pv desc;
总结来说,ROW_NUMBER函数为每一行分配唯一的行号,而RANK函数和DENSE_RANK函数在处理具有相同排序值的行时有所不同。RANK函数会跳过下一个排名,而DENSE_RANK函数会紧随其后。选择使用哪个函数取决于具体的需求和对重复值的处理方式。
累积分布cume_dist()函数,用于计算当前行在排序结果中的累积分布比例。
# 4、5的合并案例
select uid,
dt,
pv,
cume_dist() over (partition by uid order by pv) cume_dist_pv
from user_pv
order by uid, pv;
非常类似于cume_dist函数。同样用于计算当前行在排序结果中的累积分布比例。
select uid,
dt,
pv,
percent_rank() over (partition by uid order by pv) as percent_rank_uv
from user_pv
order by uid, pv;
对比看下,cume_dist和 percent_rank函数的运行效果:
select uid,
dt,
pv,
cume_dist() over (partition by uid order by pv) cume_dist_pv,
percent_rank() over (partition by uid order by pv) as percent_rank_uv
from user_pv
order by uid, pv;
学过Python的同学都知道,组内分桶,不就是组内
pd.cut()
么
ntile()函数,将每个分区的行尽可能均匀地划分为指定数量的分组。
例如,ntile(4)表示划分为4个分组,分组取决于over子句中的order by子句。
select uid,
dt,
pv,
ntile(4) over (partition by uid order by pv) as nt_pv
from user_pv;
现有用户登录表(user_active_log)一份,里面有2个字段:userID(用户ID),createdTime(登录时间戳),需要统计2021年12月连续登录7天的用户数量。
分析过程:
题目要求的核心是连续登录,那么我们思考,何为连续登录呢?
顾名思义,连续登录就是指登录的日期连续,那么用数据库的语言来表达的话,我们该描述表达日期连续呢?
我们简化一下数据来考虑这个问题,一般我们有2个办法:
构造一个连续数字构成的辅助列,用原始日期减去辅助列的数字,得到一个新日期,根据这个新日期来判断是否连续;
或者构造一个连续日期构成的辅助列,用原始日期减去这个辅助列的日期,得到一个新数字,最后根据这个数字来判断连续。
这里分别展示两种思路如下:
方法A:
日期 | 辅助列 | 新日期 |
---|---|---|
2021-12-02 | 1 | 2021-12-01 |
2021-12-03 | 2 | 2021-12-01 |
2021-12-04 | 3 | 2021-12-01 |
2021-12-05 | 4 | 2021-12-01 |
2021-12-06 | 5 | 2021-12-01 |
2021-12-07 | 6 | 2021-12-01 |
方法B:
日期 | 辅助列 | 新数字 |
---|---|---|
2021-12-02 | 2021-12-01 | 1 |
2021-12-03 | 2021-12-02 | 1 |
2021-12-04 | 2021-12-03 | 1 |
2021-12-05 | 2021-12-04 | 1 |
2021-12-06 | 2021-12-05 | 1 |
2021-12-07 | 2021-12-06 | 1 |
一般我们为了方便统计某一起始时间连续登录了多少天,多半采用方案A。
我们接着看,刚才知道了如何在SQL里面如何描述连续登录,接下来我们逐步按照题目要求拆解即可:
select userID,date(from_unixtime(createdTime)) a_createdTime
from user_active_log
where substr(date(from_unixtime(createdTime)),1,7) = '2021-12' # 知识点1:时间戳转为时间字符串格式然后取前7个字符
group by userId,date(from_unixtime(createdTime)) # 知识点2:根据userId,a_createdTime 去重
运行结果如下:
select userID,a_createdTime,row_number() over(partition by userId order by a_createdTime) a_rk # 知识点3:用row_number() 对每个userID下的a_createdTime进行排名
from
(
select userID,date(from_unixtime(createdTime)) a_createdTime
from user_active_log
where substr(from_unixtime(createdTime),1,7) = '2021-12'
group by userId,a_createdTime) t0
运行结果如下:
select *,date_sub(a_createdTime,interval a_rk day ) b_createdTime # 知识点4:date_sub 日期减去数字;datediff 日期减日期
from
(
select userID,a_createdTime,row_number() over(partition by userId order by a_createdTime) a_rk
from
(
select userID,date(from_unixtime(createdTime)) a_createdTime
from user_active_log
where substr(from_unixtime(createdTime),1,7) = '2021-12'
group by userId,a_createdTime) t0 )t1
运行结果如下:
select userId,b_createdTime,count(1) cts
from
(select *,date_sub(a_createdTime,interval a_rk day ) b_createdTime
from
(
select userID,a_createdTime,row_number() over(partition by userId order by a_createdTime) a_rk
from
(
select userID,date(from_unixtime(createdTime)) a_createdTime
from user_active_log
where substr(from_unixtime(createdTime),1,7) = '2021-12'
group by userId,a_createdTime) t0 ) t1 ) t2 group by userId,b_createdTime having count(1)>6 # 知识点5:having 用在groupby后做条件筛选
运行结果如下:
select count(distinct userId) num_users
from
(select userId,b_createdTime,count(1) cts
from
(select *,date_sub(a_createdTime,interval a_rk day ) b_createdTime
from
(
select userID,a_createdTime,row_number() over(partition by userId order by a_createdTime) a_rk
from
(
select userID,date(from_unixtime(createdTime)) a_createdTime
from user_active_log
where substr(from_unixtime(createdTime),1,7) = '2021-12'
group by userId,a_createdTime
) t0
) t1
) t2 group by userId,b_createdTime having count(1)>6
) t3;
运行结果如下:
现有用户登录表(user_active_log)一份,里面有2个字段:userID(用户ID),createdTime(登录时间戳),需要统计近1、2、3、5、7、30日留存用户数量及留存率。
分析过程:
题目要求的核心是近N日留存,那么我们思考,何为近N日留存呢?
顾名思义,就是指距离某个日期的间隔为N,那么用数据库的语言来表达的话,我们该描述表达近N日留存呢?
我们简化一下数据来考虑这个问题:
构造一个起始日期构成的辅助列,用原始日期减去辅助列的日期,得到一个新数字N,根据这个新数字,结合起始日期来判断某个日期的近N日留存;
日期 | 辅助列 | 新数字 |
---|---|---|
2021-12-02 | 2021-12-01 | 1 |
2021-12-03 | 2021-12-01 | 2 |
2021-12-04 | 2021-12-01 | 3 |
2021-12-05 | 2021-12-01 | 4 |
2021-12-06 | 2021-12-01 | 5 |
2021-12-07 | 2021-12-01 | 6 |
知道了如何在SQL里面如何描述连续登录,接下来我们逐步按照题目要求拆解即可:
select
userID,
date(from_unixtime(createdTime)) a_createdTime
from user_active_log
group by userId,a_createdTime;
运行结果截图如下:
select
userID,
a_createdTime,
first_value(a_createdTime) over(partition by userId order by a_createdTime ) first_time
from
(
select userID,date(from_unixtime(createdTime)) a_createdTime
from user_active_log
group by userId,a_createdTime
)t0;
运行结果截图如下:
select
userID,
a_createdTime,
first_value(a_createdTime) over(partition by userId order by a_createdTime ) first_time,
datediff(a_createdTime, first_value(a_createdTime) over(partition by userId order by a_createdTime )) delta_time
from
(
select userID,date(from_unixtime(createdTime)) a_createdTime
from user_active_log
group by userId,a_createdTime
)t0;
运行结果截图如下:
select
t1.first_time,
sum( case when t1.delta_time = 1 then 1 else 0 end) day_1,
sum( case when t1.delta_time = 2 then 1 else 0 end) day_2,
sum( case when t1.delta_time = 3 then 1 else 0 end) day_3,
sum( case when t1.delta_time = 5 then 1 else 0 end) day_5,
sum( case when t1.delta_time = 7 then 1 else 0 end) day_7,
sum( case when t1.delta_time = 30 then 1 else 0 end ) day_30
from
(
select
userID,
a_createdTime,
first_value(a_createdTime) over(partition by userId order by a_createdTime ) first_time,
datediff(a_createdTime, first_value(a_createdTime) over(partition by userId order by a_createdTime )) delta_time
from
(
select userID,date(from_unixtime(createdTime)) a_createdTime
from user_active_log
group by userId,a_createdTime
)t0
) t1
group by t1.first_time
order by t1.first_time;
select
t1.first_time,
sum( case when t1.delta_time = 1 then 1 else 0 end ) / count(distinct t1.userID) day_1,
sum( case when t1.delta_time = 2 then 1 else 0 end ) / count(distinct t1.userID) day_2,
sum( case when t1.delta_time = 3 then 1 else 0 end ) / count(distinct t1.userID) day_3,
sum( case when t1.delta_time = 5 then 1 else 0 end ) / count(distinct t1.userID) day_5,
sum( case when t1.delta_time = 7 then 1 else 0 end ) / count(distinct t1.userID) day_7,
sum( case when t1.delta_time = 30 then 1 else 0 end )/ count(distinct t1.userID) day_30
from
(
select
userID,
a_createdTime,
first_value(a_createdTime) over(partition by userId order by a_createdTime ) first_time,
datediff(a_createdTime , first_value(a_createdTime) over(partition by userId order by a_createdTime )) delta_time
from
(
select userID,date(from_unixtime(createdTime)) a_createdTime
from user_active_log
group by userId,a_createdTime
)t0
) t1
group by t1.first_time
order by t1.first_time;
“每一个不曾起舞的日子,都是对生命的辜负。”各位加油!我们下个系列见
数据分析咨询请扫描二维码
若不方便扫码,搜微信号:CDAshujufenxi
在 MySQL 数据库中,查询一张表但是不包含某个字段可以通过以下两种方法实现:使用 SELECT 子句以明确指定想要的字段,或者使 ...
2025-03-17在当今数字化时代,数据成为企业发展的关键驱动力,而用户画像作为数据分析的重要成果,改变了企业理解用户、开展业务的方式。无 ...
2025-03-172025年是智能体(AI Agent)的元年,大模型和智能体的发展比较迅猛。感觉年初的deepseek刚火没多久,这几天Manus又成为媒体头条 ...
2025-03-14以下的文章内容来源于柯家媛老师的专栏,如果您想阅读专栏《小白必备的数据思维课》,点击下方链接 https://edu.cda.cn/goods/sh ...
2025-03-13以下的文章内容来源于刘静老师的专栏,如果您想阅读专栏《10大业务分析模型突破业务瓶颈》,点击下方链接 https://edu.cda.cn/go ...
2025-03-12以下的文章内容来源于柯家媛老师的专栏,如果您想阅读专栏《小白必备的数据思维课》,点击下方链接 https://edu.cda.cn/goods/sh ...
2025-03-11随着数字化转型的加速,企业积累了海量数据,如何从这些数据中挖掘有价值的信息,成为企业提升竞争力的关键。CDA认证考试体系应 ...
2025-03-10推荐学习书籍 《CDA一级教材》在线电子版正式上线CDA网校,为你提供系统、实用、前沿的学习资源,助你轻松迈入数据分析的大门! ...
2025-03-07在数据驱动决策的时代,掌握多样的数据分析方法,就如同拥有了开启宝藏的多把钥匙,能帮助我们从海量数据中挖掘出关键信息,本 ...
2025-03-06在备考 CDA 考试的漫漫征途上,拥有一套契合考试大纲的优质模拟题库,其重要性不言而喻。它恰似黑夜里熠熠生辉的启明星,为每一 ...
2025-03-05“纲举目张,执本末从。”若想在数据分析领域有所收获,一套合适的学习教材至关重要。一套优质且契合需求的学习教材无疑是那关 ...
2025-03-04以下的文章内容来源于刘静老师的专栏,如果您想阅读专栏《10大业务分析模型突破业务瓶颈》,点击下方链接 https://edu.cda.cn/go ...
2025-03-04在现代商业环境中,数据分析师的角色愈发重要。数据分析师通过解读数据,帮助企业做出更明智的决策。因此,考取数据分析师证书成为了许多人提升职业竞争力的选择。本文将详细介绍考取数据分析师证书的过程,包括了解证书种类和 ...
2025-03-03在当今信息化社会,大数据已成为各行各业不可或缺的宝贵资源。大数据专业应运而生,旨在培养具备扎实理论基础和实践能力,能够应 ...
2025-03-03数据分析师认证考试全面升级后,除了考试场次和报名时间,小伙伴们最关心的就是报名费了,报 ...
2025-03-032025年刚开启,知乎上就出现了一个热帖: 2024年突然出现的经济下行,使各行各业都感觉到压力山大。有人说,大环境越来越不好了 ...
2025-03-03大数据分析师培训旨在培养学员掌握大数据分析的基础知识、技术及应用能力,以适应企业对数据分析人才的需求。根据不同的培训需求 ...
2025-03-03小伙伴们,最近被《哪吒2》刷屏了吧!这部电影不仅在国内掀起观影热潮,还在全球范围内引发了关注,成为中国电影崛起的又一里程 ...
2025-03-03以下的文章内容来源于张彦存老师的专栏,如果您想阅读专栏《Python 数据可视化 18 讲(PyEcharts、Matplotlib、Seaborn)》,点 ...
2025-02-28最近,国产AI模型DeepSeek爆火,其创始人梁文峰走进大众视野。《黑神话:悟空》制作人冯骥盛赞DeepSeek为“国运级别的科技成果” ...
2025-02-27