SQL语句5——统计用函数（MySQL）

MySQL统计用函数

COUNT函数（count）

COUNT() 是一个聚合函数，返回指定匹配条件的行数。开发中常用来统计表中数据，全部数据，不为NULL数据，或者去重数据。

COUNT函数的参数：
COUNT(1)：统计不为NULL 的记录。

COUNT(*)：统计所有的记录（包括NULL）。

COUNT(字段)：统计该"字段"不为NULL 的记录。
1.如果这个字段是定义为not null的话，一行行地从记录里面读出这个字段，判断不能为null，按行累加。
2.如果这个字段定义允许为null的话，判断到有可能是null，还要把值取出来在判断一下，不是null才累加。

COUNT(DISTINCT 字段)：统计该"字段"去重且不为NULL 的记录。

-- 统计数据：7条数据，统计所有的记录（包括NULL）。
select count(*) from user;

-- 统计数据：7条数据，统计不为NULL 的记录。
select count(1) from user;

-- 统计数据：5条数据，COUNT(字段)：统计该"字段"不为NULL 的记录，注意是null不是空''字符串
select count(name) from user;

-- 统计数据：5条数据，COUNT(DISTINCT 字段)：统计该"字段"去重且不为NULL 的记录。
select count(distinct name) from user;

DATE_FORMAT函数

DATE_FORMAT（date_format(d, f)） ：按表达式 f的要求显示日期 d

SELECT DATE_FORMAT('2011.11.11 11:11:11','%Y-%m-%d %r');
---- 返回2011-11-11 11:11:11 AM
SELECT DATE_FORMAT('2011.11.11 11:11:11',''%y年%m月%d日'');
---- 返回2011年11月11日

DATEDIFF、PERIOD_DIFF、TIMESTAMPDIFF函数

DATEDIFF（datediff）：返回两个日期之间的天数

SELECT DATEDIFF('2021-10-01','2021-09-30') AS DiffDate  ----->1
SELECT DATEDIFF('2021-09-30','2021-10-01') AS DiffDate	----->-1
SELECT DATEDIFF('2021-10-01  23:59:59','2021-09-30 00:00:00') AS DiffDate ----->1
SELECT DATEDIFF('2021-09-30 00:00:00','2021-10-01 23:59:59') AS DiffDate	----->-1

-- 查找创建时间为昨天的数据
SELECT * FROM pay_billing WHERE DATEDIFF(date_format(now(), '%Y%m%d') , date_format(create_time, '%Y%m%d' )) =1

DATE_DIFF（period_diff(date1,date2)）：返回两个日期之间的月份数，date1 和 date2 参数是只能是YYYYMM或YYMM

SELECT PERIOD_DIFF(202101,202001)	------------>12
SELECT PERIOD_DIFF(2101,2001)	-------------->12

TIMESTAMPDIFF（timestampdiff(unit,begin,end)）：可计算年、月，TIMESTAMPDIFF函数返回end-begin的结果，其中begin和end是DATE或DATETIME表达式。
TIMESTAMPDIFF函数允许其参数具有混合类型，例如，begin是DATE值，end可以是DATETIME值。 如果使用DATE值，则TIMESTAMPDIFF函数将其视为时间部分为“00:00:00”的DATETIME值。
unit参数是确定(end-begin)的结果的单位，表示为整数。 有效单位：MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR

SELECT TIMESTAMPDIFF(MONTH, '2021-01-01', '2021-06-01') ;		----->5
SELECT TIMESTAMPDIFF(DAY, '2020-01-01', '2020-06-01') ;		------>152
SELECT TIMESTAMPDIFF(MINUTE, '2021-10-01 10:01:00', '2021-10-01 10:45:00');		----->44
SELECT TIMESTAMPDIFF(SECOND, '2021-10-01 10:00:00', '2021-10-01 10:45:59');		----->45
-- 差值应该是45分59秒。 但是，我们将unit参数传递为MINUTE，因此，函数按预期返回45分钟。如果使用SECOND而不是MINUTE，则TIMESTAMPDIFF函数将考虑SECOND部分
SELECT TIMESTAMPDIFF(SECOND, '2021-10-01 10:00:00', '2021-10-01 10:45:59');		----->2759
-- 查找最近一周的记录
SELECT * FROM `pay_billing` where TIMESTAMPDIFF(WEEK,create_time,date_format(now(),'%Y-%m-%d'))=0

窗口函数

lag(number,1) over(order by week asc) as number_1

select week,number,concat(ifnull(round((number-number_1)/number_1*100,2),0),'%') as growth_rate
from (select week,number,lag(number,1) over(order by week asc) as number_1 from temp_week) a