数据分析与函数
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
第四章 数据分析与函数
函数
聚合函数
常用聚合函数
1.COUNT()函数：用来统计记录的条数 2.SUM()函数:是求和函数 3.AVG()函数:是求平均值的函数 4.MAX()函数是求最大值的函数 5.MIN()函数是求最小值的函数
coalesce
oalesce 函数表示可以返回参数中的第一个非空表达式，当你有 N 个参数时选取第 一个非空值（从左到右）。 select coalesce（null,"carrot","apple"） 返回结果：carrot
mysql> select weekday('1997-10-04 22:23:00'); -> 5
mysql> select weekday('1997-11-05'); -> 2
dayofmonth(date)
返回 date 是一月中的第几日(在 1 到 31 范围内) mysql> select dayofmonth('1998-02-03');
space(n)
返回由 n 个空格字符组成的一个字符串 select space(6);
reverse(str)
颠倒字符串 str 的字符顺序并返回 select reverse('test');
elt
返回第 n 个字符串(n 小于 1 或大于参数个数返回 null) elt(n,str1,str2,str3,...) select elt(1, 'ej', 'heja', 'hej', 'foo');
-> 1998
hour(time)
返回 time 的小时数(范围是 0 到 23) mysql> select hour('10:05:03');
-> 10
minute(time)
返回 time 的分钟数(范围是 0 到 59) mysql> select minute('98-02-03 10:05:03');
字符函数
字符串截取
• left(str, length) • right(str, length) mysql> select left('example.com', 3);
#查询某个字段后两位字符 select right(last3, 2) as last2 from historydata limit 10;
从大到小排列，如果相同则按列 2 从小到大排序
结构分析
在分组的基础上，分析各组数据占的比重（占比），比如：市场占有率，产品占有 率
select 产品 count(订单号)/(select count(订单号) from 订单表) as 占有率 from 订单表 group by 产品
分布分析
在分组的基础上，分析数据的分布情况,比如：年龄分布，消费分布
nid select num,nid,count(*),sum(score),max(score),min (score) from 表 group by num,nid # 内置函数
select num from 表 group by num having max(id) > 10 # 前面计算的结果交由后面处理
-> 2
dayname(date)
返回 date 是星期几(按英文名返回) mysql> select dayname("1998-02-05");
-> 'thursday'
monthname(date)
返回 date 是几月(按英文名返回) mysql> select monthname("1998-02-05");
-> 7 mysql> select week('1998-02-20'ቤተ መጻሕፍቲ ባይዱ0);
-> 7 mysql> select week('1998-02-20',1);
-> 8
year(date)
返回 date 的年份(范围在 1000 到 9999) mysql> select year('98-02-03');
• group by select num from 表 group by num
# 根据 num 分组
select num,nid from 表 group by num,nid # 根据 num 和 nid 分组
select num,nid from 表 where nid > 10 group by num,
field
返回 str 等于其后的第 n 个字符串的序号(如果 str 没找到返回 0) field(str,str1,str2,str3,...) select field('ej', 'hej', 'ej', 'heja', 'hej', 'foo');
find_in_set
返回 str 在字符串集 strlist 中的序号(任何参数是 null 则返回 null,如果 str 没找到 返回 0) find_in_set(str,strlist) select find_in_set('b','a,b,c,d');
字符串截取
substring(str, pos); substring(str, pos, len)
从字符串的第 4 个字符位置开始取，直到结束。 mysql> select substring('example.com', 4);
从字符串的第 4 个字符位置开始取，只取 2 个字符。 mysql> select substring('example.com', 4, 2);
从字符串的第 4 个字符位置（倒数）开始取，直到结束。 mysql> select substring('example.com', -4);
从字符串的第 4 个字符位置（倒数）开始取，只取 2 个字符。 mysql> select substring('example.com', -4, 2);
LTRIM(str)
-> 'february'
quarter(date)
返回 date 是一年的第几个季度 mysql> select quarter('98-04-01');
-> 2
week(date,first)
返回 date 是一年的第几周(first 默认值 0,first 取值 1 表示周一是 周的开始,0 从周 日开始) mysql> select week('1998-02-20');
-> 5
second(time)
返回 time 的秒数(范围是 0 到 59) mysql> select second('10:05:03');
-> 3
period_diff(p1,p2)
返回在时期 p1 和 p2 之间月数(p1 和 p2 的格式 yymm 或 yyyymm) mysql> select period_diff(9802,199703);
-> 11
数据分析
基本统计
COUNT([DISTINCT|ALL] *) 统计元组个数 COUNT([DISTINCT|ALL] <列名>) 统计一列中值的个数 SUM([DISTINCT|ALL] <列名>) 计算一列值的总和（此列必须是数值型） AVG([DISTINCT|ALL] <列名>) 计算一列值的平均值（此列必须是数值型） MAX([DISTINCT|ALL] <列名>) 求一列值中的最大值 MIN([DISTINCT|ALL] <列名>) 求一列值中的最小值
select now() 在执行开始时值就得到了
获得当前日期和时间
sysdate() 在函数执行时动态得到值
获得当前日期
curdate()
获得当前时间
curtime()
last_day()
返回该月最后一天 select last_day('2017-09-11')
datediff()
返回两个日期时间差 select datediff('2016-01-01','2016-01-02')
交叉分析
将多个数据表以交叉表的形式就行分析（类似 excel 数据透视表）
留存分析
以留存率为指标，衡量用户质量的分析方法。比如，次日留存率
矩阵分析
根据事物的两个重要属性，作为分析依据，进行关联分析，找出解决问题的一种分 析方法,比如：根据用户的年龄、消费分析出用户的质量（用户质量矩阵）
分组分析
1. 定量分组（数组分组）
2. 定性分组（属性分组） select * from table group by
排序分析
根据数值大小进行数值排序,比如：下载排行榜，销售排行榜
• order by
select * from 表 order by 列 asc # 根据“列” 从小到大排列 select * from 表 order by 列 desc # 根据 “列” 从大到小排列 select * from 表 order by 列 1 desc,列 2 asc # 根据 “列 1”
timediff()
返回两个日期时间差 select timediff('21:58:00','21:59:00')
添加指定时间间隔
date_add(date,interval expr type) 向日期添加指定时间间隔 select date_add('201708-10',interval 1 day)
-> 3
dayofyear(date)
返回 date 是一年中的第几日(在 1 到 366 范围内) mysql> select dayofyear('1998-02-03');
-> 34
month(date)
返回 date 中的月份数值 mysql> select month('1998-02-03');
大小写转换
upper 大写显示 select upper('test'); lower(str) 小写显示 select lower('test');
替换
replace(str,from_str,to_str) select REPLACE('test','e','s')
insert 函数
把字符串 str 由位置 pos 起 len 个字符长的子串替换为字符串 insert(str,pos,len,newstr) SELECT insert('test', 2, 2, 'test')
dayofweek(date)
返回日期 date 是星期几(1=星期天,2=星期一,……7=星期六,odbc 标准) mysql> select dayofweek('1998-02-03');
-> 3
weekday(date)
返回日期 date 是星期几(0=星期一,1=星期二,……6= 星期天)。
去除左空格 SELECT LTRIM(' test'); 除右空格 SELECT RTRIM('test ');
ROUND(X,D)
四舍五入 不保留小数 SELECT ROUND(5.693893); 保留两位小数 SELECT ROUND(5.693893,2);
length
获取字符串长度 select length('test');
INSTR
返回字符串在某一个字段的内容中的位置, 没有找到字符串返回 0，否则返回位置 （从 1 开始） INSTR(字段名, 字符串) select INSTR('test','t')
concat
连接字符串 concat(str1,str2,...) select concat('my', 's', 'ql');
least(x,y,...)
返回最小值 select least(2,0);
greatest(x,y,...)
返回最大值 select greatest(2,0);
转换函数
cast 和 convert
• cast(字段 as 数据类型)
• convert(字段,数据类型)
时期时间函数
获得当前日期和时间
函数
聚合函数
常用聚合函数
1.COUNT()函数：用来统计记录的条数 2.SUM()函数:是求和函数 3.AVG()函数:是求平均值的函数 4.MAX()函数是求最大值的函数 5.MIN()函数是求最小值的函数
coalesce
oalesce 函数表示可以返回参数中的第一个非空表达式，当你有 N 个参数时选取第 一个非空值（从左到右）。 select coalesce（null,"carrot","apple"） 返回结果：carrot
mysql> select weekday('1997-10-04 22:23:00'); -> 5
mysql> select weekday('1997-11-05'); -> 2
dayofmonth(date)
返回 date 是一月中的第几日(在 1 到 31 范围内) mysql> select dayofmonth('1998-02-03');
space(n)
返回由 n 个空格字符组成的一个字符串 select space(6);
reverse(str)
颠倒字符串 str 的字符顺序并返回 select reverse('test');
elt
返回第 n 个字符串(n 小于 1 或大于参数个数返回 null) elt(n,str1,str2,str3,...) select elt(1, 'ej', 'heja', 'hej', 'foo');
-> 1998
hour(time)
返回 time 的小时数(范围是 0 到 23) mysql> select hour('10:05:03');
-> 10
minute(time)
返回 time 的分钟数(范围是 0 到 59) mysql> select minute('98-02-03 10:05:03');
字符函数
字符串截取
• left(str, length) • right(str, length) mysql> select left('example.com', 3);
#查询某个字段后两位字符 select right(last3, 2) as last2 from historydata limit 10;
从大到小排列，如果相同则按列 2 从小到大排序
结构分析
在分组的基础上，分析各组数据占的比重（占比），比如：市场占有率，产品占有 率
select 产品 count(订单号)/(select count(订单号) from 订单表) as 占有率 from 订单表 group by 产品
分布分析
在分组的基础上，分析数据的分布情况,比如：年龄分布，消费分布
nid select num,nid,count(*),sum(score),max(score),min (score) from 表 group by num,nid # 内置函数
select num from 表 group by num having max(id) > 10 # 前面计算的结果交由后面处理
-> 2
dayname(date)
返回 date 是星期几(按英文名返回) mysql> select dayname("1998-02-05");
-> 'thursday'
monthname(date)
返回 date 是几月(按英文名返回) mysql> select monthname("1998-02-05");
-> 7 mysql> select week('1998-02-20'ቤተ መጻሕፍቲ ባይዱ0);
-> 7 mysql> select week('1998-02-20',1);
-> 8
year(date)
返回 date 的年份(范围在 1000 到 9999) mysql> select year('98-02-03');
• group by select num from 表 group by num
# 根据 num 分组
select num,nid from 表 group by num,nid # 根据 num 和 nid 分组
select num,nid from 表 where nid > 10 group by num,
field
返回 str 等于其后的第 n 个字符串的序号(如果 str 没找到返回 0) field(str,str1,str2,str3,...) select field('ej', 'hej', 'ej', 'heja', 'hej', 'foo');
find_in_set
返回 str 在字符串集 strlist 中的序号(任何参数是 null 则返回 null,如果 str 没找到 返回 0) find_in_set(str,strlist) select find_in_set('b','a,b,c,d');
字符串截取
substring(str, pos); substring(str, pos, len)
从字符串的第 4 个字符位置开始取，直到结束。 mysql> select substring('example.com', 4);
从字符串的第 4 个字符位置开始取，只取 2 个字符。 mysql> select substring('example.com', 4, 2);
从字符串的第 4 个字符位置（倒数）开始取，直到结束。 mysql> select substring('example.com', -4);
从字符串的第 4 个字符位置（倒数）开始取，只取 2 个字符。 mysql> select substring('example.com', -4, 2);
LTRIM(str)
-> 'february'
quarter(date)
返回 date 是一年的第几个季度 mysql> select quarter('98-04-01');
-> 2
week(date,first)
返回 date 是一年的第几周(first 默认值 0,first 取值 1 表示周一是 周的开始,0 从周 日开始) mysql> select week('1998-02-20');
-> 5
second(time)
返回 time 的秒数(范围是 0 到 59) mysql> select second('10:05:03');
-> 3
period_diff(p1,p2)
返回在时期 p1 和 p2 之间月数(p1 和 p2 的格式 yymm 或 yyyymm) mysql> select period_diff(9802,199703);
-> 11
数据分析
基本统计
COUNT([DISTINCT|ALL] *) 统计元组个数 COUNT([DISTINCT|ALL] <列名>) 统计一列中值的个数 SUM([DISTINCT|ALL] <列名>) 计算一列值的总和（此列必须是数值型） AVG([DISTINCT|ALL] <列名>) 计算一列值的平均值（此列必须是数值型） MAX([DISTINCT|ALL] <列名>) 求一列值中的最大值 MIN([DISTINCT|ALL] <列名>) 求一列值中的最小值
select now() 在执行开始时值就得到了
获得当前日期和时间
sysdate() 在函数执行时动态得到值
获得当前日期
curdate()
获得当前时间
curtime()
last_day()
返回该月最后一天 select last_day('2017-09-11')
datediff()
返回两个日期时间差 select datediff('2016-01-01','2016-01-02')
交叉分析
将多个数据表以交叉表的形式就行分析（类似 excel 数据透视表）
留存分析
以留存率为指标，衡量用户质量的分析方法。比如，次日留存率
矩阵分析
根据事物的两个重要属性，作为分析依据，进行关联分析，找出解决问题的一种分 析方法,比如：根据用户的年龄、消费分析出用户的质量（用户质量矩阵）
分组分析
1. 定量分组（数组分组）
2. 定性分组（属性分组） select * from table group by
排序分析
根据数值大小进行数值排序,比如：下载排行榜，销售排行榜
• order by
select * from 表 order by 列 asc # 根据“列” 从小到大排列 select * from 表 order by 列 desc # 根据 “列” 从大到小排列 select * from 表 order by 列 1 desc,列 2 asc # 根据 “列 1”
timediff()
返回两个日期时间差 select timediff('21:58:00','21:59:00')
添加指定时间间隔
date_add(date,interval expr type) 向日期添加指定时间间隔 select date_add('201708-10',interval 1 day)
-> 3
dayofyear(date)
返回 date 是一年中的第几日(在 1 到 366 范围内) mysql> select dayofyear('1998-02-03');
-> 34
month(date)
返回 date 中的月份数值 mysql> select month('1998-02-03');
大小写转换
upper 大写显示 select upper('test'); lower(str) 小写显示 select lower('test');
替换
replace(str,from_str,to_str) select REPLACE('test','e','s')
insert 函数
把字符串 str 由位置 pos 起 len 个字符长的子串替换为字符串 insert(str,pos,len,newstr) SELECT insert('test', 2, 2, 'test')
dayofweek(date)
返回日期 date 是星期几(1=星期天,2=星期一,……7=星期六,odbc 标准) mysql> select dayofweek('1998-02-03');
-> 3
weekday(date)
返回日期 date 是星期几(0=星期一,1=星期二,……6= 星期天)。
去除左空格 SELECT LTRIM(' test'); 除右空格 SELECT RTRIM('test ');
ROUND(X,D)
四舍五入 不保留小数 SELECT ROUND(5.693893); 保留两位小数 SELECT ROUND(5.693893,2);
length
获取字符串长度 select length('test');
INSTR
返回字符串在某一个字段的内容中的位置, 没有找到字符串返回 0，否则返回位置 （从 1 开始） INSTR(字段名, 字符串) select INSTR('test','t')
concat
连接字符串 concat(str1,str2,...) select concat('my', 's', 'ql');
least(x,y,...)
返回最小值 select least(2,0);
greatest(x,y,...)
返回最大值 select greatest(2,0);
转换函数
cast 和 convert
• cast(字段 as 数据类型)
• convert(字段,数据类型)
时期时间函数
获得当前日期和时间