求PostgreSQL中等价于pandas groupby('v1').apply(lambda x:x['v2'].nunique())的SQL语句

Hey there! Let's translate that pandas groupby + nunique logic straight into PostgreSQL—it's actually pretty straightforward once you map the pandas operations to standard SQL clauses.

Core Equivalent Query #

Assuming your source data lives in a PostgreSQL table named your_table, here's the exact equivalent SQL:

SELECT v1, COUNT(DISTINCT v2) AS v2_unique_count
FROM your_table
GROUP BY v1;

How this matches your pandas code: #

• GROUP BY v1: This does exactly what df.groupby('v1') does in pandas—it groups all rows together based on the unique values in the v1 column.
• COUNT(DISTINCT v2): This is the SQL counterpart to x['v2'].nunique(). It counts only the distinct (unique) values of v2 within each v1 group, ignoring duplicate entries entirely.

Bonus: Adding Pre-Group Filters #

If you’re filtering your pandas DataFrame before grouping (like df[df['status'] = 'active'].groupby(...)), you can replicate that with a WHERE clause in SQL:

SELECT v1, COUNT(DISTINCT v2) AS v2_unique_count
FROM your_table
WHERE status = 'active'  -- Match your pandas filter condition here
GROUP BY v1;

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