使用PostgreSQL检测性别变更：现有SQL写法是否正确？是否需用LAG窗口函数？

问题背景 #

现有数据集字段及示例值：

• id：111, 111, 111, 112, 112, 113, 113
• Year：2010, 2011, 2012, 2010, 2011, 2010, 2015
• Sex：M, M, F, F, F, M, M

其中ID=111存在性别变更（从M变为F），需要实现以下查询：

• A：统计并找出始终为男性的ID数量及具体ID
• B：统计并找出始终为女性的ID数量及具体ID
• C：统计并找出从男性变为女性的ID数量及具体ID
• D：统计并找出从女性变为男性的ID数量及具体ID

你尝试的SQL代码如下：

# problem A
SELECT COUNT(DISTINCT ID) FROM table WHERE ID NOT IN (SELECT ID FROM table WHERE SEX = 'M');
SELECT DISTINCT ID FROM table WHERE ID NOT IN (SELECT ID FROM table WHERE SEX = 'M');

# problem B
SELECT COUNT(DISTINCT ID) FROM table WHERE ID NOT IN (SELECT ID FROM table WHERE SEX = 'F');
SELECT DISTINCT ID FROM table WHERE ID NOT IN (SELECT ID FROM table WHERE SEX = 'F');

# all sex change
SELECT COUNT(DISTINCT ID) FROM table WHERE ID IN (SELECT ID FROM table WHERE SEX = 'M') AND ID IN (SELECT ID FROM table WHERE SEX = 'F');
SELECT DISTINCT ID FROM table WHERE ID IN (SELECT ID FROM table WHERE SEX = 'M') AND ID IN (SELECT ID FROM table WHERE SEX = 'F');

现有代码的问题 #

你的代码存在逻辑颠倒的问题：

• 问题A的SQL实际是找出从未出现男性记录的ID（即始终为女性的ID），和需求A完全相反；
• 问题B的SQL实际是找出从未出现女性记录的ID（即始终为男性的ID），和需求B相反；
• 最后一段代码只能找出同时存在M和F记录的ID，但无法区分是从M变F还是F变M，无法满足C、D的细分需求。

正确的查询实现 #

以下是针对各需求的正确SQL，同时提供两种写法（子查询过滤/分组聚合），可根据数据量选择更高效的版本：

需求A：始终为男性的ID

-- 统计数量（子查询写法）
SELECT COUNT(DISTINCT id) AS male_only_count
FROM your_table
WHERE id NOT IN (SELECT id FROM your_table WHERE sex = 'F');

-- 具体ID（子查询写法）
SELECT DISTINCT id
FROM your_table
WHERE id NOT IN (SELECT id FROM your_table WHERE sex = 'F');

-- 分组聚合写法（一次查询同时获取数量和ID列表）
SELECT 
  COUNT(*) AS male_only_count,
  ARRAY_AGG(DISTINCT id) AS male_only_ids
FROM (
  SELECT id
  FROM your_table
  GROUP BY id
  HAVING COUNT(DISTINCT sex) = 1 AND MAX(sex) = 'M'
) t;

需求B：始终为女性的ID

-- 统计数量（子查询写法）
SELECT COUNT(DISTINCT id) AS female_only_count
FROM your_table
WHERE id NOT IN (SELECT id FROM your_table WHERE sex = 'M');

-- 具体ID（子查询写法）
SELECT DISTINCT id
FROM your_table
WHERE id NOT IN (SELECT id FROM your_table WHERE sex = 'M');

-- 分组聚合写法
SELECT 
  COUNT(*) AS female_only_count,
  ARRAY_AGG(DISTINCT id) AS female_only_ids
FROM (
  SELECT id
  FROM your_table
  GROUP BY id
  HAVING COUNT(DISTINCT sex) = 1 AND MAX(sex) = 'F'
) t;

需求C：从男性变为女性的ID

需要按年份排序，确认该ID的最早性别为M、最晚性别为F：

-- 统计数量
SELECT COUNT(DISTINCT id) AS m_to_f_count
FROM (
  SELECT 
    id,
    FIRST_VALUE(sex) OVER (PARTITION BY id ORDER BY year) AS first_sex,
    LAST_VALUE(sex) OVER (PARTITION BY id ORDER BY year ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sex
  FROM your_table
) t
WHERE first_sex = 'M' AND last_sex = 'F';

-- 具体ID
SELECT DISTINCT id
FROM (
  SELECT 
    id,
    FIRST_VALUE(sex) OVER (PARTITION BY id ORDER BY year) AS first_sex,
    LAST_VALUE(sex) OVER (PARTITION BY id ORDER BY year ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sex
  FROM your_table
) t
WHERE first_sex = 'M' AND last_sex = 'F';

需求D：从女性变为男性的ID

逻辑与C相反：

-- 统计数量
SELECT COUNT(DISTINCT id) AS f_to_m_count
FROM (
  SELECT 
    id,
    FIRST_VALUE(sex) OVER (PARTITION BY id ORDER BY year) AS first_sex,
    LAST_VALUE(sex) OVER (PARTITION BY id ORDER BY year ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sex
  FROM your_table
) t
WHERE first_sex = 'F' AND last_sex = 'M';

-- 具体ID
SELECT DISTINCT id
FROM (
  SELECT 
    id,
    FIRST_VALUE(sex) OVER (PARTITION BY id ORDER BY year) AS first_sex,
    LAST_VALUE(sex) OVER (PARTITION BY id ORDER BY year ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sex
  FROM your_table
) t
WHERE first_sex = 'F' AND last_sex = 'M';

是否需要使用LAG窗口函数？ #

如果你的需求只是区分“从M变F”或“从F变M”的ID（以最早和最晚性别为准），用FIRST_VALUE和LAST_VALUE就足够，不需要LAG。

但如果需要查看每一次性别变更的具体过程（比如是否有多次变更），则需要用LAG函数对比相邻年份的性别：

-- 查看所有发生性别变更的记录
SELECT 
  id,
  year,
  sex,
  LAG(sex) OVER (PARTITION BY id ORDER BY year) AS prev_sex
FROM your_table
WHERE LAG(sex) OVER (PARTITION BY id ORDER BY year) IS NOT NULL
  AND LAG(sex) OVER (PARTITION BY id ORDER BY year) != sex;

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