1 SELECT
2 a.grid_name_1 县区网格,
3 f.当月_1 "新入网(户)",
4 CAST(f.当月 * 100 AS DECIMAL(10, 0)) || '%' "高质入网占比(25分)",
5 CAST(e.当月_10 * 100 AS DECIMAL(10, 1)) || '%' "首存率(10元以内)(5分)",
6 CAST(e.当月_50 * 100 AS DECIMAL(10, 1)) || '%' "首存率(50元以上)(20分)",
7 CAST(h.当月 * 100 AS DECIMAL(10, 1)) || '%' "纯新增占比(20分)",
8 CAST(f.当月_2 * 100 AS DECIMAL(10, 1)) || '%' "宽带融合率(10分)",
9 CAST(g.当月 * 100 AS DECIMAL(10, 0)) || '%' "入网当月停机率(10分)",
10 CAST(g.当月3 * 100 AS DECIMAL(10, 0)) || '%' "近2月新增停机率(10分)",
11 RANK() OVER (
12 ORDER BY
13 f.当月 * 25 + (1 - e.当月_10) * 5 + e.当月_50 * 20 + h.当月 * 20 + f.当月_2 * 10 + (1 - g.当月) * 10 + (1 - g.当月3) * 10 desc
14 ) AS 综合评估排名
15 FROM
16 (
17 SELECT
18 DISTINCT a.*
19 FROM
20 mtuser_ww.yutianhui_wangge_dingkong_day a
21 ) a
22 LEFT JOIN(
23 SELECT
24 a.grid_code,
25 COUNT(
26 DISTINCT CASE
27 WHEN b.yc_fee >= '50' THEN b.user_id
28 ELSE NULL
29 END
30 ) / NULLIF(COUNT(DISTINCT a.serv_number), 0) 当月_50,
31 COUNT(
32 DISTINCT CASE
33 WHEN b.yc_fee <= '10' THEN b.user_id
34 ELSE NULL
35 END
36 ) / NULLIF(COUNT(DISTINCT a.serv_number), 0) 当月_10
37 FROM
38 bdcis.tb_cis_user_induser_info_dtal a
39 LEFT JOIN (
40 SELECT
41 a.area_code,
42 a.user_id,
43 SUM(a.yc_fee) AS yc_fee
44 FROM
45 (
46 --普通预存费
47 SELECT
48 t11.area_code,
49 t11.user_id,
50 t11.recv_cash / 100 AS yc_fee
51 FROM
52 (
53 SELECT
54 b.area_code,
55 a.user_id,
56 recv_cash
57 FROM
58 bdbds.tb_bds_acct_am_busi_dtal_day a,
59 bdcis.tb_cis_user_induser_day_all b
60 WHERE
61 b.statis_day = to_char(CURRENT_DATE -1, 'yyyymmdd')
62 AND b.join_date BETWEEN date_trunc('month', CURRENT_DATE):: DATE
63 AND last_day(CURRENT_DATE)
64 AND a.user_id = b.user_id
65 AND cancel_flag = 'U'
66 AND b.cust_count = 1
67 AND recv_cash > 0
68 AND a.done_date BETWEEN date_trunc('month', CURRENT_DATE):: DATE
69 AND last_day(CURRENT_DATE)
70 AND TRIM(business_type_id) IN (
71 '4164',
72 '46301',
73 '4632',
74 '49051',
75 '2101',
76 '4130',
77 '4132',
78 '4178',
79 '41901',
80 '4731',
81 '4945',
82 '4172',
83 '4369',
84 '4871',
85 '4133',
86 '4162',
87 '4370',
88 '8936',
89 '4103',
90 '4105',
91 '8112',
92 '4131',
93 '4158',
94 '4187',
95 '4833',
96 '4850',
97 '12440',
98 '4102',
99 '4156',
100 '4470',
101 '4630',
102 '4190',
103 '11028',
104 '4101',
105 '4644',
106 '4905',
107 '4782',
108 '491',
109 '4234',
110 '491',
111 '4234',
112 '4735',
113 '8932'
114 )
115 ) t11
116 LEFT JOIN (
117 SELECT
118 DISTINCT sub_user_id
119 FROM
120 bdcis.tb_cis_user_induser_main_sub_card_list_day
121 WHERE
122 sub_join_date BETWEEN CAST(to_char(CURRENT_DATE, 'yyyymm') || '01' AS DATE)
123 AND last_day(CURRENT_DATE)
124 ) t12 ON t11.user_id = t12.sub_user_id
125 LEFT JOIN (
126 SELECT
127 DISTINCT user_id
128 FROM
129 bdbds.tb_bds_ser_user_offer_12_sprom_day
130 WHERE
131 create_date BETWEEN CAST(to_char(CURRENT_DATE, 'yyyymm') || '01' AS DATE)
132 AND last_day(CURRENT_DATE)
133 AND offer_id IN (
134 '101090083976',
135 '101090082998',
136 '101090082996',
137 '101090082997',
138 '101091820489'
139 )
140 ) t13 ON t11.user_id = t13.user_id
141 WHERE
142 t12.sub_user_id IS NULL
143 AND t13.user_id IS NULL
144 UNION
145 --活动预存费
146 SELECT
147 c.area_code,
148 a.user_id,
149 b.real_fee / 100 AS yc_fee
150 FROM
151 bdbds.tb_bds_ser_user_work_list_day a,
152 bdbds.tb_bds_acct_am_busi_charge_day b,
153 bdcis.tb_cis_user_induser_day_all c
154 WHERE
155 a.order_id = b.order_id
156 AND a.user_id = c.user_id
157 AND c.statis_day = to_char(CURRENT_DATE -1, 'yyyymmdd')
158 AND c.join_date BETWEEN date_trunc('month', CURRENT_DATE):: DATE
159 AND last_day(CURRENT_DATE)
160 AND b.charge_date BETWEEN date_trunc('month', CURRENT_DATE):: DATE
161 AND last_day(CURRENT_DATE)
162 AND c.cust_count = 1
163 AND c.user_id NOT IN (
164 SELECT
165 DISTINCT sub_user_id
166 FROM
167 bdcis.tb_cis_user_induser_main_sub_card_list_day
168 WHERE
169 sub_join_date BETWEEN date_trunc('month', CURRENT_DATE):: DATE
170 AND last_day(CURRENT_DATE)
171 )
172 AND c.user_id NOT IN (
173 SELECT
174 DISTINCT user_id
175 FROM
176 bdbds.tb_bds_ser_user_offer_12_sprom_day
177 WHERE
178 create_date BETWEEN date_trunc('month', CURRENT_DATE):: DATE
179 AND last_day(CURRENT_DATE)
180 AND offer_id IN (
181 '101090083976',
182 '101090082998',
183 '101090082996',
184 '101090082997',
185 '101091820489'
186 )
187 )
188 AND fee_item_id IN (
189 '23000034',
190 '21000018',
191 '21000100',
192 '21000100',
193 '21000013',
194 '21000050',
195 '26000012',
196 '21000100'
197 )
198 ) a
199 GROUP BY
200 1,
201 2
202 ) b ON a.user_id = b.user_id
203 AND b.yc_fee >= '10'
204 WHERE
205 a.join_date BETWEEN date_trunc('month', CURRENT_DATE):: DATE
206 AND last_day(CURRENT_DATE)
207 AND cust_count = '1'
208 AND plan_id NOT IN (
209 '100000999555',
210 '100000935109',
211 '100000999659',
212 '100000999660',
213 '100000999555'
214 )
215 GROUP BY
216 1
217 ) e ON a.grid_code = e.grid_code
218 LEFT JOIN(
219 SELECT
220 grid_code,
221 CAST(
222 COUNT(
223 DISTINCT CASE
224 WHEN b.plan_fee >= '79' THEN a.serv_number
225 ELSE NULL
226 END
227 ) / NULLIF(COUNT(DISTINCT a.serv_number), 0) AS DECIMAL(10, 1)
228 ) 当月,
229 COUNT(DISTINCT a.serv_number) 当月_1,
230 CAST(
231 COUNT(DISTINCT c.fix_serv_number_1) / NULLIF(COUNT(DISTINCT a.serv_number), 0) AS DECIMAL(10, 1)
232 ) 当月_2,
233 COUNT(
234 DISTINCT CASE
235 WHEN b.plan_fee <= '18' THEN a.serv_number
236 ELSE NULL
237 END
238 ) 当月18_1,
239 CAST(
240 COUNT(
241 DISTINCT CASE
242 WHEN b.plan_fee <= '18' THEN a.serv_number
243 ELSE NULL
244 END
245 ) / NULLIF(COUNT(DISTINCT a.serv_number), 0) AS DECIMAL(10, 1)
246 ) 当月18,
247 COUNT(
248 DISTINCT CASE
249 WHEN b.plan_fee BETWEEN '18.01' AND '78.99' THEN a.serv_number
250 ELSE NULL
251 END
252 ) 当月79_1,
253 CAST(
254 COUNT(
255 DISTINCT CASE
256 WHEN b.plan_fee BETWEEN '18.01' AND '78.99' THEN a.serv_number
257 ELSE NULL
258 END
259 ) / NULLIF(COUNT(DISTINCT a.serv_number), 0) AS DECIMAL(10, 1)
260 ) 当月79,
261 COUNT(
262 DISTINCT CASE
263 WHEN b.plan_fee >= '79' THEN a.serv_number
264 ELSE NULL
265 END
266 ) 当月99_1,
267 CAST(
268 COUNT(
269 DISTINCT CASE
270 WHEN b.plan_fee >= '79' THEN a.serv_number
271 ELSE NULL
272 END
273 ) / NULLIF(COUNT(DISTINCT a.serv_number), 0) AS DECIMAL(10, 1)
274 ) 当月99,
275 -----------
276 CAST(
277 SUM(
278 CASE
279 WHEN b.plan_fee <= '18' THEN a.gprs_use_b / 1024 / 1024 / 1024
280 ELSE '0'
281 END
282 ) / NULLIF(
283 COUNT(
284 DISTINCT CASE
285 WHEN b.plan_fee <= '10' THEN a.serv_number
286 ELSE NULL
287 END
288 ),
289 0
290 ) AS DECIMAL(10, 2)
291 ) 当月10dou,
292 CAST(
293 SUM(
294 CASE
295 WHEN b.plan_fee <= '18' THEN a.total_fee / 100
296 ELSE '0'
297 END
298 ) / NULLIF(
299 COUNT(
300 DISTINCT CASE
301 WHEN b.plan_fee <= '10' THEN a.serv_number
302 ELSE NULL
303 END
304 ),
305 0
306 ) AS DECIMAL(10, 1)
307 ) 当月10arpu,
308 CAST(
309 SUM(
310 CASE
311 WHEN b.plan_fee <= '18' THEN a.mo_call_duration / 60
312 ELSE '0'
313 END
314 ) / NULLIF(
315 COUNT(
316 DISTINCT CASE
317 WHEN b.plan_fee <= '10' THEN a.serv_number
318 ELSE NULL
319 END
320 ),
321 0
322 ) AS DECIMAL(10, 0)
323 ) 当月10mou,
324 -----------
325 CAST(
326 SUM(
327 CASE
328 WHEN b.plan_fee BETWEEN '18.01' AND '78.9' THEN a.gprs_use_b / 1024 / 1024 / 1024
329 ELSE '0'
330 END
331 ) / NULLIF(
332 COUNT(
333 DISTINCT CASE
334 WHEN b.plan_fee BETWEEN '10.01' AND '38.9' THEN a.serv_number
335 ELSE NULL
336 END
337 ),
338 0
339 ) AS DECIMAL(10, 2)
340 ) 当月39dou,
341 CAST(
342 SUM(
343 CASE
344 WHEN b.plan_fee BETWEEN '18.01' AND '78.9' THEN a.total_fee / 100
345 ELSE '0'
346 END
347 ) / NULLIF(
348 COUNT(
349 DISTINCT CASE
350 WHEN b.plan_fee BETWEEN '10.01' AND '38.9' THEN a.serv_number
351 ELSE NULL
352 END
353 ),
354 0
355 ) AS DECIMAL(10, 1)
356 ) 当月39arpu,
357 CAST(
358 SUM(
359 CASE
360 WHEN b.plan_fee BETWEEN '18.01' AND '78.9' THEN a.mo_call_duration / 60
361 ELSE '0'
362 END
363 ) / NULLIF(
364 COUNT(
365 DISTINCT CASE
366 WHEN b.plan_fee BETWEEN '10.01' AND '38.9' THEN a.serv_number
367 ELSE NULL
368 END
369 ),
370 0
371 ) AS DECIMAL(10, 0)
372 ) 当月39mou,
373 -----------
374 CAST(
375 SUM(
376 CASE
377 WHEN b.plan_fee >= '79' THEN a.gprs_use_b / 1024 / 1024 / 1024
378 ELSE '0'
379 END
380 ) / NULLIF(
381 COUNT(
382 DISTINCT CASE
383 WHEN b.plan_fee BETWEEN '39' AND '79' THEN a.serv_number
384 ELSE NULL
385 END
386 ),
387 0
388 ) AS DECIMAL(10, 2)
389 ) 当月79dou,
390 CAST(
391 SUM(
392 CASE
393 WHEN b.plan_fee >= '79' THEN a.total_fee / 100
394 ELSE '0'
395 END
396 ) / NULLIF(
397 COUNT(
398 DISTINCT CASE
399 WHEN b.plan_fee BETWEEN '39' AND '79' THEN a.serv_number
400 ELSE NULL
401 END
402 ),
403 0
404 ) AS DECIMAL(10, 1)
405 ) 当月79arpu,
406 CAST(
407 SUM(
408 CASE
409 WHEN b.plan_fee >= '79' THEN a.mo_call_duration / 60
410 ELSE '0'
411 END
412 ) / NULLIF(
413 COUNT(
414 DISTINCT CASE
415 WHEN b.plan_fee BETWEEN '39' AND '79' THEN a.serv_number
416 ELSE NULL
417 END
418 ),
419 0
420 ) AS DECIMAL(10, 0)
421 ) 当月79mou
422 FROM
423 bdcis.tb_cis_user_induser_info_dtal a
424 LEFT JOIN mtuser_ww.tb_kr_group_PLAN_name_mating b ON a.plan_id = b.plan_id
425 LEFT JOIN (
426 SELECT
427 serv_number,
428 fix_serv_number_1
429 FROM
430 bdcis.tb_cis_user_induser_broadband_day
431 WHERE
432 join_date >= date_trunc('month', CURRENT_DATE):: DATE
433 ) c ON c.fix_serv_number_1 = a.serv_number
434 WHERE
435 a.cust_count = '1'
436 AND join_date BETWEEN date_trunc('month', CURRENT_DATE):: DATE
437 AND last_day(CURRENT_DATE)
438 GROUP BY
439 1
440 ) f ON a.grid_code = f.grid_code
441 LEFT JOIN (
442 SELECT
443 grid_code,
444 CAST(
445 COUNT(
446 DISTINCT CASE
447 WHEN stop_flag IN ('1', '2')
448 AND join_date >= date_trunc('month', CURRENT_DATE):: DATE THEN serv_number
449 ELSE NULL
450 END
451 ) / NULLIF(
452 COUNT(
453 DISTINCT CASE
454 WHEN join_date >= date_trunc('month', CURRENT_DATE):: DATE THEN serv_number
455 ELSE NULL
456 END
457 ),
458 0
459 ) AS DECIMAL(10, 1)
460 ) 当月,
461 CAST(
462 COUNT(
463 DISTINCT CASE
464 WHEN stop_flag IN ('1', '2') THEN serv_number
465 ELSE NULL
466 END
467 ) / NULLIF(COUNT(DISTINCT serv_number), 0) AS DECIMAL(10, 1)
468 ) 当月3
469 FROM
470 bdcis.tb_cis_user_induser_day a
471 LEFT JOIN mtuser_ww.tb_kr_group_PLAN_name_mating b ON a.plan_id = b.plan_id
472 WHERE
473 a.statis_day = to_char(CURRENT_DATE -1, 'yyyymmdd')
474 AND user_type = '1'
475 AND join_date BETWEEN DATE(to_char(CURRENT_DATE, 'yyyymm') -1 || '01')
476 AND last_day(date_trunc('month', CURRENT_DATE):: DATE)
477 GROUP BY
478 1
479 ) g ON a.grid_code = g.grid_code
480 LEFT JOIN (
481 SELECT
482 a.grid_code,
483 COUNT(DISTINCT b.serv_number) / NULLIF(COUNT(DISTINCT a.serv_number), 0) 当月
484 FROM
485 bdcis.tb_cis_user_induser_info_dtal a
486 LEFT JOIN bdcis.tb_cis_user_induser_add_user_net_type_analysis_day b ON a.serv_number = b.serv_number
487 AND b.NET_TYPE = '纯新增'
488 AND b.join_date >= date_trunc('month', CURRENT_DATE):: DATE
489 WHERE
490 a.cust_count = '1'
491 AND a.join_date >= date_trunc('month', CURRENT_DATE):: DATE
492 GROUP BY
493 1
494 ) h ON a.grid_code = h.grid_code
495 ORDER BY
496 综合评估排名 desc
497 LIMIT
498 10;
帮忙优化sql，每行的序号忽略
扫一扫
1188
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
