· 8 years ago · Feb 02, 2018, 10:38 AM
1SET @date="2018-02-01"; # ввеÑти дату Ð´Ð»Ñ Ð¾Ñ‚Ñ‡ÐµÑ‚Ð°
2SET @start_date="2018-01-01"; # ввеÑти первую дату периода Ð´Ð»Ñ AVG rCPM
3SET @end_date="2018-01-31"; # ввеÑти вторую дату периода Ð´Ð»Ñ AVG rCPM
4
5DROP TEMPORARY TABLE IF EXISTS `rabota_db`.avgrcpm;
6
7CREATE TEMPORARY TABLE IF NOT EXISTS `rabota_db`.avgrcpm AS (
8SELECT # AVG rCPM за указанный период
9 sc.`site_area_id`,
10 ROUND(CAST(SUM(sc.`partner_gain_base`) AS DEC(12,2)) / SUM(sc.`block_first_view_count`) * 1000, 2) AS 'AVG_rCPM',
11 ROUND(CAST(SUM(sc.`partner_gain`) AS DEC(12,2)) / SUM(sc.`block_first_view_count`) * 1000, 2) AS 'AVG_rCPM_gbp'
12FROM rabota_db.`npm_site_area_stat_cache` sc
13WHERE (sc.`date` BETWEEN @start_date AND @end_date)
14 AND sc.`site_area_id` IN
15 (614470,609754,598736,614150,606099,609525,608582,605721,594134,611913,613910,614065,607096,613234,593695,601336,613840,609405,609345,594463,600079,611902,609747,591804,602464,612247,573674,602599,602603,582982,604539,614576,607807,587357,605252,588368,602272,593697,609882,613866,596342,614441,594320,595805,593401,585258,608252,609866,610223,599411,613633,614211,612295)
16GROUP BY sc.`site_area_id`
17);
18
19SELECT @ar:=sc.`site_area_id` AS 'area_id',
20 ac.`avg_rcpm` AS 'AVG rCPM',
21 CONCAT(
22 IF(
23 ac.`avg_rcpm` - (CAST(SUM(sc.`partner_gain_base`) AS DEC(12,2)) / SUM(sc.`block_first_view_count`) * 1000) > 0,
24 CONCAT(ROUND((ac.`avg_rcpm` - ((CAST(SUM(sc.`partner_gain_base`) AS DEC(12,2)) / SUM(sc.`block_first_view_count`)) * 1000)) * SUM(sc.`block_first_request_count`) / 1000, 2), " руб."),
25
26 0)) AS 'compensation rub', # compensation [date]
27
28 CONCAT(
29 IF(
30 ac.`avg_rcpm` - (CAST(SUM(sc.`partner_gain_base`) AS DEC(12,2)) / SUM(sc.`block_first_view_count`) * 1000) > 0,
31 IF(cur.`sign_placement`="after" ,
32 CONCAT(
33 (ROUND((ac.`avg_rcpm_gbp` - ((CAST(SUM(sc.`partner_gain`) AS DEC(12,2)) / SUM(sc.`block_first_view_count`)) * 1000)) * SUM(sc.`block_first_request_count`) / 1000, 2)), " ", cur.`sign`),
34 CONCAT(
35 cur.`sign`,(ROUND((ac.`avg_rcpm_gbp` - ((CAST(SUM(sc.`partner_gain`) AS DEC(12,2)) / SUM(sc.`block_first_view_count`)) * 1000)) * SUM(sc.`block_first_request_count`) / 1000, 2)))),
36 0)) AS 'compensation pub cur', # compensation [date]
37
38 ROUND(CAST(SUM(sc.`partner_gain_base`) AS DEC(12,2)) / SUM(sc.`block_first_view_count`) * 1000, 2) AS 'rCPM', # rCPM [date]
39 ROUND((SELECT
40 CAST(SUM(sc.`partner_gain_base`) AS DEC(12,2)) / SUM(sc.`block_first_view_count`) * 1000
41 FROM rabota_db.`npm_site_area_stat_cache` sc
42 WHERE sc.`date`=@date - INTERVAL 1 DAY
43 AND sc.`site_area_id` = @ar), 2) AS 'rCPM [date-1]', # rCPM [date-1]
44 ROUND((SELECT
45 CAST(SUM(sc.`partner_gain_base`) AS DEC(12,2)) / SUM(sc.`block_first_view_count`) * 1000
46 FROM rabota_db.`npm_site_area_stat_cache` sc
47 WHERE sc.`date`=@date - INTERVAL 7 DAY
48 AND sc.`site_area_id` = @ar), 2) AS 'rCPM [date-7]', # rCPM [date-7]
49 SUM(sc.`block_first_request_count`) AS 'requests [date]', # Ñишные запроÑÑ‹ [date]
50 (SELECT
51 SUM(sc.`block_first_request_count`)
52 FROM rabota_db.`npm_site_area_stat_cache` sc
53 WHERE sc.`date`=@date - INTERVAL 1 DAY
54 AND sc.`site_area_id` = @ar) AS 'requests [date-1]', # Ñишные запроÑÑ‹ [date-1]
55 (SELECT
56 SUM(sc.`block_first_request_count`)
57 FROM rabota_db.`npm_site_area_stat_cache` sc
58 WHERE sc.`date`=@date - INTERVAL 7 DAY
59 AND sc.`site_area_id` = @ar) AS 'requests [date-7]', # Ñишные запроÑÑ‹ [date-7]
60 SUM(sc.`block_first_view_count`) AS 'impressions [date]', # Ñишные показы [date]
61 (SELECT
62 SUM(sc.`block_first_view_count`)
63 FROM rabota_db.`npm_site_area_stat_cache` sc
64 WHERE sc.`date`=@date - INTERVAL 1 DAY
65 AND sc.`site_area_id` = @ar) AS 'impressions [date-1]', # Ñишные показы [date-1]
66 (SELECT
67 SUM(sc.`block_first_view_count`)
68 FROM rabota_db.`npm_site_area_stat_cache` sc
69 WHERE sc.`date`=@date - INTERVAL 7 DAY
70 AND sc.`site_area_id` = @ar) AS 'impressions [date-7]', # Ñишные показы [date-7]
71 CONCAT(ROUND(SUM(sc.`partner_gain_base`), 2), " руб.") AS 'revenue [date]', # доход [date]
72 CONCAT(ROUND((SELECT
73 SUM(sc.`partner_gain_base`)
74 FROM rabota_db.`npm_site_area_stat_cache` sc
75 WHERE sc.`date`=@date - INTERVAL 1 DAY
76 AND sc.`site_area_id` = @ar), 2), " руб.") AS 'revenue [date-1]', # доход [date-1]
77 CONCAT(ROUND((SELECT
78 SUM(sc.`partner_gain_base`)
79 FROM rabota_db.`npm_site_area_stat_cache` sc
80 WHERE sc.`date`=@date - INTERVAL 7 DAY
81 AND sc.`site_area_id` = @ar), 2), " руб.") AS 'revenue [date-7]' # доход [date-7]
82FROM rabota_db.`npm_site_area_stat_cache` sc
83 LEFT JOIN rabota_db.`avgrcpm` ac ON sc.`site_area_id` = ac.`site_area_id`
84 LEFT JOIN rabota_db.`site` s ON sc.`site_id` = s.`site_id`
85 LEFT JOIN adopsuser au ON s.`user_id` = au.`user_id`
86 LEFT JOIN rabota_db.`currency` cur ON au.`cur_id` = cur.`currency_id`
87WHERE
88 sc.`date` = @date # за дату
89 AND sc.`site_area_id` IN (614470,609754,598736,614150,606099,609525,608582,605721,594134,611913,613910,614065,607096,613234,593695,601336,613840,609405,609345,594463,
90 600079,611902,609747,591804,602464,612247,573674,602599,602603,582982,604539,614576,607807,587357,605252,588368,602272,593697,609882,613866,596342,614441,594320,
91 595805,593401,585258,608252,609866,610223,599411,613633,614211,612295)
92 #and
93GROUP BY sc.`site_area_id`
94 ;