能否简化统计厂商产品占比及日均产量的MySQL查询语句？

优化后的查询语句（MySQL 8.0+） #

WITH date_range_stats AS (
    SELECT
        COUNT(*) AS total_products,
        COUNT(DISTINCT product_date) AS total_days
    FROM products
    WHERE product_date BETWEEN '2017-03-28' AND '2017-03-30'
)
SELECT
    COALESCE(p.producer_name, 'Other') AS producer,
    CONCAT(ROUND(COUNT(pr.product_id) * 100 / drs.total_products), '%') AS '% share',
    ROUND(COUNT(pr.product_id) / drs.total_days) AS 'day avg'
FROM products pr
LEFT JOIN producers p ON pr.producer_id = p.producer_id
CROSS JOIN date_range_stats drs
WHERE pr.product_date BETWEEN '2017-03-28' AND '2017-03-30'
GROUP BY COALESCE(p.producer_name, 'Other'), drs.total_products, drs.total_days
ORDER BY '% share' DESC;

兼容MySQL 5.x版本的方案 #

如果你的MySQL版本不支持CTE，可用子查询替代：

SELECT
    COALESCE(p.producer_name, 'Other') AS producer,
    CONCAT(ROUND(COUNT(pr.product_id) * 100 / drs.total_products), '%') AS '% share',
    ROUND(COUNT(pr.product_id) / drs.total_days) AS 'day avg'
FROM products pr
LEFT JOIN producers p ON pr.producer_id = p.producer_id
CROSS JOIN (
    SELECT
        COUNT(*) AS total_products,
        COUNT(DISTINCT product_date) AS total_days
    FROM products
    WHERE product_date BETWEEN '2017-03-28' AND '2017-03-30'
) drs
WHERE pr.product_date BETWEEN '2017-03-28' AND '2017-03-30'
GROUP BY COALESCE(p.producer_name, 'Other'), drs.total_products, drs.total_days
ORDER BY '% share' DESC;

优化说明 #

1. 预计算全局统计量：一次性算出指定日期范围内的总产品数和总天数，避免原查询中重复执行相同子查询，降低资源消耗。
2. 合理关联表：用LEFT JOIN关联两张表，自动将producer_id为NULL的产品归类为「Other」，无需额外UNION拼接分组。
3. 修正NULL判断逻辑：原查询中producer_id = 'null'是错误写法（INT类型的NULL不能用字符串匹配），优化后的查询通过LEFT JOIN天然处理该场景。
4. 简化冗余代码：全局统计量复用，分组逻辑统一，避免重复编写计算逻辑。

原查询的问题补充 #

• 原查询中FROM products, producers会产生笛卡尔积，逻辑冗余且影响性能；
• 重复执行多次相同子查询，浪费数据库资源；
• producer_id = 'null'的判断错误，会导致NULL产品的统计结果不准确。

内容的提问来源于stack exchange，提问作者Konrad Jurkiewicz