计算值在不同列中显示的次数

I have a table of transactions, with fields for buyer and seller. I want a query that outputs a row for each person who has engaged in a transaction, with one column for how many times that person has been a buyer and one for how many times that person has been a seller.

我有一张交易表,包括买方和卖方的字段。我想要一个查询,为每个参与交易的人输出一行,其中一列表示该人已成为买方的次数,另一列表示该人已成为卖家的次数。

I'm currently achieving the results I want with:

我目前正在实现我想要的结果:

SELECT u.user_id,
     (SELECT COUNT(*) FROM transactions AS b WHERE b.buyer = u.user_id),
     (SELECT COUNT(*) FROM transactions AS s WHERE s.seller = u.user_id)
FROM users AS u;

This works fine, but it strikes me as goofy and unnecessarily slow. And when I start wanting to put conditions -- like, if I want to count how many times a person bought or sold a pencil for less than a dollar between March 1 and March 31 -- then the query gets even goofier.

这样可以正常工作,但它让我感到愚蠢和不必要的缓慢。当我开始想要设置条件时 - 例如,如果我想计算一个人在3月1日到3月31日期间以不到1美元的价格购买或卖掉一支铅笔的次数 - 那么查询就变得更加愚蠢了。

I'm sure there's a better way, but I've spent a while searching and haven't come up with much. Thanks in advance for your help.

我确信有更好的方法,但我花了一段时间寻找并且没有提出太多。在此先感谢您的帮助。

1 个解决方案

#1

Your query is quite reasonable and quite possibly the fastest way to run this query. You want to be sure that you have two indexes on transactions: transactions(buyer) and transactions(seller).

您的查询非常合理,可能是运行此查询的最快方法。您希望确保在交易中有两个索引:交易(买方)和交易(卖方)。

An alternative method would summarize the data before using explicit joins:

另一种方法是在使用显式连接之前汇总数据:

select u.*, b.numbuyer, s.numseller
from users u left join
     (select buyer, count(*) as numbuyer
      from transactions
      group by buyer
     ) b
     on  b.buyer = u.user_id left join
     (select seller, count(*) as numseller
      from transactions
      group by seller
     ) s
     on s.buyer = u.user_id;

However, your query might be the best way to express and run this logic.

但是,您的查询可能是表达和运行此逻辑的最佳方式。

#1