· 7 years ago · Feb 26, 2019, 11:02 AM
1
2-- доп параметры дома (в Ñамом доме колонки нет)
3DROP TABLE IF EXISTS tmp_house_params;
4CREATE TEMPORARY TABLE tmp_house_params AS
5SELECT gin.house_guid, MAX(val.value_integer) AS liv_numbr
6FROM hmsm.hm_house_general_infos gin
7JOIN hmsm.hm_house_general_info_params par ON par.house_general_info_guid = gin.guid AND par.actual AND last_version AND gin.status = 'APPROVED' AND gin.last_approved_version
8JOIN hmsm.hm_house_estate_objects obj ON obj.guid = par.house_estate_object_guid AND obj.status IN ('CREATED', 'MODIFIED') AND obj.param_code = '20140' -- 20140 КоличеÑтво проживающих select * from nsism.nsi_housing_fund_attr_description_forms where name like '%прожив%'
9JOIN hmsm.hm_house_estate_object_values val ON val.estate_object_guid = obj.guid AND val.system_date_from <= now() AND val.system_date_to >= now()
10GROUP BY gin.house_guid;
11ANALYZE tmp_house_params;
12
13-- доп параметры квартиры (в Ñамой квартире колонки нет)
14DROP TABLE IF EXISTS tmp_apartment_params ;
15CREATE TEMPORARY TABLE tmp_apartment_params AS
16SELECT par.apartment_guid, MAX(val.value_integer) AS liv_numbr
17FROM hmsm.hm_apartment_params par
18JOIN hmsm.hm_apartment_estate_objects obj ON par.apartment_estate_object_guid = obj.guid AND obj.status IN ('CREATED', 'MODIFIED') AND obj.param_code IN ( '20130', '20125') AND par.actual --20125 КоличеÑтво лиц, проживающих в квартире. 20130 КоличеÑтво граждан, проживающих в комнате в коммунальной квартире select * from nsism.nsi_housing_fund_attr_description_forms where name like '%прожив%'
19JOIN hmsm.hm_apartment_estate_object_values val ON val.estate_object_guid = obj.guid AND now() BETWEEN val.system_date_from AND val.system_date_to
20GROUP BY par.apartment_guid;
21CREATE INDEX idx_tmp_apartment_params ON tmp_apartment_params (apartment_guid);
22ANALYZE tmp_apartment_params;
23
24-- доп параметры комнаты (в Ñамой комнате колонка то же еÑÑ‚ÑŒ), UI вроде берет из Ñамой комнаты
25DROP TABLE IF EXISTS tmp_room_params;
26CREATE TEMPORARY TABLE tmp_room_params AS
27SELECT house_guid, apartment_guid, SUM(value_integer) AS liv_numbr
28FROM (
29SELECT r.house_guid, r.guid, r.apartment_guid, MAX(val.value_integer) AS value_integer
30FROM hmsm.hm_rooms r
31JOIN hmsm.hm_room_params par ON par.room_guid = r.guid AND r.actual AND r.status = 'APPROVED' AND r.confirmed_by_supplier
32JOIN hmsm.hm_room_estate_objects obj ON obj.guid = par.room_estate_object_guid AND obj.status IN ('CREATED', 'MODIFIED') AND par.actual
33JOIN hmsm.hm_room_estate_object_values val ON val.estate_object_guid = obj.guid AND val.system_date_from <= now() AND val.system_date_to >= now()
34GROUP BY r.house_guid, r.guid, r.apartment_guid )t
35GROUP BY house_guid, apartment_guid;
36ANALYZE tmp_room_params;
37
38-- комнаты (в доп параметрах комнаты то же еÑÑ‚ÑŒ параметр), UI вроде берет из Ñамой комнаты
39DROP TABLE IF EXISTS tmp_rooms;
40CREATE TEMPORARY TABLE tmp_rooms AS
41SELECT r.house_guid, r.apartment_guid, SUM(r.number_of_living::integer) AS liv_numbr
42FROM hmsm.hm_rooms r
43LEFT JOIN hmsm.hm_apartments ap ON ap.guid = r.apartment_guid AND ap.actual AND ap.status = 'APPROVED' AND ap.confirmed_by_supplier --комната может быть без квартиры, например Ñразу в жилом доме
44WHERE r.actual AND r.status = 'APPROVED' AND r.confirmed_by_supplier
45AND regexp_replace(r.number_of_living, '[^0-9]', '99999', 'g')::integer < 1000 --колво прож текÑтовое, поÑтому фильтруем нечиÑловые Ð·Ð½Ð°Ñ‡ÐµÐ½Ð¸Ñ Ð·Ð°Ð¼ÐµÐ½ÑÑ Ð¸Ñ… на 99999
46GROUP BY r.house_guid, r.apartment_guid;
47ANALYZE tmp_rooms;
48
49
50
51-- ЛС
52--колво проживающих из ЛС: по каждой комнате или квартире взÑÑ‚ поÑледний по дате Ð¸Ð·Ð¼ÐµÐ½ÐµÐ½Ð¸Ñ Ð›Ð¡. Комнаты Ñуммированы по квартире
53DROP TABLE IF EXISTS tmp_living_acc;
54CREATE TEMPORARY TABLE tmp_living_acc AS
55SELECT
56 house_guid,
57 apartment_guid,
58 MAX(living_cnt_hd_acc) AS living_cnt_hd_acc, --прож в ЖД
59 MAX(living_cnt_ap_acc) AS living_cnt_ap_acc, --прож в квартире
60 SUM(living_cnt_r_acc) AS living_cnt_r_acc --прож в комнате
61FROM
62 (
63 SELECT DISTINCT
64 acc.house_guid, COALESCE(ap.guid, ap_r.guid) AS apartment_guid, r.guid,
65 -- ЛС ЖД
66 CASE WHEN ap.guid IS NULL AND r.guid IS NULL THEN FIRST_VALUE(acc.living_count) OVER(PARTITION BY ap_r.guid ORDER BY acc.last_editing_date DESC, acc.living_count DESC) ELSE 0 END AS living_cnt_hd_acc,
67 -- ЛС квартиры
68 CASE WHEN ap.guid IS NOT NULL THEN FIRST_VALUE(acc.living_count) OVER(PARTITION BY ap.guid ORDER BY acc.last_editing_date DESC, acc.living_count DESC) ELSE 0 END AS living_cnt_ap_acc,
69 --ЛС комнаты
70 CASE WHEN r.guid IS NOT NULL THEN FIRST_VALUE(acc.living_count) OVER(PARTITION BY r.guid ORDER BY acc.last_editing_date DESC, acc.living_count DESC) ELSE 0 END AS living_cnt_r_acc
71 FROM
72 hmsm.hm_calc_shares sh
73 JOIN hmsm.hm_accounts acc ON acc.guid = sh.account_guid AND acc.account_status = 'ACTUAL' AND acc.is_actual AND sh.annulled = false
74 -- квартиры без комнат
75 LEFT JOIN hmsm.hm_apartments ap ON ap.guid = sh.apartments_guid AND ap.actual AND ap.status = 'APPROVED' AND ap.confirmed_by_supplier
76 -- комнаты (и квартиры - еÑли еÑÑ‚ÑŒ)
77 LEFT JOIN hmsm.hm_rooms r ON r.guid = sh.room_guid AND r.actual AND r.status = 'APPROVED' AND r.confirmed_by_supplier
78 LEFT JOIN hmsm.hm_apartments ap_r ON r.apartment_guid = ap_r.guid AND ap_r.actual AND ap_r.status = 'APPROVED' AND ap_r.confirmed_by_supplier
79 ) t
80GROUP BY house_guid, apartment_guid;
81CREATE INDEX idx_tmp_living_acc ON tmp_living_acc (house_guid) WHERE apartment_guid IS NULL;
82CREATE INDEX idx_tmp_living_acc_h_ap ON tmp_living_acc (house_guid, apartment_guid) WHERE apartment_guid IS NOT NULL;
83ANALYZE tmp_living_acc;
84
85--select * from tmp_living_acc where living_cnt_hd_acc > 0
86
87
88--SELECT * FROM nsism.nsi_addrobj ad where ad.aolevel = 1 and ad.is_actual and offname like '%Камч%' --41
89--Ð°Ð´Ñ€ÐµÑ Ð´Ð¾Ð¼Ð°
90DROP TABLE IF EXISTS tmp_nsi_addr;
91CREATE TEMPORARY TABLE tmp_nsi_addr AS
92SELECT house_guid, postalcode, regioncode, adr
93FROM DBLINK('hcsnsidb_d', '
94 SELECT house_guid, a.postalcode, ad.regioncode, nsism.func_get_address_by_attr(house_abbr,house_fullname,null,
95 region_fullname,area_abbr,area_fullname,city_abbr,city_fullname,settlement_abbr,settlement_fullname,street_abbr,
96 street_fullname,additional_territory_abbr,additional_territory_fullname,
97 additional_ter_street_abbr,additional_ter_street_fullname,ctar_abbr,ctar_fullname,planstruct_abbr,planstruct_fullname) adr
98 FROM nsism.nsi_addresses a
99 JOIN nsism.nsi_addrobj ad ON ad.aouid = a.region_guid AND ad.aolevel = 1 AND a.is_actual AND ad.is_actual
100 WHERE ad.regioncode = ''41''
101 ') AS (house_guid varchar(36), postalcode varchar(6), regioncode varchar(2), adr text);
102ANALYZE tmp_nsi_addr;
103
104--витрина по домам и квартирам (порÑдок полей как в шаблоне xls)
105DROP TABLE IF EXISTS tmp_house_apart_liv_numbr ;
106CREATE TEMPORARY TABLE tmp_house_apart_liv_numbr AS
107SELECT
108 adr.regioncode, --Ð´Ð»Ñ Ñ€Ð°Ð·Ð±Ð¸Ð²ÐºÐ¸ нет в ÑкÑеле
109 h.guid, --№ п/п
110 h.house_guid,
111 h.house_code,
112 CASE WHEN h.house_type_code = '1' THEN 'МКД' WHEN h.house_type_code = '2' AND h.blocked_house = false THEN 'ЖД' WHEN h.house_type_code = '2' AND h.blocked_house THEN 'ЖД блок' END AS house_type_code,
113 h.blocked_house,--жд блок заÑтройки
114 adr.postalcode,-- Ð˜Ð½Ð´ÐµÐºÑ Ð´Ð¾Ð¼Ð°
115 adr.adr,--ÐÐ´Ñ€ÐµÑ Ð´Ð¾Ð¼Ð°
116 h.oktmo_code, --ОКТМО
117 h.total_square AS house_total_square, --ÐžÐ±Ñ‰Ð°Ñ Ð¿Ð»Ð¾Ñ‰Ð°Ð´ÑŒ зданиÑ
118 h.wall_materials, -- Тип внутренних Ñтен wall_materials Материалы Ñтен - ÑпиÑок Ñтрокой
119 h.building_year, --Год поÑтройки
120 h.operation_year, --Год ввода в ÑкÑплуатацию
121 h.max_floor_count, --Кол-во Ñтажей
122 h.underground_floor_count, -- КоличеÑтво подземных Ñтажей
123 COALESCE(h.plan_series,'') ||'-'|| COALESCE(h.plan_type_code,'') AS house_plan_ser_type, --СериÑ, тип проекта зданиÑ
124
125 hpar.liv_numbr AS hm_houses_liv_numbr, -- Кол-во проживающих в доме из доп.параметров дома
126 rh.liv_numbr AS room_h_liv_numbr, --Кол-во проживающих из комнат (в доме без квартир)
127 rhpar.liv_numbr AS room_h_par_liv_numbr, --Кол-во проживающих из доп парам комнат (в доме без квартир)
128 acc_r.living_cnt_hd_acc AS acc_h_rooms_liv_numbr, -- Кол-во проживающих из ЛС комнат (в доме без квартир)
129
130 ap.guid AS apartment_guid, --квартиры (блоки в ЖД лежат в квартирах, в блоке могут быть комнаты)
131 ap.flat_number, --номер квартиры
132 ap.total_square AS apart_total_square, --ÐžÐ±Ñ‰Ð°Ñ Ð¿Ð»Ð¾Ñ‰Ð°Ð´ÑŒ жилого помещениÑ/блока
133 ap.residential_premise_type_code,--"ХарактериÑтика помещениÑ" = квартира коммунальго заÑÐµÐ»ÐµÐ½Ð¸Ñ 1ÐžÑ‚Ð´ÐµÐ»ÑŒÐ½Ð°Ñ ÐºÐ²Ð°Ñ€Ñ‚Ð¸Ñ€Ð° 2Квартира коммунального заÑÐµÐ»ÐµÐ½Ð¸Ñ 3Общежитие nsism.nsi_resident_premise_types
134
135 appar.liv_numbr AS apart_liv_numbr, --прож в квартире (взÑто из доп парам квартиры)
136 rpar.liv_numbr AS room_par_liv_numbr, --прож в квартире (взÑто из доп парам комнаты)
137 r.liv_numbr AS room_liv_numbr, --прож в квартире (взÑто из комнат)
138 acc_app.living_cnt_ap_acc AS acc_ap_liv_numbr, --прож в квартире (взÑто из ЛС квартиры)
139 acc_app.living_cnt_r_acc AS acc_r_liv_numbr --прож в квартире (взÑто из ЛС комнат квартиры)
140FROM
141 --дома
142 hmsm.hm_houses h --полный ÑпиÑок домов из Ð ÐО (колонки Ñ Ð¿Ñ€Ð¾Ð¶ нет) idx_hm_houses_house_guid house_guid
143 LEFT JOIN hmsm.hm_async_processes async ON async.house_guid = h.guid
144 --nsi может в tmp?
145 LEFT JOIN tmp_nsi_addr adr ON adr.house_guid = h.house_guid
146 LEFT JOIN tmp_house_params hpar ON hpar.house_guid = h.guid --доп параметры дома
147 LEFT JOIN tmp_rooms rh ON rh.house_guid = h.guid AND rh.apartment_guid IS NULL -- комнаты в домах (не имеющие квартир)
148 LEFT JOIN tmp_room_params rhpar ON rhpar.house_guid = h.guid AND rhpar.apartment_guid IS NULL -- доп параметры комнаты
149 LEFT JOIN tmp_living_acc acc_r ON acc_r.house_guid = h.guid AND acc_r.apartment_guid IS NULL -- ЛС комнат (их немного поÑтому INDEX idx_tmp_living_acc )
150 --квартиры
151 LEFT JOIN hmsm.hm_apartments ap ON ap.house_guid = h.guid AND ap.actual AND ap.status = 'APPROVED' AND ap.confirmed_by_supplier AND apartment_category_code = '1'--FK на hm_houses, квартиры (блоки) (колонки Ñ Ð¿Ñ€Ð¾Ð¶ нет) 1 Жилое 2 Ðежилое nsism.nsi_apartment_categories
152 LEFT JOIN tmp_apartment_params appar ON appar.apartment_guid = ap.guid -- доп параметры квартир idx_tmp_apartment_params apartment_guid
153 LEFT JOIN tmp_room_params rpar ON rpar.apartment_guid = ap.guid -- доп параметры комнаты
154 LEFT JOIN tmp_rooms r ON r.apartment_guid = ap.guid -- комнаты в помещениÑÑ…
155 LEFT JOIN tmp_living_acc acc_app ON acc_app.house_guid = h.guid AND acc_app.apartment_guid = ap.guid -- ЛС помещений idx_tmp_living_acc_h_ap по house_guid и apartment_guid
156WHERE h.status = 'APPROVED' AND async.guid IS NULL --дом не в аÑинх обраб
157AND h.region_code = 'd02f30fc-83bf-4c0f-ac2b-5729a866a207' --камчатка
158
159
160
161--выгрузка в ÑкÑель данных по домам
162SELECT
163MAX(regioncode) regioncode, --Ð´Ð»Ñ Ñ€Ð°Ð·Ð±Ð¸Ð²ÐºÐ¸ нет в ÑкÑеле
164guid, --№ п/п
165--MAX(house_guid) house_guid,
166--MAX(house_code) house_code,
167MAX(house_type_code) house_type_code, --Тип дома
168--BOOL_OR(blocked_house) blocked_house,--жд блок заÑтройки
169MAX(postalcode) postalcode,-- Ð˜Ð½Ð´ÐµÐºÑ Ð´Ð¾Ð¼Ð°
170MAX(adr) adr,--ÐÐ´Ñ€ÐµÑ Ð´Ð¾Ð¼Ð°
171MAX(oktmo_code) oktmo_code, --ОКТМО
172MAX(house_total_square) house_total_square, --ÐžÐ±Ñ‰Ð°Ñ Ð¿Ð»Ð¾Ñ‰Ð°Ð´ÑŒ зданиÑ
173MAX(wall_materials) wall_materials, -- Тип внутренних Ñтен wall_materials Материалы Ñтен - ÑпиÑок Ñтрокой
174MAX(building_year) building_year, --Год поÑтройки
175MAX(operation_year) operation_year, --Год ввода в ÑкÑплуатацию
176MAX(max_floor_count) max_floor_count, --Кол-во Ñтажей
177MAX(underground_floor_count) underground_floor_count, -- КоличеÑтво подземных Ñтажей
178MAX(house_plan_ser_type) house_plan_ser_type, --СериÑ, тип проекта зданиÑ
179--МКД
180COUNT(CASE WHEN house_type_code = 'МКД' THEN apartment_guid END) AS cnt_app_mkd,-- МКД Общее кол-во квартир (в Ñ‚.ч.квартир ком.заÑелениÑ)
181COUNT(CASE WHEN house_type_code = 'МКД' AND residential_premise_type_code = '2' THEN apartment_guid END) AS cnt_app_cmnl_mkd,-- МКД Кол-во квартир коммунального заÑелениÑ
182COUNT(CASE WHEN house_type_code = 'МКД' AND GREATEST(apart_liv_numbr, room_par_liv_numbr, room_liv_numbr, acc_ap_liv_numbr, acc_r_liv_numbr)>=0 THEN apartment_guid END) AS cnt_app_liv_mkd, --МКД Кол-во квартир Ñ ÑƒÐºÐ°Ð·Ð°Ð½Ð½Ñ‹Ð¼ кол-вом проживающих
183SUM(COALESCE( CASE WHEN house_type_code = 'МКД' THEN GREATEST(apart_liv_numbr, room_par_liv_numbr, room_liv_numbr, acc_ap_liv_numbr, acc_r_liv_numbr) END,0)) AS cnt_mkd_liv_numbr, --МКД Кол-во проживающих
184--ЖД
185COUNT(CASE WHEN house_type_code = 'ЖД блок' THEN apartment_guid END) AS cnt_blk_in_gd,-- ЖД блок Кол-во блоков
186COUNT(CASE WHEN house_type_code = 'ЖД блок' AND GREATEST(room_par_liv_numbr, room_liv_numbr, acc_r_liv_numbr, room_h_liv_numbr, room_h_par_liv_numbr, acc_h_rooms_liv_numbr)>=0 THEN apartment_guid END) AS cnt_blk_rms_in_gd, --ЖД блок Кол-во блоков Ñ ÐºÐ¾Ð¼Ð½Ð°Ñ‚Ð°Ð¼Ð¸ (наличие комнаты определÑем по наличию параметров по комнате)
187COUNT(CASE WHEN house_type_code = 'ЖД блок' AND GREATEST(apart_liv_numbr, room_par_liv_numbr, room_liv_numbr, acc_ap_liv_numbr, acc_r_liv_numbr, hm_houses_liv_numbr, room_h_liv_numbr, room_h_par_liv_numbr, acc_h_rooms_liv_numbr)>=0 THEN apartment_guid END) AS cnt_blk_hv_liv_gd, --ЖД блок Кол-во блоков Ñ ÑƒÐºÐ°Ð·Ð°Ð½Ð½Ñ‹Ð¼ кол-вом проживающих (??0 не Ñчитаем указанным)
188SUM(COALESCE(CASE WHEN house_type_code = 'ЖД блок' THEN GREATEST(room_par_liv_numbr, room_liv_numbr, acc_r_liv_numbr, hm_houses_liv_numbr, room_h_liv_numbr, room_h_par_liv_numbr, acc_h_rooms_liv_numbr) END,0)) AS cnt_blk_liv_gd, --ЖД блок Кол-во проживающих
189SUM(COALESCE(CASE WHEN house_type_code = 'ЖД' THEN GREATEST(apart_liv_numbr, room_par_liv_numbr, room_liv_numbr, acc_ap_liv_numbr, acc_r_liv_numbr, hm_houses_liv_numbr, room_h_liv_numbr, room_h_par_liv_numbr, acc_h_rooms_liv_numbr) END, 0)) AS cnt_liv_gd --ЖД Кол-во проживающих ÐЕ Ð±Ð»Ð¾ÐºÐ¸Ñ€Ð¾Ð²Ð°Ð½Ð½Ð°Ñ Ð·Ð°Ñтройка
190FROM tmp_house_apart_liv_numbr
191GROUP BY guid;
192
193
194--выгрузка в ÑкÑель данных по квартирам/блокам
195SELECT
196MAX(regioncode) regioncode, --Ð´Ð»Ñ Ñ€Ð°Ð·Ð±Ð¸Ð²ÐºÐ¸ нет в ÑкÑеле
197MAX(guid) guid, --Ðомер Ñтроки дома из ЛиÑта 1
198MAX(GREATEST(apart_liv_numbr, room_par_liv_numbr, room_liv_numbr, acc_ap_liv_numbr, acc_r_liv_numbr)) AS liv_numbr, --КоличеÑтво проживающих в квартире/блоке
199MAX(apart_total_square) apart_total_square--ÐžÐ±Ñ‰Ð°Ñ Ð¿Ð»Ð¾Ñ‰Ð°Ð´ÑŒ жилого помещениÑ
200FROM tmp_house_apart_liv_numbr
201WHERE apartment_guid IS not null AND GREATEST(apart_liv_numbr, room_par_liv_numbr, room_liv_numbr, acc_ap_liv_numbr, acc_r_liv_numbr) >=0
202GROUP BY apartment_guid