【例子】

1、

wsdl_url:
http://soa.gmcc.net:8011/esb/pub/plat/msg/proxy/MMSServer?wsdl

substr(
	t.wsdl_url,
	instr(t.wsdl_url,'/',-1,1)+1,
	instr(t.wsdl_url,'?',1,1)-instr(t.wsdl_url,'/',-1,1)-1
	)
	
//MMSServer

 

2、

SELECT SUBSTR(A.STATISTICS_DATE, 1, 8) AS RQ,
       SUM(A.FREQUENCY) AS ZH,
       A.SERVICE,
       SUM(DECODE(TRUNC(SUBSTR(A.STATISTICS_DATE, 9, 2) / 6),
                  0,
                  A.FREQUENCY,
                  0)) AS LC,
       SUM(DECODE(TRUNC(SUBSTR(A.STATISTICS_DATE, 9, 2) / 6),
                  1,
                  A.FREQUENCY,
                  0)) AS SW,
       SUM(DECODE(TRUNC(SUBSTR(A.STATISTICS_DATE, 9, 2) / 6),
                  2,
                  A.FREQUENCY,
                  3,
                  A.FREQUENCY,
                  0)) AS XW
  FROM EIP_ANALYSIS_FREQ A
 WHERE 1 = 1
 GROUP BY SUBSTR(A.STATISTICS_DATE, 1, 8), A.SERVICE

 

 

RQZHSERVICELCSWXW

120120925552720SB_HR_PAY_ImportPayPaymentApprovRejSrv0159160393560

220120925776440SB_FI_BMS_PageInquiryPrjInfoSrv0223720552720

320120925500080SB_HR_PAY_ImportAdjCostApprovRejSrv0144160355920

 

 

 0~5时    5~11     11~23

 凌晨LC   上午SW   下午XW

STATISTICS_DATE数据如下：2012092511  后两位是时间

 

SELECT TO_DATE(T.RQ, 'YYYY-MM-DD'),
       SUM(ZH),
       SUM(ZH) / COUNT(1),
       SUM(SW),
       SUM(SW) /
       DECODE(SUM(DECODE(SW, 0, 0, 1)), 0, 1, SUM(DECODE(SW, 0, 0, 1))),
       SUM(SW) / SUM(ZH),
       SUM(XW),
       SUM(XW) /
       DECODE(SUM(DECODE(XW, 0, 0, 1)), 0, 1, SUM(DECODE(XW, 0, 0, 1))),
       SUM(XW) / SUM(ZH),
       SUM(LC),
       SUM(LC) /
       DECODE(SUM(DECODE(LC, 0, 0, 1)), 0, 1, SUM(DECODE(LC, 0, 0, 1))),
       SUM(LC) / SUM(ZH)
  FROM (SELECT SUBSTR(A.STATISTICS_DATE, 1, 8) AS RQ,
               SUM(A.FREQUENCY) AS ZH,
               A.SERVICE,
               SUM(DECODE(TRUNC(SUBSTR(A.STATISTICS_DATE, 9, 2) / 6),
                          0,
                          A.FREQUENCY,
                          0)) AS LC,
               SUM(DECODE(TRUNC(SUBSTR(A.STATISTICS_DATE, 9, 2) / 6),
                          1,
                          A.FREQUENCY,
                          0)) AS SW,
               SUM(DECODE(TRUNC(SUBSTR(A.STATISTICS_DATE, 9, 2) / 6),
                          2,
                          A.FREQUENCY,
                          3,
                          A.FREQUENCY,
                          0)) AS XW
          FROM EIP_ANALYSIS_FREQ A
         WHERE 1 = 1
         GROUP BY SUBSTR(A.STATISTICS_DATE, 1, 8), A.SERVICE) T
 GROUP BY T.RQ
 ORDER BY T.RQ

 

 

 日期 总体调用次数 总体平均调用次数上午(5时-11时)下午(12时-23时)凌晨(0时-5时) 

上午调用次数 上午平均调用次数 上午百分比 下午调用次数 下午平均调用次数 下午百分比 凌晨调用次数 凌晨平均调用次数 凌晨百分比 

1 2012-11-01 22550524638523628.311614543671.602010.00 

2 2012-11-02 23110471663424528.711631445370.5916281.00 

3 2012-11-03 21849840625741728.641533363870.18259141.00 

 

 

SELECT TO_DATE(SUBSTR(A.STATISTICS_DATE, 1, 8), 'YYYY-MM-DD'),       
       SUM(DECODE(SUBSTR(A.STATISTICS_DATE, 9, 2), 00, A.FREQUENCY, 0)) AS A00,       
       SUM(DECODE(SUBSTR(A.STATISTICS_DATE, 9, 2), 01, A.FREQUENCY, 0)) AS A01,       
       SUM(DECODE(SUBSTR(A.STATISTICS_DATE, 9, 2), 02, A.FREQUENCY, 0)) AS A02,       
       SUM(DECODE(SUBSTR(A.STATISTICS_DATE, 9, 2), 03, A.FREQUENCY, 0)) AS A03,       
       SUM(DECODE(SUBSTR(A.STATISTICS_DATE, 9, 2), 04, A.FREQUENCY, 0)) AS A04,       
       SUM(DECODE(SUBSTR(A.STATISTICS_DATE, 9, 2), 05, A.FREQUENCY, 0)) AS A05,       
       SUM(DECODE(SUBSTR(A.STATISTICS_DATE, 9, 2), 06, A.FREQUENCY, 0)) AS A06,       
       SUM(DECODE(SUBSTR(A.STATISTICS_DATE, 9, 2), 07, A.FREQUENCY, 0)) AS A07,       
       SUM(DECODE(SUBSTR(A.STATISTICS_DATE, 9, 2), 08, A.FREQUENCY, 0)) AS A08,       
       SUM(DECODE(SUBSTR(A.STATISTICS_DATE, 9, 2), 09, A.FREQUENCY, 0)) AS A09,       
       SUM(DECODE(SUBSTR(A.STATISTICS_DATE, 9, 2), 10, A.FREQUENCY, 0)) AS A10,       
       SUM(DECODE(SUBSTR(A.STATISTICS_DATE, 9, 2), 11, A.FREQUENCY, 0)) AS A11,       
       SUM(DECODE(SUBSTR(A.STATISTICS_DATE, 9, 2), 12, A.FREQUENCY, 0)) AS A12,       
       SUM(DECODE(SUBSTR(A.STATISTICS_DATE, 9, 2), 13, A.FREQUENCY, 0)) AS A13,       
       SUM(DECODE(SUBSTR(A.STATISTICS_DATE, 9, 2), 14, A.FREQUENCY, 0)) AS A14,       
       SUM(DECODE(SUBSTR(A.STATISTICS_DATE, 9, 2), 15, A.FREQUENCY, 0)) AS A15,       
       SUM(DECODE(SUBSTR(A.STATISTICS_DATE, 9, 2), 16, A.FREQUENCY, 0)) AS A16,       
       SUM(DECODE(SUBSTR(A.STATISTICS_DATE, 9, 2), 17, A.FREQUENCY, 0)) AS A17,       
       SUM(DECODE(SUBSTR(A.STATISTICS_DATE, 9, 2), 18, A.FREQUENCY, 0)) AS A18,       
       SUM(DECODE(SUBSTR(A.STATISTICS_DATE, 9, 2), 19, A.FREQUENCY, 0)) AS A19,       
       SUM(DECODE(SUBSTR(A.STATISTICS_DATE, 9, 2), 20, A.FREQUENCY, 0)) AS A20,       
       SUM(DECODE(SUBSTR(A.STATISTICS_DATE, 9, 2), 21, A.FREQUENCY, 0)) AS A21,       
       SUM(DECODE(SUBSTR(A.STATISTICS_DATE, 9, 2), 22, A.FREQUENCY, 0)) AS A22,       
       SUM(DECODE(SUBSTR(A.STATISTICS_DATE, 9, 2), 23, A.FREQUENCY, 0)) AS A23
  FROM EIP_ANALYSIS_FREQ A
 WHERE 1 = 1
 GROUP BY SUBSTR(A.STATISTICS_DATE, 1, 8)
 ORDER BY SUBSTR(A.STATISTICS_DATE, 1, 8)

 

序号 日期 0点 1点 2点 3点 4点 5点 6点 7点 8点 9点 10点 11点 12点 13点 14点 15点 16点 17点 18点 19点 20点 21点 22点 23点 

1 2012-11-01 6  0  12  2  0  0 54 1417 1181 1220 1306 1207 1393 2517 1533 1195 1224 1198 1198 2462 1114 1120 1110  81 

2 2012-11-02 49 20 31 22 20 20 74 1431 1212 1465 1241 1211 1204 2581 1673 1246 1229 1220 1205 2490 1131 1145 1132  58 

3 2012-11-03 45 40 52 42 40 40 40 1397 1214 1203 1202 1201 1203 2556 1206 1195 1163 1160 1162 2388 1088 1104 1091  17