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 > $\text{{((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>
<#-- $\text{{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