请教一个问题:想写个存储过程统计表highrssi_cell中连续7天字段RSSI_avg_m超过-95的同一个小区,该怎么写啊
表结构如下:
create table highrssi_cell(
INT_ID integer,
scan_start_time date,
CITY varchar2(200),
BssID integer,
BtsID integer,
CellID integer,
CarrierId integer,
CI integer,
CellName varchar2(200),
bts_type varchar2(200),
traffic float,
cell_pwr float,
avg_ForwardPower float,
RSSI_max_m float,
RSSI_min_m float,
RSSI_avg_m float,
RSSI_max_d float,
RSSI_min_d float,
RSSI_avg_d float,
day_30 integer,
day integer,
day_7 integer);
表中存储的是每天超过门限的小区列表!
表结构如下:
create table highrssi_cell(
INT_ID integer,
scan_start_time date,
CITY varchar2(200),
BssID integer,
BtsID integer,
CellID integer,
CarrierId integer,
CI integer,
CellName varchar2(200),
bts_type varchar2(200),
traffic float,
cell_pwr float,
avg_ForwardPower float,
RSSI_max_m float,
RSSI_min_m float,
RSSI_avg_m float,
RSSI_max_d float,
RSSI_min_d float,
RSSI_avg_d float,
day_30 integer,
day integer,
day_7 integer);
表中存储的是每天超过门限的小区列表!
先对你表进行排序按照时间排序
(SELECT a.*,ROWNUM rn FROM highrssi_cell ORDER BY scan_start_time)
然后再直接分组 scan_start_time-rn进行分组,统计条数count(*)>=7
条件 WHERE RSSI_avg_m > -95--伪代码:
SELECT CellID FROM (
SELECT a.*,ROWNUM rn FROM highrssi_cell ORDER BY scan_start_time
)
WHERE RSSI_avg_m > -95
GROUP BY CellID,scan_start_time-rn
HAVING Count(*)>=7
------测试只查询大于3条的,7条太多了。。呵呵。。
SQL> select i.int_id,i.scan_start_time,i.rssi_avg_m from highrssi_cell i; INT_ID SCAN_START_TIME RSSI_AVG_M
--------------------------------------- --------------- --------------------------------------------------------------------------------
1 2010-11-24 下午 1
1 2010-11-25 下午 1
1 2010-11-26 下午 1
1 2010-11-27 下午 1
1 2010-11-28 下午 1
2 2010-11-23 下午 1
2 2010-11-28 下午 1
2 2010-11-24 下午 1
2 2010-11-20 下午 19 rows selectedSQL>
SQL> select INT_ID
2 from (select t.*,
3 trunc(t.scan_start_time) + 1 - rank()over(partition by INT_ID order by t.scan_start_time) cnt
4 from (select * from highrssi_cell where RSSI_avg_m > -95) t)
5 group by INT_ID,cnt
6 having count(*) > 3
7 ; INT_ID
---------------------------------------
1SQL>
------整理了下
SQL> col int_id format a10;
SQL> col rssi_avg_m format a10;
SQL> col scan_start_time format a50;
SQL> select i.int_id,i.scan_start_time,i.rssi_avg_m from highrssi_cell i; INT_ID SCAN_START_TIME RSSI_AVG_M
---------- -------------------------------------------------- ----------
1 2010-11-24 下午 03:16:17 1
1 2010-11-25 下午 03:16:17 1
1 2010-11-26 下午 03:16:17 1
1 2010-11-27 下午 03:16:17 1
1 2010-11-28 下午 03:16:17 1
2 2010-11-23 下午 03:16:17 1
2 2010-11-28 下午 03:16:18 1
2 2010-11-24 下午 03:16:18 1
2 2010-11-20 下午 03:16:18 19 rows selectedSQL>
SQL> select INT_ID
2 from (select t.*,
3 trunc(t.scan_start_time) + 1 - rank()over(partition by INT_ID order by t.scan_start_time) cnt
4 from (select * from highrssi_cell where RSSI_avg_m > -95) t)
5 group by INT_ID,cnt
6 having count(*) > 3
7 ; INT_ID
----------
1SQL>
scan_start_time-rn进行分组 就是连续的才分组!
恩 我试了的 用上面的SQL才查出一个CELLID,但是我大概看了下 结果不对 !
另:那张表中存储的已经是超过门限值的小区了 我现在只是想知道连续超过门限值的小区是那些,比如:我想看下2010-11-23号那天的超过门限的小区在包括当天的时间在内连续7天都超过门限的是哪些小区?
1.你肯定得先按照小区的分组不。
2.另:那张表中存储的已经是超过门限值的小区了 我现在只是想知道连续超过门限值的小区是那些,比如:我想看下2010-11-23号那天的超过门限的小区在包括当天的时间在内连续7天都超过门限的是哪些小区?
你把你的cellid查询出来,再查看下cellid时间里有没有2010-11-23不就行了吗?
24 -----1 --------> 24-1=23
25 -----2 --------> 25-2=23
26 -----3 --------> 26-3=23
27 -----4
28 -----5
最后可以看出日期减去rank得到的数是相同的,按照那个数字分组,得出大于7的。。
如果不连续的话,就得到不了这样的记录。
from tb
where RSSI_avg_m>-95
group by 小区编码,highrssi_cell-rownum
having count(*)>=7
select scan_start_time-rownum flag,小区编码
from highrssi_cell
where RSSI_avg_m>-95
group by 小区编码,scan_start_time-rownum
having count(*)>=7
--or
select 小区编码
from (select scan_start_time,小区编码,
row_number() over(partition by 小区编码 order by scan_start_time) flag
from highrssi_cell where RSSI_avg_m>-95)
group by 小区编码,scan_start_time-flag
having count(*)>=7