将几个查询的结果相加，然后在 SQL 中找到前 5 个

Question

我有 3 个查询：

table: pageview
SELECT event_id, count(*) AS pageviews 
FROM pageview 
GROUP BY event_id
ORDER BY pageviews DESC, rand()
LIMIT 1000

table: upvote
SELECT event_id, count(*) AS upvotes 
FROM upvote
GROUP BY event_id
ORDER BY upvotes DESC, rand()
LIMIT 1000

table: attending
SELECT event_id, count(*) AS attendants
FROM attending
GROUP BY event_id
ORDER BY attendants DESC, rand()
LIMIT 1000

我想合并按金额排序的所有 3 个查询的 event_id，然后选择前 5 个查询。我该如何操作？

编辑：这是我为实现这一目标所做的事情：

SELECT event_id, sum(amount) AS total
FROM (
(SELECT event_id, count(*) AS amount
FROM   pageview 
GROUP  BY event_id
ORDER  BY amount DESC, rand()
LIMIT  1000)

UNION ALL
(SELECT event_id, count(*) as amount
FROM   upvote
GROUP  BY event_id
ORDER  BY amount DESC, rand()
LIMIT  1000)

UNION ALL
(SELECT event_id, count(*) as amount
FROM   attending
GROUP  BY event_id
ORDER  BY amount DESC, rand()
LIMIT  1000)
) x
GROUP  BY 1
ORDER  BY  sum(amount) DESC
LIMIT  5;

Answer 1

对所有三个查询的结果行进行UNION，然后选择金额最高的 5 行：

(SELECT event_id, count(*) AS amount
FROM   pageview 
GROUP  BY event_id
ORDER  BY pageviews DESC, rand()
LIMIT  1000)

UNION ALL
(SELECT event_id, count(*)
FROM   upvote
GROUP  BY event_id
ORDER  BY upvotes DESC, rand()
LIMIT  1000)

UNION ALL
(SELECT event_id, count(*)
FROM   attending
GROUP  BY event_id
ORDER  BY attendants DESC, rand()
LIMIT  1000)

ORDER  BY 2 DESC
LIMIT  5;

手册：

要将 ORDER BY 或 LIMIT 应用于单个 SELECT，请将
将 SELECT 括起来的括号内的子句。

UNION ALL 以保留重复项。

---

添加每个 event_id 的计数：

SELECT event_id, sum(amount) AS total
FROM (
   (SELECT event_id, count(*) AS amount
    FROM   pageview 
    GROUP  BY event_id
    ORDER  BY pageviews DESC, rand()
    LIMIT  1000)
    
    UNION ALL
    (SELECT event_id, count(*)
    FROM   upvote
    GROUP  BY event_id
    ORDER  BY upvotes DESC, rand()
    LIMIT  1000)
    
    UNION ALL
    (SELECT event_id, count(*)
    FROM   attending
    GROUP  BY event_id
    ORDER  BY attendants DESC, rand()
    LIMIT  1000)
    ) x
GROUP  BY 1
ORDER  BY sum(amount) DESC
LIMIT  5;

这里棘手的部分是并非每个 event_id 都会出现在所有三个基本查询中。因此，请注意 JOIN 不会完全丢失行，并且添加不会导致 NULL。

使用UNION ALL，而不是UNION。您不想删除相同的行，而是想将它们相加。

x 是表别名和 AS x 的简写。子查询需要有一个名称。这里可以是任何其他名称。

MySQL 中未实现 SOL 功能 FULL OUTER JOIN（我上次检查过），因此您必须使用 UNION。 FULL OUTER JOIN 将连接所有三个基本查询而不会丢失行。

回答后续问题

SELECT event_id, sum(amount) AS total
FROM (
   (SELECT event_id, count(*) / 100 AS amount
    FROM   pageview ... )
    
    UNION ALL
    (SELECT event_id, count(*) * 5 
    FROM   upvote ... )
    
    UNION ALL
    (SELECT event_id, count(*) * 10
    FROM   attending ... )
    ) x
GROUP  BY 1
ORDER  BY  sum(amount) DESC
LIMIT  5;

或者，以多种方式使用基数：

SELECT event_id
      ,sum(CASE source
              WHEN 'p' THEN amount / 100
              WHEN 'u' THEN amount * 5
              WHEN 'a' THEN amount * 10
              ELSE 0
           END)  AS total
FROM (
   (SELECT event_id, 'p'::text AS source, count(*) AS amount
    FROM   pageview ... )
    
    UNION ALL
    (SELECT event_id, 'u'::text, count(*)
    FROM   upvote ... )
    
    UNION ALL
    (SELECT event_id, 'a'::text, count(*)
    FROM   attending ... )
    ) x
GROUP  BY 1
ORDER  BY 2 DESC
LIMIT  5;