Hey there! Let's adjust your existing query to add the month_id_prev field that shows the latest month from the previous id_type group. Here's a modified version that does exactly what you need:

WITH grouped_records AS (
  SELECT
    *,
    -- Create a group identifier for consecutive same id_type values
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY month_id) - 
    ROW_NUMBER() OVER (PARTITION BY id, id_type ORDER BY month_id) AS group_id,
    -- Calculate the max month_id for each consecutive id_type group
    MAX(month_id) OVER (PARTITION BY id, id_type, 
      ROW_NUMBER() OVER (PARTITION BY id ORDER BY month_id) - 
      ROW_NUMBER() OVER (PARTITION BY id, id_type ORDER BY month_id)
    ) AS group_max_month
  FROM `my_table`
  WHERE id = 123
),
prev_group_details AS (
  SELECT
    id,
    group_id,
    -- Fetch the previous group's id_type and max month_id
    LAG(id_type) OVER (PARTITION BY id ORDER BY group_id) AS id_type_prev,
    LAG(group_max_month) OVER (PARTITION BY id ORDER BY group_id) AS month_id_prev
  FROM grouped_records
  -- Keep only one row per group to avoid duplicate lag calculations
  QUALIFY ROW_NUMBER() OVER (PARTITION BY id, group_id ORDER BY month_id) = 1
)
SELECT
  gr.id,
  gr.month_id,
  gr.id_type,
  pd.id_type_prev,
  pd.month_id_prev
FROM grouped_records gr
LEFT JOIN prev_group_details pd
  ON gr.id = pd.id AND gr.group_id = pd.group_id
ORDER BY gr.month_id ASC;

How this works: #

1. grouped_records CTE:

   • We first create a group_id to identify blocks of consecutive rows with the same id_type (using the classic row_number() - row_number() trick).
   • We calculate group_max_month to get the latest (maximum) month_id for each of these consecutive groups.

2. prev_group_details CTE:

   • For each group, we use LAG() to pull the id_type and group_max_month from the previous group.
   • The QUALIFY clause ensures we only process one row per group, so we don't repeat the lag calculation for every row in the group.

3. Final Join:

   • We join the grouped records back to the previous group details, so every row in a group gets the same id_type_prev and month_id_prev values from the prior group.

Running this query will give you exactly the output you're looking for, with the month_id_prev field showing the last month of the previous id_type group.

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