在PostgreSQL中用SQL在分组内创建数据集的问题

要实现将现有数据按Flags中A,B,C的重复次数拆分对应行数的需求，可以通过计算重复次数+生成对应行数的方式实现，以下是不同数据库的具体SQL写法：

通用思路 #

1. 计算每条记录中Flags字段里完整A,B,C片段的重复次数：通过对比替换前后的字符串长度差，除以单个A,B,C的长度得到次数。
2. 生成连续数字序列，与原表关联，生成对应数量的拆分记录，每行Flags固定为A,B,C。

SQL Server 实现 #

WITH Numbers AS (
    SELECT 1 AS N
    UNION ALL
    SELECT N + 1 FROM Numbers WHERE N <= 10 -- 按需调整最大重复次数
),
RecordCounts AS (
    SELECT 
        [User],
        [Date],
        Flags,
        -- 计算A,B,C的重复次数
        (LEN(Flags) - LEN(REPLACE(Flags, 'A,B,C', ''))) / LEN('A,B,C') AS RepeatCount
    FROM YourTable
)
SELECT 
    rc.[User],
    rc.[Date],
    'A,B,C' AS Flags
FROM RecordCounts rc
JOIN Numbers n ON n.N <= rc.RepeatCount
ORDER BY rc.[User], rc.[Date], n.N
OPTION (MAXRECURSION 0); -- 若重复次数超过100需添加此选项

MySQL 8.0+ 实现 #

WITH RECURSIVE Numbers AS (
    SELECT 1 AS N
    UNION ALL
    SELECT N + 1 FROM Numbers WHERE N <= 10 -- 按需调整最大重复次数
),
RecordCounts AS (
    SELECT 
        `User`,
        `Date`,
        Flags,
        (CHAR_LENGTH(Flags) - CHAR_LENGTH(REPLACE(Flags, 'A,B,C', ''))) / CHAR_LENGTH('A,B,C') AS RepeatCount
    FROM YourTable
)
SELECT 
    rc.`User`,
    rc.`Date`,
    'A,B,C' AS Flags
FROM RecordCounts rc
JOIN Numbers n ON n.N <= rc.RepeatCount
ORDER BY rc.`User`, rc.`Date`, n.N;

PostgreSQL 实现 #

SELECT 
    "User",
    "Date",
    'A,B,C' AS Flags
FROM YourTable
CROSS JOIN LATERAL generate_series(
    1,
    (CHAR_LENGTH(Flags) - CHAR_LENGTH(REPLACE(Flags, 'A,B,C', ''))) / CHAR_LENGTH('A,B,C')
)
ORDER BY "User", "Date";

说明 #

• 上述代码假设Flags字段是A,B,C的严格重复（无其他干扰内容），若存在其他格式需调整重复片段的匹配逻辑。
• Numbers CTE中的10为预估最大重复次数，可根据实际数据调整。

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