总结一些查询的结果，然后在SQL中找到前5名 [英] Sum results of a few queries and then find top 5 in SQL

问题描述

我有3个查询：

 表：pageview 
 SELECT event_id，count（*）AS综合浏览量
 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服务员
参加
 GROUP BY event_id 
 ORDER BY服务人员DESC，rand（）
 LIMIT 1000 
  

我想结合按金额排序的所有3个查询中的 event_id s，然后选择前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 
参加
 GROUP BY event_id 
 ORDER BY amount DESC，rand（ ）
 LIMIT 1000）
）x 
 GROUP BY 1 
 ORDER BY sum（amount）DESC 
 LIMIT 5; 
  

解决方案

这个问题留下了解释的空间。至 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（*）
参加
 GROUP BY event_id 
 ORDER BY attendants DESC，rand （）
极限1000）
 
 2的顺序降序
极限5; 
  

手册： $ b

应用 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（*）
参加
 GROUP BY event_id 
 ORDER BY attendants DESC，rand（）
 LIMIT 1000）
）x 
 GROUP BY 1 
 ORDER BY金额（金额）DESC 
限额5; 
  

这里棘手的部分是，并非每个event_id都会出现在所有三个基本查询中。所以你必须注意一个JOIN不会完全丢失行，并且添加不会产生 NULL 。

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

x AS x - 表别名。对于子查询来说，它是必需的。

SOL特性 FULL OUTER JOIN 未在MySQL中实现（上次我看了），所以你必须与UNION合作。 FULL OUTER JOIN 会加入所有三个基本查询而不会丢失行。

  SELECT event_id，sum（amount）AS total 
 FROM（
（SELECT event_id，count（*）/ 100 AS 
 FROM pageview ...）
 
 UNION ALL 
（SELECT event_id，count（*）* 5 
 FROM upvote ...）
 
 UNION ALL 
（SELECT event_id，count（*）* 10 
参加......）
）x 
 GROUP BY 1 
 ORDER BY sum（金额）DESC 
限额5; 
  

或者，如果您想以多种方式使用基本计数：

  SELECT event_id 
，sum（CASE源
当'p'THEN金额/ 100 
当'你'那么金额* 5 
当'a'金额* 10 
 ELSE 0 
 END）总计
 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（*）
参加...）
） x 
 GROUP BY 1 
 ORDER BY 2 DESC 
 LIMIT 5; 
  

I have 3 queries:

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

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?

EDIT: HERE IS WHAT I DID TO MAKE IT HAPPEN:

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;

解决方案

The question leaves room for interpretation. To UNION the resulting rows of all three queries and then pick the 5 rows with the highest "amounts":

(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;

The manual:

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

UNION ALL, so duplicates are not removed.

---

If you want to add the counts for every event_id, this query should do it:

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;

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

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

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

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

Answer to follow up question

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, if you want 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屋！