SQLite查询需求：将计数小于100的分组合并为'others'行

一、SQLite 实现方式 #

方法1：CASE WHEN 二次分组 #

先对JuridicalForm分组统计数量，再通过CASE WHEN将计数不足100的分组统一标记为others，最后再次分组求和：

SELECT
    CASE
        WHEN cnt >= 100 THEN JuridicalForm
        ELSE 'others'
    END AS JuridicalForm,
    SUM(cnt) AS count
FROM (
    SELECT JuridicalForm, COUNT(*) AS cnt
    FROM enterprise
    GROUP BY JuridicalForm
) AS sub
GROUP BY CASE
    WHEN cnt >= 100 THEN JuridicalForm
    ELSE 'others'
END;

方法2：UNION 合并结果 #

分别查询计数≥100的分组，以及所有计数<100的分组的总和，再用UNION ALL合并：

-- 获取计数≥100的分组
SELECT JuridicalForm, COUNT(*) AS count
FROM enterprise
GROUP BY JuridicalForm
HAVING COUNT(*) >= 100

UNION ALL

-- 统计所有计数<100的分组总和，命名为others
SELECT 'others' AS JuridicalForm, SUM(cnt) AS count
FROM (
    SELECT COUNT(*) AS cnt
    FROM enterprise
    GROUP BY JuridicalForm
    HAVING cnt < 100
) AS sub;

二、Pandas 实现方式 #

如果SQL调试有障碍，可以用Pandas完成后续合并：

import pandas as pd
import sqlite3

# 连接数据库并读取数据
conn = sqlite3.connect('your_database.db')
df = pd.read_sql_query("SELECT JuridicalForm FROM enterprise", conn)
conn.close()

# 分组计数
count_df = df['JuridicalForm'].value_counts().reset_index()
count_df.columns = ['JuridicalForm', 'count']

# 分离主分组和其他分组
main_groups = count_df[count_df['count'] >= 100]
other_group = pd.DataFrame({
    'JuridicalForm': ['others'],
    'count': [count_df[count_df['count'] < 100]['count'].sum()]
})

# 合并最终结果
final_df = pd.concat([main_groups, other_group], ignore_index=True)
print(final_df)

内容的提问来源于stack exchange，提问作者Roku