BigQuery连续值计算问题：previous row变更后计数未重置的解决方法咨询

Ah, I see the issue here! Your original approach using COUNT() OVER (PARTITION BY type, previous row) groups all rows with the same type and previous row together, regardless of whether they're consecutive in the dataset. That's why the count doesn't reset when previous row changes and then comes back later.

To fix this, we need to first identify consecutive blocks of the same type and previous row, then calculate the descending count within each block. Here's how to do it:

Step 1: Identify Consecutive Blocks #

We'll use the LAG() function to compare each row's previous row value with the one before it. When the value changes, we'll increment a "group ID" to mark the start of a new block. This ensures only consecutive rows with the same values are grouped together.

Step 2: Calculate Descending Count per Block #

Once we have our group IDs, we can use COUNT() over a partition of type and the group ID, ordered in reverse to get the descending consecutive values you need.

Full SQL Query #

Assuming you have a column that defines the order of your rows (let's call it row_order—this could be a timestamp, primary key, or any column that maintains the sequence from your example), here's the query:

SELECT
    type,
    previous_row,
    COUNT(*) OVER (
        PARTITION BY type, group_id
        ORDER BY row_order DESC
    ) AS Consecutive_values
FROM (
    SELECT
        type,
        previous_row,
        row_order,
        -- Generate group ID for consecutive blocks
        SUM(CASE WHEN prev_previous_row = previous_row THEN 0 ELSE 1 END)
            OVER (PARTITION BY type ORDER BY row_order) AS group_id
    FROM (
        SELECT
            type,
            previous_row,
            row_order,
            -- Get the previous row's "previous row" value
            LAG(previous_row) OVER (PARTITION BY type ORDER BY row_order) AS prev_previous_row
        FROM your_table
    ) t1
) t2
ORDER BY row_order;

How It Works with Your Example #

Let's walk through your sample data (using row_order as 1 to 7):

1. For rows 1 and 2 (previous row = 1), the prev_previous_row matches, so they get the same group_id = 1. The descending count gives 2 and 1.
2. Row 3 switches to previous row = 0—since the previous value was 1, group_id increments to 2. Rows 3-5 share this group, so their counts are 3, 2, 1.
3. Row 6 switches back to previous row = 1—the previous value was 0, so group_id increments to 3. Rows 6-7 get counts 2 and 1.

This exactly matches the Consecutive values column in your example!

Important Note #

Don't forget to replace row_order with your actual ordering column. SQL tables don't have a natural order, so you need a column that defines the sequence of rows to correctly identify consecutive blocks.

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