SQL多表聚合查询：按产品统计近12个月月度总收入

核心思路 #

要实现需求，需通过表关联整合三张表的关键数据，再按「产品+月份」分组计算总收入：

1. 用order_table关联all_orders获取订单日期
2. 用order_table关联products_table获取产品名称和单价
3. 筛选近12个月的有效订单数据
4. 按产品和月份分组，计算每组的总收入（单价×销售数量的总和）

通用SQL查询示例（以MySQL为例） #

SELECT
    p.product_id,
    p.name AS product_name,
    DATE_FORMAT(o.order_date, '%Y-%m') AS month,
    SUM(p.price_usd * ot.amount) AS monthly_total_revenue
FROM
    order_table ot
JOIN
    all_orders o ON ot.order_id = o.order_id
JOIN
    products_table p ON ot.product_id = p.product_id
WHERE
    -- 筛选近12个月内的订单，排除未来日期
    o.order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH)
    AND o.order_date <= CURRENT_DATE()
GROUP BY
    p.product_id, p.name, DATE_FORMAT(o.order_date, '%Y-%m')
ORDER BY
    month DESC, monthly_total_revenue DESC;

不同数据库适配调整 #

• PostgreSQL：替换日期格式化和筛选逻辑，DATE_FORMAT(...)改为TO_CHAR(o.order_date, 'YYYY-MM')，DATE_SUB(...)改为CURRENT_DATE - INTERVAL '12 months'
• SQL Server：DATE_FORMAT(...)改为FORMAT(o.order_date, 'yyyy-MM')，DATE_SUB(...)改为DATEADD(MONTH, -12, GETDATE())

关键细节说明 #

• 分组必须包含product_id和name：避免重名产品数据混淆，保证产品唯一标识
• 用SUM(p.price_usd * ot.amount)计算总收入：order_table的amount是单订单内该产品的购买数量，乘单价后求和即为月度总销售额
• 新增o.order_date <= CURRENT_DATE()：规避测试数据中未来日期对结果的干扰

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