T-SQL Random Output of Two Values-CodePudding

I have a table of users that are owned by various Owners. One person left and the list of users needs to be split up between two owners. How would you use SQL to randomly assign the users between two ownerIds . For example, a function that theoretically behaves like below

UPDATE Users
set OwnerId = RAND(64,72)
Where OwnerId = 37

We are utilizing SQL Server 2012. Tried looking at a random function between two users but is unable to find anything specific. Thank you,

CodePudding user response：

You can use CHECKSUM(NEWID()) which returns an integer that is either odd or even, so then just check mod 2 (% 2).

UPDATE dbo.Users
   SET OwnerId = CASE 
   WHEN CHECKSUM(NEWID()) % 2 = 0 THEN 64 ELSE 72 END
WHERE OwnerId = 37;

Working example in this fiddle.

If you want to be cryptic/clever or play code golf, then something like this can work in this very specific example:

UPDATE dbo.Users 
  SET OwnerId = 64   (CHECKSUM(NEWID()) % 2 * 8)
WHERE OwnerId = 37;

The reason this works better than RAND() is because RAND() is only evaluated once, whereas NEWID() is evaluated for every row.

Following Larnu's recommendation, though, you could also do this:

WITH cte AS
(
  SELECT ID, OwnerId, rn = ROW_NUMBER() OVER (ORDER BY ID)
  FROM dbo.Users
  WHERE OwnerId = 37
)
UPDATE cte SET OwnerId = 64   (rn % 2 * 8);

Working example in this fiddle. This will produce a more even (but perhaps too predictable) distribution, it just can't be exactly even if there are an odd number of existing matching rows.

CodePudding user response：

First let's discuss how to provide example data. One of the better ways is to provide the SQL to define an object and then populate it with data. In your case something like this would use useful:

DECLARE @users TABLE (UserID INT, OwnerID INT)
INSERT INTO @users (UserID, OwnerID) VALUES
(1, 100),(2, 100),(3, 100),(4, 100),(5, 100),(6, 100),(7, 100),(8, 100),(9, 100),(10, 100)

Now we can use that to provide a demo. If you truly want to randomly assign between two users, it's a bit more complicated. To divide the users evenly between two new owners you could do something like this

SELECT *, CASE WHEN UserID % 2 = 0 THEN 101 ELSE 102 END AS NewOwner
  FROM @users

All we're doing here is finding the modulo of the UserID to look for it being 0. If the userID can be evenly divided by 2 then Owner 101 gets the user, otherwise Owner 102 gets them.

UserID  OwnerID NewOwner
------------------------
1       100     102
2       100     101
3       100     102
4       100     101
5       100     102
6       100     101
7       100     102
8       100     101
9       100     102
10      100     101

If you want to split between more users, increase the modulo and provide more WHEN options:

SELECT *, CASE WHEN UserID % 3 = 0 THEN 101 
               WHEN UserID % 3 = 1 THEN 102 
               ELSE 103 END AS NewOwner
  FROM @users

UserID  OwnerID NewOwner
------------------------
1       100     102
2       100     103
3       100     101
4       100     102
5       100     103
6       100     101
7       100     102
8       100     103
9       100     101
10      100     102

To make the split actually random we can leverage the RAND function. This isn't usually random for each row, but we can bully it a bit.

SELECT *, CASE WHEN (ROUND(((3 - 1 -1) * RAND(CAST(NEWID() AS VARBINARY))   1), 0) - 1) = 0 THEN 101 ELSE 102 END AS NewOwner
  FROM @users

UserID  OwnerID NewOwner
1       100     101
2       100     101
3       100     101
4       100     102
5       100     102
6       100     101
7       100     102
8       100     101
9       100     102
10      100     102

We can add additional values like this: (note the extra WHEN and random sequence range increased to 4)

SELECT *, CASE WHEN (ROUND(((4 - 1 -1) * RAND(CAST(NEWID() AS VARBINARY))   1), 0) - 1) = 0 THEN 101
               WHEN (ROUND(((4 - 1 -1) * RAND(CAST(NEWID() AS VARBINARY))   1), 0) - 1) = 1 THEN 102
               ELSE 103 END AS NewOwner
  FROM @users

UserID  OwnerID NewOwner
------------------------
1       100     102
2       100     103
3       100     101
4       100     101
5       100     101
6       100     103
7       100     102
8       100     103
9       100     103
10      100     103