基于R的SQL连接：统计ID的STATUS组合及分组ID列表需求

不需要使用窗口lag函数，通过基础聚合函数即可实现需求，以下是具体SQL代码实现：

步骤1：给每个ID标记类别 #

先通过CTE（公共表表达式）或子查询，基于每个ID的STATUS取值范围判断所属类别：

WITH id_category AS (
    SELECT 
        ID,
        CASE
            WHEN MIN(STATUS) = '0' AND MAX(STATUS) = '0' THEN 'A类'  -- 所有STATUS均为0
            WHEN MIN(STATUS) = '1' AND MAX(STATUS) = '1' THEN 'B类'  -- 所有STATUS均为1
            ELSE 'C类'  -- 同时存在0和1
        END AS category
    FROM your_table_name  -- 替换为你的实际表名
    GROUP BY ID
)

步骤2：统计数量、占比并生成ID列表 #

基于上述分类结果，统计各类别的数量、占比，同时拼接对应ID列表（注意不同数据库的字符串拼接函数有差异，以下是主流数据库的写法）：

SQL Server / PostgreSQL 版本 #

WITH id_category AS (
    SELECT 
        ID,
        CASE
            WHEN MIN(STATUS) = '0' AND MAX(STATUS) = '0' THEN 'A类'
            WHEN MIN(STATUS) = '1' AND MAX(STATUS) = '1' THEN 'B类'
            ELSE 'C类'
        END AS category
    FROM your_table_name
    GROUP BY ID
)
SELECT 
    category,
    COUNT(*) AS id_count,
    ROUND(COUNT(*) * 1.0 / (SELECT COUNT(DISTINCT ID) FROM your_table_name), 4) AS proportion,
    STRING_AGG(ID, ', ') AS id_list  -- 用逗号分隔ID列表
FROM id_category
GROUP BY category
ORDER BY category;

MySQL 版本 #

WITH id_category AS (
    SELECT 
        ID,
        CASE
            WHEN MIN(STATUS) = '0' AND MAX(STATUS) = '0' THEN 'A类'
            WHEN MIN(STATUS) = '1' AND MAX(STATUS) = '1' THEN 'B类'
            ELSE 'C类'
        END AS category
    FROM your_table_name
    GROUP BY ID
)
SELECT 
    category,
    COUNT(*) AS id_count,
    ROUND(COUNT(*) * 1.0 / (SELECT COUNT(DISTINCT ID) FROM your_table_name), 4) AS proportion,
    GROUP_CONCAT(ID SEPARATOR ', ') AS id_list
FROM id_category
GROUP BY category
ORDER BY category;

关键逻辑说明 #

1. 类别判断：利用MIN(STATUS)和MAX(STATUS)的组合结果快速判断ID的STATUS分布：
   • 若最小和最大STATUS都是'0'，说明该ID所有记录的STATUS均为0
   • 若最小和最大STATUS都是'1'，说明该ID所有记录的STATUS均为1
   • 其余情况则同时存在0和1
2. 占比计算：用当前类别的ID数量除以总唯一ID数，乘以1.0是为了避免整数除法，确保得到小数占比
3. ID列表拼接：使用对应数据库的字符串聚合函数，将同一类别的ID拼接成逗号分隔的字符串

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