AiBzAJJA

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