tommyduan_fingerlib 指纹库 栅格小区级别数据

tommyduan_mr_grid_cell_result_all 统计 栅格小区级别数据

业务：

以tommyduan_mr_grid_cell_result_all为主，如果某个栅格（gridid,buildingid,floor）没有小区的话，使用用指纹库的栅格（gridid,buildingid,floor）下的小区;

否则，使用tommyduan_mr_grid_cell_result_all的栅格（gridid,buildingid,floor）下的小区填充。

数据示例：

--指纹库--gridid1，buildlingid1,floor1,cell1--gridid1，buildlingid1,floor1,cell2--gridid1，buildlingid1,floor1,cell3--gridid2，buildlingid1,floor1,cell31--gridid2，buildlingid1,floor1,cell298--统计结果--gridid1，buildlingid1,floor1,cell2222--gridid1，buildlingid1,floor1,cell3333--合并后的结果:--gridid1，buildlingid1,floor1,cell2222--gridid1，buildlingid1,floor1,cell3333--gridid2，buildlingid1,floor1,cell31--gridid2，buildlingid1,floor1,cell298

实现思路：

首先，统计出每个分组属于那个表。

create table tommyduan_gridcell_group asselect t10.gridid,t10.buildingid,t10.floor,(case when isnull(t11.buildingid) then 'fingerlib' else 'mr_grid_cell' end) as datafromfrom (select gridid,buildingid,floor from tommyduan_fingerlib group by gridid,buildingid,floor) t10left outer join(select gridid,buildingid,floor    from tommyduan_mr_grid_cell_result_all group by gridid,buildingid,floor) t11on t10.gridid=t11.gridid and t10.buildingid=t11.buildingid and t10.floor=t11.floor;

其次，根据数据分组所在的表去那个表关联出数据。

select t10.gridid,t10.objectid,t10.longitude,t10.latitude,t10.gridx,t10.gridy,    t10.floor,t10.avgrsrp,t10.total_num,t10.mr_weak_num,    t10.avgrsrq,t10.avgsinrul,    t10.sinrul_total_num,t10.sinrul_low_num,t10.buildingidfrom tommyduan_fingerlib t10 inner join (select * from tommyduan_gridcell_group where datafrom='fingerlib') t11 on t10.gridid=t11.gridid and t10.buildingid=t11.buildingid and t10.floor=t11.floorunion allselect t10.gridid,t10.objectid,t10.longitude,t10.latitude,t10.gridx,t10.gridy,    t10.floor,t10.avgrsrp,t10.total_num,t10.mr_weak_num,    t10.avgrsrq,t10.avgsinrul,    t10.sinrul_total_num,t10.sinrul_low_num,t10.buildingidfrom tommyduan_mr_grid_cell_result_all t10 inner join (select * from tommyduan_gridcell_group where datafrom='mr_grid_cell') t11 on t10.gridid=t11.gridid and t10.buildingid=t11.buildingid and t10.floor=t11.floor

 需要注意事项：

1）如果inner join 关联条件中包含了buildingid或者gridid或者floor有null的数据，虽然两边都是null的条件下，也是无法关联出来的；

2）针对buildingid如果两边都是null，关联时依然需要关联出来的解决方案请参考：《》