SQL新手疑问：OVER PARTITION无法按Value1变化重置行号？

Got it, let's break down why your current query isn't behaving as expected, then fix it up!

The Problem with Your Original Query #

Your current code uses PARTITION BY Value1, which groups all rows with the same Value1 (0 or 3) into a single partition—regardless of whether they're consecutive in your table. That's why you only get two partitions total, instead of a new partition every time Value1 switches between 0 and 3.

To reset the row number every time Value1 changes, we need to group consecutive rows with the same Value1 into separate "islands" first, then generate row numbers for each island.

Step-by-Step Solution #

This is a classic "islands and gaps" problem. Here's how to solve it:

1. Get the previous row's Value1
   Use the LAG() window function to compare each row's Value1 with the one right before it:

   SELECT 
     GatewayDetailID,
     Block,
     Value1,
     LAG(Value1) OVER(ORDER BY Block ASC) AS PrevValue1
   FROM SensorData
   

   The PrevValue1 column will show the Value1 from the prior row (or NULL for the first row).

2. Create a group ID for consecutive Value1 rows
   We'll generate a unique ID for each consecutive block of the same Value1. Every time Value1 doesn't match the previous row (or it's the first row), we increment the group ID:

   SELECT 
     *,
     SUM(CASE 
       WHEN Value1 != PrevValue1 OR PrevValue1 IS NULL THEN 1 
       ELSE 0 
     END) OVER(ORDER BY Block ASC) AS GroupID
   FROM (
     SELECT 
       GatewayDetailID,
       Block,
       Value1,
       LAG(Value1) OVER(ORDER BY Block ASC) AS PrevValue1
     FROM SensorData
   ) AS Step1
   

   Now each consecutive block of 0s or 3s has its own unique GroupID.

3. Generate the resetting row number
   Finally, partition by the GroupID we created to get row numbers that reset every time Value1 changes:

   SELECT 
     ROW_NUMBER() OVER(PARTITION BY GroupID ORDER BY Block ASC) AS Row#,
     GatewayDetailID,
     Block,
     Value1
   FROM (
     SELECT 
       *,
       SUM(CASE 
         WHEN Value1 != PrevValue1 OR PrevValue1 IS NULL THEN 1 
         ELSE 0 
       END) OVER(ORDER BY Block ASC) AS GroupID
     FROM (
       SELECT 
         GatewayDetailID,
         Block,
         Value1,
         LAG(Value1) OVER(ORDER BY Block ASC) AS PrevValue1
       FROM SensorData
     ) AS Step1
   ) AS Step2
   ORDER BY Block;
   

Key Note #

Make sure you're always ordering by Block (since that's the column defining the sequence of your data). All window functions rely on this order to correctly identify consecutive Value1 blocks.

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