SQL语句功能研究

SQL语句功能研究

 

SQL语句的时间控制技术和应用

2012-07-10 13:02:16

 

1. 时间控制技术

为了更好的控制时段选择，我们对SQL语句的时段参数做一定的测试，测试结果如下：

对于Oracle数据库表中的时间参数，我们建立了相应的字段，是start_time。该参数是形如'yyyy-mm-dd hh24:mi:ss'的字符串，例如'2012-05-08 00:00:00'。Oracle对于该参数有灵活的变化，可以灵活使用。

例如：对时间参数加1，得到以下结果

select distinct(a.start_time),a.start_time+1     from wp_mscserver_net a

where start_time=to_date('2012-05-08 02:00:00','yyyy-mm-dd hh24:mi:ss')

此SQL语句得到”2012-5-8 2:00:00”和”2012-5-9 2:00:00”的结果，说明时间参数加上增量1，得到第二天(24小时后)的时间信息。如果将时间参数的增量修改为0.5，则得到"2012-5-8 2:00:00"和"2012-5-8 14:00:00"的结果，相差12小时，即得到12小时后的时间信息。

依此类推，将时间参数的增量变为1/24(即0.04167)，则得到"2012-5-8 2:00:00"和"2012-5-8 3:00:00"的结果，刚好相差1小时。

如果我们将时间增量设置为比1/24略小的数值(例如0.0416)，则得到"2012-5-8 2:00:00"和"2012-5-8 2:59:54"的结果，相差1小时以内。

如果将0.0416作为基准增量，则其倍数可以作为时段控制的良好方法。例如，如果将增量变为0.0416*5，则系统返回"2012-5-8 2:00:00"和"2012-5-8 6:59:31"的结果，相差5小时。

这说明，我们可以灵活运用时间参数的增量，来灵活的控制时段的间隔大小。

 

2.  时间增量的应用

在以下语句中，我们灵活运用时间增量，来得到特定的时段的数据库记录：

select a.start_time,b.china_name,b.vendor_name,sum(a.F0282)

from wp_mscserver_net a ,wcdma.ne_mscserver b

where start_time>=to_date('2012-05-08 00:00:00','yyyy-mm-dd hh24:mi:ss')

and start_time<=to_date('2012-05-08 00:00:00','yyyy-mm-dd hh24:mi:ss')+0.0416*7

and a.ne_id =b.ne_id  and b.active_status=1 and a.subcounter1<>'sum'

group by a.start_time ,b.china_name,b.vendor_name

order by a.start_time ,b.china_name

该语句得到如下结果，从结果可以得知，我们使用时间增量0.0416*7，就可以得到从00:00到06:00共7小时的数据库记录。同时，我们只要输入起始时间和时间增量这2个参数，就可以动态的查询到指定时段的数据。相对于用传统的方法，此方法更容易在SQL语句和程序之间传递时间参数。

 

 

 

======================== ===========================================

忙时业务量查询功能研究

一、忙时定义

      忙时是统计单元在1天24小时内，某个业务量的最大值对应的时段。本文研究的2个忙时分别是网元忙时和全省忙时，业务量是RNC的IuPS上下行流量。网元忙时是指1天内每个RNC所对应的IuPS上下行流量之和最大的那个小时，在1天中每个RNC的忙时不完全相同。全省忙时则是1天内全省所有RNC累加的IuPS上下行流量之和最大的那个小时，在1天中只有1个全省忙时。

二、网元忙时业务量查询

按照每个网元的实际忙时，查询各网元每天的忙时业务量。因为每个网元的忙时可能不一样，所以需要找出每个网元每天的忙时，再进行业务量查询。完整的SQL查询语句如下：

select a.start_time,b.territory_name,a.china_name,

round(K0035/1024,2) IuPS上行流量MB,

round(K0036/1024,2) IuPS下行流量MB

from  w_kpi_rnc a,wcdma.territory b, wcdma.ne_rnc c,

--------------------

(select china_name, to_char(start_time, 'YYYY-MM-DD') as day_time,

max(K0035+K0036) AA

from w_kpi_rnc

where

start_time>=to_date('2013-01-01 01:00:00','yyyy-mm-dd hh24:mi:ss') and

start_time<=to_date('2013-01-31 23:00:00','yyyy-mm-dd hh24:mi:ss')

group by china_name, to_char(start_time, 'YYYY-MM-DD') )  M

--------------------

where

start_time>=to_date('2013-01-01 01:00:00','yyyy-mm-dd hh24:mi:ss') and

start_time<=to_date('2013-01-31 23:00:00','yyyy-mm-dd hh24:mi:ss')

and to_char(a.start_time, 'YYYY-MM-DD')=M.day_time

and a.territory_id=b.territory_id

and a.ne_id=c.ne_id

and c.active_status=1

and a.K0035+K0036=M.AA

and M.AA>0

order by to_char(start_time, 'YYYY-MM-DD'),b.territory_name,a.china_name;

 

因为要按照网元的IuPS上下行流量之和的最大值来确定该网元的实际忙时，所以要先查出各个网元(RNC)的忙时。此语句中，我们构建了一张临时表M，表的字段包含网元名、日期和流量值，一共3个字段，语句如下：

(select china_name, to_char(start_time, 'YYYY-MM-DD') as day_time,

max(K0035+K0036) AA

from w_kpi_rnc

where

start_time>=to_date('2013-01-01 01:00:00','yyyy-mm-dd hh24:mi:ss') and

start_time<=to_date('2013-01-31 23:00:00','yyyy-mm-dd hh24:mi:ss')

group by china_name, to_char(start_time, 'YYYY-MM-DD') )  M

该语句的含义是，查询出各个RNC每天所对应的实际忙时，存入临时表M。需要注意的是，第二个字段day_time不能取小时，如果取小时，则流量无法分组聚合，就无法查询出来结果。这很重要！

加入day_time字段的另外一个重要原因，是此语句可以将很多天的忙时记录都一次性列出来，而不仅仅限于查询某天的忙时记录。如果不加入这个字段，该语句就只能查询1天的记录，如果想要查30天，则要执行30次语句才行，这无疑降低了工作效率。在加入了day_time字段后，该语句可以一次性查询无限多天的忙时记录数据，效率会提高很多。

整条SQL语句的查询条件中，a.territory_id=b.territory_id and a.ne_id=c.ne_id and c.active_status=1，起到筛选网元类型(排除非在网的网元)和关联网元编号的作用。

K0035+K0036=M.AA，这条语句很关键，它通过数据库中查询到的IuPS流量数据等于临时表M中的最大值，来确定表a(w_kpi_rnc)中的时段和流量记录数据。因为流量值一般都是小数点后带有很多位小数的浮点数，2个浮点数相等的概率实在是太低了(我们称之为浮点数据匹配原则)，所以，表M中的最大值记录，在表a中一般也唯一确定，因此表a中出现重复记录的可能性非常小，可以忽略。这是数值关联的一个技巧。

M.AA>0，是排除全0值记录。因为有可能存在某RNC全天的流量数据都为0值的情况(可能是数据没有汇总或者数据丢失)，此时每个小时都是最大值，每个小时都是忙时，这是不合理的，需要筛除。

至此，我们通过此条SQL语句，查询到了各网元的忙时业务量。查询结果如下(只摘录了1日到3日的部分RNC的数据)，从结果中来看，每天各网元的忙时不完全相同，有的在17点，有的在21点，网元的忙时分布也没有规律。

时段	城市	网元	IUPS上行流量	IUPS下行流量
2013-1-1 21:00:00	鄂州	EZRNC01	2824.87	18609.67
2013-1-1 19:00:00	黄冈	HGRNC3	122.4	1817.65
2013-1-1 21:00:00	黄冈	黄冈RNC12280	1554.21	10694.59
2013-1-1 22:00:00	黄冈	黄冈RNC42283	3511	29029.25
2013-1-1 23:00:00	黄石	HSRNC01	2434.33	20022.62
2013-1-1 21:00:00	江汉	JHRNC04	12520.85	56906.9
2013-1-1 18:00:00	江汉	JHRNC5	318.81	2749.58
2013-1-1 21:00:00	荆门	JMRNC1	4786.44	39240.13
2013-1-1 22:00:00	荆州	JZRNC1	5640.81	30219.99
2013-1-1 17:00:00	武汉	WHRNC03	4202.49	31987.58
2013-1-1 23:00:00	武汉	WHRNC15	3832.43	31671.32
2013-1-1 10:00:00	武汉	WHRNC17	254.39	1775.92
2013-1-1 20:00:00	襄樊	XFRNC3	2964.72	19833.25
2013-1-1 22:00:00	孝感	XGRNC01	2171.08	15147.61
2013-1-2 20:00:00	鄂州	EZRNC01	1841.93	16174.31
2013-1-2 22:00:00	恩施	ESRNC01	2544.58	21454.13
2013-1-2 14:00:00	黄冈	HGRNC3	103.33	1395.06
2013-1-2 16:00:00	江汉	JHRNC5	175.26	1687.7
2013-1-2 22:00:00	十堰	十堰RNC12272	2674.86	23593.47
2013-1-2 15:00:00	襄樊	XFRNC2	3432.04	31858.86
2013-1-2 19:00:00	襄樊	XFRNC3	2158.59	19090.48
2013-1-2 19:00:00	宜昌	YCRNC3	2967.03	24037.93
2013-1-3 21:00:00	鄂州	EZRNC01	1724.32	15107.01
2013-1-3 15:00:00	武汉	WHRNC25	5468.78	41322.18
2013-1-3 22:00:00	咸宁	XNRNC01	1929.06	17571.94
2013-1-3 21:00:00	襄樊	XFRNC3	1670.71	17538.83
2013-1-3 22:00:00	孝感	XGRNC04	2691.02	24450.71
2013-1-3 20:00:00	孝感	XGRNC3	285.12	2949.77
2013-1-3 21:00:00	宜昌	YCRNC3	3781.29	28494.24

 

三、全省忙时业务量查询

       全省忙时业务量查询和网元忙时业务量查询语句思想基本一致，只是在实施中会有一些变化。

       在按全省忙时查询各个地市的业务量的时候，我们不能采用网元忙时的语句了，因为网元忙时语句的基础数据都是以网元为最小单位，不用进行数据聚合，用该语句可以一次性查出所有数据。而全省忙时需要按照所有网元累加业务量来判断忙时(全省只有1个忙时)，需要用到聚合函数sum()，需要进行数据聚合，而max函数和sum函数是不能同时用在Oracle语句中的，所以要变通一下，重新构造SQL语句，以达到目的。

        对于需全省聚合的业务量数据，我们先构建一张临时表hebiao，存放全省每小时的业务量数据，该表包含2个字段：时段和业务量(在本例中是IuPS上行和下行流量之和)，业务量按照时段进行聚合，每个时段对应1个业务量数值。

然后，利用hebiao的时段字段，获得日期，根据日期，统计全省每天业务量的最大值，形成临时表M，该表包含2个字段：日期和忙时最大业务量值。

接着，我们对表hebiao和表M进行关联，关联字段有2组，1组是2张表的日期对等关联，1组是2张表的业务量对等关联。其中业务量对等关联仍然采用的是浮点数据匹配原则。然后，在查询结果中得到表hebiao的时段信息，这样，我们就得到了全省每天的忙时信息。

得到了忙时信息，接下来的语句就好写了，就是普通的业务量查询语句，只要按照城市汇聚一下数据和排序即可。

完整的SQL查询语句如下：

select a.start_time,b.territory_name,

round(sum(K0035)/1024,2) IuPS下行流量MB,

round(sum(K0036)/1024,2) IuPS下行流量MB

from  w_kpi_rnc a,wcdma.territory b, wcdma.ne_rnc c

where a.start_time in (

-----------------------

select hebiao.start_time from

(

select to_char(start_time, 'YYYY-MM-DD') as day_time,

max(hebiao.he) AA

from (

select start_time,

sum(K0035+K0036) he

from  w_kpi_rnc  where

start_time>=to_date('2013-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and

start_time<=to_date('2013-01-31 23:00:00','yyyy-mm-dd hh24:mi:ss')

group by start_time ) hebiao

group by to_char(start_time, 'YYYY-MM-DD')

) M,-------表M统计每个日期的最大业务量值；

(

select start_time,

sum(K0035+K0036) he

from  w_kpi_rnc  where

start_time>=to_date('2013-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and

start_time<=to_date('2013-01-31 23:00:00','yyyy-mm-dd hh24:mi:ss')

group by start_time

) hebiao------此表统计每小时的业务量

where

M.day_time=to_char(hebiao.start_time,'YYYY-MM-DD') and

M.AA=hebiao.he

)

-------------------

and a.territory_id=b.territory_id

and a.ne_id=c.ne_id

and c.active_status=1

group by a.start_time,b.territory_name

order by a.start_time,b.territory_name

 

        该语句得到的结果如下(只摘录不同忙时的3天的数据)，从中得知，全省每天只有1个忙时，忙时有可能不是同一个小时(本例中分别是19点、21点和22点)。每天忙时确定后，地市业务量也很快就可以查出来了。

时段	城市	IUPS下行流量	IUPS下行流量
2013-1-2 19:00:00	鄂州	1603.69	14453.39
2013-1-2 19:00:00	恩施	4476.54	34037.71
2013-1-2 19:00:00	黄冈	3424.09	30577.29
2013-1-2 19:00:00	黄石	2771.34	24210
2013-1-2 19:00:00	江汉	15795.68	87722.38
2013-1-2 19:00:00	荆门	6030.73	56167.12
2013-1-2 19:00:00	荆州	7596.64	54586.18
2013-1-2 19:00:00	十堰	3357.66	26656.57
2013-1-2 19:00:00	随州	1461.39	12022.68
2013-1-2 19:00:00	武汉
2013-1-2 19:00:00	咸宁	3157.05	20840.96
2013-1-2 19:00:00	襄樊	7608.81	57794.11
2013-1-2 19:00:00	孝感	6626.14	47747.08
2013-1-2 19:00:00	宜昌	8479.88	65070.46
2013-1-12 21:00:00	鄂州	2004.66	18008.71
2013-1-12 21:00:00	恩施	5316.57	39179.29
2013-1-12 21:00:00	黄冈	6126.84	44820.31
2013-1-12 21:00:00	黄石	4545.7	28883.28
2013-1-12 21:00:00	江汉
2013-1-12 21:00:00	荆门	6801.35	65901.72
2013-1-12 21:00:00	荆州	7135.23	54357.80
2013-1-12 21:00:00	十堰	5414.82	47011.21
2013-1-12 21:00:00	随州	2723.79	18178.34
2013-1-12 21:00:00	武汉
2013-1-12 21:00:00	咸宁	3867.65	26018.62
2013-1-12 21:00:00	襄樊
2013-1-12 21:00:00	孝感	6795.85	50883.11
2013-1-12 21:00:00	宜昌	10266.37	72078.92
2013-1-28 22:00:00	鄂州	2909.45	18267.07
2013-1-28 22:00:00	恩施	6944.17	54153.11
2013-1-28 22:00:00	黄冈	8649.12	59134.26
2013-1-28 22:00:00	黄石	3963.49	31764.55
2013-1-28 22:00:00	江汉	28598.67	155059.26
2013-1-28 22:00:00	荆门	6015.41	62991.06
2013-1-28 22:00:00	荆州	8282	57032
2013-1-28 22:00:00	十堰	5548.12	52233.41
2013-1-28 22:00:00	随州	2507.66	21298.19
2013-1-28 22:00:00	武汉	61118.43	475695.67
2013-1-28 22:00:00	咸宁	4917.67	37476.2
2013-1-28 22:00:00	襄樊	8164	71176.21
2013-1-28 22:00:00	孝感	9377.97	76688.36
2013-1-28 22:00:00	宜昌	10940.31	81156

 

四、小结

        在本文中，我们只使用了2条语句，就查出了多天的网元忙时业务量数据，和多天的全省忙时业务量数据，提高了数据查询效率。在SQL语句查询速度方面，多天网元忙时业务量查询速度很快，只用了1秒钟；对于多天的全省忙时业务量数据，语句查询速度稍慢，花了40秒钟。不过对于多天数据查询来说，40秒钟的查询速度还是可以接受的，而且这2条语句通用型很强，可以适用在大多数的业务量查询方面。