求一个分组统计的SQL语句

Question

有一张账目流水表【table_Flow】，分别记录了账目发生的名称[itemname]和具体时间[optime],时间格式为：yyyy-mm-dd hh24:mi
:ss
先需要统计当前日前一周内，每天发生账目的次数，例如：
日期：               发生次数：
2015-07-31         10
2015-08-01          8
2015-08-02          0
2015-08-03         12
2015-08-04          7
2015-08-05          6
2015-08-06          0
目前使用传统的方法：
select  to_Char(optime,'yyyy-mm-dd') as times,count(1) as nums from table_flow  where optime between to_date('2015-07-31','yyyy-mm-dd') and  to_date('2015-08-07','yyyy-mm-dd')  group by to_Char(optime,'yyyy-mm-dd')  order by times asc 
可以得到不含发生次数为0的天数的统计值，现在需要有个语句能够自动统计出每一日前一周的统计数据值，如果当日没有流水记录自动补0。
麻烦会的人帮助下，谢谢!!!

Answer 1

示例：with table_Flow as (
select 'X_NAME' itemname,to_date('2015-08-06 08:00:00','yyyy-mm-dd hh24:mi:ss') optime from dual
union all
select 'X_NAME' itemname,to_date('2015-08-06 09:00:00','yyyy-mm-dd hh24:mi:ss') optime from dual
union all
select 'X_NAME' itemname,to_date('2015-08-04 08:00:00','yyyy-mm-dd hh24:mi:ss') optime from dual
union all
select 'X_NAME' itemname,to_date('2015-08-03 08:00:00','yyyy-mm-dd hh24:mi:ss') optime from dual
union all
select 'Y_NAME' itemname,to_date('2015-08-03 08:00:00','yyyy-mm-dd hh24:mi:ss') optime from dual
union all
select 'Y_NAME' itemname,to_date('2015-08-04 08:00:00','yyyy-mm-dd hh24:mi:ss') optime from dual
)      
  select itemname,
         t_date,
         sum(case when trunc(A.optime) = T.t_date then 1 else 0 end) nums
    from table_Flow A,
         (select (trunc(sysdate) - 7 + rownum) t_date
            from dual
          connect by rownum <= 7) T
   group by itemname, t_date

Answer 2

这样的 inner join 行不行？
可能应该用T表去 left join table_Flow 表吧？

Answer 3

Select  optime As 日期,Sum(num) As 数量
,(Select Sum(num) From table_flow  a Where  a.optime <= table_flow.optime 
And  table_flow.optime -  a.optime < 7
)  As 前一周数量
From table_flow 
Group By optime Order By 日期    ;
看看这个可以不，待大神除更厉害的方法，这个应该效率什么的不好

Answer 4

--------创建表--------------------------
CREATE TABLE T1(ITEMNAME VARCHAR2(20),OPTIME DATE);
--------创建测试数据--------------------------
DECLARE
CNT NUMBER:=20;
BEGIN
  WHILE CNT >=0
    LOOP
    INSERT INTO T1(ITEMNAME,OPTIME) VALUES('A',SYSDATE-CNT);  
    CNT:=CNT-1;
END LOOP;
 END;
 
 DELETE FROM  T1 WHERE  TO_CHAR(OPTIME,'YYYY-MM-DD')='2015-08-10'
    INSERT INTO T1(ITEMNAME,OPTIME) VALUES('A',SYSDATE-2);
 
------------查询语句------------------------------
 
 WITH A AS(
 SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') AS C1 FROM DUAL
 UNION
SELECT TO_CHAR(SYSDATE-1,'YYYY-MM-DD') AS C1 FROM DUAL
 UNION
SELECT TO_CHAR(SYSDATE-2,'YYYY-MM-DD') AS C1 FROM DUAL
 UNION
SELECT TO_CHAR(SYSDATE-3,'YYYY-MM-DD') AS C1 FROM DUAL
 UNION
SELECT TO_CHAR(SYSDATE-4,'YYYY-MM-DD') AS C1 FROM DUAL
 UNION
SELECT TO_CHAR(SYSDATE-5,'YYYY-MM-DD') AS C1 FROM DUAL
 UNION
SELECT TO_CHAR(SYSDATE-6,'YYYY-MM-DD') AS C1 FROM DUAL
),
B AS(
SELECT TO_CHAR(OPTIME,'YYYY-MM-DD') AS OPTIME,COUNT(1) AS CNT FROM T1 GROUP BY TO_CHAR(OPTIME,'YYYY-MM-DD')
 )
 SELECT A.C1,NVL(B.CNT,0) 
 FROM A LEFT JOIN B ON A.C1=B.OPTIME
 ORDER BY A.C1
 ;

Answer 5

with table_Flow as (
select 'X_NAME' itemname,to_date('2015-08-11 08:00:00','yyyy-mm-dd hh24:mi:ss') optime from dual
union all
select 'X_NAME' itemname,to_date('2015-08-11 09:00:00','yyyy-mm-dd hh24:mi:ss') optime from dual
union all
select 'X_NAME' itemname,to_date('2015-08-13 08:00:00','yyyy-mm-dd hh24:mi:ss') optime from dual
union all
select 'X_NAME' itemname,to_date('2015-08-15 08:00:00','yyyy-mm-dd hh24:mi:ss') optime from dual
union all
select 'Y_NAME' itemname,to_date('2015-08-15 08:00:00','yyyy-mm-dd hh24:mi:ss') optime from dual
union all
select 'Y_NAME' itemname,to_date('2015-08-22 08:00:00','yyyy-mm-dd hh24:mi:ss') optime from dual
) ,
tmp as
(select trunc(sysdate,'iw')-8+level dt from dual connect by level<=7)
select dt, sum(times)
  from (select a.dt, nvl2(b.optime, 1, 0) times
           from tmp a
           left join table_flow b
             on a.dt = trunc(b.optime))
 group by dt order by dt