求Pandas分组移位代码对应的标准SQL等价实现

Sure thing! Let's break down how to replicate your Pandas logic in standard SQL. Your code groups data by article_id and shifts the num_views column 1 to 4 positions to create lag1 through lag4 — this is a classic use case for SQL's LAG() window function, which lets you pull values from previous rows in the same group without messy self-joins.

Full Equivalent SQL Query #

SELECT
    article_id,
    section,
    time,
    num_views,
    comments,
    -- Pull num_views from the immediately preceding row in the same article group
    LAG(num_views, 1) OVER (PARTITION BY article_id ORDER BY time) AS lag1,
    -- Pull num_views from 2 rows back
    LAG(num_views, 2) OVER (PARTITION BY article_id ORDER BY time) AS lag2,
    -- Pull num_views from 3 rows back
    LAG(num_views, 3) OVER (PARTITION BY article_id ORDER BY time) AS lag3,
    -- Pull num_views from 4 rows back
    LAG(num_views, 4) OVER (PARTITION BY article_id ORDER BY time) AS lag4
FROM
    your_table_name; -- Replace this with your actual table name

Important Notes to Match Pandas Behavior #

• PARTITION BY article_id: This is the SQL equivalent of Pandas' groupby('article_id') — it splits your dataset into groups where every row in a group shares the same article_id.
• ORDER BY time: Don't skip this! Pandas uses the existing row order for shift(), but SQL doesn't guarantee any inherent row order. Since your sample data is ordered by time per article, sorting by time within each partition ensures we get the exact same lag values as your Pandas code.
• Handling Missing Values: Just like Pandas returns NaN when there aren't enough prior rows in a group, SQL will return NULL for lag1-lag4 when there's no earlier row to pull from (e.g., the first 1-4 rows of an article group).

Quick Example with Your Sample Data #

For the nnn678www article group:

• Row 5 (time 05:00): All lag1-lag4 are NULL (no prior rows)
• Row 6 (time 06:00): lag1 = 39 (from row 5), others NULL
• Row 7 (time 07:00): lag1 = 38 (row6), lag2 = 39 (row5), others NULL
• Row 8 (time 08:00): lag1 = 66 (row7), lag2 = 38 (row6), lag3 = 39 (row5), lag4 = NULL
• Row 9 (time 09:00): lag1 = 65 (row8), lag2 = 66 (row7), lag3 = 38 (row6), lag4 = 39 (row5)

This matches exactly what your Pandas code would output.

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