1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
package mobvista.dmp.datasource.rtdmp.lazada
/**
* @package: mobvista.dmp.datasource.rtdmp.lazada
* @author: wangjf
* @date: 2021/8/5
* @time: 6:13 下午
* @email: jinfeng.wang@mobvista.com
*/
object Constant {
val etl_install_sql: String =
"""
|SELECT campaign_id,gaid
| FROM dwh.ods_adn_trackingnew_install
| WHERE CONCAT(yyyy,mm,dd) = @dt AND UPPER(country_code) IN ('ID','PH')
| AND ext_campaignpackagename = 'com.lazada.android'
| GROUP BY campaign_id,gaid
|""".stripMargin
val etl_event_sql: String =
"""
|SELECT campaign_id,gaid
| FROM dwh.ods_adn_tracking_ss_event
| WHERE CONCAT(yyyy,mm,dd) = @dt AND country in('ID','PH') AND event_name = 'REGISTRATION'
| GROUP BY campaign_id,gaid
|""".stripMargin
val merge_install_sql: String =
"""
|SELECT campaign_id, gaid, '@new_date' update_date
| FROM dwh.etl_adn_tracking_install
| WHERE dt = '@dt'
| UNION ALL
|SELECT campaign_id, gaid, update_date
| FROM dwh.merge_adn_tracking_install
| WHERE dt = '@dt' AND update_date > '@update_date'
|""".stripMargin
val process_rtdmp_audience_sql: String =
"""
|SELECT t2.gaid
| FROM
| (SELECT campaign_id,gaid
| FROM dwh.etl_adn_tracking_event WHERE dt = '@dt'
| ) t1
| RIGHT JOIN
| (SELECT campaign_id,gaid
| FROM dwh.merge_adn_tracking_install WHERE dt = '@dt'
| ) t2
| ON t1.campaign_id = t2.campaign_id AND t1.gaid = t2.gaid
| WHERE t1.gaid IS NULL
| GROUP BY t2.gaid
|""".stripMargin
}