SQL截断/组/按日期(天/月/季度/年)排序(不包括数据的总和跳过日期) [英] SQL trunc/group/order by dates (day/month/quarter/year) with sum skip dates with no data

问题描述

我参与了需要按日期构建直方图的项目.在我之前，这是通过Java代码通过对每个矩形(日期子区域)向数据库进行大量SQL查询来完成的.

I involved in project where I need to build histogram by dates. Before me this done in Java code by tons of SQL queries to DB for each rectangles (date subregions).

我尝试另一种方法:

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收集数据.但是，如果某个月没有数据，我会错过直方图中的矩形！！！

and collect data from ResultSet in Java code. But if some month have no data, I miss rectangle in histogram!!!

是否可以修复SQL(或者可能是PL/SQL)表达式，以使结果中包含缺失的日期且总和为零?

Is it possible to fix SQL (or may be PL/SQL) expression to include missing dates in result with zero sum?

或者如何在Java中构建更优雅的日期序列生成器以查找丢失的日期(与天/月/季度/年对齐)?

Or how to build more elegant date sequence generator in Java to find missing dates (aligned to days/months/quarters/years)?

推荐答案

您需要先创建日期列表；通过创建日历表或使用连接方式语法.

You need to create your list of dates first; either by creating a calender table or by using the CONNECT BY syntax.

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

然后您可以将OUTLE OUTER JOIN联接到您的主查询中，以确保填充了缺口:

You can then LEFT OUTER JOIN this to your main query to ensure the gaps are populated:

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列，而是使用生成的日期中的日期.如果您想将日期修改为不属于月末，则可以使用该方法，但是如果您希望按月进行修改，则可以在WITH子句中截断该日期.不过，在执行此操作之前，您应该了解索引.如果您的表是在DATE而不是TRUNC(DATE, 'MM')上建立索引，则最好在DATE单独进行JOIN.

You no longer need the WHERE clause as this is taken care of in the JOIN. Note that I'm not using the DATE column from your main table but the date from the generated one instead. This will work if you want to modify the dates to not be the end of the month but if you want it month-wise you could truncate the date in the WITH clause. You should be aware of indexes before doing this though. If your table is indexed on DATE and not TRUNC(DATE, 'MM') then it is preferable to JOIN on DATE alone.

DATE是列的坏名称，因为它是保留字；我怀疑您没有使用它，但是您应该知道.

DATE is a bad name for a column as it's a reserved word; I suspect you're not using it but you should be aware.

如果您使用日历表，它将看起来像这样:

If you were using a calender table it would look something like this:

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')

这篇关于SQL截断/组/按日期(天/月/季度/年)排序(不包括数据的总和跳过日期)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！