sql 纪录

路测数据[MR数据分析]

-- 点集表数据纪录数SELECT "count"(0) FROM plan_res_mr_data_20161213;-- 小区表数据SELECT "count"(0) FROM plan_res_lte_cell;-- 小区类型select DISTINCT cover_type from plan_res_lte_cell;-- 删除基础表数据-- DELETE from plan_res_mr_indoor_20161213;-- DELETE from plan_res_mr_outdoor_20161213;-- 查询基础数据表记录数select count(1) from plan_res_mr_indoor_20161213;select count(1) from plan_res_mr_outdoor_20161213;select * from plan_res_mr_indoor_20161213;select * from plan_res_mr_outdoor_20161213;-- 判断表名是否存在select count(*) from pg_class where relname = 'plan_res_mr_outdoor_20161213';-- 删除表DROP TABLE IF EXISTS "public"."plan_res_mr_outdoor_20161213";DROP TABLE IF EXISTS "public"."plan_res_mr_indoor_20161213";-- 第一步：创建两张表-- 创建outdoor类型表-- ------------------------------  Table structure for plan_res_mr_outdoor_20161213-- ----------------------------DROP TABLE IF EXISTS "public"."plan_res_mr_outdoor_20161213";CREATE TABLE "public"."plan_res_mr_outdoor_20161213" ("id" serial  NOT NULL,"cell_id" int4 NOT NULL,"data_id" int4 NOT NULL,"imsi" text COLLATE "default","cover_type" text COLLATE "default","about_eci" text COLLATE "default","start_eci" text COLLATE "default","end_eci" text COLLATE "default","time_bucket" text COLLATE "default","speed" float8,"start_lng" float8,"start_lat" float8,"end_lng" float8,"end_lat" float8,"journey" float8,"tim_difference" float8,"start_time" text COLLATE "default","end_time" text COLLATE "default")WITH (OIDS=FALSE);ALTER TABLE "public"."plan_res_mr_outdoor_20161213" OWNER TO "postgres";-- ------------------------------  Primary key structure for table plan_res_mr_outdoor_20161213-- ----------------------------ALTER TABLE "public"."plan_res_mr_outdoor_20161213" ADD PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE;-- 创建indoor类型表-- ------------------------------  Table structure for plan_res_mr_indoor_20161213-- ----------------------------DROP TABLE IF EXISTS "public"."plan_res_mr_indoor_20161213";CREATE TABLE "public"."plan_res_mr_indoor_20161213" ("id" serial NOT NULL,"cell_id" int4 NOT NULL,"data_id" int4 NOT NULL,"imsi" text COLLATE "default","cover_type" text COLLATE "default","eci" text COLLATE "default")WITH (OIDS=FALSE);ALTER TABLE "public"."plan_res_mr_indoor_20161213" OWNER TO "postgres";-- ------------------------------  Primary key structure for table plan_res_mr_indoor_20161213-- ----------------------------ALTER TABLE "public"."plan_res_mr_indoor_20161213" ADD PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE;-- 第二步  查询数据-- 1)小区表select cell.id cellId,cell.enbid_lcell_id eci,cell.cover_type coverTypefromplan_res_lte_cell cellwhere cell.cover_type='indoor';-- 2)数据表select datas.id data_id,datas.imsi imsi ,datas.start_time startTime,datas.lng lng,datas.lat lat,datas.eci ecifrom plan_res_mr_data_20161213 datas;-- 3)关联查询(indoor)select tb_data.dataId dataId,tb_cell.cellId cellId,tb_data.imsi imsi,tb_data.startTime startTime, tb_data.lng lng,tb_data.lat lat,tb_data.eci eci,tb_cell.coverType coverTypefrom (select datas.id dataId,datas.imsi imsi ,datas.start_time startTime,datas.lng lng,datas.lat lat,datas.eci ecifrom plan_res_mr_data_20161213 datas) tb_dataleft join (select cell.id cellId,cell.enbid_lcell_id eci,cell.cover_type coverTypefromplan_res_lte_cell cellwhere cell.cover_type='indoor') tb_cellon tb_data.eci=tb_cell.eciWHERE tb_cell.cellId is NOT NULLORDER BY tb_data.imsi ,tb_data.startTime;-- 3)关联查询(outdoor)select tb_data.dataId dataId,tb_cell.cellId cellId,tb_data.imsi imsi,tb_data.startTime startTime, tb_data.lng lng,tb_data.lat lat,tb_data.eci eci,tb_cell.coverType coverTypefrom (select datas.id dataId,datas.imsi imsi ,datas.start_time startTime,datas.lng lng,datas.lat lat,datas.eci ecifrom plan_res_mr_data_20161213 datas) tb_dataleft join (select cell.id cellId,cell.enbid_lcell_id eci,cell.cover_type coverTypefromplan_res_lte_cell cellwhere cell.cover_type='outdoor') tb_cellon tb_data.eci=tb_cell.eciWHERE tb_cell.cellId is NOT NULLORDER BY tb_data.imsi ,tb_data.startTime;-- 第三步： 批量插入基础数据-- 1)批量插入基础数据（outdoor）Insert into plan_res_mr_outdoor_20161213(cell_id,data_id,imsi,start_time,start_lng,start_lat,cover_type) select tb_cell.cellId cellId,tb_data.dataId dataId,tb_data.imsi imsi,tb_data.startTime startTime, tb_data.lng lng,tb_data.lat lat,tb_cell.coverType coverTypefrom (select datas.id dataId,datas.imsi imsi ,datas.start_time startTime,datas.lng lng,datas.lat lat,datas.eci ecifrom plan_res_mr_data_20161213 datas) tb_dataleft join (select cell.id cellId,cell.enbid_lcell_id eci,cell.cover_type coverTypefromplan_res_lte_cell cellwhere cell.cover_type='outdoor') tb_cellon tb_data.eci=tb_cell.eciWHERE tb_cell.cellId is NOT NULLORDER BY tb_data.imsi ,tb_data.startTime;-- 2)批量插入基础数据（indoor）Insert into plan_res_mr_indoor_20161213(cell_id,data_id,imsi,eci,cover_type) select tb_cell.cellId cellId,tb_data.dataId dataId,tb_data.imsi imsi,tb_data.eci eci,tb_cell.coverType coverTypefrom (select datas.id dataId,datas.imsi imsi ,datas.start_time startTime,datas.lng lng,datas.lat lat,datas.eci ecifrom plan_res_mr_data_20161213 datas) tb_dataleft join (select cell.id cellId,cell.enbid_lcell_id eci,cell.cover_type coverTypefromplan_res_lte_cell cellwhere cell.cover_type='indoor') tb_cellon tb_data.eci=tb_cell.eciWHERE tb_cell.cellId is NOT NULLORDER BY tb_data.imsi ,tb_data.startTime;--  查询处理的数据SELECToutdoor.id "id",outdoor.data_id "dataId",outdoor.cell_id "cellId",outdoor.imsi "imsi",outdoor.start_time "startTime",outdoor.start_lng "lng",outdoor.start_lat "lat",outdoor.start_eci "eci",outdoor.cover_type "coverType"FROMplan_res_mr_outdoor_20161213 outdoorORDER BYoutdoor.imsi,outdoor.start_timeLIMIT 10 OFFSET 11;SELECTcount(1)FROMplan_res_mr_outdoor_20161213 outdoor;-- 判断表是否存在SELECTsubstr(tablename,LENGTH (tablename) - 7,LENGTH (tablename))FROMpg_tables t1WHERE(t1.tablename LIKE CONCAT ('plan_res_mr_data_', '%')AND LENGTH (t1.tablename) - 8 = 17 );SELECTtablenameFROMpg_tables t1WHERE(t1.tablename LIKE CONCAT ('plan_res_mr_data_', '%')AND LENGTH (t1.tablename) - 7 = 18AND NOT EXISTS (SELECT*FROMpg_tables t2WHEREt2.tablename LIKE CONCAT ('plan_res_mr_outdoor_', '%')AND substr(t1.tablename,LENGTH (t1.tablename) - 7,LENGTH (t1.tablename)) = substr(t2.tablename,LENGTH (t2.tablename) - 7,LENGTH (t2.tablename))));-- 查询处理完的数据SELECToutdoor. ID "id",outdoor.imsi "imsi",outdoor.start_time "startTime",outdoor.end_time "end_time",outdoor.journey "journey",outdoor.time_difference "time_difference",outdoor.speed "speed"FROMplan_res_mr_outdoor_20161213 outdoorORDER BYoutdoor.imsi,outdoor.start_timeLIMIT 1000 OFFSET 19999