13-4-统计分析--线索统计--线索转化率漏斗图（实现）

YangeIT大约 5 分钟

13-4-统计分析--线索统计--线索转化率漏斗图（实现）

统计分析--线索统计--线索转化率漏斗图

需求：在一段时间范围内，查询线索数量中，哪些是有效线索，在有效线索中查询哪些被转成了商机,在商机中查询哪些被转换成了合同，有效线索的结果集要从线索中来，商机的结果要从有效线索中找，合同的结果要从商机中找

其中线索不用考虑状态有多少线索全部统计

有效线索指的是待跟进，跟进中，转换成商机，转换成客户这个状态的枚举类在TbClue中

转换成商机的需要考虑状态是：待跟进，根进中, 转换成客户

转换成合同的不用考虑状态，只要是合同统计但是结果集是要从转换成商机中的结果中取

注意：由于可以人工的添加商机和合同这部分人工的由于没有线索部分，所以这部分的数据不要统计在漏斗图中，比如我现在人工的添加了一份商机，该商机是没有线索的，所以不需要统计在漏斗图中

接口名：/report/getVulnerabilityMap

请求方式：GET

参数列表：

传入参数：

/report/getVulnerabilityMap/2021-03-08/2022-04-08

beginCreateTime 开始时间

endCreateTime 结束时间

返回值：

{
    "msg":"操作成功",
    "code":200,
    "data":{
        "cluesNums":752,				线索数量
        "effectiveCluesNums":506,       有效线索数
        "businessNums":268,    			商机数量
        "contractNums":111				合同数量
    }
}

步骤：

1.阅读产品文档（接口名，请求方式，参数列表）

2.根据产品的返回值和接收参数构建VO类

3.编写mapper层操作数据库

4.编写service层操作数据

5.编写controller层接收参数和返回数据

1.基于接口文档中的数据，编写VO对象

package com.huike.report.domain.vo;

/**
 * 漏斗图VO对象
 */
public class VulnerabilityMapVo {
    private Integer cluesNums;     //线索数
    private Integer effectiveCluesNums;  //有效线索数
    private Integer businessNums;  //商机数
    private Integer contractNums ;  //合同数

    getter/setter
}

2.先确定SQL语句再确定对应的Mapper

查询时间范围内的线索数

SELECT COUNT(DISTINCT id)
FROM `tb_clue`
WHERE create_time BETWEEN '2021-11-01' AND '2022-03-01'

查询有效线索数量

SELECT COUNT(DISTINCT id)
FROM `tb_clue`
WHERE `status` IN ('1', '2', '6', '7')
	AND create_time BETWEEN '2021-11-01' AND '2022-03-01'

查询转换的商机数量，需要在有效线索数量这个结果集内，寻找转换成商机的数量

SELECT COUNT(DISTINCT id)
FROM `tb_business`
WHERE clue_id IN (
	SELECT id
	FROM `tb_clue`
	WHERE `status` IN ('1', '2', '6', '7')
		AND create_time BETWEEN '2021-11-01' AND '2022-03-01'
)

寻找商机转换的合同数量，需要在转换成商机的结果集中，寻找转换成功的合同数量

SELECT COUNT(DISTINCT id)
FROM `tb_contract`
WHERE `business_id` IN (
	SELECT id
	FROM `tb_business`
	WHERE clue_id IN (
		SELECT id
		FROM `tb_clue`
		WHERE `status` IN ('1', '2', '6', '7')
			AND create_time BETWEEN '2021-11-01' AND '2022-03-01'
	)
)

3.基于sql语句编写xml文件

统计所有线索

TbClueMapper.xml

    <select id="countAllClues" resultType="int">
       select count(id) from tb_clue
        <where>
            <if test="beginCreateTime != null and beginCreateTime != ''"><!-- 开始创建时间 -->
                and date_format(create_time,'%y-%m-%d') >= date_format(#{beginCreateTime},'%y-%m-%d')
            </if>
            <if test="endCreateTime != null and endCreateTime != ''"><!--  -->
                and date_format(create_time,'%y-%m-%d') <= date_format(#{endCreateTime},'%y-%m-%d')
            </if>
        </where>
    </select>

统计所有有效线索

TbClueMapper.xml

    <select id="effectiveCluesNums" resultType="int">
        select count(id) from tb_clue where status in ('1','2','6','7')
            <if test="beginCreateTime != null and beginCreateTime != ''"><!-- 开始创建时间 -->
                and date_format(create_time,'%y-%m-%d') &gt;= date_format(#{beginCreateTime},'%y-%m-%d')
            </if>
            <if test="endCreateTime != null and endCreateTime != ''"><!--  -->
                and date_format(create_time,'%y-%m-%d') &lt;= date_format(#{endCreateTime},'%y-%m-%d')
            </if>
    </select>

统计从有效线索转化来的商机数

TbBusinessMapper.xml

 <select id="businessNumsFromClue"  resultType="int">
	SELECT COUNT(DISTINCT id)
    FROM `tb_business`
    WHERE clue_id IN (
        SELECT id
        FROM `tb_clue`
        WHERE `status` IN ('1', '2', '6', '7')
       <if test="beginCreateTime != null and beginCreateTime != ''">
                and date_format(create_time,'%y-%m-%d') &gt;= date_format(#{beginCreateTime},'%y-%m-%d')
       </if>
        <if test="endCreateTime != null and endCreateTime != ''"><!--  -->
                and date_format(create_time,'%y-%m-%d') &lt;= date_format(#{endCreateTime},'%y-%m-%d')
        </if>
      )
    </select>

统计从商机数转化来的合同

TbContractMapper.xml

    <!--合同统计-->
    <select id="contractNumsFromBusiness"
            resultType="int">
     SELECT COUNT(DISTINCT id)
      FROM `tb_contract`
      WHERE `business_id` IN (
          SELECT id
          FROM `tb_business`
          WHERE clue_id IN (
              SELECT id
              FROM `tb_clue`
              WHERE `status` IN ('1', '2', '6', '7')
            <if test="beginCreateTime != null and beginCreateTime != ''"><!-- 开始创建时间 -->
                and date_format(create_time,'%y-%m-%d') &gt;= date_format(#{beginCreateTime},'%y-%m-%d')
            </if>
            <if test="endCreateTime != null and endCreateTime != ''"><!--  -->
                and date_format(create_time,'%y-%m-%d') &lt;= date_format(#{endCreateTime},'%y-%m-%d')
            </if>
 		)
      )
    </select>

4.编写service

IReportService

 /**
     * 漏斗统计
     * @param beginCreateTime
     * @param endCreateTime
     * @return
     */
    public VulnerabilityMapVo getVulnerabilityMap(String beginCreateTime, String endCreateTime);

ReportServiceImpl

封装每一部分的属性

@Override
    public VulnerabilityMapVo getVulnerabilityMap(String beginCreateTime, String endCreateTime) {
        VulnerabilityMapVo vulnerabilityMapDTO =new VulnerabilityMapVo();
        //线索数
        vulnerabilityMapDTO.setCluesNums(clueMapper.countAllClues(beginCreateTime,endCreateTime));
        //有效线索数
        vulnerabilityMapDTO.setEffectiveCluesNums(clueMapper.effectiveCluesNums(beginCreateTime,endCreateTime));
        //商机数
        vulnerabilityMapDTO.setBusinessNums(businessMapper.businessNumsFromClue(beginCreateTime,endCreateTime));
        //合同数
        vulnerabilityMapDTO.setContractNums(contractMapper.contractNumsFromBusiness(beginCreateTime,endCreateTime));
        return vulnerabilityMapDTO;
    }

5.编写controller

ReportController

    /**
     * 漏斗图数据
     * @param beginCreateTime
     * @param endCreateTime
     * @return
     */
    @GetMapping("/getVulnerabilityMap/{beginCreateTime}/{endCreateTime}")
    public AjaxResult getVulnerabilityMap(@PathVariable String beginCreateTime, @PathVariable String endCreateTime){
        VulnerabilityMapVo vulnerabilityMapDTO= reportService.getVulnerabilityMap(beginCreateTime,endCreateTime);
        return AjaxResult.success(vulnerabilityMapDTO);
    }

PLUS:使用一条SQL语句完成所有的操作

避免了多次查询数据库

SELECT (
			SELECT COUNT(DISTINCT id)
			FROM `tb_clue`
			WHERE create_time BETWEEN '2019-05-05' AND '2022-05-05'
		) AS `cluesNums`
			 , (
			SELECT COUNT(DISTINCT id)
			FROM `tb_clue`
			WHERE `status` IN ('1', '2', '6', '7')
			  AND create_time BETWEEN '2019-05-05' AND '2022-05-05'
		) AS `effectiveCluesNums`
			 , (
			SELECT COUNT(DISTINCT id)
			FROM `tb_business`
			WHERE clue_id IN (
				SELECT id
				FROM `tb_clue`
				WHERE `status` IN ('1', '2', '6', '7')
				  AND create_time BETWEEN '2019-05-05' AND '2022-05-05'
			)
		) AS `businessNums`
			 , (
			SELECT COUNT(DISTINCT id)
			FROM `tb_contract`
			WHERE `business_id` IN (
				SELECT id
				FROM `tb_business`
				WHERE clue_id IN (
					SELECT id
					FROM `tb_clue`
					WHERE `status` IN ('1', '2', '6', '7')
					  AND create_time BETWEEN '2019-05-05' AND '2022-05-05'
				)
			)
		) AS `contractNums`

对应的xml文件

<select id="getVulnerabilityMap" resultType="VulnerabilityMapVo">
		SELECT (
			SELECT COUNT(DISTINCT id)
			FROM `tb_clue`
			WHERE create_time BETWEEN #{beginCreateTime} AND #{endCreateTime}
		) AS `cluesNums`
			 , (
			SELECT COUNT(DISTINCT id)
			FROM `tb_clue`
			WHERE `status` IN ('1', '2', '6', '7')
			  AND create_time BETWEEN #{beginCreateTime} AND #{endCreateTime}
		) AS `effectiveCluesNums`
			 , (
			SELECT COUNT(DISTINCT id)
			FROM `tb_business`
			WHERE clue_id IN (
				SELECT id
				FROM `tb_clue`
				WHERE `status` IN ('1', '2', '6', '7')
				  AND create_time BETWEEN #{beginCreateTime} AND #{endCreateTime}
			)
		) AS `businessNums`
			 , (
			SELECT COUNT(DISTINCT id)
			FROM `tb_contract`
			WHERE `business_id` IN (
				SELECT id
				FROM `tb_business`
				WHERE clue_id IN (
					SELECT id
					FROM `tb_clue`
					WHERE `status` IN ('1', '2', '6', '7')
					  AND create_time BETWEEN #{beginCreateTime} AND #{endCreateTime}
				)
			)
		) AS `contractNums`
	</select>

13-4-统计分析--线索统计--线索转化率漏斗图 （实现）

# 13-4-统计分析--线索统计--线索转化率漏斗图 （实现）

# 统计分析--线索统计--线索转化率漏斗图

# 1.基于接口文档中的数据，编写VO对象

# 2.先确定SQL语句再确定对应的Mapper

# 3.基于sql语句编写xml文件

# 4.编写service

# 5.编写controller

# PLUS:使用一条SQL语句完成所有的操作

13-4-统计分析--线索统计--线索转化率漏斗图（实现）

13-4-统计分析--线索统计--线索转化率漏斗图（实现）

统计分析--线索统计--线索转化率漏斗图

1.基于接口文档中的数据，编写VO对象

2.先确定SQL语句再确定对应的Mapper

3.基于sql语句编写xml文件

4.编写service

5.编写controller

PLUS:使用一条SQL语句完成所有的操作