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

背景介绍：

主要使用功能是年终了，需要多表进行查询，关联上查询统计出数据，然后进行数据的比值进行查询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 ;

查询结果如图所示：

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;

查询结果如下，

正常的比值查询出来：