使用多个和优化查询

Question

我有表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;

但从查询计划来看，它看起来效率很低(我认为(。如何改进上述查询？

Answer 1

我认为家庭是购物者的一个层次。因此，我建议使用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