统计SQL

一、统计分析 - 用户记录查询

查询条件

SELECT TC.*,TD.AREA_NAME FROM ( SELECT TA.*,TB.AREA_ID FROM ( SELECT * FROM T_IMP_MAP_DATA WHERE 1=1 AND

MDN = ? AND IMP_TIME BETWEEN ? AND ? ) TA LEFT JOIN T_HCODE TB ON TA.HCODE = TB.HCODE)TC LEFT JOIN T_HCODE_AREA TD ON TC.AREA_ID = TD.AREA_ID ORDER BY TC.MDN

二、统计漫入用户短信发送

SELECT TEMPA.USERCOUNT, TEMPB.* FROM (SELECT SUBSTR(TIMESTAMP,1,8)

SU8 ,COUNT(*) USERCOUNT FROM T_IMP_MAP_DATA WHERE SUBSTR(TIMESTAMP,1,8) BETWEEN ? AND ? GROUP BY SUBSTR(TIMESTAMP,1,8)) TEMPA ,( SELECT COUNT(*) AS TOTALCOUNT , SUM(CASE STATUS WHEN 'OK' THEN 1 ELSE 0 END) SUCCESSCOUNT, SUM(CASE STATUS WHEN 'OK' THEN 0 ELSE 1 END) FAILCOUNT,

SUBSTR(A.SUBMITTIME,1,8) SUBTIME, B.SERVICE_NAME, C.SMS_NAME FROM T_SMS_CDR A LEFT JOIN T_SERVICE B ON A.SERVICE_ID = B.SERVICE_ID LEFT JOIN T_SERVICE_SMS C ON C.SMS_ID = A.SMS_ID WHERE 1=1 AND A.SERVICE_ID = ? AND A.SUBMITTIME BETWEEN ? AND ? GROUP BY SUBSTR(A.SUBMITTIME,1,8) ,

B.SERVICE_NAME,C.SMS_NAME ORDER BY SUBSTR(A.SUBMITTIME,1,8) DESC ) TEMPB WHERE TEMPA.SU8 = TEMPB.SUBTIME

三、按归属地统计漫游数

SELECT COUNT(*) TOTALCOUNT, E.SU8 TIMESTAMP ,area_name FROM ( SELECT MDN ,SUBSTR(TIMESTAMP,0,8) SU8 ,area_name FROM( SELECT * FROM T_IMP_MAP_DATA B LEFT JOIN T_HCODE C ON B.HCODE = C.HCODE LEFT JOIN

T_HCODE_AREA D ON D.AREA_ID = C.AREA_ID WHERE B.TIMESTAMP BETWEEN ? AND ? AND C.AREA_ID = ? )A GROUP BY

MDN ,SUBSTR(TIMESTAMP,0,8),area_name )E GROUP BY E.SU8,area_name

四、用户到访的MSC统计

SELECT C.UT AS USERCOUNT,C.GT,C.SU8 TIMESTAMP,D.CT SENDCOUNT FROM ( SELECT COUNT(1) UT, GT,SU8 FROM( SELECT GT,SUBSTR(A.TIMESTAMP,1,8) SU8 ,MDN FROM T_IMP_MAP_DATA A WHERE SUBSTR(A.TIMESTAMP,1,8) BETWEEN ? AND ? GROUP BY GT,SUBSTR(A.TIMESTAMP,1,8) ,MDN ) B GROUP BY GT,SU8 )C ,( SELECT SUM(CT) CT ,GT,SU8 FROM ( SELECT DSTADDR , GT , SUBSTR(A.SUBMITTIME,1,8) SU8,COUNT(1) CT FROM T_SMS_CDR A WHERE SUBSTR(A.SUBMITTIME,1,8) BETWEEN ? AND ? GROUP BY DSTADDR , GT ,

SUBSTR(A.SUBMITTIME,1,8)) GROUP BY GT,SU8)D WHERE D.GT = C.GT AND C.SU8 = D.SU8

五、查询已发短信

1） 不选择业务名 与短信名

SELECT A.DSTADDR,A.SUBMITTIME,B.SERVICE_ID, B.SERVICE_NAME ,C.SMS_NAME SERNAME,A.STATUS, A.NOTIFYCODE,A.GT,A.CELL_CODE,E.SMS_NAME RNAME FROM T_SMS_CDR A LEFT JOIN T_SERVICE B ON B.SERVICE_ID= A.SERVICE_ID LEFT JOIN T_SERVICE_SMS C ON C.SMS_ID = A.SMS_ID LEFT JOIN T_ROAM_SMS E ON E.SMS_ID = A.SMS_ID WHERE 1 = 1 AND A.DSTADDR = ? AND A.SUBMITTIME BETWEEN ? AND ? ORDER BY A.SUBMITTIME DESC

2） 选择业务名

SELECT A.DSTADDR,A.SUBMITTIME,B.SERVICE_ID,

B.SERVICE_NAME ,C.SMS_NAME SERNAME,A.STATUS,

A.NOTIFYCODE,A.GT,A.CELL_CODE,E.SMS_NAME RNAME FROM T_SMS_CDR A LEFT JOIN T_SERVICE B ON B.SERVICE_ID= A.SERVICE_ID LEFT JOIN

T_SERVICE_SMS C ON C.SMS_ID = A.SMS_ID LEFT JOIN T_ROAM_SMS E ON E.SMS_ID = A.SMS_ID WHERE 1 = 1 AND a.SERVICE_ID = ? AND

A.DSTADDR = ? AND A.SUBMITTIME BETWEEN ? AND ? ORDER BY A.SUBMITTIME DESC

3） 选择业务名与短信名

SELECT A.DSTADDR,A.SUBMITTIME,B.SERVICE_ID,

B.SERVICE_NAME ,C.SMS_NAME SERNAME,A.STATUS,

A.NOTIFYCODE,A.GT,A.CELL_CODE,E.SMS_NAME RNAME FROM T_SMS_CDR A LEFT JOIN T_SERVICE B ON B.SERVICE_ID= A.SERVICE_ID LEFT JOIN

T_SERVICE_SMS C ON C.SMS_ID = A.SMS_ID LEFT JOIN T_ROAM_SMS E ON E.SMS_ID = A.SMS_ID WHERE 1 = 1 AND a.SERVICE_ID = ? AND

A.SMS_ID = ? AND A.DSTADDR = ? AND A.SUBMITTIME BETWEEN ? AND ? ORDER BY A.SUBMITTIME DESC

六、按业务统计发送短信

a) 一般

1)不选择业务名与短信名

SELECT COUNT(*) AS TOTALCOUNT , SUM(CASE STATUS WHEN 'OK' THEN 1 ELSE 0 END) SUCCESSCOUNT, SUM(CASE STATUS WHEN 'OK' THEN 0 ELSE 1 END) FAILCOUNT, SUBSTR(A.SUBMITTIME,1,8) SUBTIME,

B.SERVICE_NAME,B.SERVICE_ID,C.SMS_NAME ,e.sms_name rname FROM

T_SMS_CDR A LEFT JOIN T_SERVICE B ON A.SERVICE_ID = B.SERVICE_ID LEFT JOIN T_SERVICE_SMS C ON C.SMS_ID = A.SMS_ID left join t_roam_sms E on E.SMS_ID = a.SMS_ID WHERE 1=1 AND A.SUBMITTIME BETWEEN ? AND ? GROUP BY SUBSTR(A.SUBMITTIME,1,8) ,

B.SERVICE_NAME,C.SMS_NAME,e.sms_name,B.SERVICE_ID ORDER BY SUBSTR(A.SUBMITTIME,1,8) DESC

2)选择业务名

SELECT COUNT(*) AS TOTALCOUNT , SUM(CASE STATUS WHEN 'OK' THEN 1 ELSE 0 END) SUCCESSCOUNT, SUM(CASE STATUS WHEN 'OK' THEN 0 ELSE 1 END) FAILCOUNT, SUBSTR(A.SUBMITTIME,1,8) SUBTIME,

B.SERVICE_NAME,B.SERVICE_ID,C.SMS_NAME ,e.sms_name rname FROM

T_SMS_CDR A LEFT JOIN T_SERVICE B ON A.SERVICE_ID = B.SERVICE_ID LEFT JOIN T_SERVICE_SMS C ON C.SMS_ID = A.SMS_ID left join

t_roam_sms E on E.SMS_ID = a.SMS_ID WHERE 1=1 AND A.SERVICE_ID = ? AND A.SUBMITTIME BETWEEN ? AND ? GROUP BY SUBSTR(A.SUBMITTIME,1,8) , B.SERVICE_NAME,C.SMS_NAME,e.sms_name,B.SERVICE_ID ORDER BY SUBSTR(A.SUBMITTIME,1,8) DESC

3)选择业务名与短信名

SELECT COUNT(*) AS TOTALCOUNT , SUM(CASE STATUS WHEN 'OK' THEN 1 ELSE 0 END) SUCCESSCOUNT, SUM(CASE STATUS WHEN 'OK' THEN 0 ELSE 1 END) FAILCOUNT, SUBSTR(A.SUBMITTIME,1,8) SUBTIME,

B.SERVICE_NAME,B.SERVICE_ID,C.SMS_NAME ,e.sms_name rname FROM

T_SMS_CDR A LEFT JOIN T_SERVICE B ON A.SERVICE_ID = B.SERVICE_ID LEFT JOIN T_SERVICE_SMS C ON C.SMS_ID = A.SMS_ID left join

t_roam_sms E on E.SMS_ID = a.SMS_ID WHERE 1=1 AND A.SERVICE_ID = ? AND A.SMS_ID = ? AND A.SUBMITTIME BETWEEN ? AND ? GROUP BY SUBSTR(A.SUBMITTIME,1,8) ,

B.SERVICE_NAME,C.SMS_NAME,e.sms_name,B.SERVICE_ID ORDER BY SUBSTR(A.SUBMITTIME,1,8) DESC

b) 最忙时

1) 不选择业务名与短信名

select d.*, TEMPD.SERVICE_NAME, TEMPE.SMS_NAME, TEMPF.SMS_NAME RNAME from (select max(b.totalcount) maxtotalcount, b.service_id, b.sms_id from (select * from v_sms_cdr_st a where a.subtime between ? and ?) b group by b.service_id, b.sms_id) c,

(select * from v_sms_cdr_st a where a.subtime between ? and ?) d LEFT JOIN T_SERVICE TEMPD ON D.SERVICE_ID = TEMPD.SERVICE_ID

LEFT JOIN T_SERVICE_SMS TEMPE ON D.SMS_ID = TEMPE.SMS_ID LEFT JOIN T_ROAM_SMS TEMPF ON D.SMS_ID = TEMPF.SMS_ID

where c.maxtotalcount = d.totalcount and c.service_id = d.service_id and c.sms_id = d.sms_id

2) 选择业务名

SELECT D.*,TEMPD.SERVICE_NAME,TEMPE.SMS_NAME,TEMPF.SMS_NAME RNAME FROM (SELECT MAX(B.TOTALCOUNT) MAXTOTALCOUNT, B.SERVICE_ID, B.SMS_ID FROM (SELECT * FROM V_SMS_CDR_ST A WHERE 1=1 AND SERVICE_ID = ? AND A.SUBTIME BETWEEN ? AND ? ) B GROUP BY B.SERVICE_ID, B.SMS_ID) C, (SELECT * FROM V_SMS_CDR_ST A WHERE 1 = 1 AND SERVICE_ID = ? AND A.SUBTIME BETWEEN ? AND ? )D LEFT JOIN T_SERVICE TEMPD ON D.SERVICE_ID = TEMPD.SERVICE_ID LEFT JOIN T_SERVICE_SMS TEMPE ON D.SMS_ID = TEMPE.SMS_ID LEFT JOIN T_ROAM_SMS TEMPF ON D.SMS_ID = TEMPF.SMS_ID WHERE C.MAXTOTALCOUNT = D.TOTALCOUNT AND C.SERVICE_ID = D.SERVICE_ID AND C.SMS_ID = D.SMS_ID

3) 选择业务名与短信名

SELECT D.*,TEMPD.SERVICE_NAME,TEMPE.SMS_NAME,TEMPF.SMS_NAME RNAME FROM (SELECT MAX(B.TOTALCOUNT) MAXTOTALCOUNT, B.SERVICE_ID, B.SMS_ID FROM (SELECT * FROM V_SMS_CDR_ST A WHERE 1=1 AND SERVICE_ID = ? AND SMS_ID = ? AND A.SUBTIME BETWEEN ? AND ? ) B GROUP BY

B.SERVICE_ID, B.SMS_ID) C, (SELECT * FROM V_SMS_CDR_ST A WHERE 1 = 1 AND SERVICE_ID = ? AND SMS_ID = ? AND A.SUBTIME BETWEEN ? AND ? )D LEFT JOIN T_SERVICE TEMPD ON D.SERVICE_ID =

TEMPD.SERVICE_ID LEFT JOIN T_SERVICE_SMS TEMPE ON D.SMS_ID = TEMPE.SMS_ID LEFT JOIN T_ROAM_SMS TEMPF ON D.SMS_ID = TEMPF.SMS_ID WHERE C.MAXTOTALCOUNT = D.TOTALCOUNT AND C.SERVICE_ID = D.SERVICE_ID AND C.SMS_ID = D.SMS_ID

c) 按时

1) 不选择业务名与短信名

SELECT A.*,B.SMS_NAME,C.SMS_NAME RNAME,D.SERVICE_NAME FROM ( SELECT * FROM V_SMS_CDR_ST WHERE 1=1 AND SUBTIME BETWEEN ? AND ? ) A LEFT JOIN T_SERVICE_SMS B ON A.SMS_ID = B.SMS_ID LEFT JOIN T_ROAM_SMS C ON A.SMS_ID = C.SMS_ID LEFT JOIN T_SERVICE D ON D.SERVICE_ID = A.SERVICE_ID ORDER BY A.SUBTIME DESC

2) 选择业务名

SELECT A.*,B.SMS_NAME,C.SMS_NAME RNAME,D.SERVICE_NAME FROM ( SELECT * FROM V_SMS_CDR_ST WHERE 1=1 AND SERVICE_ID = ? AND SUBTIME BETWEEN ? AND ? ) A LEFT JOIN T_SERVICE_SMS B ON A.SMS_ID = B.SMS_ID LEFT JOIN T_ROAM_SMS C ON A.SMS_ID = C.SMS_ID LEFT JOIN T_SERVICE D ON D.SERVICE_ID = A.SERVICE_ID ORDER BY A.SUBTIME DESC

3) 选择业务名与短信名

SELECT A.*,B.SMS_NAME,C.SMS_NAME RNAME,D.SERVICE_NAME FROM ( SELECT * FROM V_SMS_CDR_ST WHERE 1=1 AND SERVICE_ID = ? AND SMS_ID = ? AND SUBTIME BETWEEN ? AND ? ) A LEFT JOIN T_SERVICE_SMS B ON A.SMS_ID = B.SMS_ID LEFT JOIN T_ROAM_SMS C ON A.SMS_ID = C.SMS_ID LEFT JOIN T_SERVICE D ON D.SERVICE_ID = A.SERVICE_ID ORDER BY A.SUBTIME DESC