使用多个总和优化查询? [英] Optimizing query with multiple sums?

问题描述

我有表 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个总计:

For each product, I want to calculate 2 totals:

• 每个购物者的总数量
每个家庭的总数量.同一家庭中所有购物者的总数量.

• total quantity per shopper
• total quantity per family. Sum of total quantities for all shoppers in the same family.

决赛桌应该像这样:

+----------+----------+-------------------------+-----------------------+
|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;

但是从查询计划来看，它看起来效率很低(我认为).如何改善上面的查询?

But looking at the query plan, it looks very inefficient (I think). How can I improve the query above?

推荐答案

我认为家庭是购物者的层次结构.因此，我建议分组依据和窗口函数:

I think the family is a hierarchy for the shopper. So, I would suggest group by and window functions:

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

这篇关于使用多个总和优化查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！