首页 文章

使用具有多个联接的SQL聚合函数

提问于
浏览 次
3

我试图在单个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 .

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

sql postgresql join left-join aggregate-functions

3 回答

  • 5

    您查询的简单方法是使用 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 表上的过滤，我更喜欢这种预聚合 . 这仅对今年创建的用户进行计算 - 这对于预聚合来说更难 .

    回复于
  • 2

    SQL Fiddle

    通过横向连接，可以预先仅聚合已过滤的用户

    select user_id, total_item_sold, total_item_bought
from
    "user" u
    left join lateral (
        select sold_user_id, count(*) as total_item_sold
        from item_sold
        where sold_user_id = u.user_id
        group by sold_user_id
    ) item_sold on user_id = sold_user_id
    left join lateral (
        select bought_user_id, count(*) as total_item_bought
        from item_bought
        where bought_user_id = u.user_id
        group by bought_user_id
    ) item_bought on user_id = bought_user_id
where u.user_date_created >= '2014-01-01'

    请注意，您需要在过滤器中使用 >= ，否则可能会错过一年中的确切第一时刻 . 尽管自然输入的数据不太可能存在时间戳，但通常是自动作业 .

    回复于
  • 1

    解决此问题的另一种方法是使用两个嵌套选择 .

    select user_id,
       (select count(*) from item_sold where sold_user_id = user_id),
       (select count(*) from item_bought where bought_user_id = user_id)
from user
where user_date_created > '2014-01-01'
    回复于

相关问题