合作会员

合作会员总数
/dbs/api/cooperativeStores/count?merchantCode=800202&end=2018-04-01

select decode(sum(a.cooperativequantity),null,0,sum(a.cooperativequantity)) from 
(select b.merchantcode,b.cooperativequantity,row_number() 
over (partition by b.merchantcode order by to_date(b.statisticstime,'yyyy-MM-dd') desc)rn
from bi_cooperative_store b where b.merchantcode='800202' and b.statisticstime<='2018-04-01' ) a where rn=1 

期间新增会员数
/dbs/api/cooperativeStores/added?merchantCode=800202&start=2018-01-01&end=2018-04-01

select decode(sum(addedquantity),null,0,sum(addedquantity)) from bi_cooperative_store 
where statisticstime>='2018-01-01' and statisticstime<='2018-03-01' 
and merchantcode='800202' 

期间每月会员数
/dbs/api/cooperativeStores/countByMonth?merchantCode=800202&start=2018-01-01&end=2018-04-01

select * from 
(select to_char(to_date('2018-01','yyyy-MM')-1,'yyyy-MM') monthlist,max(cooperativequantity) qty from bi_cooperative_store 
where merchantcode='800202' and substr(statisticstime,0,7)<'2018-01' 
union
select t1.monthlist,decode(t3.qty,null,0,t3.qty) qty from
(SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2018-01', 'yyyy-MM'), ROWNUM - 1),'yyyy-MM') as monthlist FROM DUAL
CONNECT BY ROWNUM <=  months_between(to_date('2018-04', 'yyyy-MM'),TO_DATE('2018-01', 'yyyy-MM')) + 1) t1
left join 
(select t2.monthlist ,sum(t2.qty) qty from
(select merchantcode,substr(statisticstime,0,7) monthlist,cooperativequantity qty,row_number() 
over (partition by merchantcode,substr(statisticstime,0,7) order by to_date(statisticstime,'yyyy-MM-dd') desc)rn
from bi_cooperative_store 
where substr(statisticstime,0,7)>='2018-01' and substr(statisticstime,0,7)<='2018-04' 
and merchantcode='800202'
)t2 where t2.rn=1 group by t2.monthlist)t3 on t1.monthlist=t3.monthlist)a order by a.monthlist

select * from 
(select to_char(to_date('2018-01','yyyy-MM')-1,'yyyy-MM') monthlist,sum(t6.qty) from
(select merchantcode,max(cooperativequantity) qty from bi_cooperative_store 
where substr(statisticstime,0,7)<'2018-01' 
and merchantcode='800202'
group by merchantcode)t6 
union
select t1.monthlist,decode(t3.qty,null,0,t3.qty) from 
(SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2018-01', 'yyyy-MM'), ROWNUM - 1),'yyyy-MM') as monthlist FROM DUAL
CONNECT BY ROWNUM <=  months_between(to_date('2018-04', 'yyyy-MM'),TO_DATE('2018-01', 'yyyy-MM')) + 1) t1
left join 
(select substr(statisticstime,0,7) monthlist,sum(addedquantity) qty from bi_cooperative_store 
where substr(statisticstime,0,7)>='2018-01' and substr(statisticstime,0,7)<='2018-04'  
and merchantcode='800202'
group by substr(statisticstime,0,7))t3 
on t1.monthlist=t3.monthlist) a order by a.monthlist

每天合作会员总数
/dbs/api/cooperativeStores/countByDay?merchantCode=800202&start=2018-01-01&end=2018-04-01

select * from
(select to_char(to_date('2018-01-01','yyyy-MM-dd')-1,'yyyy-MM-dd') daylist,sum(t6.qty) from
(select merchantcode,max(cooperativequantity) qty from bi_cooperative_store 
where statisticstime<'2018-01-01' 
and merchantcode='800202'
group by merchantcode)t6 
union
select t1.daylist,decode(t2.cooperativequantity,null,0,t2.cooperativequantity) qty from
(SELECT TO_CHAR(TO_DATE('2018-01-01', 'yyyy-MM-dd') + ROWNUM - 1,'yyyy-MM-dd') as daylist FROM DUAL
CONNECT BY ROWNUM <=  trunc(to_date('2018-04-01', 'yyyy-MM-dd') - TO_DATE('2018-01-01', 'yyyy-MM-dd')-1) + 1) t1
left join 
(select statisticstime,sum(addedquantity) cooperativequantity from bi_cooperative_store 
where statisticstime>='2018-01-01' and statisticstime<='2018-04-01'
and merchantcode='800202' 
group by statisticstime)t2 on t1.daylist=t2.statisticstime) a order by a.daylist