1. SELECT
2.   `layers`.`id` AS `id`,
3.   `hc`.`name` AS `hc_name`,
4.   `b`.`start_sales` AS `start_sales`,
5.   `b`.`name` AS `b_name`,
6.   `b`.`id` AS `building_id`,
7.   `loc`.`address` AS `address`,
8.   `loc`.`coordinates` AS `coords`,
9.   `loc`.`lat` AS `lat`,
10.   `loc`.`lng` AS `lng`,
11.   `loc`.`area` AS `area`,
12.   `loc`.`district` AS `district`,
13.   `loc`.`region` AS `region`,
14.   `loc`.`city` AS `city`,
15.   `m`.`station` AS `metro`,
16.   `b`.`apartments` AS `apartments`,
17.   `lt`.`name` AS `lot_type_name`,
18.   `dev`.`developer` AS `developer`,
19.   `hc`.`total_square` AS `total_square`,
20.   `hc`.`living_square` AS `living_square`,
21.   `b`.`rooms_st` AS `rooms_st`,
22.   `b`.`rooms_1` AS `rooms_1`,
23.   `b`.`rooms_2` AS `rooms_2`,
24.   `b`.`rooms_3` AS `rooms_3`,
25.   `b`.`rooms_4` AS `rooms_4`,
26.   `b`.`rooms_0` AS `rooms_0`,
27.   `b`.`floors_from` AS `floors_from`,
28.   `b`.`floors_to` AS `floors_to`,
29.   `oc`.`class` AS `class`,
30.   `cn`.`construction` AS `construction`,
31.   `layers`.`expo_num` AS `expo_num`,
32.   `layers`.`sumRmin` AS `sumRmin`,
33.   `layers`.`sumRavg` AS `sumRavg`,
34.   `layers`.`sumRmax` AS `sumRmax`,
35.   `b`.`update_time` AS `project_from`,
36.   `layers`.`createTimeMax` AS `createTimeMax`,
37.   `b`.`parking` AS `parking`,
38.   `b`.`car_spaces_num` AS `car_spaces_num`,
39.   `b`.`floor_type` AS `floor_type`,
40.   `layers`.`carSpacesPriceMin` AS `carSpacesPriceMin`,
41.   `layers`.`carSpacesPriceMax` AS `carSpacesPriceMax`,
42.   `b`.`car_square_min` AS `carSpacesSquareMin`,
43.   `b`.`car_square_max` AS `carSpacesSquareMax`,
44.   `layers`.`seller_id` AS `sellers`,
45.   `layers`.`desc` AS `discount_desc`,
46.   `layers`.`metrPriceRAvg` AS `metrPriceRAvg`,
47.   `layers`.`expo_st` AS `expo_st`,
48.   `layers`.`squareAll_st` AS `squareAll_st`,
49.   `layers`.`squareMin_st` AS `squareMin_st`,
50.   `layers`.`squareAvg_st` AS `squareAvg_st`,
51.   `layers`.`squareMax_st` AS `squareMax_st`,
52.   `layers`.`sumRmin_st` AS `sumRmin_st`,
53.   `layers`.`sumRavg_st` AS `sumRavg_st`,
54.   `layers`.`sumRmax_st` AS `sumRmax_st`,
55.   `layers`.`metrPriceRMin_st` AS `metrPriceRMin_st`,
56.   `layers`.`metrPriceRAvg_st` AS `metrPriceRAvg_st`,
57.   `layers`.`metrPriceRMax_st` AS `metrPriceRMax_st`,
58.   `layers`.`expo_1` AS `expo_1`,
59.   `layers`.`squareAll_1` AS `squareAll_1`,
60.   `layers`.`squareMin_1` AS `squareMin_1`,
61.   `layers`.`squareAvg_1` AS `squareAvg_1`,
62.   `layers`.`squareMax_1` AS `squareMax_1`,
63.   `layers`.`sumRmin_1` AS `sumRmin_1`,
64.   `layers`.`sumRavg_1` AS `sumRavg_1`,
65.   `layers`.`sumRmax_1` AS `sumRmax_1`,
66.   `layers`.`metrPriceRMin_1` AS `metrPriceRMin_1`,
67.   `layers`.`metrPriceRAvg_1` AS `metrPriceRAvg_1`,
68.   `layers`.`metrPriceRMax_1` AS `metrPriceRMax_1`,
69.   `layers`.`expo_2` AS `expo_2`,
70.   `layers`.`squareAll_2` AS `squareAll_2`,
71.   `layers`.`squareMin_2` AS `squareMin_2`,
72.   `layers`.`squareAvg_2` AS `squareAvg_2`,
73.   `layers`.`squareMax_2` AS `squareMax_2`,
74.   `layers`.`sumRmin_2` AS `sumRmin_2`,
75.   `layers`.`sumRavg_2` AS `sumRavg_2`,
76.   `layers`.`sumRmax_2` AS `sumRmax_2`,
77.   `layers`.`metrPriceRMin_2` AS `metrPriceRMin_2`,
78.   `layers`.`metrPriceRAvg_2` AS `metrPriceRAvg_2`,
79.   `layers`.`metrPriceRMax_2` AS `metrPriceRMax_2`,
80.   `layers`.`expo_3` AS `expo_3`,
81.   `layers`.`squareAll_3` AS `squareAll_3`,
82.   `layers`.`squareMin_3` AS `squareMin_3`,
83.   `layers`.`squareAvg_3` AS `squareAvg_3`,
84.   `layers`.`squareMax_3` AS `squareMax_3`,
85.   `layers`.`sumRmin_3` AS `sumRmin_3`,
86.   `layers`.`sumRavg_3` AS `sumRavg_3`,
87.   `layers`.`sumRmax_3` AS `sumRmax_3`,
88.   `layers`.`metrPriceRMin_3` AS `metrPriceRMin_3`,
89.   `layers`.`metrPriceRAvg_3` AS `metrPriceRAvg_3`,
90.   `layers`.`metrPriceRMax_3` AS `metrPriceRMax_3`,
91.   `layers`.`expo_4` AS `expo_4`,
92.   `layers`.`squareAll_4` AS `squareAll_4`,
93.   `layers`.`squareMin_4` AS `squareMin_4`,
94.   `layers`.`squareAvg_4` AS `squareAvg_4`,
95.   `layers`.`squareMax_4` AS `squareMax_4`,
96.   `layers`.`sumRmin_4` AS `sumRmin_4`,
97.   `layers`.`sumRavg_4` AS `sumRavg_4`,
98.   `layers`.`sumRmax_4` AS `sumRmax_4`,
99.   `layers`.`metrPriceRMin_4` AS `metrPriceRMin_4`,
100.   `layers`.`metrPriceRAvg_4` AS `metrPriceRAvg_4`,
101.   `layers`.`metrPriceRMax_4` AS `metrPriceRMax_4`,
102.   `layers`.`expo_0` AS `expo_0`,
103.   `layers`.`squareAll_0` AS `squareAll_0`,
104.   `layers`.`squareMin_0` AS `squareMin_0`,
105.   `layers`.`squareAvg_0` AS `squareAvg_0`,
106.   `layers`.`squareMax_0` AS `squareMax_0`,
107.   `layers`.`sumRmin_0` AS `sumRmin_0`,
108.   `layers`.`sumRavg_0` AS `sumRavg_0`,
109.   `layers`.`sumRmax_0` AS `sumRmax_0`,
110.   `layers`.`metrPriceRMin_0` AS `metrPriceRMin_0`,
111.   `layers`.`metrPriceRAvg_0` AS `metrPriceRAvg_0`,
112.   `layers`.`metrPriceRMax_0` AS `metrPriceRMax_0`,
113.   `layers`.*,
114.   `ds`.`amount` AS `amount`,
115.   `ds`.`flats` AS `flats`,
116.   `ds`.`apart` AS `apart`,
117.   `ds`.`car_spaces` AS `car_spaces`,
118.   `ds`.`non_residential` AS `non_residential`,
119.   `ds`.`pantry` AS `pantry`,
120.   `ds`.`flats_fl` AS `flats_fl`,
121.   `ds`.`flats_ul` AS `flats_ul`,
122.   `ds`.`apart_fl` AS `apart_fl`,
123.   `ds`.`apart_ul` AS `apart_ul`,
124.   `ds`.`flats_fl_mortgage` AS `flats_fl_mortgage`,
125.   `ds`.`flats_fl_non_mortgage` AS `flats_fl_non_mortgage`,
126.   `ds`.`apart_fl_mortgage` AS `apart_fl_mortgage`,
127.   `ds`.`apart_fl_non_mortgage` AS `apart_fl_non_mortgage`,
128.   `ds`.`pace_flats` AS `pace_flats`,
129.   `ds`.`pace_flats_fl` AS `pace_flats_fl`,
130.   `ds`.`pace_flats_ul` AS `pace_flats_ul`,
131.   `ds`.`pace_apart` AS `pace_apart`,
132.   `ds`.`pace_apart_fl` AS `pace_apart_fl`,
133.   `ds`.`pace_apart_ul` AS `pace_apart_ul`,
134.   `ds`.`pace_flats_pre_1` AS `pace_flats_pre_1`,
135.   `ds`.`pace_flats_fl_pre_1` AS `pace_flats_fl_pre_1`,
136.   `ds`.`pace_flats_ul_pre_1` AS `pace_flats_ul_pre_1`,
137.   `ds`.`pace_apart_pre_1` AS `pace_apart_pre_1`,
138.   `ds`.`pace_apart_fl_pre_1` AS `pace_apart_fl_pre_1`,
139.   `ds`.`pace_apart_ul_pre_1` AS `pace_apart_ul_pre_1`,
140.   `ds`.`pace_flats_pre_3` AS `pace_flats_pre_3`,
141.   `ds`.`pace_flats_fl_pre_3` AS `pace_flats_fl_pre_3`,
142.   `ds`.`pace_flats_ul_pre_3` AS `pace_flats_ul_pre_3`,
143.   `ds`.`pace_apart_pre_3` AS `pace_apart_pre_3`,
144.   `ds`.`pace_apart_fl_pre_3` AS `pace_apart_fl_pre_3`,
145.   `ds`.`pace_apart_ul_pre_3` AS `pace_apart_ul_pre_3`,
146.   `ds`.`pace_flats_pre_6` AS `pace_flats_pre_6`,
147.   `ds`.`pace_flats_fl_pre_6` AS `pace_flats_fl_pre_6`,
148.   `ds`.`pace_flats_ul_pre_6` AS `pace_flats_ul_pre_6`,
149.   `ds`.`pace_apart_pre_6` AS `pace_apart_pre_6`,
150.   `ds`.`pace_apart_fl_pre_6` AS `pace_apart_fl_pre_6`,
151.   `ds`.`pace_apart_ul_pre_6` AS `pace_apart_ul_pre_6`,
152.   `ds`.`pace_flats_pre_12` AS `pace_flats_pre_12`,
153.   `ds`.`pace_flats_fl_pre_12` AS `pace_flats_fl_pre_12`,
154.   `ds`.`pace_flats_ul_pre_12` AS `pace_flats_ul_pre_12`,
155.   `ds`.`pace_apart_pre_12` AS `pace_apart_pre_12`,
156.   `ds`.`pace_apart_fl_pre_12` AS `pace_apart_fl_pre_12`,
157.   `ds`.`pace_apart_ul_pre_12` AS `pace_apart_ul_pre_12`,
158.   `ds`.`flats_fl_square` AS `flats_fl_square`,
159.   `ds`.`apart_fl_square` AS `apart_fl_square`,
160.   `ds`.`flats_fl_square_avg` AS `flats_fl_square_avg`,
161.   `ds`.`apart_fl_square_avg` AS `apart_fl_square_avg`,
162.   `ds`.`flats_fl_metrprice_avg` AS `flats_fl_metrprice_avg`,
163.   `ds`.`apart_fl_metrprice_avg` AS `apart_fl_metrprice_avg`,
164.   `ds`.`flats_fl_sum_avg` AS `flats_fl_sum_avg`,
165.   `ds`.`apart_fl_sum_avg` AS `apart_fl_sum_avg`,
166.   `ub`.`unrealized_amount` AS `unrealized_amount`,
167.   `ub`.`unrealized_square` AS `unrealized_square`,
168.   `ub`.`avg_annual_amount` AS `avg_annual_amount`,
169.   `ub`.`avg_annual_square` AS `avg_annual_square`,
170.   `ub`.`absorption_by_lot_amount_1` AS `absorption_by_lot_amount_1`,
171.   `ub`.`absorption_by_square_1` AS `absorption_by_square_1`,
172.   `ub`.`absorption_by_lot_amount_2` AS `absorption_by_lot_amount_2`,
173.   `ub`.`absorption_by_square_2` AS `absorption_by_square_2`,
174.   `ub`.`unrealized_amount_1` AS `unrealized_amount_1`,
175.   `ub`.`unrealized_square_1` AS `unrealized_square_1`,
176.   `ub`.`avg_annual_amount_1` AS `avg_annual_amount_1`,
177.   `ub`.`avg_annual_square_1` AS `avg_annual_square_1`,
178.   `ub`.`unrealized_amount_2` AS `unrealized_amount_2`,
179.   `ub`.`unrealized_square_2` AS `unrealized_square_2`,
180.   `ub`.`avg_annual_amount_2` AS `avg_annual_amount_2`,
181.   `ub`.`avg_annual_square_2` AS `avg_annual_square_2`,
182.   `ub`.`unrealized_amount_3` AS `unrealized_amount_3`,
183.   `ub`.`unrealized_square_3` AS `unrealized_square_3`,
184.   `ub`.`avg_annual_amount_3` AS `avg_annual_amount_3`,
185.   `ub`.`avg_annual_square_3` AS `avg_annual_square_3`,
186.   `ub`.`unrealized_amount_4` AS `unrealized_amount_4`,
187.   `ub`.`unrealized_square_4` AS `unrealized_square_4`,
188.   `ub`.`avg_annual_amount_4` AS `avg_annual_amount_4`,
189.   `ub`.`avg_annual_square_4` AS `avg_annual_square_4`,
190.   `ub`.`unrealized_amount_0` AS `unrealized_amount_0`,
191.   `ub`.`unrealized_square_0` AS `unrealized_square_0`,
192.   `ub`.`avg_annual_amount_0` AS `avg_annual_amount_0`,
193.   `ub`.`avg_annual_square_0` AS `avg_annual_square_0`,
194.   `ub`.`unrealized_amount_st` AS `unrealized_amount_st`,
195.   `ub`.`unrealized_square_st` AS `unrealized_square_st`,
196.   `ub`.`avg_annual_amount_st` AS `avg_annual_amount_st`,
197.   `ub`.`avg_annual_square_st` AS `avg_annual_square_st`,
198.   `ub`.`conversion_factor` AS `conversion_factor`,
199.   `ub`.`conversion_factor_1` AS `conversion_factor_1`,
200.   `ub`.`conversion_factor_2` AS `conversion_factor_2`,
201.   `ub`.`conversion_factor_3` AS `conversion_factor_3`,
202.   `ub`.`conversion_factor_4` AS `conversion_factor_4`,
203.   `ub`.`conversion_factor_0` AS `conversion_factor_0`,
204.   `ub`.`conversion_factor_st` AS `conversion_factor_st`,
205.   `ps`.`metr_price` AS `metr_price`,
206.   `ps`.`metr_price_st` AS `metr_price_st`,
207.   `ps`.`metr_price_1` AS `metr_price_1`,
208.   `ps`.`metr_price_2` AS `metr_price_2`,
209.   `ps`.`metr_price_3` AS `metr_price_3`,
210.   `ps`.`metr_price_4` AS `metr_price_4`,
211.   `ps`.`metr_price_0` AS `metr_price_0`,
212.   `ps`.`sum` AS `sum`,
213.   `ps`.`sum_st` AS `sum_st`,
214.   `ps`.`sum_1` AS `sum_1`,
215.   `ps`.`sum_2` AS `sum_2`,
216.   `ps`.`sum_3` AS `sum_3`,
217.   `ps`.`sum_4` AS `sum_4`,
218.   `ps`.`sum_0` AS `sum_0`,
219.   IFNULL (ip.inc_price, '0.00') AS `inc_price`,
220.   IFNULL (ip.inc_price_st, '0.00') AS `inc_price_st`,
221.   IFNULL (ip.inc_price_1, '0.00') AS `inc_price_1`,
222.   IFNULL (ip.inc_price_2, '0.00') AS `inc_price_2`,
223.   IFNULL (ip.inc_price_3, '0.00') AS `inc_price_3`,
224.   IFNULL (ip.inc_price_4, '0.00') AS `inc_price_4`,
225.   IFNULL (ip.inc_price_0, '0.00') AS `inc_price_0`,
226.   IFNULL (ip.inc_sum, '0.00') AS `inc_sum`,
227.   IFNULL (ip.inc_sum_st, '0.00') AS `inc_sum_st`,
228.   IFNULL (ip.inc_sum_1, '0.00') AS `inc_sum_1`,
229.   IFNULL (ip.inc_sum_2, '0.00') AS `inc_sum_2`,
230.   IFNULL (ip.inc_sum_3, '0.00') AS `inc_sum_3`,
231.   IFNULL (ip.inc_sum_4, '0.00') AS `inc_sum_4`,
232.   IFNULL (ip.inc_sum_0, '0.00') AS `inc_sum_0`
233. FROM
234.   `bnmap_building_collection` `layers`
235.   INNER JOIN `parser_building` `b`
236.     ON b.id = layers.object_id
237.   INNER JOIN `core_location` `loc`
238.     ON b.location_id = loc.id
239.   INNER JOIN `parser_housing_complex` `hc`
240.     ON hc.id = b.housing_complex_id
241.   LEFT JOIN `parser_objectclass` `oc`
242.     ON oc.id = b.objectclass_id
243.   LEFT JOIN `parser_construction` `cn`
244.     ON cn.id = b.construction_id
245.   LEFT JOIN `parser_salestage` `pss`
246.     ON pss.id = layers.salestage_id
247.   LEFT JOIN `parser_area` `ar`
248.     ON ar.id = b.area_id
249.   LEFT JOIN `parser_metro` `m`
250.     ON m.id = b.metro_id
251.   LEFT JOIN `parser_developer` `dev`
252.     ON dev.id = b.developer_id
253.   LEFT JOIN `parser_lot_types` `lt`
254.     ON lt.id = b.apartments
255.   LEFT JOIN `bnmap_building_deals_collection` `ds`
256.     ON ds.collection_id = layers.id
257.   LEFT JOIN `bnmap_building_unrealized_balances` `ub`
258.     ON ub.collection_id = layers.id
259.   LEFT JOIN `bnmap_building_price_dynamics` `ps`
260.     ON ps.collection_id = layers.id
261.   LEFT JOIN `bnmap_building_increase_prices` `ip`
262.     ON ip.collection_id = layers.id
263.   INNER JOIN
264.     (SELECT
265.       MAX(sub_t2.createTimeMax) AS `max_date`,
266.       `sub_t2`.`object_id` AS `object_id`
267.     FROM
268.       `bnmap_building_collection` `sub_t2`
269.     GROUP BY `sub_t2`.`object_id`) `t2`
270.     ON layers.createTimeMax = t2.max_date
271.     AND layers.object_id = t2.object_id
272. WHERE (`b`.`complete` = 1)
273.   AND (
274.     `b`.`region_id` IN ('3', '1', '2', '4')
275.   )
276.   AND (
277.     (loc.lat IS NOT NULL)
278.     AND (loc.lng IS NOT NULL)
279.   )
280.   AND (`pss`.`alias` = 'frozen')