sql经典实例_记录02（数值、日期的处理）

数值的处理

均值

avg函数会忽略null值
例：计算demo表中的age均值。

avg计算均值之后再经过cast转化为整数，得到18说明avg已经忽略null值。

select cast(avg(age) as DECIMAL(10)) as avgage
from demo

将null值计算进去

思路：

• 使用coalesce将null值填充为0，然后使用avg

例：使用coalesce

select cast(avg(COALESCE(age,0)) as DECIMAL(10)) as avgage
from demo;

最小值、最大值

思路：

• 使用min、max
• 使用orderby，再使用limit限制一行数据

例：使用min、max拿出最小最大的age

select min(age),max(age)
from demo;

例：使用orderby和limit，使用标量子查询合并

select age as minage,(select age as maxge from demo where age is not null order by age desc limit 1) as maxage
from demo
where age is not null
order by age asc
limit 1;

求和

思路：sum函数
例：将年龄相加再除以总人数算平均值
注意：指定列名得count(age)会忽略null值，count(*)会把null值加进去

select sum(age) as allage,count(age) as agenum,cast(sum(age)/count(age) as DECIMAL(10)) as avgage
from demo

求和后排序

思路：sum、over、group by一起使用
例：工资求和后按照create_by排序

select sum(salary_money) over(order by salary_money,create_by) as list,create_by
from demo
group by create_by;

日期的处理

datetime类型

datetime的时间差得到秒数
例：计算跟新时间与创建时间之间的天数

select CONCAT(cast((update_time-create_time)/(60*60*60*24) as DECIMAL(10)),' 天') as cast_day,name
from demo  
where update_time is not null and create_time is not null;

timesmap类型

时间差获得秒数
例：计算更新时间与创建时间相隔的天数

select concat(cast((update_time-create_time)/(60*60*60*24) as DECIMAL(10)),' 天') as cast_day,name
from test_timestamp

计算星期

思考：
方法一：给定年月日的数值，先判断闰年还是平年，得出2月的天数，算出所有的天数对7取余数（较为麻烦）。
方法二：内置函数dayname自动计算。
例：dayname函数获知每一个日期的星期几

select dayname(update_time) as dayname
from test_timestamp;