mvf3QL0p

1#kolesa.ru
2
3SET @sa1 = 612791; # 300x600 Только баннеры - adx waterfall внизу
4SET @start_date = '2017-12-05';
5SET @end_date = '2017-12-31';
6SET @adx_tags = '3326aXl4,0049DwO2,5524vXg1,8099230453';
7SET @default_tag = '7959477733';
8
9DROP TEMPORARY TABLE IF EXISTS adopstempdb.adfox_t;
10CREATE TEMPORARY TABLE adopstempdb.adfox_t
11SELECT
12  'kolesa.ru' AS site,  
13  'AdX-AdFox: AdX' AS source,
14  ts.`site_area_id`,  
15  ts.`date`,
16  SUM(ts.`block_first_request_count`) AS requests,  
17  SUM(ts.`external_view_count`) AS impressions,
18  SUM(ts.`partner_gain_base`) AS partner_gain
19FROM 
20  npm_site_area_tag_stat ts
21WHERE
22  ts.site_area_id IN (@sa1)
23  AND ts.date BETWEEN @start_date AND @end_date
24  AND NOT FIND_IN_SET(ts.ext_ad_unit_id, @adx_tags)
25  AND NOT FIND_IN_SET(ts.ext_ad_unit_id, @default_tag)
26  AND ts.`sds_id` NOT IN (2312993) -- исключаем из подсчета Default Yandex
27  GROUP BY ts.`site_area_id`, `date`
28UNION
29SELECT
30  'kolesa.ru' AS site,  
31  'Clickio Chain' AS source,
32  ts.`site_area_id`,  
33  ts.`date`,  
34  SUM(ts.`block_first_request_count`) AS requests,  
35  SUM(ts.`external_view_count`) AS impressions,
36  SUM(ts.`partner_gain_base`) AS partner_gain
37FROM 
38  npm_site_area_tag_stat ts
39WHERE
40  ts.site_area_id IN (@sa1)
41  AND ts.date BETWEEN @start_date AND @end_date
42  AND FIND_IN_SET(ts.`ext_ad_unit_id`, @adx_tags)
43  GROUP BY ts.`site_area_id`, `date`
44UNION
45SELECT
46  'kolesa.ru' AS site,
47  'Default AdX' AS source,
48  ts.`site_area_id`,  
49  ts.`date`,
50  SUM(IF(ts.sds_id = 0, 0, ts.`block_first_request_count`)) AS requests,  
51  SUM(ts.`external_view_count`) AS impressions,
52  SUM(ts.`partner_gain_base`) AS partner_gain
53FROM 
54  npm_site_area_tag_stat ts
55WHERE
56  ts.site_area_id IN (@sa1)
57  AND ts.date BETWEEN @start_date AND @end_date
58  AND FIND_IN_SET(ts.`ext_ad_unit_id`, @default_tag)
59  GROUP BY ts.`site_area_id`, `date`
60UNION
61SELECT
62  'kolesa.ru' AS site,  
63  'Default Yandex' AS source,
64  ts.`site_area_id`,  
65  ts.`date`,
66  SUM(ts.`block_first_request_count`) AS requests,  
67  '' AS impressions,
68  '' AS partner_gain
69FROM 
70  npm_site_area_tag_stat ts
71WHERE
72  ts.site_area_id IN (@sa1)
73  AND ts.date BETWEEN @start_date AND @end_date
74  AND ts.`sds_id` IN (2312993)
75  GROUP BY ts.`site_area_id`, `date`
76UNION
77SELECT
78  'kolesa.ru' AS site,  
79  'AdX-AdFox: Yandex' AS source,
80  ts.`site_area_id`,  
81  ts.`date`,
82  '' AS requests,  
83  '' AS impressions,
84  '' AS partner_gain
85FROM 
86  npm_site_area_tag_stat ts
87WHERE
88  ts.site_area_id IN (@sa1)
89  AND ts.date BETWEEN @start_date AND @end_date
90  GROUP BY ts.`site_area_id`, `date`
91;
92
93DROP TEMPORARY TABLE IF EXISTS adopstempdb.adfox_t2;
94CREATE TEMPORARY TABLE adopstempdb.adfox_t2
95SELECT
96  t1.site,
97  t1.source,
98  t1.site_area_id,
99  t2.`name`,
100  t1.date,
101  t1.requests,
102  t1.impressions,
103  t1.partner_gain
104FROM
105  adopstempdb.adfox_t t1
106  INNER JOIN adopstempdb.adfox_match t2 USING (site_area_id)
107;
108
109DROP TEMPORARY TABLE IF EXISTS adopstempdb.adfox_t3;
110CREATE TEMPORARY TABLE adopstempdb.adfox_t3
111SELECT
112  t1.*,
113  IF(t1.source='AdX-AdFox: Yandex',t2.adfox_banner_id,t2.rtb_banner_id) AS yandex_banner_id
114FROM
115  adopstempdb.adfox_t2 t1
116  LEFT JOIN adopstempdb.`adfox_match` t2 ON (t1.site_area_id = t2.site_area_id AND t1.source IN ('AdX-AdFox: Yandex','Default Yandex'));
117
118SELECT
119  *
120FROM
121  adopstempdb.adfox_t3 t3
122ORDER BY site,
123  source,
124  site_area_id,
125  `date`
126;