基于ROW_NUMBER()分区的SQL过滤：跳过限制行并优化过滤逻辑

Let's work through this step by step to make sure we first remove the restricted rows (a user's 3rd Activity where Activity = 'A') before applying the minimum activity count filter. Here's a reliable approach:

Step 1: Flag and Remove Restricted Rows #

First, we'll target exactly the rows that need to be skipped: any user's 3rd row (from the original partitioned set) where the Activity is 'A'. We'll use a CTE to isolate valid rows by excluding these restricted entries.

Step 2: Recompute Row Numbers After Exclusion #

Once restricted rows are removed, the original Row values will be out of order for each user. We'll recalculate the row numbering to reflect the remaining sequence of activities.

Step 3: Apply Minimum Activity Filter #

Finally, we'll filter to keep only users who still have more than 1 activity left after removing the restricted rows. This ensures the filter acts on the processed dataset, not the original unfiltered data.

Complete SQL Query #

WITH ValidRows AS (
    -- First pass: exclude restricted rows, apply base filters
    SELECT 
        t1.Row AS OriginalRow,
        t1.Activity,
        t1.User
    FROM Table1 t1
    WHERE [Filters] -- Replace with your actual base filters
      AND NOT (
          t1.Row = 3 
          AND t1.Activity = 'A'
          -- Only target users who actually have a 3rd row in the filtered data
          AND EXISTS (
              SELECT 1
              FROM Table1 t2
              WHERE t2.User = t1.User
                AND [Filters] -- Match base filters
              GROUP BY t2.User
              HAVING COUNT(*) >= 3
          )
      )
),
ReorderedRows AS (
    -- Reassign sequential row numbers per user after removing restricted rows
    SELECT 
        ROW_NUMBER() OVER(PARTITION BY User ORDER BY OriginalRow) AS Row,
        Activity,
        User
    FROM ValidRows
),
UserActivityCounts AS (
    -- Count remaining activities per user for the minimum filter
    SELECT 
        User,
        COUNT(*) AS TotalActivities
    FROM ReorderedRows
    GROUP BY User
)
-- Final result: users with >1 activities, correct row numbering
SELECT 
    rr.Row,
    rr.Activity,
    rr.User
FROM ReorderedRows rr
JOIN UserActivityCounts uac 
    ON rr.User = uac.User
WHERE uac.TotalActivities > 1
ORDER BY rr.User, rr.Row;

Key Details: #

• Restricted Row Accuracy: The NOT (t1.Row = 3 AND t1.Activity = 'A') condition targets exactly the rows we need to skip, with an EXISTS check to avoid incorrectly flagging users who don't have 3 rows in the filtered dataset.
• Row Number Consistency: By using OriginalRow in the ORDER BY clause of the second CTE, we preserve the original activity sequence while reassigning sequential row numbers after filtering.
• Safe Minimum Filter: Calculating activity counts on the already filtered dataset ensures we don't exclude users who would have qualified after removing restricted rows (a risk of applying the filter too early).

Note: #

If your original Row column was generated using a specific ordered field (like a timestamp or sequence ID), replace OriginalRow in the ORDER BY of ReorderedRows with that field to maintain precise activity order.

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