如何计算剔除全天无库存天数的商品日均订单量？SQL查询问题

问题原因 #

你之前的两个写法核心问题如下：

1. 首个单表查询仅统计有订单的日期，有库存但无订单的日期未计入统计分母，导致计算出的均值偏高。
2. 第二个关联查询未先对两张表做日粒度聚合就直接关联，订单表单日多条记录和库存表单日多条记录产生笛卡尔积，最终求和结果被重复放大。

正确实现方案 #

处理思路是先分别将订单、库存表聚合到「商品+日期」的唯一粒度，再对齐最近5天的日期关联计算，避免重复统计和日期遗漏。以下是支持CTE的MySQL 8.0+ 示例代码：

SET @target_product_id = 2;
-- 取订单表中的最新日期作为统计结束日期
WITH max_date AS (
    SELECT MAX(Date) as latest FROM `Order`
),
-- 生成最近5天的连续日期序列，覆盖所有可能的统计日期
date_range AS (
    SELECT DATE_SUB(latest, INTERVAL 4 DAY) as dt FROM max_date
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM date_range WHERE dt < (SELECT latest FROM max_date)
),
-- 按天聚合库存数据，标记当天是否有有效库存（只要单日有一次库存>0就算有效）
daily_stock AS (
    SELECT 
        DATE(DateTime) AS dt,
        MAX(Qunatity) > 0 AS has_stock
    FROM ProductQuantity
    WHERE ProductId = @target_product_id
    GROUP BY DATE(DateTime)
),
-- 按天聚合订单数据，统计每日订单量
daily_orders AS (
    SELECT
        Date AS dt,
        COUNT(*) AS order_cnt
    FROM `Order`
    WHERE ProductId = @target_product_id
    GROUP BY Date
)
-- 关联计算最终的日均订单量
SELECT 
    SUM(COALESCE(do.order_cnt, 0)) / COUNT(*) AS avg_daily_orders
FROM date_range dr
LEFT JOIN daily_stock ds ON dr.dt = ds.dt
LEFT JOIN daily_orders do ON dr.dt = do.dt
-- 剔除全天无库存的日期
WHERE COALESCE(ds.has_stock, FALSE) = TRUE;

逻辑说明 #

• 连续日期序列避免了漏掉「有库存但无订单」的日期，保证统计范围是完整的最近5天
• 库存、订单表先单独按天聚合，保证每个日期只有一条记录，避免关联时产生笛卡尔积
• 最终仅统计有有效库存的日期，用这些天的总订单量除以有效天数，得到正确的日均订单量

如果使用不支持CTE的低版本数据库，可以将CTE拆为嵌套子查询，核心逻辑保持一致即可。

小提示：你给出的库存表中数量字段拼写为Qunatity，如果是手误拼写错误，请替换为实际的字段名Quantity。

内容的提问来源于stack exchange，提问作者daniil sidorov