如何使用SQL实现已在Pandas中完成的分组计数与百分比计算

Got it, let's walk through how to replicate your Pandas grouping, counting, and percentage logic using SQL. The goal is to get the exact same output: counts of each (col_1, col_2) pair, plus the percentage each pair makes up within its col_1 group, rounded to one decimal place.

Modern SQL Solution (Window Functions) #

Most modern databases (MySQL 8+, PostgreSQL, SQL Server, BigQuery, etc.) support window functions, which make this task clean and efficient—this is the closest equivalent to your Pandas code's approach.

SELECT
    col_1,
    col_2,
    COUNT(*) AS count,
    ROUND((COUNT(*) * 100.0) / SUM(COUNT(*)) OVER (PARTITION BY col_1), 1) AS percentage
FROM example_data
GROUP BY col_1, col_2
ORDER BY col_1, col_2;

How this maps to your Pandas code: #

• COUNT(*) replaces groupby(["col_1", "col_2"]).size(): it counts rows for each (col_1, col_2) combination.
• SUM(COUNT(*)) OVER (PARTITION BY col_1) is the SQL equivalent of groupby("col_1")["count"].sum(): the window function OVER (PARTITION BY col_1) calculates the total number of rows per col_1 group, even as we're grouped by both columns.
• ROUND(..., 1) matches Pandas' round(1) to keep one decimal place in the percentage.
• ORDER BY col_1, col_2 replicates sort_values(by=["col_1", "col_2"]) to ensure the results are sorted the same way.

Legacy SQL Solution (No Window Functions) #

If you're working with an older database that doesn't support window functions (e.g., MySQL 5.x), you can use a subquery or CTE to precompute group totals, then join back to calculate percentages:

Using CTE (Common Table Expression) #

WITH col1_totals AS (
    SELECT col_1, COUNT(*) AS total_rows
    FROM example_data
    GROUP BY col_1
)
SELECT
    ed.col_1,
    ed.col_2,
    COUNT(*) AS count,
    ROUND((COUNT(*) * 100.0) / ct.total_rows, 1) AS percentage
FROM example_data ed
JOIN col1_totals ct ON ed.col_1 = ct.col_1
GROUP BY ed.col_1, ed.col_2, ct.total_rows
ORDER BY ed.col_1, ed.col_2;

Using Correlated Subquery #

Alternatively, you can calculate the total directly in the SELECT clause without a CTE:

SELECT
    col_1,
    col_2,
    COUNT(*) AS count,
    ROUND((COUNT(*) * 100.0) / (SELECT COUNT(*) FROM example_data WHERE col_1 = ed.col_1), 1) AS percentage
FROM example_data ed
GROUP BY col_1, col_2
ORDER BY col_1, col_2;

All three approaches will produce exactly the same output as your Pandas code:

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