小菜完全没思路, Oracle 报表统计问题

Question

有一张表,结构如下
查询条件:
1. 查询类型(0--按日查询; 1--按周查询; 2--按月查询)
2. 时间段(当查询类型为0和1时,时间段格式为 开始时间:2012-01-30 结束时间:2013-01-08, 当查询类型为2时, 时间段格式为 开始时间:2012-01 结束时间:2013-08,相比前者少了日 )要求:
日统计以日期为单位进行倒序显示
记录管理员进行管理记录审核通过资源，审核不通过资源，下架资源还有操作时间。
审核通过资源，审核不通过资源，下架资源  通过字段AUDIT_STATE判断
如下为统计的数量报表效果,写出SQL语句, 只要实现效果,不考虑性能日期        工作量 通过     未通过 产品下架

2011-7-25 213 213 213 213
2011-7-24 54 54 54 54
2011-7-23 65 65 65 65
总计 332 332 332 332
以周为单位进行倒序显示
周                工作量 通过 未通过  产品下架

(2013/7/18-2013/7/24) 213 213 213 213
(2013/7/11-2013/7/17) 54 54 54 54
(2013/7/10-2013/7/16) 65 65 65 65
总计 332 332 332 332
以月为单位进行顺序显示
月 工作量 通过   未通过   产品下架

1月 213 213 213 213
2月 54 54 54 54
3月 65 65 65 65Oracle报表统计数量

Answer 1

我也在求类似的sql代码，期待牛人现身说法。

Answer 2

按日期查询，1代表通过，2代表不通过，3代表下架
select t.audit_time,
       count(1),
       case t.audit_state
         when '1' then
          count(1)
         else
          0
       end as a,
       case t.audit_state
         when '2' then
          count(1)
         else
          0
       end as b,
       case t.audit_state
         when '3' then
          count(1)
         else
          0
       end as c
  from testtable t
 where t.audit_time between to_date('2013-08-01', 'yyyy-mm-dd') and
       to_date('2013-08-09', 'yyyy-mm-dd')
 group by t.audit_time, t.audit_state
 order by t.audit_time desc

Answer 3

如果不考虑效率 看着符合要求不..
--构造表 假设有2个字段 审核时间和审核状态
--状态 0：通过  1：未通过  2 已下架
with t1 as
(
     select date'2013-06-13' a_time,0 a_state from dual union all
     select date'2013-06-14' a_time,1 a_state from dual union all
     select date'2013-06-29' a_time,0 a_state from dual union all
     select date'2013-06-29' a_time,1 a_state from dual union all
     select date'2013-06-30' a_time,2 a_state from dual union all
     select date'2013-06-30' a_time,2 a_state from dual union all
     select date'2013-07-05' a_time,1 a_state from dual union all
     select date'2013-07-18' a_time,0 a_state from dual union all
     select date'2013-07-30' a_time,0 a_state from dual 
)
select to_char(t.b_date,'yyyy/mm/dd')||'-'||to_char(t.e_date,'yyyy/mm/dd') "周",
       sum(decode(t1.a_state,0,1,0)) "通过",
       sum(decode(t1.a_state,1,1,0)) "未通过",
       sum(decode(t1.a_state,2,1,0)) "已下架"
from 
(
select date'2013-06-10'+7*(level-1) b_date,date'2013-06-10'+7*level-1 e_date
from dual
connect by level <= (date'2013-08-04' - date'2013-06-10')/7+1
) t left join t1 on t1.a_time between t.b_date and t.e_date
group by to_char(t.b_date,'yyyy/mm/dd')||'-'||to_char(t.e_date,'yyyy/mm/dd')
order by to_char(t.b_date,'yyyy/mm/dd')||'-'||to_char(t.e_date,'yyyy/mm/dd') desc
            周            通过   未通过  已下架
------------------------------------------------------
1 2013/07/29-2013/08/04 1 0 0
2 2013/07/22-2013/07/28 0 0 0
3 2013/07/15-2013/07/21 1 0 0
4 2013/07/08-2013/07/14 0 0 0
5 2013/07/01-2013/07/07 0 1 0
6 2013/06/24-2013/06/30 1 1 2
7 2013/06/17-2013/06/23 0 0 0
8 2013/06/10-2013/06/16 1 1 0

Answer 4

按周查询，这里只是按开始时间为标准，周没有按你的要求显示，只是显示开始时间为多少周
select (select to_char(to_date(substr('2013-08-01', 1, 10), 'yyyy-MM-dd'), 'iw') as week
  from dual) as week,count(1),
       case t.audit_state
         when '1' then
          count(1)
         else
          0
       end as a,
       case t.audit_state
         when '2' then
          count(1)
         else
          0
       end as b,
       case t.audit_state
         when '3' then
          count(1)
         else
          0
       end as c
  from testtable t
 where (select to_char(to_date(substr(to_char(t.audit_time, 'yyyy-mm-dd'),
                                      1,
                                      10),
                               'yyyy-MM-dd'),
                       'iw') as week
          from dual) =
       (select to_char(to_date(substr('2013-08-01', 1, 10), 'yyyy-MM-dd'),
                       'iw') as week
          from dual)
          group by t.audit_state

Answer 5

如果每天都有数据的话 可以通过计算范围内每周to_char(a_time,'ww')的最大最小日期来统计数据

Answer 6

天、周、月分开三张表，加周期时间，天以当天日期为周期时间，周以周的第一天为周期时间，月以月的第一天为周期时
间，每个表以周期时间进行分区。直接SQL查就是了。简单超快，支持千万级数据，亿级数据再加索引就是了。