想要得到如下结果：
统计原则：
根据 CITY,SDATE,S_CELL_ID,GRIDX,GRIDY,SCELL_EARFCN 分组，
按 每个 N1_CELL_ID 的平均 N1_RSRP 排序，取平均 N1_RSRP 最大值 对应的 N1_CELL_ID, 平均 N1_RSRP, N1_PCI, N1_CELL_ID出现的次数
按 每个 N2_CELL_ID 的平均 N2_RSRP 排序，取平均 N2_RSRP 最大值 对应的 N2_CELL_ID, 平均 N2_RSRP, N2_PCI, N2_CELL_ID出现的次数
表结构：
-- Create table
create table HJ_TEST
(
city VARCHAR2(64),
sdate DATE,
s_cell_id NUMBER,
gridx NUMBER,
gridy NUMBER,
scell_earfcn NUMBER,
n1_cell_id NUMBER,
n1_rsrp NUMBER,
n1_pci NUMBER,
n2_cell_id NUMBER,
n2_rsrp NUMBER,
n2_pci NUMBER
)
数据：
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278274, 1825, 180370224, -106, 504, 180754482, -107, 238);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278274, 1825, 180370224, -103, 504, 180754482, -107, 238);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278274, 1825, 180370224, -95, 504, 180754485, -103, 235);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278274, 1825, 180370225, -103, 505, 180754485, -105, 235);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278274, 1825, 180370225, -103, 505, 180754485, -105, 235);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278274, 1825, 180370225, -95, 505, 180754482, -108, 238);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278276, 1825, 180370226, -95, 506, 180754482, -103, 238);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278276, 1825, 180370226, -104, 506, 180754482, -105, 238);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278276, 1825, 180370226, -106, 506, 180754482, -107, 238);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278276, 1825, 180370226, -103, 506, 180754482, -104, 238);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278276, 1825, 180370227, -103, 507, 180754482, -105, 238);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278276, 1825, 180370227, -103, 507, 180754482, -105, 238);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278276, 1825, 180370227, -100, 507, 180754482, -114, 238);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278275, 1825, 180370225, -100, 502, 180754482, -114, 238);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278275, 1825, 180370225, -98, 502, 180754482, -115, 238);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278275, 1825, 180370225, -102, 502, 180754482, -104, 238);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278275, 1825, 180370225, -95, 502, 180754482, -103, 238);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278275, 1825, 180370225, -103, 502, 180754482, -104, 238);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278275, 1825, 180370225, -95, 502, 180754482, -104, 238);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278275, 1825, 180370225, -104, 502, 180754482, -105, 238);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278275, 1825, 180370225, -95, 502, 180754482, -103, 238);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278275, 1825, 180370225, -95, 502, 180754482, -103, 238);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278275, 1825, 180370225, -104, 502, 180754482, -105, 238);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278275, 1825, 180370225, -103, 502, 180754482, -104, 238);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278275, 1825, 180370225, -82, 502, 180754482, -108, 238);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278275, 1825, 180370225, -103, 502, 180754482, -104, 238);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278275, 1825, 180370225, -98, 502, 180754482, -114, 238);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278275, 1825, 180370225, -103, 502, 180754482, -105, 238);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278275, 1825, 180370225, -95, 502, 180754482, -103, 238);
insert into hj_test (CITY, SDATE, S_CELL_ID, GRIDX, GRIDY, SCELL_EARFCN, N1_CELL_ID, N1_RSRP, N1_PCI, N2_CELL_ID, N2_RSRP, N2_PCI)
values ('BEIJING', to_date('22-05-2018 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 180370225, 1140875, 278275, 1825, 180370225, -103, 502, 180754482, -104, 238);
2 个解决方案
#1
你描述的原则是不是有问题，没搞懂呢，而且你查询到的三条数据，是不是有问题？
#2
结果集三条记录是没问题的，但是平均值RSRP我是随便写的，没有真实计算
#1
你描述的原则是不是有问题，没搞懂呢，而且你查询到的三条数据，是不是有问题？
#2
结果集三条记录是没问题的，但是平均值RSRP我是随便写的，没有真实计算