TkioOfflineRepository.java 1.58 KB
Newer Older
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
package offline.repository;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * @Author shaowei.Liu
 * @Date 2020/11/5 19:48
 * @Description 查询离线数据: appKey对应的流量
 */
public class TkioOfflineRepository extends JdbcDaoSupport {

    public Map<String,Double> findOfflineResult(String startDate,String endDate){
        String sql = "SELECT appid as appkey,sum(if(click_num is null,0,click_num))+sum(if(num_impression is null,0,num_impression)) as appVal " +
                     "FROM measures_trackingio_new " +
                     "where DATE_FORMAT(ds,'%Y-%m-%d')>='"+startDate+"' and DATE_FORMAT(ds,'%Y-%m-%d')<='"+endDate+"' " +
                     "GROUP BY appid";

        List<Map<String, Double>> list = this.getJdbcTemplate().query(sql, new RowMapper<Map<String, Double>>() {
            @Override
            public Map<String, Double> mapRow(ResultSet rs, int rowNum) throws SQLException {
                String key = rs.getString("appkey");
                Double val = rs.getDouble("appVal");
                Map<String,Double> row = new HashMap<>();
                row.put(key, val);
                return row;
            }
        });
        Map<String,Double> result = new HashMap<>();
        for(Map<String,Double> map:list){
            for(String key:map.keySet()){
                result.put(key,map.get(key));
            }
        }
        return result;
    }

}