sqlite 如何在使用GROUP BY时创建“其他”组？

m3eecexj 于 2023-06-06 发布在 SQLite

关注(0)|答案(2)|浏览(123)

摘要：我对数据进行分组、计数和排序。我想只保留第一个X线，并有其他积累作为一个线称为“其他”

CREATE TABLE cities (name TEXT);
INSERT INTO cities(name) VALUES ('paris'), ('paris'), ( 'london'), ( 'london'), ( 'london'), ( 'london'), ( 'rome'), ( 'madrid');

SELECT name, COUNT(name) AS count FROM cities
GROUP BY name
ORDER BY count DESC
LIMIT 2

上面的代码生成了最常见的两个城市（条目数量最多的城市）：

| name   | count |
| ------ | ----- |
| london | 4     |
| paris  | 2     |

我想要达到的是

| name   | count |
| ------ | ----- |
| london | 4     |
| paris  | 2     |
| other  | 2     |

其中所有剩余的行（在我的例子中是'rome'和'madrid'）都被分组在other下。
我不太懂SQL，但在伪代码中，我正在考虑类似于

SELECT name, COUNT(name) AS count FROM cities
GROUP BY name
ORDER BY count DESC
LIMIT 2 AS top_2
LIMIT 2+ AS other

这可能在一次通过中实现吗？作为一种变通方法，我将有相同的查询，但排序的其他方式轮，并限制为“行数-前2”（所以3个查询总数，我认为）。

（我很惊讶这之前没有被问过，但找不到匹配）*

sqlite

来源：https://stackoverflow.com/questions/76356990/how-to-create-an-other-group-when-using-group-by

2条答案

按热度按时间

agyaoht71#

一种选择是在row_number函数上使用带有case表达式的另一个聚合级别：

WITH T AS
(
  SELECT
     name, 
     COUNT(name) AS count,
     ROW_NUMBER() OVER (ORDER BY COUNT(name) DESC) AS rn 
  FROM cities
  GROUP BY name
  ORDER BY count DESC
)
SELECT 
   CASE 
     WHEN rn IN (1, 2) 
     THEN name 
     ELSE 'Others' 
   END AS name,
   SUM(count) AS count
FROM T
GROUP BY 1
ORDER BY count DESC

若要确保“Other”出现在结果集中的最后，您可以ORDER BY CASE WHEN rn IN (1, 2) THEN 1 ELSE 2 END, count DESC
Demo

赞(0）回复(0）举报 2023-06-06

ergxz8rk2#

使用公用表表达式：

WITH
    CTE_All (ct_all) AS
        (SELECT COUNT(name)
        FROM cities),
    CTE_Top (ct_name, ct_count) AS
        (SELECT TOP 2 name, COUNT(name)
        FROM cities
        GROUP BY name
        HAVING COUNT(name) > 1
        ORDER BY COUNT(name) DESC)

SELECT * FROM CTE_Top
UNION ALL
SELECT 'other', (SELECT ct_all FROM CTE_All) - SUM(ct_count)
    FROM CTE_Top;

赞(0）回复(0）举报 2023-06-06

我来回答

sqlite 如何在使用GROUP BY时创建“其他”组？

2条答案

相关问题

热门标签

最新问答