Mysql 数据统计 百分比计算 2022-12-07

-- 统计
sum(CASE WHEN `state`=2 THEN 1 ELSE 0 END ) 
count(`state` = 2 or null)
count((`state` = 2 and `is_ok` = 1) or null)
count(`other_state` IN(1,3,4) or null)
count((`other_state` >= 1 and `other_state`<=3) or null)
-- 计算百分比
concat(round(count((`state` = 2 and `is_ok` = 1) or null) / count(`state` = 2 or null) *100 ,2),'%') 

• 例子：

-- 根据业务名称计算当前业务的 质检量，合格量，合格率，复义量，复义率，复核量，复核率
SELECT 
`name` as 业务名称,
concat(*) as 总量,
-- 统计数据量
-- sum(CASE WHEN `state`=2 THEN 1 ELSE 0 END ) as 质检量，
count(`state` = 2 or null) as 质检量,  
count((`state` = 2 and `is_ok` = 1) or null)  as 合格量,
-- 计算百分比
concat(round(count((`state` = 2 and `is_ok` = 1) or null) / count(`state` = 2 or null) *100 ,2),'%') as 合格率,

count(`is_people_checked` = 1 or null) as 复检量,

-- count(`other_state` IN(1,3,4) or null) as 复议量,
count((`other_state` >= 1 and `other_state`<=3) or null) as 复议量,

count((`other_state` >= 2 and `other_state`<=3) or null) as 复核量,

-- 三元判断如果相除>0 再去计算百分比，如果小于或者不等0 则默认 0%
IF(count((`other_state` >= 2 and `other_state`<=3) or null) / count((`other_state` >= 1 and `other_state`<=3) or null) > 0,
-- 计算百分比
concat(round(count((`other_state` >= 2 and `other_state`<=3) or null) / count((`other_state` >= 1 and `other_state`<=3) or null) *100 ,2),'%'),
concat(0,'%')) as 复核率

FROM set_audios_copy1

GROUP BY `name`;

image.png

• 例子2：多表查询统计

SELECT
    a.`name` AS 配送员,
    b.`配送区县` AS `配送区`,
    DATE_FORMAT( b.`下单时间`, '%Y-%m' ) AS `日期`,
    b.`剔除标识` AS `剔标总量`,
    IFNULL( b.`剔除总5%`, 0 ) AS `剔标总量5%`,
    IFNULL( b.`成功关闭`, 0 ) AS `成功关闭`,
    IFNULL( b.`激活率`, concat( 0, '%' ) ) AS `激活率`,
    IFNULL( b.`充值>=50`, 0 ) AS `充值>=50`,
    IFNULL( b.`充值>=50率`, concat( 0, '%' ) ) AS `充值>=50率`,
    IFNULL( b.`充值>=100`, 0 ) AS `充值>=100`,
    IFNULL( b.`充值>=100率`, concat( 0, '%' ) ) AS `充值>=100率`,
    IFNULL( c.`携转`, 0 ) AS `携转`,
    IFNULL( c.`携转>=50`, 0 ) AS `携转>=50`,
    IFNULL( c.`携转>=50率`, concat( 0, '%' ) ) AS `携转>=50率`,
    IFNULL(
        concat(
            ROUND(
                ( b.`剔除标识` + IFNULL( c.`携转>=50`, 0 ) * 3 ) / IFNULL( b.`剔除总5%`, 0 ) * 100,
                2 
            ),
            '%' 
        ),
        concat( 0, '%' ) 
    ) AS `综合转化率`,
    IFNULL( d.`宽带`, 0 ) AS `宽带`,
    IFNULL( e.`PULS分母`, 0 ) AS `PULS分母`,
    IFNULL( f.`云PULS`, 0 ) AS `云PULS`,
    IFNULL(
        concat(
            round( IFNULL( f.`云PULS`, 0 ) / IFNULL( e.`PULS分母`, 0 ) * 100, 2 ),
            '%' 
        ),
        concat( 0, '%' ) 
    ) AS `PLUS占比50%`,
    IFNULL( g.`副卡`, 0 ) AS `副卡`,
    IFNULL(
        concat(
            round( IFNULL( g.`副卡`, 0 ) / IFNULL( e.`PULS分母`, 0 ) * 100, 2 ),
            '%' 
        ),
        concat( 0, '%' ) 
    ) AS `副卡率`,
    IFNULL( e.`已登`, 0 ) + IFNULL( e.`未登`, 0 ) AS `手厅分母`,
    IFNULL( e.`已登`, 0 ) AS `手厅分子`,
    IFNULL(
        concat(
            round(
                IFNULL( e.`已登`, 0 ) / ( IFNULL( e.`已登`, 0 ) + IFNULL( e.`未登`, 0 ) ) * 100,
                2 
            ),
            '%' 
        ),
        concat( 0, '%' ) 
    ) AS `手厅搭载率70%`,
    IFNULL( e.`5G分母`, 0 ) AS `5G分母`,
    IFNULL( e.`5G分子`, 0 ) AS `5G分子`,
    IFNULL(
        concat(
            round(
                IFNULL( e.`5G分子`, 0 ) / ( IFNULL( e.`5G分母`, 0 ) + IFNULL( e.`未登`, 0 ) ) * 100,
                2 
            ),
            '%' 
        ),
        concat( 0, '%' ) 
    ) AS `5G渗透率80%` 
FROM
    `dd_user` AS a
    LEFT JOIN (
    SELECT
        `配送区县`,
        `属网格ID`,
        `下单时间`,
        SUM( `剔除标识` = '' ) AS `剔除标识`,
        ROUND( SUM( `剔除标识` = '' ) - ( SUM( `剔除标识` = '' ) * 0.05 ) ) AS `剔除总5%`,
        SUM( `订单状态` = '成功关闭' ) AS `成功关闭`,
        concat( ROUND( SUM( `订单状态` = '成功关闭' ) / SUM( `剔除标识` = '' ) * 100, 2 ), '%' ) AS `激活率`,
        SUM( `累计充值金额` >= 50 ) AS `充值>=50`,
        concat( ROUND( SUM( `累计充值金额` >= 50 ) / SUM( `订单状态` = '成功关闭' ) * 100, 2 ), '%' ) AS `充值>=50率`,
        SUM( `累计充值金额` >= 100 ) AS `充值>=100`,
        concat( ROUND( SUM( `累计充值金额` >= 100 ) / SUM( `订单状态` = '成功关闭' ) * 100, 2 ), '%' ) AS `充值>=100率` 
    FROM
        `dd_综转明细` 
    WHERE
        DATE_FORMAT( `下单时间`, '%Y-%m' ) = '2023-02' 
    GROUP BY
        `属网格ID` 
    ) AS b ON a.`name` = b.`属网格ID`
    LEFT JOIN (
    SELECT
        SUM( `累计充值金额` >= 0 ) AS `携转`,
        SUM( `累计充值金额` >= 50 ) AS `携转>=50`,
        concat( round( SUM( `累计充值金额` >= 50 ) / SUM( `累计充值金额` >= 0 ) * 100, 2 ), '%' ) AS `携转>=50率`,
        `姓名` 
    FROM
        `dd_新融合携入` 
    WHERE
        DATE_FORMAT( `激活时间`, '%Y-%m' ) = '2023-02' 
    GROUP BY
        `姓名` 
    ) AS c ON a.`name` = c.`姓名`
    LEFT JOIN ( SELECT COUNT( `NAME` ) AS `宽带`, `NAME` FROM `dd_宽带明细` GROUP BY `NAME` ) AS d ON a.`name` = d.`NAME`
    LEFT JOIN (
    SELECT
        SUM( `订单状态` = '成功关闭' ) AS `PULS分母`,
        SUM( `手厅登录` = '已登' ) AS `已登`,
        SUM( `手厅登录` = '未登' ) AS `未登`,
        SUM( `5G叠加` <> '非目标用户' ) AS `5G分母`,
        SUM( `5G叠加` <> '非目标用户' AND `5G叠加` <> '未叠加' ) AS `5G分子`,
        `姓名` 
    FROM
        `dd_激活明细` 
    WHERE
        DATE_FORMAT( `激活时间`, '%Y-%m' ) = '2023-02' 
    GROUP BY
        `姓名` 
    ) AS e ON a.`name` = e.`姓名`
    LEFT JOIN ( SELECT COUNT( `配送员` ) AS `云PULS`, `配送员` FROM `dd_云plus` GROUP BY `配送员` ) AS f ON a.`name` = f.`配送员`
    LEFT JOIN ( SELECT SUM( `订单状态` = '成功关闭' ) AS `副卡`, `名称` FROM `dd_二次营销` WHERE DATE_FORMAT( `激活时间`, '%Y-%m' ) = '2023-02' GROUP BY `名称` ) AS g ON a.`name` = g.`名称` 
WHERE
    a.`delete_id` = 0 
    AND a.`terminal` = '手机端' 
    AND b.`配送区县` <> 'null' 
GROUP BY
    a.`name`

image.png