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