mysql数据分析SQL

目录

同比

环比

占比

获取分组后每组的最大值

字符串中的数字、英文、汉字提取

SUM求和返回null改为返回0

字段分割统计数量

常用计算函数

---

同比

概念：同比就是与历史同时期比较。

例如，2019年7月份与2018年7月份相比，就是与不同年份的同一时期作比较，叫同比。

select
本月时间,
case when 2019-07数据 is null or 2019-07数据 = 0 then 0 else 2019-07数据 end 2019-07数据,
case when 2018-07数据 is null or 2018-07数据 = 0 then 0 else 2018-07数据 end 2018-07数据,
case when 2018-07数据 is null or 2018-07数据 = 0 then 0 
else ( convert ( ( ( 2019-07数据 - 2018-07数据 ) / 2018-07数据 ) * 100, decimal ( 10, 2 ) ) ) 
end 同比
from 
( 2019-07数据表 ) 2019-07数据
left join 
( 2018-07数据表 ) 2018-07数据
on 2019-07数据.产品名 = 2018-07数据.产品名

环比

概念：与上一统计段比较。

（1）就是先取本月的数据
（2）再用date_add函数向后延迟一个月（用interval）同样的方法取值
（3）二者进行关联
（4）从错位关联的两张大临时表中取数并做计算

例如，2019年7月份与2019年6月份相比较，叫环比。

select
本月时间,
case when 2019-07数据 is null or 2019-07数据 = 0 then 0 else 2019-07数据 end 2019-07数据,
case when 2019-06数据 is null or 2019-06数据 = 0 then 0 else 2019-06数据 end 2019-06数据,
case when 2019-06数据 is null or 2019-06数据 = 0 then 0 
else ( convert ( ( ( 2019-07数据 - 2019-06数据 ) / 2019-06数据 ) * 100, decimal ( 10, 2 ) ) ) 
end 环比
from 
( 2019-07数据表 ) 2019-07数据
left join 
( 2019-06数据表 ) 2019-06数据
on 2019-07数据.产品名 = 2019-06数据.产品名

占比

（1）求出分组、汇总

（2）笛卡尔积连接

（3）相除

例如，品类销量占比

select 
t1.name as 品类名称,
t1.num as 销售量,
concat(	format( t1.num / t2.total * 100, 2 ),'%') as 占比 
from
(select name,sum(销售量) num from 销售表 group by name ) t1
inner join 
( select sum(销售量) total from 销售表 ) t2 
on 1=1

参考：https://blog.csdn.net/zzqhello2018/article/details/101533565

获取分组后每组的最大值

select a.* from test a

inner join (select name,max(score) score from test group by name) b

on a.name=b.name

and a.score=b.score

order by a.name;

参考：https://blog.csdn.net/u011734144/article/details/51982134

字符串中的数字、英文、汉字提取

在MySQL中创建一个名为Num_char_extract的函数

第二个参数为0：代表提取数字

第二个参数为1：代表提取字母

第二个参数为2：代表提取数字+字母

第二个参数为3：代表提取汉字

第二个参数为其他数字：打印错误提示

DELIMITER $$
 
DROP FUNCTION IF EXISTS `Num_char_extract`$$
 
CREATE FUNCTION `Num_char_extract`(Varstring VARCHAR(100)CHARSET utf8, flag INT) RETURNS VARCHAR(50) CHARSET utf8
BEGIN
	DECLARE len INT DEFAULT 0;
	DECLARE Tmp VARCHAR(100) DEFAULT '';
	SET len=CHAR_LENGTH(Varstring);
	IF flag = 0 
	THEN
		WHILE len > 0 DO
		IF MID(Varstring,len,1)REGEXP'[0-9]' THEN
		SET Tmp=CONCAT(Tmp,MID(Varstring,len,1));
		END IF;
		SET len = len - 1;
		END WHILE;
	ELSEIF flag=1
	THEN
		WHILE len > 0 DO
		IF (MID(Varstring,len,1)REGEXP '[a-zA-Z]') 
		THEN
		SET Tmp=CONCAT(Tmp,MID(Varstring,len,1));
		END IF;
		SET len = len - 1;
		END WHILE;
	ELSEIF flag=2
	THEN
		WHILE len > 0 DO
		IF ( (MID(Varstring,len,1)REGEXP'[0-9]')
		OR (MID(Varstring,len,1)REGEXP '[a-zA-Z]') ) 
		THEN
		SET Tmp=CONCAT(Tmp,MID(Varstring,len,1));
		END IF;
		SET len = len - 1;
		END WHILE;
	ELSEIF flag=3
	THEN
		WHILE len > 0 DO
		IF NOT (MID(Varstring,len,1)REGEXP '^[u0391-uFFE5]')
		THEN
		SET Tmp=CONCAT(Tmp,MID(Varstring,len,1));
		END IF;
		SET len = len - 1;
		END WHILE;
	ELSE 
		SET Tmp = 'Error: The second paramter should be in (0,1,2,3)';
		RETURN Tmp;
	END IF;
	RETURN REVERSE(Tmp);
    END$$
 
DELIMITER ;

参考：https://blog.csdn.net/oyezhou/article/details/81665643

SUM求和返回null改为返回0

--原
SELEC SUM(ORDER_MONEY ) FROM gm_order WHERE ADD_UID = #{userCode};
--改为：
SELECT COALESCE(SUM(ORDER_MONEY),0) FROM gm_order WHERE ADD_UID = #{userCode};

字段分割统计数量

-- XXXXX按照、分割计算数量
select sum(length(XXXXX)-length(replace(XXXXX,’、’,’’))+1) from dual

常用计算函数

avg(input)：求平均值

max(input)：求最大值

min(input)：求最小值

sum(input)：求和

count(input)：求行数，如果使用*则不会忽略空值的行，

stddev(input)：求标准差

variance(input)：求方差

参考：https://blog.csdn.net/qq_42490630/article/details/107211249