Oracle 按年、月、日、周、季度连续查询

1、前言

很多时候，我们需要统计某某状态下的数据呈现给客户或者以图表的方式显示出来。在按年、月、日、周、季度统计的时候，没有数据那一年或者那一月、那一天...它是不显示的。这里以天举例，有时候我们需要将每一天的数据都统计出来，哪怕这天该状态的数据不存在或者说是数量为0，那么我们怎么来显示呢，以下是最近的一些查询方面的总结，记录一下，方便以后查看，也希望能帮助到整有这方面需求的各位朋友。

2、引入

说明：本查询是在pl/sql下完成

(1)、有时候我们查询的数据类似下表（按天统计）：

Day	Num
2017-12-04	1
2017-12-05	2
2017-12-08	4
2017-12-12	3

但是我们需要下面的数据：

Day	Num
2017-12-04	2
2017-12-05	3
2017-12-06	0
2017-12-07	1
2017-12-08	0

那么现在的这个sql怎么写呢，有的时候可能会想到NVL()或者ISNULL()（Oracle没有）等，但是是不是发现一些情况下并不能如意呢？是的，对于我至少是这样的。那么请看下面。

3、连续查询

其实说真的，我不知道叫什么，所以为了好记忆，起了个“连续查询”，勿喷啊。

先看一下下面的代码：

select floor(to_number(sysdate-to_date('2017-12-18','yyyy-mm-dd'))) as spanDays from dual             --时间差-天
select ceil(MONTHS_BETWEEN(sysdate,to_date('2017-8-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanMonths from dual        --时间差-月
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))/365) as spanYears from dual        --时间差-年
select ceil(MONTHS_BETWEEN(sysdate,to_date('2017-10-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))/3) as spanMonths from dual        --时间差-季度

以上代码可以获取当前时间与指定时间之间的时间差（天，月。。。），至于周就是7天一周，大家得到日期之间相差的天数就可以算了。
那么我们的查询就可以和以上查询结果进行关联，从而得出每天，每月，每季度。。。的数据，如下：

这里用到了with语句：

WITH m AS (
SELECT to_char(to_date('2017-12-01','yyyy-mm-dd')+(LEVEL-1),'yyyy-mm-dd') as dt
  FROM DUAL
CONNECT BY LEVEL<=to_number(to_date('2017-12-30','yyyy-mm-dd')-to_date('2017-12-01','yyyy-mm-dd'))+1
)
,T2 as 
(
  select ? as date1,count(1) as count1 from ....
  --这里的语句自己定了，我这只是对应下面的条件而已
)
SELECT m.dt,nvl(T2.count1,0) 
  FROM m LEFT JOIN T2 ON m.dt=T2.date1 order by m.dt;

这里的LEVEL表示两个时间之间的差值，比如：

SELECT to_char(ADD_MONTHS(to_date('2017-01-01','yyyy-mm-dd'),LEVEL-1),'yyyy-mm') as dt
FROM DUAL
CONNECT BY LEVEL<=MONTHS_BETWEEN(to_date('2017-12-30','yyyy-mm-dd'),to_date('2017-01-01','yyyy-mm-dd'))+1

其中LEVEL-1那里，LEVEL从1开始往上递增至12,也就循环了12次（不知道理解对不对，应该是的）

那么以上内容基本就能满足你的“连续的数据”了

4、补充

（1）、按年统计：

select ... from ...group by to_char(你的时间字段,'yyyy')

（2）、按月统计

select ... from ...group by to_char(你的时间字段,'yyyy-mm')

（3）、按日统计

select ... from ...group by to_char(你的时间字段,'yyyy-mm-dd')

（4）、按季度统计

select ... from ...group by to_char(你的时间字段,'q')

（5）、按周统计

select ... from ...group by to_char(你的时间字段,'iw')

举个例子：（按周统计）

select to_char(actualenddate,'iw') as day,nvl(count(1),0) as count1
 from   T1
 group by to_char(actualenddate,'iw') order by to_char(actualenddate,'iw')