SQL trunc/group/order by dates（天/月/季度/年），带无数据的总跳过日期

Question

我参与了一个项目，我需要按日期建立直方图。在我之前，这是在Java代码中完成的，通过对每个矩形（日期子区域）的DB进行大量SQL查询。

我尝试另一种方法：

select sum(CNT), trunc(DATE, 'MM') from DATA
  where DATE >= TO_DATE('01-01-2012','DD-MM-YYYY')
  and INC_DATE <= TO_DATE('31-12-2012','DD-MM-YYYY')
  group by trunc(DATE, 'MM')
  order by trunc(DATE, 'MM');

用Java代码从ResultSet中收集数据。但是如果某个月没有数据，我会错过直方图中的矩形！！！

有没有可能修复SQL（或者可能是PL/SQL）表达式，使其在结果中包含缺失的日期，并使用零和？

或者如何在Java中构建更优雅的日期序列生成器来查找丢失的日期（对齐到天/月/季度/年）？

Answer 1

我的产品代码基于当地大师的建议和@Ben技术：

-- generate sequence 1..N:
SELECT level FROM dual CONNECT BY level <= 4;

-- generates days:
select to_date('01-01-2012','DD-MM-YYYY') + level - 1
  from dual
connect by level <= to_date('31-12-2012','DD-MM-YYYY') - to_date('01-01-2012','DD-MM-YYYY') + 1;

with dates as (
  select (to_date('01-01-2012','DD-MM-YYYY') + level - 1) as daterange
    from dual
    connect by level <= to_date('31-12-2012','DD-MM-YYYY') - to_date('01-01-2012','DD-MM-YYYY') + 1
  ) select sum(tbl.cnt) as summ, trunc(dates.daterange, 'DDD')
      from dates
           left outer join DATA_TBL tbl
        on trunc(tbl.inc_date, 'DDD') = trunc(dates.daterange, 'DDD')
      group by trunc(dates.daterange, 'DDD')
      order by trunc(dates.daterange, 'DDD');

-- generates months:
select ADD_MONTHS(to_date('01-01-2012','DD-MM-YYYY'), level - 1)
  from dual
connect by level <= months_between(to_date('31-12-2012','DD-MM-YYYY'), to_date('01-01-2012','DD-MM-YYYY')) + 1;

with dates as (
  select add_months(to_date('01-01-2012','DD-MM-YYYY'), level-1) as daterange
    from dual
    connect by level <= months_between(to_date('31-12-2012','DD-MM-YYYY'), to_date('01-01-2012','DD-MM-YYYY')) + 1
  ) select sum(tbl.cnt) as summ, trunc(dates.daterange, 'MM')
      from dates
           left outer join DATA_TBL tbl
        on trunc(tbl.inc_date, 'MM') = trunc(dates.daterange, 'MM')
      group by trunc(dates.daterange, 'MM')
      order by trunc(dates.daterange, 'MM');

-- generates quarters:
select ADD_MONTHS(to_date('01-01-2012','DD-MM-YYYY'), (level-1)*3)
  from dual
  connect by level <= months_between(to_date('31-12-2012','DD-MM-YYYY'), to_date('01-01-2012','DD-MM-YYYY'))/3 + 1;

with dates as (
  select add_months(to_date('01-01-2012','DD-MM-YYYY'), (level-1)*3) as daterange
    from dual
    connect by level <= months_between(to_date('31-12-2012','DD-MM-YYYY'), to_date('01-01-2012','DD-MM-YYYY'))/3 + 1
  ) select sum(tbl.cnt) as summ, trunc(dates.daterange, 'Q')
      from dates
           left outer join DATA_TBL tbl
        on trunc(tbl.inc_date, 'Q') = trunc(dates.daterange, 'Q')
      group by trunc(dates.daterange, 'Q')
      order by trunc(dates.daterange, 'Q');

-- generates years:
select add_months(to_date('01-01-2007','DD-MM-YYYY'), (level-1)*12)
  from dual
  connect by level <= months_between(to_date('31-01-2012','DD-MM-YYYY'), to_date('01-01-2007','DD-MM-YYYY'))/12 + 1;

with dates as (
  select add_months(to_date('01-01-2007','DD-MM-YYYY'), (level-1)*12) as daterange
    from dual
    connect by level <= months_between(to_date('31-01-2012','DD-MM-YYYY'), to_date('01-01-2007','DD-MM-YYYY'))/12 + 1
  ) select sum(tbl.cnt) as summ, trunc(dates.daterange, 'YYYY')
      from dates
           left outer join DATA_TBL tbl
        on trunc(tbl.inc_date, 'YYYY') = trunc(dates.daterange, 'YYYY')
      group by trunc(dates.daterange, 'YYYY')
      order by trunc(dates.daterange, 'YYYY');

但连接的水平是根据黑客：

• http://www.sqlsnippets.com/en/topic-11821.html（明白了。使用或不使用提前，这是个问题）

Answer 2

您需要首先创建日期列表；通过创建日历表或使用CONNECT BY语法。

select to_date('01-01-2012','DD-MM-YYYY') + level - 1
  from dual
connect by level <= to_date('31-12-2012','DD-MM-YYYY') 
                    - to_date('01-01-2012','DD-MM-YYYY') + 1

然后，您可以将LEFT OUTER加入到主查询中，以确保填充空白：

with the_dates as (
  select to_date('01-01-2012','DD-MM-YYYY') + level - 1 as the_date
    from dual
 connect by level <= to_date('01-01-2012','DD-MM-YYYY') 
                      - to_date('31-12-2012','DD-MM-YYYY') + 1
         )
select sum(b.cnt), trunc(a.the_date, 'MM') 
  from the_dates a
  left outer join data b
    on a.the_date = b.date
 group by trunc(a.the_date, 'MM')
 order by trunc(a.the_date, 'MM')

您不再需要WHERE子句，因为这在JOIN中得到了处理。请注意，我使用的不是主表中的DATE列，而是生成表中的日期。如果您想将日期修改为不是月末，这将是可行的，但是如果您希望按月份进行，您可以截断AND子句中的日期。不过，在这样做之前，您应该注意索引。如果您的表是在DATE上索引的，而不是TRUNC（DATE，'MM'），那么最好仅在DATE上进行JOIN。

DATE对于列来说是个坏名字，因为它是一个保留字；我怀疑你没有使用它，但你应该知道。

如果你用的是日历桌，它看起来像这样：

select sum(b.cnt), trunc(a.the_date, 'MM') 
  from calender_table a
  left outer join data b
    on a.the_date = b.date
 where a.the_date >= to_date('01-01-2012','DD-MM-YYYY') 
   and a.the_date <= to_date('31-12-2012','DD-MM-YYYY')
 group by trunc(a.the_date, 'MM')
 order by trunc(a.the_date, 'MM')

Answer 3

试试这样的方法（简化的例子）：

with 
months_int as
(select trunc(min(inc_date), 'MM') min_month, trunc(max(inc_date), 'MM') max_month
 from data),
months as
(
  select add_months(min_month, level-1) mnth_date
  from months_int 
  connect by add_months(min_month, level-1)<= max_month
  )
select  mnth_date, sum(cnt) 
from data  right outer join months on trunc(inc_date, 'MM') = mnth_date
group by mnth_date
order by mnth_date

下面是一个sqlfiddle示例