hSCNcErg

1# Задаём даты
2SET @start_date='2018-01-31';
3SET @end_date='2018-02-02';
4
5# Список арий
6DROP TEMPORARY TABLE IF EXISTS temp_areas_list;
7CREATE TEMPORARY TABLE temp_areas_list
8SELECT * FROM adopstempdb.lazy_load_areas l
9; #можно просто хранить все арии в этой таблице
10
11# Статистика по всем ариям за весь период + ещё 7 дней до этого
12DROP TEMPORARY TABLE IF EXISTS  temp_area_stat;
13CREATE TEMPORARY TABLE temp_area_stat
14SELECT
15  c.`date`,
16  c.`site_area_id`,
17  c.`user_id`,
18  c.`site_id`,
19  SUM(c.`partner_gain_base`) AS 'partner_gain_base',
20  SUM(c.`partner_gain`) AS 'partner_gain',
21  SUM(c.`block_first_request_count`) AS 'requests',
22  SUM(c.`block_first_view_count`) AS 'impressions',
23  ROUND(SUM(c.`partner_gain_base`) / SUM(c.`block_first_request_count`) * 1000,3) AS 'rcpm', 
24  l.lazy_load_start_date AS 'lazy_load_start_date'
25FROM
26  `npm_site_area_stat_cache` c
27  JOIN temp_areas_list l
28    ON l.site_area_id = c.site_area_id
29WHERE 1 = 1
30  AND c.`date` BETWEEN @start_date - INTERVAL 7 DAY AND @end_date
31GROUP BY c.`date`,
32  c.site_area_id;
33
34# Далее немного геморроя, чтобы обойти ограничение СУБД на возможность джойнить временную таблицу саму на себя или просто несколько раз джойнить её в одном запросе
35
36# Статистика по всем ариям за "текущий" день
37DROP TEMPORARY TABLE IF EXISTS  temp_area_stat_curdate;
38CREATE TEMPORARY TABLE temp_area_stat_curdate
39SELECT
40  #####
41  s.date,
42  s.site_area_id,
43  s.user_id,
44  s.site_id,
45  s.lazy_load_start_date,
46  s.rcpm AS rcpm_today,
47  s.partner_gain AS partner_gain_today,
48  s.partner_gain_base AS partner_gain_base_today,
49  s.requests AS requests_today,
50  s.impressions AS impressions_today
51FROM
52  temp_area_stat s;
53
54# Статистика по всем ариям за "текущий" и предыдущий день
55DROP TEMPORARY TABLE IF EXISTS  temp_area_stat_curdate_and_yesterday;
56CREATE TEMPORARY TABLE temp_area_stat_curdate_and_yesterday
57SELECT
58  s.*,
59  yesterday.partner_gain_base AS partner_gain_base_yesterday,
60  yesterday.requests AS requests_yesterday,
61  yesterday.impressions AS impressions_yesterday,
62  yesterday.rcpm AS rCPM_yesterday
63FROM
64  temp_area_stat_curdate s
65  JOIN temp_area_stat yesterday
66    ON s.site_area_id = yesterday.site_area_id
67    AND yesterday.date = s.date - INTERVAL 1 DAY;
68
69# Статистика по всем ариям за "текущий" и предыдущий день и за день на прошлой неделе
70DROP TEMPORARY TABLE IF EXISTS  temp_area_stat_all_days;
71CREATE TEMPORARY TABLE temp_area_stat_all_days
72SELECT
73  s.*,
74  weekago.partner_gain_base AS partner_gain_base_weekago,
75  weekago.requests AS requests_weekago,
76  weekago.impressions AS impressions_weekago,
77  weekago.rcpm AS rCPM_weekago
78FROM
79  temp_area_stat_curdate_and_yesterday s
80  JOIN temp_area_stat weekago
81    ON s.site_area_id = weekago.site_area_id
82    AND weekago.date = s.date - INTERVAL 7 DAY;   
83    
84# данные по ариям за неделю перед включением lazy load   
85DROP TEMPORARY TABLE IF EXISTS  temp_previous_data;
86CREATE TEMPORARY TABLE temp_previous_data
87 SELECT
88  sc.`site_area_id`,sc.`date`,
89  SUM(sc.`partner_gain_base`) AS partner_gain_base,
90  SUM(sc.`partner_gain`) AS partner_gain,
91  SUM(sc.`block_first_request_count`) AS block_first_request_count
92FROM
93  temp_areas_list
94  LEFT JOIN rabota_db.`npm_site_area_stat_cache` sc
95    ON sc.site_area_id = temp_areas_list.site_area_id
96WHERE (sc.`date` BETWEEN temp_areas_list.lazy_load_start_date - INTERVAL 8 DAY AND temp_areas_list.lazy_load_start_date - INTERVAL 1 DAY)
97GROUP BY sc.`site_area_id`, sc.`date` 
98  ;
99  
100# все cpm за неделю перед включением lazy load для исключения равных нулю
101DROP TEMPORARY  TABLE IF EXISTS temp_all_rcpm;
102CREATE TEMPORARY TABLE IF NOT EXISTS temp_all_rcpm
103SELECT
104sc.`site_area_id`,
105  (SUM(sc.`partner_gain_base`) / SUM(sc.`block_first_request_count`) * 1000) AS 'rcpm'
106FROM
107  temp_areas_list
108  LEFT JOIN rabota_db.`npm_site_area_stat_cache` sc
109    ON sc.site_area_id = temp_areas_list.site_area_id
110WHERE (
111    sc.`date` BETWEEN temp_areas_list.lazy_load_start_date - INTERVAL 8 DAY
112          AND temp_areas_list.lazy_load_start_date - INTERVAL 1 DAY
113  )
114GROUP BY sc.`site_area_id`,
115  sc.`date` 
116HAVING (SUM(sc.`partner_gain_base`) / SUM(sc.`block_first_request_count`) * 1000) > 0
117ORDER BY sc.`site_area_id`, rcpm
118;
119
120# AVG rCPM и min CPM за неделю перед включением lazy load
121DROP TEMPORARY  TABLE IF EXISTS temp_avgrcpm;
122CREATE TEMPORARY TABLE IF NOT EXISTS temp_avgrcpm AS (
123SELECT 
124      d.`site_area_id`,
125      ROUND(MIN(c.`rcpm`), 3) AS 'min_rcpm',
126      ROUND(SUM(d.`partner_gain_base`)/ SUM(d.`block_first_request_count`) * 1000,3) AS 'avg_rcpm_before_rub',
127      ROUND(SUM(d.`partner_gain`) / SUM(d.`block_first_request_count`) * 1000,3) AS 'avg_rcpm_before_cur'
128FROM 
129    temp_previous_data d
130    LEFT JOIN temp_all_rcpm c ON d.`site_area_id` = c.`site_area_id`
131GROUP BY d.`site_area_id`
132);
133
134# основные данные
135DROP TEMPORARY TABLE IF EXISTS  temp_basic_data;
136CREATE TEMPORARY TABLE temp_basic_data
137SELECT
138        s.url,
139        sa.name,
140	a.*,
141	b.min_rcpm,
142	#b.min_rcpm_0,
143	b.avg_rcpm_before_rub,
144	b.avg_rcpm_before_cur,
145	a.partner_gain_base_today - b.avg_rcpm_before_rub*a.requests_today/1000 AS profit_rub,
146	IF(b.min_rcpm > a.rcpm_today, ABS(a.partner_gain_today - b.avg_rcpm_before_cur*a.requests_today/1000),0) AS to_compensate,
147	u.`email`,
148	cur.`sign` AS pub_currency
149FROM temp_area_stat_all_days a
150LEFT JOIN site s ON s.site_id=a.site_id # для добавления url сайта
151LEFT JOIN site_area sa ON sa.site_area_id = a.site_area_id
152#LEFT JOIN min_rcpm mr ON mr.site_area_id = a.site_area_id # для получиния минимального rcpm
153LEFT JOIN temp_avgrcpm b ON a.site_area_id = b.site_area_id # для среднего rCPM из временной таблицы
154LEFT JOIN `adopsuser` u ON a.`user_id` = u.`user_id` # для получения валюты юзера
155LEFT JOIN `currency` cur ON u.`cur_id` = cur.`currency_id` # для добавления знака валюты
156GROUP BY a.site_area_id
157;
158
159SELECT
160  *
161FROM
162  temp_basic_data t
163  GROUP BY site_area_id
164  ORDER BY url
165;
166
167SELECT
168t.date,
169  t.site_id,
170  t.url,
171  t.site_area_id,
172  t.name,
173  t.to_compensate,
174  t.rcpm_today,
175  t.avg_rcpm_before_rub,
176  CONCAT(
177	'http://id',
178	t.`user_id`,
179	'.medianet.adlabsnetworks.com/site/PtzFixedBlock/?id=',
180	t.`site_area_id`,
181	'#tab=tab3') AS 'area_url'
182FROM
183  temp_basic_data t
184WHERE t.to_compensate > 0
185ORDER BY to_compensate DESC
186;