SQLite实现分组求和：取Top4分类金额并将剩余分类合并为“其他”行的查询方法

Here's a clean, efficient query that meets your requirements, using modern SQLite window functions (available in version 3.25.0+):

WITH category_totals AS (
    -- First, calculate total amount per category
    SELECT category, SUM(amount) AS total
    FROM transactions
    GROUP BY category
),
ranked_categories AS (
    -- Assign a rank to each category based on total amount (descending)
    SELECT category, total,
           RANK() OVER (ORDER BY total DESC) AS rnk
    FROM category_totals
)
-- Select top 4 categories, then add the 'others' row
SELECT category, total AS Amount, 0 AS sort_key
FROM ranked_categories
WHERE rnk <= 4
UNION ALL
SELECT 'others' AS category, SUM(total) AS Amount, 1 AS sort_key
FROM ranked_categories
WHERE rnk > 4
-- Sort to keep top 4 first (descending by amount), then 'others' at the end
ORDER BY sort_key, Amount DESC;

How This Works: #

1. category_totals CTE: This step groups all transactions by their category and calculates the total amount spent in each category. For your sample data, this gives us totals like mortgage:40, shopping:25, etc.
2. ranked_categories CTE: Using the RANK() window function, we assign a rank to each category based on its total amount (highest first). Crucially, RANK() ensures that categories with identical totals get the same rank—so both insurance and some category 2 (each with total 10) get rank 3, and both are included in the top 4.
3. Combining Results:
   • We select all categories with a rank ≤4 (our top 4 categories).
   • We union that with a single row labeled others, which sums the totals of all categories with a rank >4.
   • The sort_key column ensures that the top 4 categories appear first (sorted by amount descending), and the others row is always last—even if its total is higher than some entries in the top 4 (like in your example where others totals 13, which is more than 10).

For Older SQLite Versions (Pre-3.25.0): #

If you're stuck with an older version that doesn't support window functions, here's an alternative approach using subqueries:

WITH category_totals AS (
    SELECT category, SUM(amount) AS total
    FROM transactions
    GROUP BY category
)
SELECT category, total AS Amount
FROM category_totals
WHERE total IN (
    SELECT DISTINCT total
    FROM category_totals
    ORDER BY total DESC
    LIMIT 4
)
UNION ALL
SELECT 'others' AS category, SUM(total) AS Amount
FROM category_totals
WHERE total NOT IN (
    SELECT DISTINCT total
    FROM category_totals
    ORDER BY total DESC
    LIMIT 4
)
ORDER BY 
    CASE WHEN category = 'others' THEN 1 ELSE 0 END,
    Amount DESC;

Note that this alternative uses distinct totals to capture ties, but it's less flexible than the window function approach. The window function method is preferred for clarity and robustness.

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