在SELECT中优化子查询

debugcn 发表于 Dev

bu步

我的表架构如下：

索引：

products.id主键
products.description唯一
experience.id主键
experience.product_id为product.id的外键

我的目标是加载

当月每种产品的成本（AS成本_十一月）
上个月每个产品的费用（AS费用_十月）
与上月相比的
当月成本变化（当月成本-上个月成本）（AS成本）
与上个月相比的当月成本变化百分比
（上月成本* 100 /当月成本）（AS percent_diff）

我设法编写了完全做到这一点的SQL：

SELECT description, (SUM(cost) - IFNULL(
(
    SELECT SUM(cost)
    FROM expenses
    WHERE month = 9 AND year = 2019 AND product_id = e.product_id
    GROUP BY product_id
), 0)) AS costs,

SUM(cost) * 100 / 
(
    SELECT SUM(cost)
    FROM expenses
    WHERE month = 9 AND year = 2019 AND product_id = e.product_id
    GROUP BY product_id
) AS percent_diff,

SUM(cost) AS costs_october,

IFNULL(
(
    SELECT SUM(cost)
    FROM expenses
    WHERE month = 9 AND year = 2019 AND product_id = e.product_id
    GROUP BY product_id
), 0) AS costs_september

FROM expenses e
JOIN products p ON (e.product_id = p.id)
WHERE month = 10 AND year = 2019
GROUP BY product_id
ORDER BY product_id;

但是，将同一子查询复制粘贴三遍确实是解决方案吗？从理论上讲，它要求每个产品运行四个查询。有没有更优雅的方式？

感谢任何帮助！

专线小巴

我将通过条件聚合解决此问题：

select 
    p.description,
    sum(case when e.month = 11 then e.cost else 0 end) costs_november,
    sum(case when e.month = 10 then e.cost else 0 end) costs_october,
    sum(case when e.month = 11 then e.cost else -1 * e.cost end) costs,
    sum(case when e.month = 10 then e.cost else 0 end)
        * 100
        / nullif(
            sum(case when e.month = 11 then e.cost else 0 end),
            0
        ) percent_diff
from expenses e
inner join products p on p.id = e.product_id
where e.year = 2019 and e.month in (10, 11)
goup by e.product_id

您可以通过使用子查询来避免重复相同的条件总和（您的RDBMS可能仍会对其进行优化，但这会使查询更具可读性）：

select 
    description,
    costs_november,
    costs_october,
    costs_november - costs_october costs,
    costs_october * 100 / nullif(costs_november, 0) percent_diff
from (
    select 
        p.description,
        sum(case when e.month = 11 then e.cost else 0 end) costs_november,
        sum(case when e.month = 10 then e.cost else 0 end) costs_october
    from expenses e
    inner join products p on p.id = e.product_id
    where e.year = 2019 and e.month in (10, 11)
    goup by e.product_id
) t

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。