oracle统计id出现次数,oracle 统计sql

oracle 统计月平均交易次数 ：

select n_tsc_src_usr_id , floor(count(c_tsc_no)/trunc(months_between(max(d_tsc_req_time),min(d_tsc_req_time))))

from tbl_tsc_base

group by n_tsc_src_usr_id

having months_between(max(d_tsc_req_time),min(d_tsc_req_time)) >=1

oracle 日交易统计：

select *

from (select rownum rn, r.*

from (select to_char(to_date(substr(txn_tm, 0, 8), 'yyyy-mm-dd'),

'yyyy-mm-dd') as txn_date,

sum(txn_amt) as txn_amt_sum,

count(1) as txn_count,

-sum(MERC_FEE_AMT) txn_fee_sum

from HPSTJNL

where merc_id = '800000370110002'

and txn_cd = 2010020

group by substr(txn_tm, 0, 8)

order by txn_date desc) r)

where rn >= 1

and rn < 16

oracle 统计总行数：

select count(1)

from (select count(1)

from HPSTJNL

where merc_id = '800000370110002'

and txn_cd = 2010020

group by substr(txn_tm, 0, 8))

最近一个月的统计信息：

and  to_char(to_date(txn_tm,'yyyy-mm-dd hh24:mi:ss'), 'yyyymmdd') between to_char(add_months(sysdate,-1),'yyyymmdd') and to_char(trunc(sysdate), 'yyyymmdd')

上一个月的统计信息: txn_tm字段格式：20120314102320

substr(txn_tm,0,6) = to_char(add_months(sysdate,-1),'yyyymm')