使用多个总和优化查询？

admin

使用多个总和优化查询？

sql

我有表products：

+----------+-----------+----------+---------+
|family_id |shopper_id |product_id|quantity |
+----------+-----------+----------+---------+
|A         |1          |Kit Kat   |10       |
|A         |1          |Kit Kat   |5        |
|A         |1          |Snickers  |9        |
|A         |2          |Kit Kat   |7        |
|B         |3          |Kit Kat   |2        |
+----------+---------- +----------+---------+

对于每个产品，我要计算2个总计：

每个购物者的总数量
每个家庭的总数量。同一家庭中所有购物者的总数量之和。

最终表应如下所示：

+----------+----------+-------------------------+-----------------------+
|shopper_id|product_id|total_quantity_shopper   |total_quantity_family  |
+----------+----------+-------------------------+-----------------------+
|1         |Kit Kat   | 15                      | 22                    |
|1         |Snickers  | 9                       | 9                     |
|2         |Kit Kat   | 7                       | 22                    |
|3         |Kit Kat   | 2                       | 2                     |
+----------+----------+-------------------------|-----------------------|

这是我的查询：

SELECT
    distinct shopper_id,
    product_id,
    sum(quantity) OVER (PARTITION BY shopper_id, product_id) as total_quantity_shopper,
    sum(quantity) OVER (PARTITION BY family_id, product_id) as total_quantity_family
FROM
    products;

但是从查询计划来看，它看起来效率很低（我认为）。如何改善上面的查询？

阅读 160

2021-07-01

共1个答案

admin

我认为家庭是购物者的等级制度。因此，我建议使用group by和窗口函数：

select family_id, shopper_id, product_id,
       sum(quantity) as total_quantity_shopper,
       sum(sum(quantity)) over (partition by family_id, product_id) as total_quantity_family
from products
group by family_id, shopper_id, product_id

2021-07-01