如何在SQL中按分组获取前N%数据并按类型拆分训练集、测试集与验证集？

Got it, let's work through this problem step by step. You need to split your dataset into train, test, and validation sets grouped by the type field (with specific percentage splits and rounding up for odd counts), plus you want to know how to fetch the top N% of data per group in SQL. Here's a complete solution:

SQL Implementation #

First, we'll use window functions to calculate row numbers and total rows per group, then use a CASE statement to assign each row to the correct dataset.

WITH grouped_data AS (
    SELECT 
        text,
        type,
        -- Assign a unique row number to each entry within its type group
        ROW_NUMBER() OVER (PARTITION BY type ORDER BY text) AS row_num,
        -- Calculate total number of rows per type group
        COUNT(*) OVER (PARTITION BY type) AS total_rows
    FROM your_table_name -- Replace with your actual table name
)
SELECT 
    text,
    type,
    CASE
        -- First 20% (rounded up) → test set
        WHEN row_num <= CEIL(0.2 * total_rows) THEN 'test'
        -- Next 20% (rounded up) → validation set
        WHEN row_num <= CEIL(0.2 * total_rows) + CEIL(0.2 * total_rows) THEN 'validation'
        -- Remaining 60% → train set
        ELSE 'train'
    END AS data
FROM grouped_data
ORDER BY type, row_num;

Breaking Down the Code #

Let's walk through each part to understand how it works:

• ROW_NUMBER() OVER (PARTITION BY type ORDER BY text): This assigns a sequential number to each row within its type group. We sort by text here to ensure consistent ordering—you can replace text with another column (like a timestamp) if you need a different sort logic.
• COUNT(*) OVER (PARTITION BY type): This calculates the total number of rows for each type group, which we need to compute the percentage splits.
• CEIL(0.2 * total_rows): The CEIL function rounds up the result of 20% of the group's total rows, handling cases where the group has an odd number of entries (e.g., 3 rows → 20% is 0.6, rounded up to 1 row).
• CASE Statement: This checks the row number against our calculated thresholds to assign the correct dataset label.

Getting Top N% of Data per Group #

If you just need to fetch the top N% of rows per group (e.g., top 20% for validation), you can adapt the CTE with a WHERE clause:

WITH grouped_data AS (
    SELECT 
        text,
        type,
        ROW_NUMBER() OVER (PARTITION BY type ORDER BY text) AS row_num,
        COUNT(*) OVER (PARTITION BY type) AS total_rows
    FROM your_table_name
)
SELECT text, type
FROM grouped_data
WHERE row_num <= CEIL(0.2 * total_rows); -- Replace 0.2 with your desired percentage

Example Verification #

Let's test this with your sample data:

• For type=6069 (10 rows): CEIL(0.2*10)=2. So rows 1-2 → test, 3-4 → validation, 5-10 → train (matches your expected output).
• For type=5012 (4 rows): CEIL(0.2*4)=1. Rows 1 → test, 2 → validation, 3-4 → train (matches your expected output).
• For type=6070 (3 rows): CEIL(0.2*3)=1. Rows 1 → test, 2 → validation, 3 → train (matches your expected output).

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