WKWpb2BH

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# все cpm за неделю перед включением lazy load для исключения равных нулю
46DROP TEMPORARY  TABLE IF EXISTS temp_all_rcpm;
47CREATE TEMPORARY TABLE IF NOT EXISTS temp_all_rcpm
48SELECT
49sc.`site_area_id`,
50  (SUM(sc.`partner_gain_base`) / SUM(sc.`block_first_request_count`) * 1000) AS 'rcpm'
51FROM
52  temp_areas_list
53  LEFT JOIN rabota_db.`npm_site_area_stat_cache` sc
54    ON sc.site_area_id = temp_areas_list.site_area_id
55WHERE (
56    sc.`date` BETWEEN temp_areas_list.lazy_load_start_date - INTERVAL 8 DAY
57          AND temp_areas_list.lazy_load_start_date - INTERVAL 1 DAY
58  )
59GROUP BY sc.`site_area_id`,
60  sc.`date` 
61HAVING (SUM(sc.`partner_gain_base`) / SUM(sc.`block_first_request_count`) * 1000) > 0
62ORDER BY sc.`site_area_id`, rcpm
63;
64
65# AVG rCPM и min CPM за неделю перед включением lazy load
66DROP TEMPORARY  TABLE IF EXISTS temp_avgrcpm;
67CREATE TEMPORARY TABLE IF NOT EXISTS temp_avgrcpm
68SELECT 
69      d.`site_area_id`,
70      ROUND(MIN(c.`rcpm`), 3) AS 'min_rcpm',
71      ROUND(SUM(d.`partner_gain_base`)/ SUM(d.`block_first_request_count`) * 1000,3) AS 'avg_rcpm_before_rub',
72      ROUND(SUM(d.`partner_gain`) / SUM(d.`block_first_request_count`) * 1000,3) AS 'avg_rcpm_before_cur'
73FROM 
74    temp_previous_data d
75    LEFT JOIN temp_all_rcpm c ON d.`site_area_id` = c.`site_area_id`
76GROUP BY d.`site_area_id`
77;
78
79# основные данные
80DROP TEMPORARY TABLE IF EXISTS  temp_basic_data;
81CREATE TEMPORARY TABLE temp_basic_data
82SELECT
83        s.url,
84        sa.name,
85	a.*,
86	b.avg_rcpm_before_rub,
87	IF(b.min_rcpm > a.rcpm, ABS(a.partner_gain - b.avg_rcpm_before_cur*a.requests/1000),0) AS to_compensate,
88	IF(b.min_rcpm > a.rcpm, ABS(a.partner_gain_base - b.avg_rcpm_before_rub*a.requests/1000),0) AS to_compensate_rub,
89	cur.`sign` AS pub_currency
90FROM temp_area_stat a
91LEFT JOIN site s ON s.site_id=a.site_id # для добавления url сайта
92LEFT JOIN site_area sa ON sa.site_area_id = a.site_area_id # для названия арии
93LEFT JOIN temp_avgrcpm b ON a.site_area_id = b.site_area_id # для среднего rCPM из временной таблицы
94LEFT JOIN `adopsuser` u ON a.`user_id` = u.`user_id` # для получения валюты юзера
95LEFT JOIN `currency` cur ON u.`cur_id` = cur.`currency_id` # для добавления знака валюты
96GROUP BY a.site_area_id
97;
98
99# вывод данных
100SELECT
101  t.site_id,
102  SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(t.url, '/', 3), '://', -1), '/', 1), '?', 1),'www.',-1) AS domain, # домен
103  t.site_area_id,
104  t.name,
105  ROUND(t.to_compensate, 2) AS to_compensate,
106  t.pub_currency,
107  ROUND(t.to_compensate_rub, 2) AS to_compensate_rub,
108  t.rcpm,
109  t.avg_rcpm_before_rub,
110  CONCAT(
111	'http://id',
112	t.`user_id`,
113	'.medianet.adlabsnetworks.com/site/PtzFixedBlock/?id=',
114	t.`site_area_id`,
115	'#tab=tab3') AS 'area_url'
116FROM
117  temp_basic_data t
118WHERE t.to_compensate > 0
119ORDER BY to_compensate_rub DESC
120;