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

我试图在单个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表进行了过滤，因此与预聚合相比，我更喜欢这样做。这仅针对今年创建的用户进行计算-使用预汇总更困难。