ZciaQ9hn

1# Список арий
2DROP TEMPORARY TABLE IF EXISTS temp_areas_list;
3CREATE TEMPORARY TABLE temp_areas_list
4SELECT * FROM adopstempdb.lazy_load_areas l
5;
6
7# Статистика по всем ариям за вчерашний день
8DROP TEMPORARY TABLE IF EXISTS  temp_area_stat;
9CREATE TEMPORARY TABLE temp_area_stat
10SELECT
11  c.`date`,
12  c.`site_area_id`,
13  c.`user_id`,
14  c.`site_id`,
15  SUM(c.`partner_gain_base`) AS 'partner_gain_base',
16  SUM(c.`partner_gain`) AS 'partner_gain',
17  SUM(c.`block_first_request_count`) AS 'requests',
18  ROUND(SUM(c.`partner_gain_base`) / SUM(c.`block_first_request_count`) * 1000,3) AS 'rcpm', 
19  l.lazy_load_start_date AS 'lazy_load_start_date'
20FROM
21  `npm_site_area_stat_cache` c
22  JOIN temp_areas_list l
23    ON l.site_area_id = c.site_area_id
24WHERE 1 = 1
25  AND c.`date` = CURDATE() - INTERVAL 1 DAY
26GROUP BY c.site_area_id;
27
28# данные по ариям за неделю перед включением lazy load   
29DROP TEMPORARY TABLE IF EXISTS  temp_previous_data;
30CREATE TEMPORARY TABLE temp_previous_data
31 SELECT
32  sc.`site_area_id`,
33  sc.`date`,
34  SUM(sc.`partner_gain_base`) AS partner_gain_base,
35  SUM(sc.`partner_gain`) AS partner_gain,
36  SUM(sc.`block_first_request_count`) AS block_first_request_count
37FROM
38  temp_areas_list
39  LEFT JOIN rabota_db.`npm_site_area_stat_cache` sc
40    ON sc.site_area_id = temp_areas_list.site_area_id
41WHERE (sc.`date` BETWEEN temp_areas_list.lazy_load_start_date - INTERVAL 8 DAY AND temp_areas_list.lazy_load_start_date - INTERVAL 1 DAY)
42GROUP BY sc.`site_area_id`, sc.`date` 
43  ;
44
45# AVG rCPM и min CPM за неделю перед включением lazy load
46DROP TEMPORARY  TABLE IF EXISTS temp_avgrcpm;
47CREATE TEMPORARY TABLE IF NOT EXISTS temp_avgrcpm AS (
48SELECT 
49      d.`site_area_id`,
50      ROUND(MIN(d.`partner_gain_base`/d.`block_first_request_count`)*1000,3) AS min_rcpm,
51      ROUND(SUM(d.`partner_gain_base`)/ SUM(d.`block_first_request_count`) * 1000,3) AS 'avg_rcpm_before_rub',
52      ROUND(SUM(d.`partner_gain`) / SUM(d.`block_first_request_count`) * 1000,3) AS 'avg_rcpm_before_cur'
53FROM 
54    temp_previous_data d
55GROUP BY d.`site_area_id`
56);
57
58# основные данные
59DROP TEMPORARY TABLE IF EXISTS  temp_basic_data;
60CREATE TEMPORARY TABLE temp_basic_data
61SELECT
62        s.url,
63        sa.name,
64	a.*,
65	b.avg_rcpm_before_rub,
66	IF(b.min_rcpm > a.rcpm, ABS(a.partner_gain - b.avg_rcpm_before_cur*a.requests/1000),0) AS to_compensate,
67	IF(b.min_rcpm > a.rcpm, ABS(a.partner_gain_base - b.avg_rcpm_before_rub*a.requests/1000),0) AS to_compensate_rub,
68	cur.`sign` AS pub_currency
69FROM temp_area_stat a
70LEFT JOIN site s ON s.site_id=a.site_id # для добавления url сайта
71LEFT JOIN site_area sa ON sa.site_area_id = a.site_area_id # для названия арии
72LEFT JOIN temp_avgrcpm b ON a.site_area_id = b.site_area_id # для среднего rCPM из временной таблицы
73LEFT JOIN `adopsuser` u ON a.`user_id` = u.`user_id` # для получения валюты юзера
74LEFT JOIN `currency` cur ON u.`cur_id` = cur.`currency_id` # для добавления знака валюты
75GROUP BY a.site_area_id
76;
77
78# вывод данных
79SELECT
80  t.site_id,
81  SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(t.url, '/', 3), '://', -1), '/', 1), '?', 1),'www.',-1) AS domain, # домен
82  t.site_area_id,
83  t.name,
84  ROUND(t.to_compensate, 2) AS to_compensate,
85  t.pub_currency,
86  ROUND(t.to_compensate_rub, 2) AS to_compensate_rub,
87  t.rcpm,
88  t.avg_rcpm_before_rub,
89  CONCAT(
90	'http://id',
91	t.`user_id`,
92	'.medianet.adlabsnetworks.com/site/PtzFixedBlock/?id=',
93	t.`site_area_id`,
94	'#tab=tab3') AS 'area_url'
95FROM
96  temp_basic_data t
97WHERE t.to_compensate > 0
98ORDER BY to_compensate_rub DESC
99;