MySQL 统计查询比值年终统计多表比值查询SQL，多表统计算出多个总数的比值

深藏阁楼爱情的钟 2023-01-03 14:20 176阅读 0赞

背景介绍：

主要使用功能是年终了，需要多表进行查询，关联上查询统计出数据，然后进行数据的比值进行查询SQL。

# 1、贡献率    单源公开量 / 总公开量 #

## 对应的SQL示例， ##

需要俩个表分别统计出来数据，然后进行关联出来获取这个比值。然后才可以达到效果！

SELECT
    	ds.ds_code,
    	ds.ds_source_name,
    	ds.ds_news_columns,
    	a.`单源公开量`,
    	b.`总公开量`,
    	CASE
    WHEN a.`单源公开量` / b.`总公开量` IS NULL THEN
    	1.0000
    ELSE
    	a.`单源公开量` / b.`总公开量`
    END AS '贡献率'
    FROM
    	(
    		SELECT
    			tnp.ds_code,
    			COUNT(1) AS '单源公开量'
    		FROM
    			tbm.tbm_news_processing tnp
    		WHERE
    			tnp.publish_status = 1
    		AND tnp.news_type = 1
    		AND tnp.ds_code IS NOT NULL
    		GROUP BY
    			tnp.ds_code
    	) AS a,
    	(
    		SELECT
    			tnp.ds_code,
    			COUNT(1) AS '总公开量'
    		FROM
    			tbm.tbm_news_processing tnp
    		WHERE
    			tnp.publish_status = 1
    		AND tnp.news_type = 1
    		AND tnp.ds_code IS NOT NULL
    	) AS b,
    	hw_business.hw_datasource ds
    WHERE
    	a.ds_code = ds.ds_code ;

## 查询结果如图所示： ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE0ODgwMDk_size_16_color_FFFFFF_t_70][]

# 2、转化率    公开量 / 入库量 #

## 对应的SQL， ##

难点在同一张表里面需要统计出俩个字段，然后进行比值数据计算出结果！

SELECT
    	ds.ds_code,
    	ds.ds_source_name,
    	ds.ds_news_columns,
    	a.`公开量`,
    	b.`入库量`,
     CASE
    WHEN a.`公开量` / b.`入库量` IS NULL THEN
    	1.0000
    ELSE
    	a.`公开量` / b.`入库量`
    END AS '转化率'
    FROM
    	(
    		SELECT
    			tnp.ds_code,
    			COUNT(1) AS '公开量'
    		FROM
    			tbm.tbm_news_processing tnp
    		WHERE
    			tnp.publish_status = 1
    		AND tnp.news_type = 1
    		AND tnp.ds_code IS NOT NULL
    		GROUP BY
    			tnp.ds_code
    	) AS a,
    	(
    		SELECT
    			tnp.ds_code,
    			COUNT(1) AS '入库量'
    		FROM
    			tbm.tbm_news_processing tnp
    		WHERE
    			tnp.news_type = 1
    		AND tnp.ds_code IS NOT NULL
    		GROUP BY
    			tnp.ds_code
    	) AS b,
    	hw_business.hw_datasource ds
    WHERE
    	a.ds_code = ds.ds_code
    AND b.ds_code = ds.ds_code;

## 查询结果如下， ##

正常的比值查询出来：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE0ODgwMDk_size_16_color_FFFFFF_t_70 1][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE0ODgwMDk_size_16_color_FFFFFF_t_70]: /images/20221119/76685933cdc34afebac55b146eace043.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE0ODgwMDk_size_16_color_FFFFFF_t_70 1]: /images/20221119/59268fce447046b788bf1799bda80f89.png