如何随机获取年龄相同的男女配对样本（200组共400条记录）

数据库表结构及示例数据 #

需求说明 #

需要随机抽取400条记录组成样本：

• 包含200条男性记录和200条女性记录
• 每条男性记录对应一条同年龄的女性记录，最终得到200组年龄匹配的男女配对

原尝试代码问题 #

你当前的代码仅能随机抽取200条男性和200条女性记录，但无法保证两者年龄一一对应：

DROP TABLE IF EXISTS #SampleTableM
DROP TABLE IF EXISTS #SampleTableF

SELECT TOP (200) [Year],[Age],[Gender],[OrderID]
INTO #SampleTableM
  FROM [database.name]
  WHERE Age <=90 AND Sex = 'M'
  ORDER BY NEWID()

SELECT TOP (200) [Year],[Age],[Gender],[OrderID]
INTO #SampleTableF
  FROM [database.name]
  WHERE Age <=90 AND Sex = 'F'
  ORDER BY NEWID()

SELECT * FROM #SampleTableM
UNION
SELECT * FROM #SampleTableF;

解决方案 #

要实现年龄匹配的男女配对，需要按年龄分组后随机抽取并关联，以下是可行的SQL代码：

DROP TABLE IF EXISTS #MaleRecords;
DROP TABLE IF EXISTS #FemaleRecords;
DROP TABLE IF EXISTS #MatchedPairs;

-- 对每个年龄组的男性记录随机排序并编号
SELECT 
    [Year], [Age], [Gender], [OrderID],
    ROW_NUMBER() OVER (PARTITION BY Age ORDER BY NEWID()) AS RowNum
INTO #MaleRecords
FROM [database.name]
WHERE Age <= 90 AND Gender = 'M';

-- 对每个年龄组的女性记录随机排序并编号
SELECT 
    [Year], [Age], [Gender], [OrderID],
    ROW_NUMBER() OVER (PARTITION BY Age ORDER BY NEWID()) AS RowNum
INTO #FemaleRecords
FROM [database.name]
WHERE Age <= 90 AND Gender = 'F';

-- 匹配同年龄、同编号的男女记录，取前200对
SELECT TOP 200
    m.[Year] AS Male_Year, m.Age, m.[Gender] AS Male_Gender, m.OrderID AS Male_OrderID,
    f.[Year] AS Female_Year, f.[Gender] AS Female_Gender, f.OrderID AS Female_OrderID
INTO #MatchedPairs
FROM #MaleRecords m
INNER JOIN #FemaleRecords f 
    ON m.Age = f.Age 
    AND m.RowNum = f.RowNum;

-- 输出最终的400条样本记录（200对）
SELECT Male_Year, Age, Male_Gender, Male_OrderID FROM #MatchedPairs
UNION ALL
SELECT Female_Year, Age, Female_Gender, Female_OrderID FROM #MatchedPairs;

代码逻辑说明 #

1. 分组随机编号：使用ROW_NUMBER() OVER (PARTITION BY Age ORDER BY NEWID())，给每个年龄组内的男女记录随机分配序号，确保同年龄组内的记录是随机抽取的。
2. 年龄匹配关联：通过INNER JOIN关联同年龄且同编号的男女记录，保证每一对记录的年龄完全一致。
3. 输出样本：最后用UNION ALL合并配对后的男女记录，得到400条符合要求的样本。

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