Splitting Data For Machine Learning, Using T-SQL

It is a common practice in data science to split our data up into three separate datasets in order to perform machine learning (ML) on it. The majority of the data will be used to Train our ML models and then a portion of the remaining data will be used to Validate those models. The best of these models will then be applied to our Training data to evaluate its performance.

Graphical ML tools such as Azure Machine Learning often provide easily configurable drag-and-drop tools to split our data in this manner, but what happens if we are working on a custom solution, perhaps using something like SQL Server’s In-Database Machine Learning? In this blog post we’ll look at a couple of different T-SQL solutions to quickly split our data randomly into these datasets.

A common approach is to use 60-80% of our data for the Training set and to divide the remainder evenly between Validation and Testing. For our purposes we’ll split the difference and dedicate 70% of our data to training, leaving 15% to Validation and 15% to Testing.

For these examples, we’ll be looking at patient appointment data from our sample data warehouse. Firstly we want to maintain a record of which rows of data were in which dataset for the purposes of reproducibility and tracking data lineage, so we’ll add a column to our table to hold that information.

ALTER TABLE fact.Appointment
ADD Dataset VARCHAR(10) NULL;

Next we’ll update that column randomly to one of Training, Validation, or Testing based on the percentages we specified above and run a quick GROUP BY query to look at the results.

--  Method #1 - Using the RAND() function
--  Definitely not the results we want!

DECLARE
    @TrainingPercentage     INT = 70,
    @ValidationPercentage   INT = 15,
    @TestingPercentage      INT = 15;


UPDATE
    fact.Appointment
SET
    Dataset =   CASE
                    WHEN 100 * RAND() < @TrainingPercentage THEN 'Training'
                    WHEN 100 * RAND() < (@TrainingPercentage + @ValidationPercentage) THEN 'Validation'
                    ELSE 'Testing'
                END;


SELECT
    Dataset,
    NumberOfRows = COUNT(*)
FROM
    fact.Appointment
GROUP BY
    Dataset
ORDER BY
    Dataset;

We see right away that this method failed horribly as all of the data was placed into the same dataset. This holds true no matter how many times we execute the code, and it happens because the RAND() function is only evaluated once for the whole query, and not individually for each row. To correct this we’ll instead use a method that Jeff Moden taught me at a SQL Saturday in Detroit several years ago – generating a NEWID() for each row, using the CHECKSUM() function to turn it into a random number, and then the % (modulus) function to turn it into a number between 0 and 99 inclusive.

-- Method #2 - Using the NEWID() function
-- Getting closer, but still not quite there

DECLARE
    @TrainingPercentage     INT = 70,
    @ValidationPercentage   INT = 15,
    @TestingPercentage      INT = 15;


UPDATE
    fact.Appointment
SET
    Dataset =   CASE
                    WHEN ABS(CHECKSUM(NEWID())) % 100 < @TrainingPercentage THEN 'Training'
                    WHEN ABS(CHECKSUM(NEWID())) % 100 < (@TrainingPercentage + @ValidationPercentage) THEN 'Validation'
                    ELSE 'Testing'
                END;


SELECT
    Dataset,
    NumberOfRows = COUNT(*)
FROM
    fact.Appointment
GROUP BY
    Dataset
ORDER BY
    Dataset;

So this method failed too. Although we are getting the expected number of Training rows, the split between Validation and Testing is way off. What we find is that the NEWID() generated in the second line of the CASE statement is not the same value as in the first line, so we are getting an 85/15 split on the 30% of non-Training data instead of the expected 50/50. Let’s tweak the logic in that third line to fix the error.

-- Method #3 - Fixing the logical error
-- This works, but the size of the datasets varies slightly each run

DECLARE
    @TrainingPercentage     INT = 70,
    @ValidationPercentage   INT = 15,
    @TestingPercentage      INT = 15;


UPDATE
    fact.Appointment
SET
    Dataset =   CASE
                    WHEN ABS(CHECKSUM(NEWID())) % 100 < @TrainingPercentage THEN 'Training'
                    WHEN ABS(CHECKSUM(NEWID())) % 100 < (@ValidationPercentage * 100 / (@ValidationPercentage + @TestingPercentage)) THEN 'Validation'
                    ELSE 'Testing'
                END;


SELECT
    Dataset,
    NumberOfRows = COUNT(*)
FROM
    fact.Appointment
GROUP BY
    Dataset
ORDER BY
    Dataset;

And success! The only thing we may notice is that if we execute this code multiple times, we get a slightly different number of rows in each dataset.

What if we have a requirement to have exactly the desired percentage of rows in each dataset, and to get that same exact percentage every time we execute? In this situation, we are going to generate a temp table that distributes the exact number of each dataset value randomly across all the rows, and then use that temp table to update the original table.

-- Method #4 - Consistent and exact

DECLARE
    @RowCount               INT,
    @TrainingPercentage     INT = 70,
    @TrainingCount          INT,
    @ValidationPercentage   INT = 15,
    @ValidationCount        INT,
    @TestingPercentage      INT = 15,
    @TestingCount           INT;


SELECT
    @RowCount = COUNT(*)
FROM
    fact.Appointment;


SET @TrainingCount = (FLOOR(@RowCount * @TrainingPercentage / 100.00) + 0.5);
SET @ValidationCount = (FLOOR(@RowCount * @ValidationPercentage / 100.00) + 0.5);
SET @TestingCount = @RowCount - (@TrainingCount + @ValidationCount);

SELECT
    TrainingCount = @TrainingCount,
    ValidationCount = @ValidationCount,
    TestingCount = @TestingCount;


IF EXISTS (SELECT name FROM tempdb.sys.objects WHERE name LIKE '%Dataset%')
    DROP TABLE #Dataset;

CREATE TABLE
    #Dataset
(
    AppointmentKey      INT PRIMARY KEY,
    Dataset             VARCHAR(10) NULL
);


WITH SourceRows AS
(
    SELECT
        AppointmentKey,
        RowNumber = ROW_NUMBER() OVER (ORDER BY CHECKSUM(NEWID()))
    FROM
        fact.Appointment
)
INSERT INTO
    #Dataset
SELECT
    AppointmentKey,
    Dataset =   CASE
                    WHEN RowNumber <= @TrainingCount THEN 'Training'
                    WHEN RowNumber <= (@TrainingCount + @ValidationCount) THEN 'Validation'
                    ELSE 'Testing'
                END
FROM
    SourceRows;


UPDATE
    Appt
SET
    Dataset = Dt.Dataset
FROM
    fact.Appointment Appt
        INNER JOIN
    #Dataset Dt
        ON Appt.AppointmentKey = Dt.AppointmentKey


SELECT
    Dataset,
    NumberOfRows = COUNT(*)
FROM
    fact.Appointment
GROUP BY
    Dataset
ORDER BY
    Dataset;


SELECT TOP 25
    AppointmentKey,
    Dataset
FROM
    fact.Appointment;

Now we see that we divide the rows in exactly the same proportions with each execution, and we’ve also included a SELECT TOP 25 so we can verify that each execution assigns different datasets to each row.

It’s possible that due to the JOIN this method might not be the best performing over extremely large datasets, so we also developed a brute-force method of splitting that performs three sequential updates that we could use instead in that specific circumstance.

-- Method #5 - The brute-force sequential method

DECLARE
    @RowCount               INT,
    @TrainingPercentage     INT = 70,
    @TrainingCount          INT,
    @ValidationPercentage   INT = 15,
    @ValidationCount INT,
    @TestingPercentage INT = 15,
    @TestingCount INT;


SELECT
    @RowCount = COUNT(*)
FROM
    fact.Appointment;


SET @TrainingCount = (FLOOR(@RowCount * @TrainingPercentage / 100.00) + 0.5);
SET @ValidationCount = (FLOOR(@RowCount * @ValidationPercentage / 100.00) + 0.5);
SET @TestingCount = @RowCount - (@TrainingCount + @ValidationCount);

SELECT
    TrainingCount = @TrainingCount,
    ValidationCount = @ValidationCount,
    TestingCount = @TestingCount;

UPDATE
    fact.Appointment
SET
    Dataset = NULL;


UPDATE
    Appt
SET
    Dataset = 'Training'
FROM
    (
        SELECT TOP (@TrainingCount)
            *
        FROM
            fact.Appointment
        ORDER BY
            CHECKSUM(NEWID())
    ) Appt;


UPDATE
    Appt
SET
    Dataset = 'Validation'
FROM
    (
        SELECT TOP (@ValidationCount)
            *
        FROM
            fact.Appointment
        WHERE
            Dataset IS NULL
        ORDER BY
            CHECKSUM(NEWID())
    ) Appt;


UPDATE
    fact.Appointment
SET
    Dataset = 'Testing'
WHERE
    Dataset IS NULL;


SELECT
    Dataset,
    NumberOfRows = COUNT(*)
FROM
    fact.Appointment
GROUP BY
    Dataset
ORDER BY
    Dataset;


SELECT TOP 25
    AppointmentKey,
    Dataset
FROM
    fact.Appointment;

Hopefully you’ve enjoyed this look at randomly splitting our data in T-SQL. Please let me know in the comments what other methods you’ve used to tackle this problem!