《SQL经典实例》：4. number及date相关函数

        目录

Number相关函数

Group by+聚合函数

累计求和

累计乘积

查找众数

查找中位数

 Date相关函数

datediff / interval

year/month/day

 date_add/adddate

date_format

last_day

---

Number相关函数

Group by+聚合函数

• group by函数将拥有同等的某列值的记录summary成一行记录，和聚合函数(count, min, max, avg, sum等)一起使用；
• 可以按照多个字段group by，比如group by ename, empno;
• group by后跟着的字段可以不出现在select语句里，比如SELECT avg(sal) FROM emp e GROUP BY deptno是可以的；
• 但select语句里不能有group by后没有的非聚合的列，比如SELECT ename, empno, avg(sal) FROM emp e GROUP BY ename(会报错，应为GROUP BY ename, empno)。

一个简单的例子：

SELECT deptno, min(sal) AS min_sal, max(sal) AS max_sal
FROM emp
GROUP BY deptno;

累计求和

内嵌一个标量子查询即可

-- 累计求和salary形成新的一列runningtotal
SELECT ename, e.sal, 
(SELECT sum(d.sal) FROM emp d WHERE d.empno <= e.empno)running_total
FROM emp e;

累计乘积

通过对数求和再exp的方式，计算累计乘积。原理和累计求和一样，都是内嵌一个标量子查询。

-- 由于乘积太大，这里只看了deptno=10的几条记录的累计乘积
SELECT e.ename, e.sal, 
(SELECT exp(sum(ln(d.sal))) FROM emp d WHERE d.empno <= e.empno AND d.deptno = e.deptno) running_prod
FROM emp e
WHERE e.deptno = 10; 

查找众数

利用group by+having条件，找到count(*)最大的即为众数。

SELECT sal
FROM emp
WHERE deptno = 20
GROUP BY sal
Having count(*) >= all
(SELECT count(*) FROM emp WHERE deptno = 20 GROUP BY sal);

查找中位数

SELECT AVG(DISTINCT x.sal) AS median_salary
FROM
	(SELECT e.sal
	 FROM emp e, emp AS b
	 GROUP BY e.sal
	 HAVING SUM(CASE WHEN b.sal >= e.sal THEN 1 ELSE 0 END) >= COUNT(*) / 2 
	 AND SUM(CASE WHEN b.sal <= e.sal THEN 1 ELSE 0 END) >= COUNT(*) / 2 ) x;

 Date相关函数

datediff / interval

得到两个日期间相差天数

-- 1. interval年月日加减法
SELECT empno,	
	   hiredate - interval 5 day as hd_minus_5D,
       hiredate + interval 5 day as hd_plus_5D,
       hiredate - interval 5 month as hd_minus_5M,
       hiredate + interval 5 month as hd_plus_5M,
       hiredate - interval 5 year as hd_minus_5Y,
       hiredate + interval 5 year as hd_plus_5Y
FROM emp
WHERE deptno = 10;

-- 2. datediff计算两日期间相差天数
SELECT datediff(ward_hd, allen_hd) AS diff
FROM 
(SELECT hiredate AS allen_hd FROM emp WHERE ename = 'ALLEN') x,
(SELECT hiredate AS ward_hd FROM emp WHERE ename = 'WARD') y;
-- 注意把日期靠后的放在前面(ward:1981-2-22, allen:1981-2-20)

year/month/day

得到一个date的年/月/日

-- 得到第一个和最后一个入职的相差的月份
SELECT ((year(max_hd)-year(min_hd))*12+(month(max_hd)-month(min_hd))) AS mnth
FROM 
(SELECT max(hiredate) AS max_hd, min(hiredate) AS min_hd FROM emp)x

 date_add/adddate

添加指定日子到一个date上，二者功能一样。date_add(date, interval 1 day).  

-- 计算一年有多少天
SELECT datediff(curr_year + interval 1 year, curr_year)
FROM
(SELECT date_add(date_add(current_date, interval -dayofyear(current_date) day), interval 1 day) curr_year) x;

date_format

提取一个日期的各种格式，有太多种format了。

-- 得到Blake和Jones两者入职日期之间的工作日天数
SELECT sum(CASE WHEN date_format(date_add(jones_hd, interval t100.id-1 DAY),'%a') in ('Sat', 'Sun') THEN 0 ELSE 1 END) as days
FROM
(SELECT max(CASE WHEN ename = 'BLAKE' THEN hiredate END) AS blake_hd,
	   max(CASE WHEN ename = 'JONES' THEN hiredate END) AS jones_hd
FROM emp)x, t100
WHERE t100.id <= datediff(blake_hd, jones_hd) + 1;

-- 从给定日期值提取年月日时分秒
SELECT date_format(current_time,'%k') hr,
date_format(current_time,'%i') min,
date_format(current_time,'%s') sec,
date_format(current_time,'%d') dy,
date_format(current_time,'%m') mon,
date_format(current_timestamp,'%Y') yr
from t1;

还可以任意组合使用

-- 找到emp表中同月同weekday入职的员工
SELECT concat(a.ename, ' was hired on the same month and weekday as ', b.ename) msg
FROM emp a, emp b
WHERE date_format(a.hiredate,'%w%M') =
      date_format(b.hiredate,'%w%M')
AND a.empno < b.empno
ORDER BY a.ename;

last_day

得到一个日期的当月最后一天，可以用来判断是否是闰年

SELECT 
last_day(
date_add(
date_add(
date_add(current_date, interval -dayofyear(current_date) day), interval 1 day), interval 1 month))
FROM T1;
-- 第一层date_add 得到2021.12.31； 第二层date_add得到2022.1.1；第三层date_add得到2022.2.1；通过判断二月份有28还是29天判断是否是闰年