基于Teradata实现行计数器的条件重置方案问询

Hey there! Let’s work through that row number reset problem you’re stuck on. It sounds like your current setup increments row numbers line by line, but you need the count to start over every time the combination of row_rk and your lagged date changes—totally get why that’s tricky.

Here’s a reliable solution using window functions (this works across most major SQL dialects like PostgreSQL, BigQuery, SQL Server, and more):

WITH flagged_groups AS (
  SELECT
    *,
    -- Mark a new group when row_rk + lagged date doesn't match the previous row
    CASE
      WHEN LAG(ROW(row_rk, lagged_date)) OVER (ORDER BY your_sort_column) 
           != ROW(row_rk, lagged_date)
      THEN 1
      ELSE 0
    END AS new_group,
    -- Generate a unique ID for each consecutive group
    SUM(CASE
          WHEN LAG(ROW(row_rk, lagged_date)) OVER (ORDER BY your_sort_column) 
               != ROW(row_rk, lagged_date)
          THEN 1
          ELSE 0
        END) OVER (ORDER BY your_sort_column ROWS UNBOUNDED PRECEDING) AS group_id
  FROM your_table
)
SELECT
  *,
  -- Reset row number for each group
  ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY your_sort_column) AS reset_row_num
FROM flagged_groups;

Let me break this down so it makes sense:

• We use LAG() to compare the current row's row_rk + lagged date pair with the one from the previous row. If they don't match, we flag it as a new_group.
• The running SUM() on that flag creates a unique group_id for every consecutive block of rows that share the same row_rk and lagged date combination.
• Finally, ROW_NUMBER() partitions by that group_id—so every time the combination changes, the row number starts back at 1.

A quick note: If your SQL dialect doesn’t support ROW() for comparing pairs, you can concatenate the values instead (just make sure to handle date formatting properly, e.g., CONCAT(row_rk, '-', CAST(lagged_date AS VARCHAR))).

Hope this fixes your issue—let me know if you need to tweak it for your specific database setup!

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