数据库“行专列”操作---使用row_number()over(partition by 分组字段 [order by 排序字段])

测试样例：

create table test(rsrp string,rsrq string,tkey string,distan string);

insert into test values('-90.28','-37','tkey1','10');
insert into test values('-92.35','-40','tkey1','30');
insert into test values('-94.36','-34','tkey2','5');
insert into test values('-93.88','-38','tkey2','19');

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