前言
本篇文章讲解的主要内容是：汇总报表时常要求按季度分类汇总这就需要通过给定年份获取对应的季度开始结束时间、业务数据不连续的情况下如何统计所有年份数据、如何统计相同月份与周内日期聘用的员工、如何返回2月或12月聘用的所有员工以及周二聘用的所有员工
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。
一、确定指定年份季度的开始日期和结束日期
生成汇总报表时常要求按季度分类汇总这就需要通过给定年份提取对应的季度信息，
那么问题来了，怎么快速获取某个季度的开始结束日期呢？
接下来给出解决方案：
SQL> with t as
2 (select to_char(sysdate, 'yyyy') as 年, level as sn
3 from dual
4 connect by level <= 4
5 )
6 select sn as 季度,
7 (sn - 1) * 3 + 1 as 开始月份,
8 add_months(trunc(to_date(年, 'yyyy'), 'y'), (sn - 1) * 3) as 开始日期,
9 add_months(trunc(to_date(年, 'yyyy'), 'y'), sn * 3) - 1 as 结束日期
10 from t;
季度 开始月份 开始日期 结束日期
---------- ---------- ----------- -----------
1 1 2023-1-1 2023-3-31
2 4 2023-4-1 2023-6-30
3 7 2023-7-1 2023-9-30
4 10 2023-10-1 2023-12-31
SQL> 这种枚举季度信息的语句在写报表查询时可能会用到有必要记录下来备用。
二、补充时间范围内丢失的值
有时业务数据并不是连续的如下面的数据：
SQL> select empno,hiredate from emp order by 2;
EMPNO HIREDATE
----- -----------
7369 1980-12-17
7499 1981-2-20
7521 1981-2-22
7566 1981-4-2
7698 1981-5-1
7782 1981-6-9
7844 1981-9-8
7654 1981-9-28
7839 1981-11-17
7900 1981-12-3
7902 1981-12-3
7934 1982-1-23
7788 1987-4-19
7876 1987-5-23
1001 2021-10-9 1
15 rows selected有的年份没有招聘员工，这时按年份查询招聘人数结果如下：
SQL> select to_char(hiredate,'yyyy') as year, count(*) as cnt
2 from emp
3 group by to_char(hiredate,'yyyy')
4 order by 1;
YEAR CNT
--------------------------------------------------------------------------- ----------
1980 1
1981 10
1982 1
1987 2
2021 1
SQL> 为了分析数据一般需要把表中没有的年份（如1983年）内的人数统计为0,这时就需要先根据表中的信息生成一个年份的枚举列表。
SQL> with t as
2 (select extract(year from min(hiredate)) as 开始年份,
3 extract(year from max(hiredate)) as 结束年份
4 from emp where empno<>1001)
5 select 开始年份 + (level - 1) as 年份
6 from t
7 connect by level <= ((结束年份 - 开始年份) + 1);
年份
----------
1980
1981
1982
1983
1984
1985
1986
1987
8 rows selected通过这个列表关联查询就可以得到所有年份的数据。
SQL> with t as
2 (select extract(year from min(hiredate)) as 开始年份,
3 extract(year from max(hiredate)) as 结束年份
4 from emp
5 where empno <> 1001),
6 t1 as
7 (select 开始年份 + (level - 1) as 年份
8 from t
9 connect by level <= ((结束年份 - 开始年份) + 1))
10 select t1.年份, count(emp.empno) as 聘用人数
11 from emp
12 right join t1
13 on (extract(year from emp.hiredate) = t1.年份)
14 group by 年份
15 order by 1;
年份 聘用人数
---------- ----------
1980 1
1981 10
1982 1
1983 0
1984 0
1985 0
1986 0
1987 2
8 rows selected三、按照给定的时间单位进行查找
有时需要查找特定的条件如要求返回2月或12月聘用的所有员工以及周二聘用的所有员工。
若要得到三个条件返回结果的合集用to_char函数分别确认雇佣日期是几月及周几再过滤就可以。
SQL> select ename,hiredate,to_char(hiredate,'day') as 星期
2 from emp
3 where to_char(hiredate,'mm') in('02','12')
4 or to_char(hiredate,'d')='3';
ENAME HIREDATE 星期
---------- ----------- ---------------------------------------------------------------------------
SMITH 1980-12-17 星期三
ALLEN 1981-2-20 星期五
WARD 1981-2-22 星期日
CLARK 1981-6-9 星期二
KING 1981-11-17 星期二
TURNER 1981-9-8 星期二
JAMES 1981-12-3 星期四
FORD 1981-12-3 星期四
8 rows selected要点在于要避免字符集的影响如这里分别用to_char(hiredate,'mm')及to_char(hiredate,'d')来生成与字符集无关的数值信息。
四、使用日期的特殊部分比较记录
在报表统计中常有同期对比的需求演示案例需求为：
统计相同月份与周内日期聘用的员工，如：有两个员工都是3月份周一聘用的则可以用分析函数计算次数然后进行过滤语句如下：
SQL>
SQL> select ename,hiredate,to_char(hiredate,'mon day') as 星期
2 from (
3 select ename,hiredate,count(*)over(partition by to_char(hiredate,'mon day')) as ct
4 from emp
5 )
6 where ct >1;
ENAME HIREDATE 星期
---------- ----------- ---------------------------------------------------------------------------
FORD 1981-12-3 12月 星期四
JAMES 1981-12-3 12月 星期四
SQL> 上述语句中要注意以下几点：
- 我们使用了分析函数COUNT()over(),这样可以只访问一次emp就同时得到了明细(ename,hiredate)及汇总信息(cnt)。与分析函数之前需要两次访问emp的写法相比提高了效率。
- 因为不需要过滤to_char函数的结果这里可以不必使用"mmd"写法。
总结
以上四种案例是仓库报表统计中常见又容易出错的几种场景，是博主几个粉丝网友提问的，博主总结发出来的！
