4

BACKGROUND

I am using Microsoft SQL Server Management Studio v18.9.1 on Windows 10.

I've created a database to store test results of our company's widget (not yet being produced). I am working on a stored procedure to populate that database with random test result records. The purpose of this is to have a data set to use for query development while waiting for real widgets to start being produced and tested.

In what follows, our test suite is composed of multiple subtest types. For this post, I've created only one subtest type. The example below runs the test suite one time (with a [test_id] of 0).

Within a test, a given subtest may be run multiple times if the tester deems it necessary for any reason. In the example below, the one subtest I show is run five times for illustrative purposes.

The subtest I show has two parameters, the first of which can take on two discrete values, and the second of which can take on three discrete values. All subtests must be run over all parameter combinations. So, a subtest result consists of 2 * 3 = 6 partial results (one for each parameter combination).

When a parameter combination is tested, either anomalies are not observed (pass) or one or more anomalies are observed (fail). If any of the six parameter combinations produces one or more anomalies, the subtest as a whole fails.

A subtest result record contains the following columns:

  • [test_id]
  • [subtest_run_number]
  • [parameter_1]
  • [parameter_2]
  • [anomaly_id]
  • [anomaly_magnitude]
  • [subtest_type]

If testing with a given parameter combination produces no anomalies, this is noted by creating a single subtest record containing the two parameter values, an [anomaly_id] of 0, and an [anomaly_magnitude] of NULL.

If testing with a given parameter combination produces N anomalies, this is noted by creating N subtest records. Each subtest record contains the parameter values that produced the anomaly. Each subtest record also contains a 1-based anomaly ID for one of the anomalies seen as well as the "magnitude" of that anomaly.

In producing this data set of random test results, I am trying to use set-based operations rather than iteration. As can be seen below, I did have to use iteration when 1) looping over the number of times the subtest would be run and when 2) looping over the (randomly-chosen) number of anomalies that were to be created for a given parameter combination.

Where I was able to use set-based operations was when creating test result data for every parameter combination. This was a natural place to use a cross join since each parameter has a table defining its valid values. However, there's a problem...

THE PROBLEM

Within a subtest run, every parameter combination has the same number of anomalies associated with it. What I want is for the anomaly count associated with each parameter combination to be able to vary independently.

AN UNDESIRABLE SOLUTION

The only way I can think of accomplishing this is to forego set-based operations and iterate as follows:

DECLARE @parameter_1 as INT = 1;
DECLARE @parameter_2 as INT = 1;

DECLARE @anomaly_count AS INT;
EXEC [dbo].[rand_int] @a = 0, @b = 2, @random_int = @anomaly_count OUTPUT;

WHILE @parameter_1 <= 2
BEGIN
   WHILE @parameter_2 <= 3
   BEGIN
      IF @anomaly_count = 0
      BEGIN
         -- Insert a record listing the parameters and showing an "anomaly"
         -- with an anomaly ID of 0 and an anomaly magnitude of NULL.
      END;
      ELSE
      BEGIN
         DECLARE @anomaly_loop AS INT;
         SET @anomaly_loop = 1;

         WHILE @anomaly_loop <= @anomaly_count
         BEGIN
            -- Insert a record listing the parameters and showing an anomaly
            -- with an anomaly ID of @anomaly_count and a randomly-chosen
            -- anomaly magnitude.

            SET @anomaly_loop = @anomaly_loop + 1;
         END;
      END;

      SET @parameter_2 = @parameter_2 + 1;
   END;

   SET @parameter_1 = @parameter_1 + 1;
END;

The obvious drawbacks of iterating are:

  • Iteration will very likely run slower than set-based operations
  • The number of values each parameter can take on must be hardcoded

MICROSOFT T-SQL SCRIPTS

The two Microsoft T-SQL scripts below are self-contained, runnable, and will demonstrate the problem.

The first script needs to be invoked only once (though it may be invoked multiple times). Then, the second script may be invoked as many times as desired to generate a new data set.

SCRIPT 1

-- Script 1
-- Create needed tables, allowed parameter values, and stored procedures

DROP PROC IF EXISTS [dbo].[generate_random_subtest_type_1_data];
DROP PROC IF EXISTS [dbo].[rand_int];
DROP TABLE IF EXISTS [dbo].[subtest_type_1_data];
DROP TABLE IF EXISTS [dbo].[test_runs];
DROP TABLE IF EXISTS [dbo].[parameter_2_values];
DROP TABLE IF EXISTS [dbo].[parameter_1_values];
GO

CREATE TABLE [dbo].[parameter_1_values]
(
   [parameter_1_value] INT NOT NULL,
   CONSTRAINT [PK_parameter_1_values] PRIMARY KEY ([parameter_1_value])
);

CREATE TABLE [dbo].[parameter_2_values]
(
   [parameter_2_value] INT NOT NULL,
   CONSTRAINT [PK_parameter_2_values] PRIMARY KEY ([parameter_2_value])
);

SET NOCOUNT ON;

INSERT INTO [parameter_1_values] ([parameter_1_value]) VALUES (1), (2);
INSERT INTO [parameter_2_values] ([parameter_2_value]) VALUES (1), (2), (3);

CREATE TABLE [dbo].[test_runs]
(
   [test_id] VARCHAR(20) NOT NULL, -- 0-based
   [subtest_type] VARCHAR(50) NOT NULL, -- 'subtest_type_1', 'subtest_type_2', etc.
   [subtest_run_number] INT NOT NULL, -- 0-based, within a given [test_id]
   [subtest_result] VARCHAR(20) NOT NULL, -- 'pass', 'fail'

   CONSTRAINT [PK_test_runs] PRIMARY KEY ([test_id], [subtest_type], [subtest_run_number])
);

CREATE TABLE [dbo].[subtest_type_1_data]
(
   [test_id] VARCHAR(20) NOT NULL, -- Foreign key back to [test_runs]
   [subtest_run_number] INT NOT NULL, -- Foreign key back to [test_runs]
   [parameter_1] INT NOT NULL,
   [parameter_2] INT NOT NULL,
   [anomaly_id] INT NOT NULL, -- 1-based; 0 indicates no anomalies seen
   [anomaly_magnitude] REAL NULL, -- Must be nullable for case when [anomaly_id] = 0

   [subtest_type] VARCHAR(50) NOT NULL CONSTRAINT [DF_subtest_type_1_data_test_type]
                  DEFAULT 'subtest_type_1',

   CONSTRAINT [CHK_subtest_type_1_data_subtest_type_EQ_subtest_type_1]
              CHECK ([subtest_type] = 'subtest_type_1'),

   CONSTRAINT [PK_subtest_type_1_data] PRIMARY KEY
              (
                 [test_id], [subtest_run_number],
                 [parameter_1], [parameter_2], [anomaly_id]
              ),

   CONSTRAINT [FK_subtest_type_1_data_test_id_subtest_type_subtest_run_number]
              FOREIGN KEY ([test_id], [subtest_type], [subtest_run_number])
              REFERENCES [dbo].[test_runs]([test_id], [subtest_type], [subtest_run_number]),

   CONSTRAINT [FK_subtest_type_1_data_parameter_1]
              FOREIGN KEY([parameter_1])
              REFERENCES [dbo].[parameter_1_values] ([parameter_1_value]),

   CONSTRAINT [FK_subtest_type_1_data_parameter_2]
              FOREIGN KEY([parameter_2])
              REFERENCES [dbo].[parameter_2_values] ([parameter_2_value]),

   CONSTRAINT [CHK_subtest_type_1_data_anomaly_id_EQ_0_anomaly_magnitude_NULL]
              CHECK (
                       ([anomaly_id] <> 0 AND [anomaly_magnitude] IS NOT NULL)
                       OR
                       ([anomaly_id] = 0 AND [anomaly_magnitude] IS NULL)
                    )
);

GO

-- Stored Procedure
CREATE PROC [dbo].[rand_int]
   @a AS INT,
   @b AS INT,
   @random_int AS INT OUTPUT
AS
SET NOCOUNT ON;

SET @random_int = FLOOR(RAND() * (@b - @a + 1)) + @a;

GO

-- Stored Procedure
CREATE PROC [dbo].[generate_random_subtest_type_1_data]
   @test_id AS VARCHAR(20)
AS
SET NOCOUNT ON;

DECLARE @subtest_type_1_count AS INT = 5;
DECLARE @subtest_type_1_loop AS INT = 0;

WHILE @subtest_type_1_loop < @subtest_type_1_count
BEGIN
   DECLARE @anomaly_count AS INT;
   EXEC [dbo].[rand_int] @a = 0, @b = 2, @random_int = @anomaly_count OUTPUT;

   DECLARE @subtest_result AS VARCHAR(20);

   IF @anomaly_count > 0
      SET @subtest_result = 'fail';
   ELSE
      SET @subtest_result = 'pass';

   INSERT INTO [dbo].[test_runs]
               ([test_id], [subtest_run_number], [subtest_type], [subtest_result])
          VALUES (@test_id, @subtest_type_1_loop, 'subtest_type_1', @subtest_result);

   IF @anomaly_count = 0
   BEGIN
      INSERT INTO [dbo].[subtest_type_1_data] ([test_id], [subtest_run_number],
                  [parameter_1], [parameter_2], [anomaly_id], [anomaly_magnitude])
                  SELECT @test_id AS [test_id],
                         @subtest_type_1_loop AS [subtest_run_number],
                         [parameter_1_value] AS [parameter_1_value],
                         [parameter_2_value] AS [parameter_2_value],
                         0 AS [anomaly_id],
                         NULL AS [anomaly_magnitude]
                  FROM [dbo].[parameter_1_values]
                       CROSS JOIN [dbo].[parameter_2_values];
   END;
   ELSE
   BEGIN
      DECLARE @anomaly_loop AS INT;
      SET @anomaly_loop = 1;

      WHILE @anomaly_loop <= @anomaly_count
      BEGIN
         -- Instead of RAND(), use ABS(CHECKSUM(NewId()) / 2147483647.0).
         -- This is because RAND() gets invoked only once in the INSERT statement below.
         --
         -- By using an expression based on NewID(), every row will be assigned its
         -- own random number.

         INSERT INTO [dbo].[subtest_type_1_data] ([test_id], [subtest_run_number],
                     [parameter_1], [parameter_2], [anomaly_id], [anomaly_magnitude])
                     SELECT @test_id AS [test_id],
                            @subtest_type_1_loop AS [subtest_run_number],
                            [parameter_1_value] AS [parameter_1_value],
                            [parameter_2_value] AS [parameter_2_value],
                            @anomaly_loop AS [anomaly_id],
                            ABS(CHECKSUM(NewId()) / 2147483647.0) AS [anomaly_magnitude]
                     FROM [dbo].[parameter_1_values]
                          CROSS JOIN [dbo].[parameter_2_values];

         SET @anomaly_loop = @anomaly_loop + 1;
      END;
   END;

   SET @subtest_type_1_loop = @subtest_type_1_loop + 1;
END;

GO

SCRIPT 2

-- Script 2
-- Delete old data, generate a new data set of random test results, and display it

DELETE FROM [dbo].[subtest_type_1_data];
DELETE FROM [dbo].[test_runs];
GO

EXEC [dbo].[generate_random_subtest_type_1_data] @test_id = '0';
GO

SELECT * FROM [dbo].[test_runs];
SELECT * FROM [dbo].[subtest_type_1_data];

GO

EXAMPLE OUTPUT (current, incorrect)

In the example output below:

  • All parameter combinations within subtest run 0 have the same number (0) of anomalies
  • All parameter combinations within subtest run 1 have the same number (0) of anomalies
  • All parameter combinations within subtest run 2 have the same number (1) of anomaly
  • All parameter combinations within subtest run 3 have the same number (0) of anomalies
  • All parameter combinations within subtest run 4 have the same number (2) of anomalies

I would like each parameter combination's anomaly count to be able to vary independently of any other parameter combination's anomaly count.

    test_id subtest_run_number  parameter_1 parameter_2 anomaly_id  anomaly_magnitude   subtest_type
    ------- ------------------  ----------- ----------- ----------  -----------------   ------------
    0       0                   1           1           0           NULL                subtest_type_1
    0       0                   1           2           0           NULL                subtest_type_1
    0       0                   1           3           0           NULL                subtest_type_1
    0       0                   2           1           0           NULL                subtest_type_1
    0       0                   2           2           0           NULL                subtest_type_1
    0       0                   2           3           0           NULL                subtest_type_1
    0       1                   1           1           0           NULL                subtest_type_1
    0       1                   1           2           0           NULL                subtest_type_1
    0       1                   1           3           0           NULL                subtest_type_1
    0       1                   2           1           0           NULL                subtest_type_1
    0       1                   2           2           0           NULL                subtest_type_1
    0       1                   2           3           0           NULL                subtest_type_1
    0       2                   1           1           1           0.8730268           subtest_type_1
    0       2                   1           2           1           0.5566615           subtest_type_1
    0       2                   1           3           1           0.4599889           subtest_type_1
    0       2                   2           1           1           0.9322677           subtest_type_1
    0       2                   2           2           1           0.3515796           subtest_type_1
    0       2                   2           3           1           0.872755            subtest_type_1
    0       3                   1           1           0           NULL                subtest_type_1
    0       3                   1           2           0           NULL                subtest_type_1
    0       3                   1           3           0           NULL                subtest_type_1
    0       3                   2           1           0           NULL                subtest_type_1
    0       3                   2           2           0           NULL                subtest_type_1
    0       3                   2           3           0           NULL                subtest_type_1
    0       4                   1           1           1           0.6965834           subtest_type_1
    0       4                   1           1           2           0.4588626           subtest_type_1
    0       4                   1           2           1           0.1284888           subtest_type_1
    0       4                   1           2           2           0.4331938           subtest_type_1
    0       4                   1           3           1           0.3083588           subtest_type_1
    0       4                   1           3           2           0.8907238           subtest_type_1
    0       4                   2           1           1           0.4016767           subtest_type_1
    0       4                   2           1           2           0.8041269           subtest_type_1
    0       4                   2           2           1           0.5932015           subtest_type_1
    0       4                   2           2           2           0.5389434           subtest_type_1
    0       4                   2           3           1           0.7058043           subtest_type_1
    0       4                   2           3           2           0.749176            subtest_type_1

EXAMPLE OUTPUT (desired)

Below, I show an example of what desired output might look like. (Here, I show only rows associated with [subtest_run_number] = 0. Of course, what's being demonstrated here holds for all [subtest_run_number] values.)

In the example output below:

  • The parameter combination (1, 1) has one anomaly ([anomaly_id] value is only 1)
  • The parameter combination (1, 2) has zero anomalies ([anomaly_id] value is only 0)
  • The parameter combination (1, 3) has two anomalies ([anomaly_id] values are 1, 2)
  • The parameter combination (2, 1) has one anomaly ([anomaly_id] value is only 1)
  • The parameter combination (2, 2) has zero anomalies ([anomaly_id] value is only 0)
  • The parameter combination (2, 3) has three anomalies ([anomaly_id] values are 1, 2, 3)

The salient point is that the number of anomalies per parameter combination can vary within a given value of [subtest_run_number]. In the incorrect example output shown earlier, the number of anomalies per parameter combination could not vary within a given value of [subtest_run_number].

    test_id subtest_run_number  parameter_1 parameter_2 anomaly_id  anomaly_magnitude   subtest_type
    ------- ------------------  ----------- ----------- ----------  -----------------   ------------
    0       0                   1           1           1           0.5095024           subtest_type_1
    0       0                   1           2           0           NULL                subtest_type_1
    0       0                   1           3           1           0.5062660           subtest_type_1
    0       0                   1           3           2           0.3940517           subtest_type_1
    0       0                   2           1           1           0.6216237           subtest_type_1
    0       0                   2           2           0           NULL                subtest_type_1
    0       0                   2           3           1           0.5802680           subtest_type_1
    0       0                   2           3           2           0.5673455           subtest_type_1
    0       0                   2           3           3           0.5517588           subtest_type_1

MY QUESTION

Is there a way I can continue to use set-based operations but have each parameter combination have its own, independent anomaly count? Or, must I resort to iteration?

1
  • do you happen to use python or R? or what would be your preferred programming language other than T-SQL?
    – mpag
    Nov 2, 2022 at 21:31

1 Answer 1

0

This questions is a bit old, but the concept maybe helpful to others.

You should be able to accomplish this using CROSS JOINS and an OUTER APPLY against your 'How Many Anomalies' logic. The OUTER APPLY ensures the number of anomalies for that set is computed (as opposed to an inner join and testing row-by-row where it could conceivably skip a number)

Here is an example that foregoes Test, but I think you'll get the idea.

/*SETUP DATA*/

DROP TABLE If EXISTS #SubTests
SELECT * INTO #SubTests FROM (
    VALUES 
(1),(2),(3)
) #SubTests(SubTestId)

DROP TABLE If EXISTS #Parameter1
SELECT * INTO #Parameter1 FROM (
    VALUES 
('A'),('B')
) #Parameter1(Param1)

DROP TABLE If EXISTS #Parameter2
SELECT * INTO #Parameter2 FROM (
    VALUES 
('i'),('ii'),('iii')
) #Parameter2(Param2)


DROP TABLE IF EXISTS #Numbers
SELECT top 100 ROW_NUMBER() OVER(ORDER BY (SELECT 1)) -1 as N into #Numbers FROM sys.columns

DECLARE @MaxAnomolies INT = 4


SELECT SubTestId, Param1, Param2, ISNULL(AnomalyNum, 0) AnomolyNum, IIF(AnomalyNum > 0, RAND(CHECKSUM(NewId())),NULL) as Magnitude 
FROM #SubTests 
    CROSS JOIN #Parameter1 
    CROSS JOIN #Parameter2
    OUTER APPLY (SELECT n as AnomalyNum FROM #Numbers WHERE  N < ABS(CHECKSUM(NewId()) % (@MaxAnomolies +1 ))) Anom
ORDER BY 1, 2, 3, 4

Sample Results: |SubTestId |Param1 |Param2 |AnomolyNum |Magnitude |-|-|-|-|-| 1 |A |i |0 |NULL 1 |A |i |1 |0.92109365803847 1 |A |i |2 |0.498503831764117 1 |A |i |3 |0.99798894972308 ...||||

You could bias frequency of occurrence either in the Where clause of the Anom sub-query and adding a column to #numbers. For example, could added the following:

SELECT * FROM (VALUES(0,4),(1,7),(2,8),(3,9)) Numbers(n,f) 
DECLARE @MaxF INT = (SELECT MAX(F) FROM #Numbers WHERE n = @MaxAnomolies)

Along with the updated WHERE clause of

WHERE f < ABS(CHECKSUM(NewId()) % (@MaxF +1 ))

This would produce '0' half the time, '1' 30% of the, '2' and '3' each 10% of the time, on average.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.