Python从PostgreSQL按规则筛选员工工时数据的方案问询

To meet your requirements efficiently, we can construct a single PostgreSQL query using window functions and CTEs (Common Table Expressions) to filter exactly the records you need, then execute it once from Python. This avoids multiple database calls and unnecessary data pulls.

Step 1: SQL Query #

This query implements both of your rules:

1. Includes all records for per_ids with only one level.
2. For per_ids with multiple levels, fetches only the most recent consecutive block of records where the level matches the latest month's level (stopping when the level changes).

WITH per_latest AS (
    -- Get the level from the most recent month for each per_id
    SELECT per_id, level AS latest_level
    FROM table_name
    WHERE month_id = (SELECT MAX(month_id) FROM table_name WHERE per_id = table_name.per_id)
    GROUP BY per_id, level
),
per_level_count AS (
    -- Count distinct levels per per_id
    SELECT per_id, COUNT(DISTINCT level) AS level_count
    FROM table_name
    GROUP BY per_id
),
ranked_records AS (
    -- Calculate group numbers to identify consecutive latest-level records
    SELECT 
        t.per_id,
        t.month_id,
        t.area_grp,
        t.area_hrs,
        t.level,
        pl.latest_level,
        plc.level_count,
        -- Increment group number each time the level changes from the previous row (descending order)
        SUM(CASE WHEN t.level != LAG(t.level) OVER (PARTITION BY t.per_id ORDER BY t.month_id DESC) THEN 1 ELSE 0 END) OVER (PARTITION BY t.per_id ORDER BY t.month_id DESC) AS group_num
    FROM table_name t
    JOIN per_latest pl ON t.per_id = pl.per_id
    JOIN per_level_count plc ON t.per_id = plc.per_id
)
-- Final filter to get desired records
SELECT per_id, month_id, area_grp, area_hrs, level
FROM ranked_records
WHERE 
    level_count = 1 
    OR 
    (level_count > 1 AND group_num = 0 AND level = latest_level)
ORDER BY per_id DESC, month_id DESC; -- Matches your expected output order

Step 2: Python Integration #

Replace your loop with this code to execute the query once and fetch the results directly into a DataFrame:

import pandas as pd

# Assuming 'cnxn' is your existing PostgreSQL connection
sql_query = """
WITH per_latest AS (
    SELECT per_id, level AS latest_level
    FROM table_name
    WHERE month_id = (SELECT MAX(month_id) FROM table_name WHERE per_id = table_name.per_id)
    GROUP BY per_id, level
),
per_level_count AS (
    SELECT per_id, COUNT(DISTINCT level) AS level_count
    FROM table_name
    GROUP BY per_id
),
ranked_records AS (
    SELECT 
        t.per_id,
        t.month_id,
        t.area_grp,
        t.area_hrs,
        t.level,
        pl.latest_level,
        plc.level_count,
        SUM(CASE WHEN t.level != LAG(t.level) OVER (PARTITION BY t.per_id ORDER BY t.month_id DESC) THEN 1 ELSE 0 END) OVER (PARTITION BY t.per_id ORDER BY t.month_id DESC) AS group_num
    FROM table_name t
    JOIN per_latest pl ON t.per_id = pl.per_id
    JOIN per_level_count plc ON t.per_id = plc.per_id
)
SELECT per_id, month_id, area_grp, area_hrs, level
FROM ranked_records
WHERE 
    level_count = 1 
    OR 
    (level_count > 1 AND group_num = 0 AND level = latest_level)
ORDER BY per_id DESC, month_id DESC;
"""

# Fetch data into DataFrame
df_net = pd.read_sql(sql_query, cnxn)

Key Benefits #

• Efficiency: Only one database call instead of multiple, and we only pull the exact records you need (no extra rows to filter out in Python).
• Accuracy: The query strictly follows your rules, ensuring you get the correct records for both single-level and cross-level per_ids.
• Maintainability: All logic is encapsulated in the SQL query, making it easier to adjust if your rules change later.

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