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
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
package mobvista.prd.datasource.dao.impl;
import mobvista.prd.datasource.dao.DBCommonDAO;
import mobvista.prd.datasource.dao.ExportExcelDAO;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.List;
import java.util.Map;
/**
* Created by fl on 2017/5/15.
*/
public class ExportExcelDAOImpl extends DBCommonDAOImpl implements ExportExcelDAO {
public static final String DB_DRIVER = "com.mysql.jdbc.Driver";
public static final String DB_URL = "jdbc:mysql://datameta.c5yzcdreb1xr.us-east-1.rds.amazonaws.com:3306/monitor_effect";
public static final String DB_USER = "huifang";
public static final String DB_PWD = "Mobvista_123";
/**
*
* @param date
* @param tagType
* @param dataType 整体和分国家
* @return
* @throws Exception
*/
@Override
public List<Map<String, Object>> findDataByTag(String date, String tagType, String dataType) throws Exception {
StringBuilder builder = new StringBuilder();
builder.append("SELECT a.day_key, a.dimension_type2, a.dimension_desc, a.dimension_desc2,\n" )
.append(" a.dimension_desc3, a.dimension_desc4, concat('', a.video_value) as sample, concat('', b.video_value) as recommend\n")
.append("FROM dmp_report_reuslt a\n" )
.append("JOIN dmp_report_reuslt b\n" )
.append("on a.day_key=b.day_key and a.dimension_type1=b.dimension_type1\n" )
.append(" and a.dimension_type2=b.dimension_type2 and a.dimension_desc=b.dimension_desc\n")
.append(" and a.dimension_desc2=b.dimension_desc2 and a.dimension_desc3=b.dimension_desc3\n")
.append(" and a.dimension_desc4=b.dimension_desc4 AND b.video_desc='算法推算量'\n")
.append("where a.day_key=? AND a.dimension_type1='DMP标签数据'\n")
.append("AND a.dimension_type2=? and a.dimension_desc=? and a.video_desc='样本量' ");
if ("分国家".equals(dataType)) {
builder.append(" and a.dimension_desc2 in ('DE','HK','TW','JO','BH','FR','SA','QA','SG','UK','ID','OM','US','EG','AE','IN','KR','CN','KW','MY','ES','TH','PH','VN','PK','CA','MX')");
}
return findMapList(builder.toString(), new Object[]{date, tagType, dataType});
}
/**
*
* @param date
* @param dataType 整体和分国家
* @return
* @throws Exception
*/
public List<Map<String, Object>> findInterestTagData (String date, String dataType) throws Exception {
StringBuilder sql = new StringBuilder();
sql.append("SELECT t.day_key, t.dimension_type2, t.dimension_desc,\n")
.append(" t.dimension_desc2, t.dimension_desc3, t.dimension_desc4, concat('', t.video_value) as sample\n" )
.append("FROM dmp_report_reuslt t\n" )
.append("WHERE t.day_key=? and t.dimension_type1='DMP标签数据'\n" )
.append("AND t.dimension_type2='兴趣' and t.dimension_desc=? and t.video_desc='样本量'");
if ("分国家".equals(dataType)) {
sql.append(" and t.dimension_desc2 in ('DE','HK','TW','JO','BH','FR','SA','QA','SG','UK','ID','OM','US','EG','AE','IN','KR','CN','KW','MY','ES','TH','PH','VN','PK','CA','MX')");
}
return findMapList(sql.toString(), new Object[]{date, dataType});
}
/**
* 查询M系统分国家、app和平台DAU
*
* @param country
* @param appId
* @param date
* @param platform
* @return
* @throws Exception
*/
public String getAppDau (String country, String appId, String date, String platform) throws Exception {
Connection conn = null;
try {
Class.forName(DB_DRIVER);
conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PWD);
DBCommonDAO dao = new DBCommonDAOImpl();
StringBuilder sql = new StringBuilder();
sql.append("select dev_num ")
.append("from country_platform_dau ")
.append("where country_code=? and date=? and app_id=? and platform=? and unit_id='null'");
List<Map<String, Object>> list = dao.findMapList(conn, sql.toString(), new Object[]{country, date, appId, platform}, false);
return list.size() > 0 ? String.valueOf(list.get(0).get("dev_num")) : "0";
} finally {
if (conn != null) {
conn.close();
}
}
}
/**
* 查询各个数据源的相关数据
* @param date
* @param type
* @return
* @throws Exception
*/
public List<Map<String, Object>> findSourcesData (String date, String type) throws Exception {
StringBuilder sql = new StringBuilder();
sql.append("select t.day_key, t.dimension_type1, t.dimension_type2, t.video_value\n")
.append("from dmp_report_reuslt t\n")
.append("where t.day_key=?")
.append(" and t.dimension_type1 = ?");
if ("DMP新增数据".equals(type) || "DMP日活数据".equals(type)) {
sql.append(" and t.dimension_type2 in ('3S', 'ALL', 'DSP', 'GA', 'M')");
}
return findMapList(sql.toString(), new Object[]{date, type});
}
/**
* 获取各种ID统计数据
* @param date
* @return
* @throws Exception
*/
public List<Map<String, Object>> findIdsData(String date) throws Exception {
StringBuilder sql = new StringBuilder();
sql.append("select t.day_key, t.dimension_type2, sum(t.dau) as dau, sum(t.new) as new\n")
.append("from (\n")
.append(" SELECT t.day_key, t.dimension_type2, t.video_value AS dau, 0 AS new\n")
.append(" FROM dmp_report_reuslt t\n" )
.append( " WHERE t.day_key = ? AND t.dimension_type1 = 'DMP日活数据'\n" )
.append(" AND t.dimension_type2 IN ('androidid', 'gaid', 'idfa', 'imei', 'mac')\n")
.append(" UNION ALL\n" )
.append(" SELECT t.day_key, t.dimension_type2, 0 AS dau, t.video_value AS new\n" )
.append(" FROM dmp_report_reuslt t\n" )
.append(" WHERE t.day_key = ? AND t.dimension_type1 = 'DMP新增数据'\n" )
.append(" AND t.dimension_type2 IN ('androidid', 'gaid', 'idfa', 'imei', 'mac')\n")
.append(") t\n")
.append("GROUP BY t.day_key, t.dimension_type2");
return findMapList(sql.toString(), new Object[]{date, date});
}
public static void main(String[] args) throws Exception {
ExportExcelDAO dao = new ExportExcelDAOImpl();
List<Map<String, Object>> list = dao.findIdsData("20170822");
System.out.println(list.size());
for (Map<String, Object> map: list) {
System.out.println(map.toString());
}
}
}