关于统计一段时间内数据的SQL语句

[delphiwcdj]

问题描述：
例如如下的sql语句，我想提取的数据格式为：
20120601 时间段date >= "20120601" and date <= "20120601"内的数据
20120602 时间段date >= "20120601" and date <= "20120602"内的数据
20120603 时间段date >= "20120601" and date <= "20120603"内的数据
。。。
20120630 时间段date >= "20120601" and date <= "20120630"内的数据

不想手动一条条修改日期，如何写一条sql语句来完成上述任务呢，请各位大侠帮忙！

insert table AnalyzeJQ
(
       date,
       num,
       amt,

)
select "20120601",
       count(distinct num),
       sum(amt)

from t_log
where (date >= "20120601" and date <= "20120630")

[delphiwcdj]

谢谢各位，我描述的不是很准确：
1，这里有个限制，即要去重。count(distinct num)
2，mysql有好的实现方法吗

[大明湖畔_帅锅]

再具体点 列出数据看看 知道表结构 不清楚数据呢

[大明湖畔_帅锅]

这类型?

with t1 as
(
     select date'2012-06-01' c1,'a' c2,100 c3 from dual
     union all
     select date'2012-06-01' c1,'a' c2,200 c3 from dual
     union all
     select date'2012-06-10' c1,'b' c2,300 c3 from dual
     union all
     select date'2012-06-10' c1,'c' c2,400 c3 from dual
     union all
     select date'2012-06-20' c1,'c' c2,500 c3 from dual
     union all
     select date'2012-06-25' c1,'c' c2,600 c3 from dual
     union all
     select date'2012-06-30' c1,'c' c2,700 c3 from dual
     union all
     select date'2012-06-30' c1,'d' c2,700 c3 from dual
)

select t2.d_date,
       nvl(c2,0) c2,
       nvl(c3,0) c3,
       sum(c3) over(order by t2.d_date) s_c3
from 
(
    select date'2012-06-01'+level-1 d_date
    from dual
    connect by level <= 30
) t2 left join 
(
    select c1,count(distinct c2) c2,sum(c3) c3
    from t1 
    group by c1
) t1
 on t2.d_date = t1.c1



      d_date     c2     c3     s_c3
---------------------------------------------------
1	2012/6/1	1	300	300
2	2012/6/2	0	0	300
3	2012/6/3	0	0	300
4	2012/6/4	0	0	300
5	2012/6/5	0	0	300
6	2012/6/6	0	0	300
7	2012/6/7	0	0	300
8	2012/6/8	0	0	300
9	2012/6/9	0	0	300
10	2012/6/10	2	700	1000
11	2012/6/11	0	0	1000
12	2012/6/12	0	0	1000
13	2012/6/13	0	0	1000
14	2012/6/14	0	0	1000
15	2012/6/15	0	0	1000
16	2012/6/16	0	0	1000
17	2012/6/17	0	0	1000
18	2012/6/18	0	0	1000
19	2012/6/19	0	0	1000
20	2012/6/20	1	500	1500
21	2012/6/21	0	0	1500
22	2012/6/22	0	0	1500
23	2012/6/23	0	0	1500
24	2012/6/24	0	0	1500
25	2012/6/25	1	600	2100
26	2012/6/26	0	0	2100
27	2012/6/27	0	0	2100
28	2012/6/28	0	0	2100
29	2012/6/29	0	0	2100
30	2012/6/30	2	1400	3500

[勿勿]

不是很明白你的意思。

[大明湖畔_帅锅]

就是查询1-30 每天对应的数据总和吧? 可以构造日期表关联

[Aquarius_Uranus]

给个例子，scott用户下emp
select hiredate,count(1),sum(sal) from emp
where to_char(hiredate,'yyyymmdd') >='19810220' and to_char(hiredate,'yyyymmdd') <='19810222'
group by hiredate

[hupeng213]

with t as (
select date'2012-06-01' as fdate,trunc(dbms_random.value(100,1000),2) as price from dual
union all
select date'2012-06-02',trunc(dbms_random.value(100,1000),2) from dual
union all
select date'2012-06-03',trunc(dbms_random.value(100,1000),2) from dual
union all
select date'2012-06-04',trunc(dbms_random.value(100,1000),2) from dual
union all
select date'2012-06-05',trunc(dbms_random.value(100,1000),2) from dual
union all
select date'2012-06-06',trunc(dbms_random.value(100,1000),2) from dual
)
select fdate,price,sum(price)over(order by fdate) as aa from t
group by fdate,price

FDATE PRICE AA
------------------------- ---------------------- ----------------------
2012-06-01 00:00:00 604.63 604.63
2012-06-02 00:00:00 858.61 1463.24
2012-06-03 00:00:00 519.11 1982.35
2012-06-04 00:00:00 878.45 2860.8
2012-06-05 00:00:00 530.97 3391.77
2012-06-06 00:00:00 325.98 3717.75

[没_没_没超疯]

用分析函数会比较好，先把数据按日期group by一下计算出每天数据的条数，然后用分析函数算出累计至当天的数据之和。

select c_date,
       sum(c_count) over(partition by substr(c_date,1,6) --按月分组
                      order by c_date) as data_count
  from 
  (select c_date,
          count(*) as c_count)
     from tab
    group by c_date)       

[大明湖畔_帅锅]

with t1 as
(
     select date'2012-06-01' c1,100 c2 from dual
     union all
     select date'2012-06-04' c1,200 c2 from dual
     union all
     select date'2012-06-10' c1,300 c2 from dual
     union all
     select date'2012-06-15' c1,400 c2 from dual
     union all
     select date'2012-06-20' c1,500 c2 from dual
     union all
     select date'2012-06-25' c1,600 c2 from dual
     union all
     select date'2012-06-30' c1,700 c2 from dual
)

select t2.d_date,sum(c2) over(order by t2.d_date) c2
from 
(select date'2012-06-01'+level-1 d_date
from dual
connect by level <= 30) t2 left join t1 on t2.d_date = t1.c1

       d_date      c2
-------------------------------
1	2012/6/1	100
2	2012/6/2	100
3	2012/6/3	100
4	2012/6/4	300
5	2012/6/5	300
6	2012/6/6	300
7	2012/6/7	300
8	2012/6/8	300
9	2012/6/9	300
10	2012/6/10	600
11	2012/6/11	600
12	2012/6/12	600
13	2012/6/13	600
14	2012/6/14	600
15	2012/6/15	1000
16	2012/6/16	1000
17	2012/6/17	1000
18	2012/6/18	1000
19	2012/6/19	1000
20	2012/6/20	1500
21	2012/6/21	1500
22	2012/6/22	1500
23	2012/6/23	1500
24	2012/6/24	1500
25	2012/6/25	2100
26	2012/6/26	2100
27	2012/6/27	2100
28	2012/6/28	2100
29	2012/6/29	2100
30	2012/6/30	2800

[大明湖畔_帅锅]

如果还要查询当天的数量 添加一个就可以了

select t2.d_date,nvl(c2,0) c1,sum(c2) over(order by t2.d_date) c2
from 
(select date'2012-06-01'+level-1 d_date
from dual
connect by level <= 30) t2 left join t1 on t2.d_date = t1.c1

[delphiwcdj]

[Quote=引用 9 楼 的回复:]

再具体点 列出数据看看 知道表结构 不清楚数据呢
[/Quote]
抱歉，数据是这样的，比如：

insert table AnalyzeJQ
(
       date, -- 日期
       num,  -- 用户的ID
       amt   -- 支付金额
)
-- 每天用户支付过就会产生一条上述格式的流水

-- 要统计的格式：
-- 20120701 消费的用户数 消费的总金额
-- 20120702 1号至2号消费的用户数 1号至2号消费的总金额
-- 20120703 1号至3号消费的用户数 1号至3号消费的总金额

即一个叠加的数据，其中消费的用户数要去重

[delphiwcdj]

mysql有木有好的实现方法呢？

[贝鱼]

不用那么麻烦，4楼的稍微修改一下就可以实现

[delphiwcdj]

[Quote=引用 13 楼 的回复:]

SQL code

with t1 as
(
select date'2012-06-01' c1,'a' c2,100 c3 from dual
union all
select date'2012-06-01' c1,'a' c2,200 c3 from dual
union all
select date'2012-06-10' c1,'……
[/Quote]
谢谢，和我的意思还有点偏差，我是想得到这样的数据

     c1      c2     c3
------------------------------------------
1    2012/6/1    1    300
2    2012/6/10    2    1000
3    2012/6/20    3   1500
4    2012/6/25    3    2100
5    2012/6/30    4    3500

[delphiwcdj]

用子查询就可以了，结贴，谢谢各位！

[大明湖畔_帅锅]

with t1 as
(
     select date'2012-06-01' c1,'a' c2,100 c3 from dual
     union all
     select date'2012-06-01' c1,'a' c2,200 c3 from dual
     union all
     select date'2012-06-10' c1,'b' c2,300 c3 from dual
     union all
     select date'2012-06-10' c1,'c' c2,400 c3 from dual
     union all
     select date'2012-06-20' c1,'c' c2,500 c3 from dual
     union all
     select date'2012-06-25' c1,'c' c2,600 c3 from dual
     union all
     select date'2012-06-30' c1,'c' c2,700 c3 from dual
     union all
     select date'2012-06-30' c1,'d' c2,700 c3 from dual
)

select c1,
       count(distinct c2) c2,
       (select sum(c3) from t1 b where b.c1 <= a.c1) c3
from t1 a
group by c1


     c1      c2     c3
------------------------------------------
1	2012/6/1	1	300
2	2012/6/10	2	1000
3	2012/6/20	1	1500
4	2012/6/25	1	2100
5	2012/6/30	2	3500

[yuyu1980]

select a.date,count(b.num) num,sum(b.amt) amt
from AnalyzeJQ a,AnalyzeJQ b
where a.date >= '20120701'
and a.date < '20120801'
and b.date >= '20120701'
and b.date <= a.date;

[yuyu1980]

select a.date,count(distinct b.num) num,sum(b.amt) amt
from AnalyzeJQ a,AnalyzeJQ b
where a.date >= '20120701'
and a.date < '20120801'
and b.date >= '20120701'
and b.date <= a.date;

[yejihui9527]

select date
count(distinct num),
sum(amt)

from t_log
where (date >= "20120601" and date <= "20120630")
group by date