如何在PostgreSQL查询中创建dummy variable并保留全部观测

当然可以轻松实现这个需求！你只需要用PostgreSQL的CASE条件语句生成0/1形式的哑变量，同时去掉原查询里的WHERE筛选子句，就能保留所有观测记录了。下面分两种常见场景给你具体示例：

场景1：保留所有原始行，为每行添加哑变量 #

如果你的需求是保留表中每一行，并标记该行的var2是否属于blue或yellow，可以用下面的SQL：

SELECT
    id,
    var1,
    var2,
    var3,
    -- 生成哑变量：满足条件为1，否则为0
    CASE WHEN var2 ILIKE ANY('{blue,yellow}') THEN 1 ELSE 0 END AS is_target_color
FROM table;

这里用ILIKE是为了忽略大小写（比如你的示例里有Blue和Red），如果不需要忽略大小写，直接用=即可。

场景2：每个id仅保留一条记录（保持DISTINCT ON逻辑），同时标记该记录是否满足条件 #

如果你还是需要每个id只返回一条记录（和原查询的DISTINCT ON逻辑一致），但要覆盖所有id（不管是否满足var2条件），可以调整排序逻辑，确保DISTINCT ON能正确选取你想要的行，同时添加哑变量：

SELECT DISTINCT ON (id)
    id,
    var1,
    var2,
    var3,
    CASE WHEN var2 = ANY('{blue,yellow}') THEN 1 ELSE 0 END AS is_target_color
FROM table
-- 排序规则：优先选取满足var2条件的行作为每个id的代表
ORDER BY id, CASE WHEN var2 = ANY('{blue,yellow}') THEN 0 ELSE 1 END;

这个查询会为每个id优先选择满足var2条件的行（如果存在的话）；如果没有满足条件的行，就选取该id的任意一行，同时给每一行标记是否符合条件。

小提示 #

• 哑变量的名称可以自定义，比如把is_target_color换成你更易理解的名字就行。
• 如果var2的值存在大小写差异（比如Blue和blue），也可以用LOWER(var2) = ANY('{blue,yellow}')来统一转换后判断，避免漏判。

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