测试样例：
create table test(rsrp string,rsrq string,tkey string,distan string);
insert into test values('-90.28','-37','tkey1','');
insert into test values('-92.35','-40','tkey1','');
insert into test values('-94.36','-34','tkey2','');
insert into test values('-93.88','-38','tkey2','');
select * from test;
+------------+------------+------------+--------------+--+
| test.rsrp | test.rsrq | test.tkey | test.distan |
+------------+------------+------------+--------------+--+
| -90.28 | -37 | tkey1 | 10 |
| -92.35 | -40 | tkey1 | 30 |
| -94.36 | -34 | tkey2 | 5 |
| -93.88 | -38 | tkey2 | 19 |
+------------+------------+------------+--------------+--+
select rsrp,rsrq,tkey,distan,row_number()over(partition by tkey order by distan asc) rn
from test
group by rsrp,rsrq,tkey,distan;
+---------+-------+--------+---------+-----+--+
| rsrp | rsrq | tkey | distan | rn |
+---------+-------+--------+---------+-----+--+
| -90.28 | -37 | tkey1 | 10 | 1 |
| -92.35 | -40 | tkey1 | 30 | 2 |
| -93.88 | -38 | tkey2 | 19 | 1 |
| -94.36 | -34 | tkey2 | 5 | 2 |
+---------+-------+--------+---------+-----+--+
新建测试表：
create table grid_test(
buildingid nvarchar(32),
gridid nvarchar(32),
height int,
gridx nvarchar(32),
gridy nvarchar(32),
eci nvarchar(32),
total_num int
)
测试分组
insert into grid_test values('building1','gridid1',1,'gridid1 x','gridid1 y','eci1',123)
insert into grid_test values('building1','gridid1',1,'gridid1 x','gridid1 y','eci1',2)
insert into grid_test values('building1','gridid1',1,'gridid1 x','gridid1 y','eci1',19)
insert into grid_test values('building1','gridid1',1,'gridid1 x','gridid1 y','eci1',50)
insert into grid_test values('building1','gridid1',1,'gridid1 x','gridid1 y','eci1',78)
insert into grid_test values('building1','gridid1',1,'gridid1 x','gridid1 y','eci2',98)
insert into grid_test values('building1','gridid1',1,'gridid1 x','gridid1 y','eci2',22)
insert into grid_test values('building1','gridid1',1,'gridid1 x','gridid1 y','eci2',19)
insert into grid_test values('building1','gridid1',1,'gridid1 x','gridid1 y','eci2',87)
insert into grid_test values('building1','gridid1',1,'gridid1 x','gridid1 y','eci2',78)
--栅格小区分组排序
select buildingid,gridid,height,gridx,gridy,eci,total_num,row_number()over(partition by buildingid,gridid,height,gridx,gridy,eci order by total_num desc) rn
from grid_test
group by buildingid,gridid,height,gridx,gridy,eci,total_num;
buildingid gridid height gridx gridy eci total_num rn
building1 gridid1 1 gridid1 x gridid1 y eci1 123 1
building1 gridid1 1 gridid1 x gridid1 y eci1 78 2
building1 gridid1 1 gridid1 x gridid1 y eci1 50 3
building1 gridid1 1 gridid1 x gridid1 y eci1 19 4
building1 gridid1 1 gridid1 x gridid1 y eci1 2 5
building1 gridid1 1 gridid1 x gridid1 y eci2 98 1
building1 gridid1 1 gridid1 x gridid1 y eci2 87 2
building1 gridid1 1 gridid1 x gridid1 y eci2 78 3
building1 gridid1 1 gridid1 x gridid1 y eci2 22 4
building1 gridid1 1 gridid1 x gridid1 y eci2 19 5
--栅格分组排序
select buildingid,gridid,height,gridx,gridy,eci,total_num,row_number()over(partition by buildingid,gridid,height,gridx,gridy order by total_num desc) rn
from grid_test
group by buildingid,gridid,height,gridx,gridy,eci,total_num
buildingid gridid height gridx gridy eci total_num rn
building1 gridid1 1 gridid1 x gridid1 y eci1 123 1
building1 gridid1 1 gridid1 x gridid1 y eci2 98 2
building1 gridid1 1 gridid1 x gridid1 y eci2 87 3
building1 gridid1 1 gridid1 x gridid1 y eci1 78 4
building1 gridid1 1 gridid1 x gridid1 y eci2 78 5
building1 gridid1 1 gridid1 x gridid1 y eci1 50 6
building1 gridid1 1 gridid1 x gridid1 y eci2 22 7
building1 gridid1 1 gridid1 x gridid1 y eci1 19 8
building1 gridid1 1 gridid1 x gridid1 y eci2 19 9
building1 gridid1 1 gridid1 x gridid1 y eci1 2 10
行专列：
truncate table grid_test;
insert into grid_test values('building1','gridid1',1,'gridid1 x','gridid1 y','eci10',123);
insert into grid_test values('building1','gridid1',1,'gridid1 x','gridid1 y','eci11',2);
insert into grid_test values('building1','gridid1',1,'gridid1 x','gridid1 y','eci12',19);
insert into grid_test values('building1','gridid1',1,'gridid1 x','gridid1 y','eci13',50);
insert into grid_test values('building1','gridid1',1,'gridid1 x','gridid1 y','eci14',78);
insert into grid_test values('building1','gridid2',1,'gridid2 x','gridid2 y','eci21',98);
insert into grid_test values('building1','gridid2',1,'gridid2 x','gridid2 y','eci22',22);
insert into grid_test values('building1','gridid2',1,'gridid2 x','gridid2 y','eci23',19);
insert into grid_test values('building1','gridid2',1,'gridid2 x','gridid2 y','eci24',87);
insert into grid_test values('building1','gridid2',1,'gridid2 x','gridid2 y','eci25',78); select buildingid,gridid,height,gridx,gridy,eci,total_num,row_number()over(partition by buildingid,gridid,height,gridx,gridy order by total_num desc) rn
from grid_test
group by buildingid,gridid,height,gridx,gridy,eci,total_num buildingid gridid height gridx gridy eci total_num rn
building1 gridid1 1 gridid1 x gridid1 y eci10 123 1
building1 gridid1 1 gridid1 x gridid1 y eci14 78 2
building1 gridid1 1 gridid1 x gridid1 y eci13 50 3
building1 gridid1 1 gridid1 x gridid1 y eci12 19 4
building1 gridid1 1 gridid1 x gridid1 y eci11 2 5
building1 gridid2 1 gridid2 x gridid2 y eci21 98 1
building1 gridid2 1 gridid2 x gridid2 y eci24 87 2
building1 gridid2 1 gridid2 x gridid2 y eci25 78 3
building1 gridid2 1 gridid2 x gridid2 y eci22 22 4
building1 gridid2 1 gridid2 x gridid2 y eci23 19 5 select * from (
select buildingid,gridid,height,gridx,gridy,eci,total_num,row_number()over(partition by buildingid,gridid,height,gridx,gridy order by total_num desc) rn
from grid_test
group by buildingid,gridid,height,gridx,gridy,eci,total_num
) t10
where rn<=3
buildingid gridid height gridx gridy eci total_num rn
building1 gridid1 1 gridid1 x gridid1 y eci10 123 1
building1 gridid1 1 gridid1 x gridid1 y eci14 78 2
building1 gridid1 1 gridid1 x gridid1 y eci13 50 3
building1 gridid2 1 gridid2 x gridid2 y eci21 98 1
building1 gridid2 1 gridid2 x gridid2 y eci24 87 2
building1 gridid2 1 gridid2 x gridid2 y eci25 78 3 truncate table grid_test;
insert into grid_test values('building1','gridid1',1,'gridid1 x','gridid1 y','eci10',123);
insert into grid_test values('building1','gridid1',1,'gridid1 x','gridid1 y','eci11',2);
insert into grid_test values('building1','gridid1',1,'gridid1 x','gridid1 y','eci12',19);
insert into grid_test values('building1','gridid1',1,'gridid1 x','gridid1 y','eci12',19);
insert into grid_test values('building1','gridid1',1,'gridid1 x','gridid1 y','eci13',50);
insert into grid_test values('building1','gridid1',1,'gridid1 x','gridid1 y','eci14',78);
insert into grid_test values('building1','gridid1',1,'gridid1 x','gridid1 y','eci14',78);
insert into grid_test values('building1','gridid2',1,'gridid2 x','gridid2 y','eci21',98);
insert into grid_test values('building1','gridid2',1,'gridid2 x','gridid2 y','eci22',22);
insert into grid_test values('building1','gridid2',1,'gridid2 x','gridid2 y','eci23',19);
insert into grid_test values('building1','gridid2',1,'gridid2 x','gridid2 y','eci24',87);
insert into grid_test values('building1','gridid2',1,'gridid2 x','gridid2 y','eci25',78); select t10.buildingid,t10.gridid,t10.height,t10.gridx,t10.gridy,
t10.cell1,t10.cell1_mrcount,
t11.cell2,t11.cell2_mrcount,
t12.cell3,t12.cell3_mrcount
from
(
select buildingid,gridid,height,gridx,gridy,eci as cell1,total_num cell1_mrcount from
(
select * from
(
select buildingid,gridid,height,gridx,gridy,eci,total_num,row_number()over(partition by buildingid,gridid,height,gridx,gridy order by total_num desc) rn
from grid_test
group by buildingid,gridid,height,gridx,gridy,eci,total_num
) t10
where rn<=3
) t where rn=1
) t10
inner join
(
select buildingid,gridid,height,gridx,gridy,eci as cell2,total_num cell2_mrcount from (
select * from (
select buildingid,gridid,height,gridx,gridy,eci,total_num,row_number()over(partition by buildingid,gridid,height,gridx,gridy order by total_num desc) rn
from grid_test
group by buildingid,gridid,height,gridx,gridy,eci,total_num
) t10
where rn<=3
) t where rn=2
) t11 on t10.gridid=t11.gridid and t10.height=t11.height
inner join
(
select buildingid,gridid,height,gridx,gridy,eci as cell3,total_num cell3_mrcount from (
select * from (
select buildingid,gridid,height,gridx,gridy,eci,total_num,row_number()over(partition by buildingid,gridid,height,gridx,gridy order by total_num desc) rn
from grid_test
group by buildingid,gridid,height,gridx,gridy,eci,total_num
) t10
where rn<=3
) t where rn=3
) t12 on t10.gridid=t12.gridid and t10.height=t12.height buildingid gridid height gridx gridy cell1 cell1_mrcount cell2 cell2_mrcount cell3 cell3_mrcount
building1 gridid1 1 gridid1 x gridid1 y eci10 123 eci14 78 eci13 50
building1 gridid2 1 gridid2 x gridid2 y eci21 98 eci24 87 eci25 78