如何根据字段内容分配分组ID？含续行标识的数据分组需求

Got it, let's work through this problem step by step. The goal is to group rows where each group starts with a row whose key doesn't contain ..., followed by all subsequent rows where key includes ... (the continuation lines). We can use SQL window functions to assign unique group IDs easily—here's how:

Step-by-Step Explanation #

First, we need to spot which rows kick off a new group: any row where key doesn't have ... is a group starter. Then, we'll accumulate a count of these starter rows as we traverse the table (ordered by linenr, since it's monotonically increasing) to get our unique group ID.

SQL Code to Create the View #

Assuming your source table is named source_table, here's the view creation query:

CREATE VIEW grouped_records AS
SELECT
    linenr,
    key,
    -- Calculate unique group ID by counting group starters up to the current row
    SUM(CASE WHEN key NOT LIKE '%...%' THEN 1 ELSE 0 END) 
        OVER (ORDER BY linenr) AS group_id
FROM source_table;

Breakdown of the Code #

• The CASE statement tags group starter rows with 1 and continuation rows with 0.
• The SUM() OVER (ORDER BY linenr) window function adds up these tags from the first row all the way to the current row. Every time we hit a new group starter, the sum increments—giving each group a unique ID (they don't have to be consecutive, which fits your requirement perfectly).

Example Output #

If your source table looks like this:

The view will return:

This works in most modern databases (PostgreSQL, MySQL 8+, SQL Server, etc.) that support window functions. If you're using an older database, feel free to ask for an adjusted approach!

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