如何识别Postgresql中排名前三的客户

Question

我正试图根据他们对dog_salon业务的访问次数来确定前三名客户(owner_id(。

有些主人有不止一只宠物，所以我想把他们所有宠物的访问次数加起来。前三名客户(owner_id(应根据访问次数按降序列出(使用别名num_visits(！我有四个表的所有者、所有者_2、交易和访问。

• owners有3列。owner_id、pet_id和size(owner_id在owners和owners_2表中不同(
• owners_2有3列。owner_id、pet_id和大小
• transactions有4列。transaction_id、date、pet_id和service
• visits有2列。pet_id和visits_count

这是我尝试的公式，但运气不好。

SELECT
owners.owner_id, owners_2.owner_id, owners.pet_id, owners_2.pet_id, 
COUNT(visits.visits_count) AS num_visits 
FROM 
owners 
INNER JOIN 
owners_2 ON owners.pet_id = owner_2.pet_id 
INNER JOIN 
visits ON visits.pet_id = owners_2.pet_id 
GROUP BY 
owners.owner_id, owners_2.owner_id, owners.pet_id, owners_2.pet_id 
ORDER BY 
visits.visits_count 
DESC LIMIT 3

Answer 1

如果我们的owners和owners_2只是包含不同所有者的两个所有者表，那么您可以在加入visits表之前将它们联合起来，如下面的所示

with all_owners as (
select owner_id, pet_id from owners union all
select owner_id, pet_id from owners_2)
select owners.owner_id, sum(visits.visits_count) AS num_visits
from all_owners owners INNER JOIN visits on visits.pet_id=owners.pet_id
group by owners.owner_id
ORDER BY 2 desc desc
limit 3