sql - 如何在 SQL 中获取 "top X with the rest"？

Question

假设我有一个包含“名称”和“数量”列的表格，我想获取最常出现的前 X 个产品，以及另一行，这是所有其他产品的总和。

我们可以以这个示例 SQL 脚本为例:

CREATE TEMPORARY TABLE product
(
    name TEXT,
    quantity INT
);

INSERT INTO product (name, quantity)
VALUES
    ('carrot', 5),
    ('tomato', 1),
    ('potato', 3),
    ('grape', 8),
    ('salad', 10);

从这个样本集中，我想检索这种结果:

name    quantity
----------------
salad   10
grape   8
others  9

我目前正在使用以下解决方案，但我想知道是否有更漂亮和/或更高效的解决方案:

WITH top AS (
      SELECT name, 
             quantity
        FROM product
    ORDER BY quantity DESC
    LIMIT 2),
     without AS (
      SELECT 'others' AS other, 
             sum(product.quantity) AS quantity
        FROM product
       WHERE product.name NOT IN (SELECT name FROM top)
    GROUP BY other)
SELECT name, quantity
  FROM (SELECT name, quantity
          FROM top
        UNION
        SELECT other, quantity
          FROM without) AS t
ORDER BY quantity DESC;

Answer 1

使用:

WITH summary AS (
    SELECT p.*,
           ROW_NUMBER() OVER (ORDER BY p.quantity DESC) AS rank
      FROM PRODUCT p)
  SELECT s.name, 
         s.quantity, 
         s.rank
    FROM summary s
   WHERE s.rank <= 2
UNION ALL
  SELECT 'other', 
         SUM(t.quantity), 
         3
    FROM summary t
   WHERE t.rank > 2
ORDER BY rank, quantity DESC

关于联合

如果您需要删除重复项，请使用 UNION。否则，使用更快的替代方法 UNION ALL，将查询与 SELECT 子句中的位置匹配的数据类型结合起来，而不删除重复项(并且速度更快)。

如果不需要，不要使用派生表/内联 View

这个:

SELECT name, quantity
 FROM (SELECT name, quantity
         FROM top
       UNION
      SELECT other, quantity
        FROM without) AS t
ORDER BY quantity DESC;

..只需要:

SELECT name, quantity
  FROM top
UNION
SELECT other, quantity
  FROM without
ORDER BY quantity DESC