MiniDao + MYSQL 复杂SQL 客户转化周期统计

SELECT departid,departname,create_time,

SUM( IF(conversion_time = ${((startDate?substring(2,7))?replace("-",""))?number},conversion_count,0)) as 'one',

SUM( IF(conversion_time = ${((startDate?substring(2,7))?replace("-",""))?number} + 1,conversion_count,0)) as 'two',

SUM( IF(conversion_time = ${((startDate?substring(2,7))?replace("-",""))?number} + 2,conversion_count,0)) as 'three',

SUM( IF(conversion_time = ${((startDate?substring(2,7))?replace("-",""))?number} + 3,conversion_count,0)) as 'four',

SUM( IF(conversion_time = ${((startDate?substring(2,7))?replace("-",""))?number} + 4,conversion_count,0)) as 'five',

SUM( IF(conversion_time = ${((startDate?substring(2,7))?replace("-",""))?number} + 5,conversion_count,0)) as 'six',

SUM( IF(conversion_time = ${((startDate?substring(2,7))?replace("-",""))?number} + 6,conversion_count,0)) as 'seven',

SUM( IF(conversion_time > ${((startDate?substring(2,7))?replace("-",""))?number} + 6,conversion_count,0)) as 'more'

FROM (

SELECT org_code,

parentdepartid,

departid,

departname,

create_time,

conversion_time,

conversion_period,

COUNT(id) AS conversion_count

FROM

(

SELECT

t_s_depart.org_code,

t_s_depart.parentdepartid,

t_s_depart.id as departid,

t_s_depart.departname,

c.id,

c.create_date AS lead_create_date,

public_sea_operation_record.create_date AS customer_create_date,

DATE_FORMAT( c.create_date, '%y%m' ) as create_time,

DATE_FORMAT( public_sea_operation_record.create_date, '%y%m' ) AS conversion_time,

PERIOD_DIFF(DATE_FORMAT(public_sea_operation_record.create_date,'%y%m'),DATE_FORMAT(c.create_date,'%y%m')) + 1 AS conversion_period

FROM customer c

INNER JOIN public_sea_operation_record ON c.id = public_sea_operation_record.obj_id AND public_sea_operation_record.operation_type = 5

AND c.is_deleted = 0 AND c.stage = 2

<#-- 操作时间：范围 -->

<#if startDate?? && startDate?has_content>

<#-- ${startDate?string["yyyy-MM-dd HH:mm:ss"]}-->

AND c.create_date >= :startDate

AND c.create_date <= CONCAT(LAST_DAY(DATE_FORMAT(:startDate ,'%Y-%m-%d')),' 23:59:59')

</#if>

<#if endDate?? && endDate?has_content>

<#-- ${endDate?string["yyyy-MM-dd HH:mm:ss"]}-->

AND c.create_date <= :endDate

</#if>

<#-- 操作部门：关联部门 -->

<#if ( deptIdList )?? && deptIdList?size gt 0 >

INNER JOIN crm_user_customer ON crm_user_customer.obj_id = c.id AND crm_user_customer.priority = 1

INNER JOIN t_s_base_user ON crm_user_customer.member_id = t_s_base_user.id

<#-- 关联用户状态-->

<#if userStatus?? && userStatus?has_content>

AND t_s_base_user.status = :userStatus

</#if>

INNER JOIN t_s_depart ON t_s_base_user.departid = t_s_depart.id

AND t_s_depart.id IN ( ${DaoFormat.getInStrs ( deptIdList )} )

<#else>

<#-- 关联用户状态-->

<#if userStatus?? && userStatus?has_content>

INNER JOIN crm_user_customer ON crm_user_customer.obj_id = c.id AND crm_user_customer.priority = 1

INNER JOIN t_s_base_user ON crm_user_customer.member_id = t_s_base_user.id

AND t_s_base_user.status = :userStatus

</#if>

</#if>

) customer_conversion

GROUP BY

departid,

create_time,

conversion_time,

conversion_period

) customer_conversion_count

GROUP BY departid,create_time

ORDER BY org_code,parentdepartid,departname