u0TXQksq

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  SUM(c.`partner_gain_base`) AS 'partner_gain_base',
15  SUM(c.`partner_gain`) AS 'partner_gain',
16  SUM(c.`block_first_request_count`) AS 'requests',
17  ROUND(SUM(c.`partner_gain_base`) / SUM(c.`block_first_request_count`) * 1000,3) AS 'rcpm', 
18  l.lazy_load_start_date AS 'lazy_load_start_date'
19FROM
20  `npm_site_area_stat_cache` c
21  JOIN temp_areas_list l
22    ON l.site_area_id = c.site_area_id
23WHERE 1 = 1
24  AND c.`date` = CURDATE() - INTERVAL 1 DAY
25GROUP BY c.site_area_id;
26
27# данные по ариям за неделю перед включением lazy load   
28DROP TEMPORARY TABLE IF EXISTS  temp_previous_data;
29CREATE TEMPORARY TABLE temp_previous_data
30 SELECT
31  sc.`site_area_id`,
32  sc.`date`,
33  SUM(sc.`partner_gain_base`) AS partner_gain_base,
34  SUM(sc.`partner_gain`) AS partner_gain,
35  SUM(sc.`block_first_request_count`) AS block_first_request_count
36FROM
37  temp_areas_list
38  LEFT JOIN rabota_db.`npm_site_area_stat_cache` sc
39    ON sc.site_area_id = temp_areas_list.site_area_id
40WHERE (sc.`date` BETWEEN temp_areas_list.lazy_load_start_date - INTERVAL 8 DAY AND temp_areas_list.lazy_load_start_date - INTERVAL 1 DAY)
41GROUP BY sc.`site_area_id`, sc.`date` 
42  ;
43  
44# все cpm за неделю перед включением lazy load для исключения равных нулю
45DROP TEMPORARY  TABLE IF EXISTS temp_all_rcpm;
46CREATE TEMPORARY TABLE IF NOT EXISTS temp_all_rcpm
47SELECT
48sc.`site_area_id`,
49  (SUM(sc.`partner_gain_base`) / SUM(sc.`block_first_request_count`) * 1000) AS 'rcpm'
50FROM
51  temp_areas_list
52  LEFT JOIN rabota_db.`npm_site_area_stat_cache` sc
53    ON sc.site_area_id = temp_areas_list.site_area_id
54WHERE (
55    sc.`date` BETWEEN temp_areas_list.lazy_load_start_date - INTERVAL 8 DAY
56          AND temp_areas_list.lazy_load_start_date - INTERVAL 1 DAY
57  )
58GROUP BY sc.`site_area_id`,
59  sc.`date` 
60HAVING (SUM(sc.`partner_gain_base`) / SUM(sc.`block_first_request_count`) * 1000) > 0
61ORDER BY sc.`site_area_id`, rcpm
62;
63
64# AVG rCPM и min CPM за неделю перед включением lazy load
65DROP TEMPORARY  TABLE IF EXISTS temp_avgrcpm;
66CREATE TEMPORARY TABLE IF NOT EXISTS temp_avgrcpm
67SELECT 
68      d.`site_area_id`,
69      ROUND(MIN(c.`rcpm`), 3) AS 'min_rcpm',
70      ROUND(SUM(d.`partner_gain_base`)/ SUM(d.`block_first_request_count`) * 1000,3) AS 'avg_rcpm_before_rub',
71      ROUND(SUM(d.`partner_gain`) / SUM(d.`block_first_request_count`) * 1000,3) AS 'avg_rcpm_before_cur'
72FROM 
73    temp_previous_data d
74    LEFT JOIN temp_all_rcpm c ON d.`site_area_id` = c.`site_area_id`
75GROUP BY d.`site_area_id`
76;
77
78# основные данные
79DROP TEMPORARY TABLE IF EXISTS  temp_basic_data;
80CREATE TEMPORARY TABLE temp_basic_data
81SELECT
82	a.*,
83	IF(b.min_rcpm > a.rcpm, ABS(a.partner_gain - b.avg_rcpm_before_cur*a.requests/1000),0) AS to_compensate,
84	IF(b.min_rcpm > a.rcpm, ABS(a.partner_gain_base - b.avg_rcpm_before_rub*a.requests/1000),0) AS to_compensate_rub,
85	cur.`sign` AS pub_currency
86FROM temp_area_stat a
87LEFT JOIN temp_avgrcpm b ON a.site_area_id = b.site_area_id # для среднего rCPM из временной таблицы
88LEFT JOIN `adopsuser` u ON a.`user_id` = u.`user_id` # для получения валюты юзера
89LEFT JOIN `currency` cur ON u.`cur_id` = cur.`currency_id` # для добавления знака валюты
90GROUP BY a.site_area_id
91;
92
93# таблица для вывода данных
94SELECT
95  t.date,
96  t.site_area_id,
97  ROUND(t.to_compensate, 2) AS to_compensate,
98  t.pub_currency,
99  ROUND(t.to_compensate_rub, 2) AS to_compensate_rub,
100  0 AS moderated,
101  0 AS paid 
102FROM
103  temp_basic_data t
104WHERE t.to_compensate > 0
105ORDER BY to_compensate_rub DESC
106;