kZXN5YX1

1# Задаём даты
2SET @start_date=%start_date%;
3SET @end_date=%end_date%;
4
5# Список арий
6DROP TEMPORARY TABLE IF EXISTS temp_areas_list;
7CREATE TEMPORARY TABLE temp_areas_list
8SELECT * FROM lazy_load_areas_list l
9WHERE 1=1
10%filter_area_id%
11; #можно просто хранить все арии в этой таблице
12
13# Статистика по всем ариям за весь период + ещё 7 дней до этого
14DROP TEMPORARY TABLE IF EXISTS  temp_area_stat;
15CREATE TEMPORARY TABLE temp_area_stat
16SELECT
17  c.`date`,
18  c.`site_area_id`,
19  c.`user_id`,
20  SUM(c.`partner_gain_base`) AS 'partner_gain_base',
21  SUM(c.`partner_gain`) AS 'partner_gain',
22  SUM(c.`block_first_request_count`) AS 'requests',
23  SUM(c.`block_first_view_count`) AS 'impressions',
24  ROUND(SUM(c.`partner_gain_base`) / SUM(c.`block_first_request_count`) * 1000,3) AS 'rcpm', 
25  l.lazy_load_start_date AS 'lazy_load_start_date'
26FROM
27  `npm_site_area_stat_cache` c
28  JOIN temp_areas_list l
29    ON l.site_area_id = c.site_area_id
30WHERE 1 = 1
31  AND c.`date` BETWEEN @start_date - INTERVAL 7 DAY AND @end_date
32GROUP BY c.`date`,
33  c.site_area_id;
34
35# Далее немного геморроя, чтобы обойти ограничение СУБД на возможность джойнить временную таблицу саму на себя или просто несколько раз джойнить её в одном запросе
36
37# Статистика по всем ариям за "текущий" день
38DROP TEMPORARY TABLE IF EXISTS  temp_area_stat_curdate;
39CREATE TEMPORARY TABLE temp_area_stat_curdate
40SELECT
41	s.date,	s.site_area_id,	s.user_id, s.lazy_load_start_date, s.rcpm AS rcpm_today,
42	s.partner_gain AS partner_gain_today, s.partner_gain_base AS partner_gain_base_today, s.requests AS requests_today, s.impressions AS impressions_today
43FROM temp_area_stat s;
44
45# Статистика по всем ариям за "текущий" и предыдущий день
46DROP TEMPORARY TABLE IF EXISTS  temp_area_stat_curdate_and_yesterday;
47CREATE TEMPORARY TABLE temp_area_stat_curdate_and_yesterday
48SELECT
49  s.*,
50  yesterday.partner_gain_base AS partner_gain_base_yesterday,
51  yesterday.requests AS requests_yesterday,
52  yesterday.impressions AS impressions_yesterday,
53  yesterday.rcpm AS rCPM_yesterday
54FROM
55  temp_area_stat_curdate s
56  JOIN temp_area_stat yesterday
57    ON s.site_area_id = yesterday.site_area_id
58    AND yesterday.date = s.date - INTERVAL 1 DAY;
59
60# Статистика по всем ариям за "текущий" и предыдущий день и за день на прошлой неделе
61DROP TEMPORARY TABLE IF EXISTS  temp_area_stat_all_days;
62CREATE TEMPORARY TABLE temp_area_stat_all_days
63SELECT
64  s.*,
65  weekago.partner_gain_base AS partner_gain_base_weekago,
66  weekago.requests AS requests_weekago,
67  weekago.impressions AS impressions_weekago,
68  weekago.rcpm AS rCPM_weekago
69FROM
70  temp_area_stat_curdate_and_yesterday s
71  JOIN temp_area_stat weekago
72    ON s.site_area_id = weekago.site_area_id
73    AND weekago.date = s.date - INTERVAL 7 DAY;   
74    
75# таблица с rcpm за все дни
76DROP TEMPORARY TABLE IF EXISTS  all_rcpm;
77CREATE TEMPORARY TABLE all_rcpm
78 SELECT
79  sc.`site_area_id`,
80  (SUM(sc.`partner_gain_base`) / SUM(sc.`block_first_request_count`) * 1000) AS 'rcpm'
81FROM
82  temp_areas_list
83  LEFT JOIN rabota_db.`npm_site_area_stat_cache` sc
84    ON sc.site_area_id = temp_areas_list.site_area_id
85WHERE (
86    sc.`date` BETWEEN temp_areas_list.lazy_load_start_date - INTERVAL 8 DAY
87    AND temp_areas_list.lazy_load_start_date - INTERVAL 1 DAY
88  )
89GROUP BY sc.`site_area_id`,
90  sc.`date` 
91ORDER BY sc.`site_area_id`, rcpm
92  ;
93
94# выберем минимальный rcpm для каждой арии
95DROP TEMPORARY TABLE IF EXISTS  min_rcpm;
96CREATE TEMPORARY TABLE min_rcpm
97SELECT site_area_id, MIN(rcpm) AS 'min_rcpm' FROM all_rcpm GROUP BY site_area_id ORDER BY site_area_id, rcpm;
98  
99# AVG rCPM за неделю перед включением lazy load
100DROP TEMPORARY  TABLE IF EXISTS temp_avgrcpm;
101CREATE TEMPORARY TABLE IF NOT EXISTS temp_avgrcpm AS (
102SELECT 
103      sc.`site_area_id`,
104      ROUND(SUM(sc.`partner_gain_base`)/ SUM(sc.`block_first_request_count`) * 1000,4) AS 'avg_rcpm_before_rub',
105      ROUND(SUM(sc.`partner_gain`) / SUM(sc.`block_first_request_count`) * 1000,4) AS 'avg_rcpm_before_cur'
106FROM 
107    temp_areas_list
108LEFT JOIN rabota_db.`npm_site_area_stat_cache` sc  ON sc.site_area_id = temp_areas_list.site_area_id
109WHERE (sc.`date` BETWEEN temp_areas_list.lazy_load_start_date - INTERVAL 8 DAY AND temp_areas_list.lazy_load_start_date - INTERVAL 1 DAY)
110GROUP BY sc.`site_area_id`
111); 
112
113# основные данные
114DROP TEMPORARY TABLE IF EXISTS  temp_basic_data;
115CREATE TEMPORARY TABLE temp_basic_data
116SELECT
117	a.*,
118	b.avg_rcpm_before_rub,
119	b.avg_rcpm_before_cur,
120	a.partner_gain_base_today - b.avg_rcpm_before_rub*a.requests_today/1000 AS profit_rub,
121	IF(a.partner_gain_base_today - b.avg_rcpm_before_rub*a.requests_today/1000 < 0 AND mr.min_rcpm > a.rcpm_today, b.avg_rcpm_before_cur*a.requests_today/1000 - a.partner_gain_today,0) AS to_compensate,
122	u.`email`,
123	CONCAT(u.user_id,' - ',sa.name) AS NAME,
124	cur.`sign` AS pub_currency
125FROM temp_area_stat_all_days a
126LEFT JOIN `site_area` sa ON sa.site_area_id=a.site_area_id
127LEFT JOIN temp_avgrcpm b ON a.site_area_id = b.site_area_id # для среднего rCPM из временной таблицы
128LEFT JOIN min_rcpm mr ON mr.site_area_id = a.site_area_id # для получиния минимального rcpm
129LEFT JOIN `user` u ON a.`user_id` = u.`user_id` # для получения валюты юзера
130LEFT JOIN `currency` cur ON u.`cur_id` = cur.`currency_id` # для добавления знака валюты
131;
132
133
134 
135SELECT %cols% FROM temp_basic_data
136%groupby%;