将SQL聚合函数与多个联接一起使用

Tanaki 发表于 Dev

田aki

我试图在单个SQL查询（使用Postgres）中的多个表中使用多个聚合函数。

我的表的结构类似于以下内容：

CREATE TABLE user (user_id INT PRIMARY KEY, user_date_created TIMESTAMP NOT NULL);
CREATE TABLE item_sold (item_sold_id INT PRIMARY KEY, sold_user_id INT NOT NULL);
CREATE TABLE item_bought (item_bought_id INT PRIMARY KEY, bought_user_id INT NOT NULL);

我想计算每个用户购买和出售的商品数量。我想到的解决方案不起作用：

SELECT user_id, COUNT(item_sold_id), COUNT(item_bought_id)
FROM user
LEFT JOIN item_sold ON sold_user_id=user_id
LEFT JOIN item_bought ON bought_user_id=user_id
WHERE user_date_created > '2014-01-01'
GROUP BY user_id;

这似乎执行了（item_sold_id，item_bought_id）的所有组合，例如，如果有4个已售商品和2个已购商品，则COUNT（）均为8。

如何正确查询表以获取两个计数？

戈登·利诺夫

您查询的简单解决方法是使用distinct：

SELECT user_id, COUNT(distinct item_sold_id), COUNT(distinct item_bought_id)
FROM user
LEFT JOIN item_sold ON sold_user_id=user_id
LEFT JOIN item_bought ON bought_user_id=user_id
WHERE user_date_created > '2014-01-01'
GROUP BY user_id;

但是，查询正在做不必要的工作。如果某人购买了100件商品并售出了200件商品，那么该联接将产生20,000个中间行。好多

解决方案是预先汇总结果或在中使用相关的子查询select。在这种情况下，我更喜欢相关的子查询解决方案（假设正确的索引可用）：

SELECT u.user_id,
       (select count(*) from item_sold s where u.user_id = s.sold_user_id),
       (select count(*) from item_bought b where u.user_id = b.bought_user_id)
FROM user u
WHERE u.user_date_created > '2014-01-01';

正确的索引是item_sold(sold_user_id)和item_bought(bought_user_id)。由于对user表进行了筛选，因此与预聚合相比，我更喜欢这样做。这仅针对今年创建的用户进行计算-使用预汇总更困难。

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

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