oracle 可有什么方法 统计每月累积购买人数
比如说有个门店 2008年购买商品的人数
表:t_buy_log 字段 f_username 用户名称;购买时间f_buytime1 如果是统计每月购买人数
select to_char(f_buytime,'yyymm'),count(distinct f_username)
from t_buy_log
group by to_char(f_buytime,'yyymm')
order by to_char(f_buytime,'yyymm') asc结果:
1月 100人
2月 150人
3月 50人2 如果统计每月累积购买人数 ?
1月 100人
2月 是1月和2月的购买人数 客户有重复 值在100-250之间
3月 是1-3月的购买人数这个该怎么写呢?
用分析函数的SUM 好像不行
1-1
select to_char(f_buytime,'yyymm'),count(distinct f_username)
from t_buy_log
WHERE to_char(f_buytime,'yyymm')='200901' 1-2
select to_char(f_buytime,'yyymm'),count(distinct f_username)
from t_buy_log
WHERE to_char(f_buytime,'yyymm') between '200901' and '200902'
1-3
select to_char(f_buytime,'yyymm'),count(distinct f_username)
from t_buy_log
WHERE to_char(f_buytime,'yyymm') between '200901' and '200903'
比如说有个门店 2008年购买商品的人数
表:t_buy_log 字段 f_username 用户名称;购买时间f_buytime1 如果是统计每月购买人数
select to_char(f_buytime,'yyymm'),count(distinct f_username)
from t_buy_log
group by to_char(f_buytime,'yyymm')
order by to_char(f_buytime,'yyymm') asc结果:
1月 100人
2月 150人
3月 50人2 如果统计每月累积购买人数 ?
1月 100人
2月 是1月和2月的购买人数 客户有重复 值在100-250之间
3月 是1-3月的购买人数这个该怎么写呢?
用分析函数的SUM 好像不行
1-1
select to_char(f_buytime,'yyymm'),count(distinct f_username)
from t_buy_log
WHERE to_char(f_buytime,'yyymm')='200901' 1-2
select to_char(f_buytime,'yyymm'),count(distinct f_username)
from t_buy_log
WHERE to_char(f_buytime,'yyymm') between '200901' and '200902'
1-3
select to_char(f_buytime,'yyymm'),count(distinct f_username)
from t_buy_log
WHERE to_char(f_buytime,'yyymm') between '200901' and '200903'
UNION ALL 外可有其它办法呢? 一条语句能搞定 不用零时表的
select t_time.ct,
(select count(distinct f_username) from t_buy_log t where t.to_char(f_buytime, 'yyyyMM') <= t_time.ct) rs
from
(select disinct(t.to_char(f_buytime, 'yyyyMM') ct from t_buy_log t) t_time
这样行不行?
from
(select distinct f_username USERNAME, to_char(f_buytime,'yyyymm') BUYTIME
from t_buy_log) a
WHERE a.BUYTIME between 'YYYYMM' and 'YYYYMM'
---------- ----------
1 1
1 2
1 3
2 4
2 5
2 6
2 7
3 1
3 2
3 310 rows selected.
SQL> select id1,sum(cnt) over(order by id1) total
2 from
3 (
4 select id1,count(id2) cnt
5 from t3
6 group by id1
7 ); ID1 TOTAL
---------- ----------
1 3
2 7
3 10
sum(count(distinct f_username))over(order by to_char(f_buytime,'yyymm')),
from t_buy_log
group by to_char(f_buytime,'yyymm')
order by to_char(f_buytime,'yyymm') asc
前面没考虑到如果有重复用户的话那么不计入的情况
下面我们在ID1=3的情况下再增加一条记录看看
这个时候ID1=3时既有与前面ID1为1、2重复的(ID2为1、2、3) 又有新的(ID2为9)
SQL> insert into t3 values(3,9);1 row created.
SQL> select * from t3; ID1 ID2
---------- ----------
1 1
1 2
1 3
2 4
2 5
2 6
2 7
3 1
3 2
3 3
3 911 rows selected.
d1) total
2 from
3 (
4 select id1,count(case when rn=1 then id2 else 0 end) cnt,rn
5 from
6 (
7 select id1,id2,
8 row_number() over(partition by id2 order by id1) rn
9 from t3
10 order by 1
11 )
12 group by id1,rn
13 )
14 order by 1
15 ; ID1 TOTAL
---------- ----------
1 3
2 7
3 8
SQL> select distinct id1,
2 sum(case when rn=1 then cnt else 0 end) over(order by id1) total
3 from
4 (
5 select id1,count(id2) cnt,rn
6 from
7 (
8 select id1,id2,
9 row_number() over(partition by id2 order by id1) rn
10 from t3
11 order by 1
12 )
13 group by id1,rn
14 )
15 order by 1; ID1 TOTAL
---------- ----------
1 3
2 7
3 8
from t_Base_Prouser_Suc b
Where f_Expectenddate Between to_date('2009-01-01','YYYY-MM-dd') And to_date('2009-03-01','YYYY-MM-dd')
group by to_char(f_Expectenddate,'yyymm')
order by to_char(f_Expectenddate,'yyymm') asc sweetBug的我改成INNER JOIN 就能跑出正确数据来了
select /*+parallel(b,8)*/ oldtable.tm,Count(Distinct f_username) t
from t_Base_Prouser_Suc b
Inner Join
(
select (to_char(to_date('2009-01-01','YYYY-MM-DD'),'yyyymm')+Level-1) As tm
from dual
connect by level < months_between(to_date('2009-03-01','YYYY-MM-DD'),to_date('2009-01-01','YYYY-MM-DD'))+1
) oldtable
On f_yearmonth = tm
Where f_yearmonth <= oldtable.tm
Group By oldtable.tm