将几个查询的结果相加，然后在 SQL 中找到前 5 个 [英] Sum results of a few queries and then find top 5 in SQL

问题描述

我有 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 个.我该怎么做?

I'd like to combine the event_ids of all 3 queries ordered by amount and then choose the top 5. How do I do that?

这是我为让它发生而做的:

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;

推荐答案

将所有三个查询的结果行UNION，然后选择amount最高的5行:

To UNION the resulting rows of all three queries and then pick the 5 rows with the highest amount:

(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 的括号内的子句.

To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT.

UNION ALL 保留重复项.

要为每个event_id添加计数:

To add the counts for every 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.

The tricky part here is that not every event_id will be present in all three base queries. So take care that a JOIN does not lose rows completely and additions don't turn out NULL.

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

Use UNION ALL, not UNION. You don't want to remove identical rows, you want to add them up.

x 是AS x 的表别名和简写.子查询必须具有名称.此处可以是任何其他名称.

x is a table alias and shorthand for AS x. It is required for for a subquery to have a name. Can be any other name here.

SOL 特性 FULL OUTER JOIN 在 MySQL 中没有实现(我上次检查过)，所以你必须使用 UNION.FULL OUTER JOIN 将连接所有三个基本查询而不会丢失行.

The SOL feature FULL OUTER JOIN is not implemented in MySQL (last time I checked), so you have to make do with UNION. FULL OUTER JOIN would join all three base queries without losing rows.

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;

或者，以多种方式使用基本计数:

Or, to use the base counts in multiple ways:

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;

这篇关于将几个查询的结果相加，然后在 SQL 中找到前 5 个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！