tsl-data/src/main/java/com/nokia/tsl_data/dao/AvgDurationMapper.java

package com.nokia.tsl_data.dao;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/* cspell: disable */
@Mapper
public interface AvgDurationMapper {

    /**
     * 插入上月的平均处理时长
     */
    @Insert("with t1 as (select compl_area_local, " +
            "  case when proce_time != '' then (extract('epoch' from to_timestamp(proce_time, 'YYYY-MM-DD HH24:MI:SS')) " +
            "            - extract('epoch' from to_timestamp(accept_time, 'YYYY-MM-DD HH24:MI:SS'))) / 3600 " +
            "       when is_online_complete = '是' then 0 " +
            "       else (extract('epoch' from to_timestamp(end_time, 'YYYY-MM-DD HH24:MI:SS')) " +
            "            - extract('epoch' from to_timestamp(accept_time, 'YYYY-MM-DD HH24:MI:SS'))) / 3600 end as duration " +
            "from tsl_data.mobile_complaint_day hdmc where month_id = #{month_id} " +
            "and day_id::float8 = extract('day' from to_timestamp(#{month_id}, 'YYYYMM') + interval '1 month' - interval '1 day')) " +
            "insert into tsl_data.avg_duration (month_id, city_name, avg_duration) " +
            "select #{month_id} as month_id, compl_area_local as city_name, avg(duration) as avg_duration " +
            "from t1 group by compl_area_local ")
    void insertOldTsDurationForMonth(String monthId);

    /**
     * 查询历史处理时长
     */
    @Select("select city_name, avg_duration from tsl_data.avg_duration where month_id = #{monthId}")
    List<Map<String, Object>> selectOldTsDurationForMonth(String monthId);
}