Created
October 29, 2020 14:11
-
-
Save davidtedfordholt/a0c50134e699d7b8d91011ad9d1cf1e8 to your computer and use it in GitHub Desktop.
BigQuery - "random" number within a specified percentage of a number
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- implementation | |
WITH input AS ( | |
SELECT * | |
FROM UNNEST([100,100,100,100,100,100,100,100,100,100]) x | |
JOIN UNNEST([.4,.4,.4,.4,.4,.4,.4,.4,.4,.4]) p | |
) | |
SELECT x * (1 + (-p + (RAND() * (2 * p)))) AS rand_within_p_percent_of_x | |
FROM input; | |
-- verification of concept | |
WITH input AS ( | |
SELECT * | |
FROM UNNEST([100,100,100,100,100,100,100,100,100,100]) x | |
JOIN UNNEST([.4,.4,.4,.4,.4,.4,.4,.4,.4,.4]) p | |
), | |
calcs AS ( | |
SELECT x * (1 + (-p + (RAND() * (2 * p)))) AS rand_within_p_percent_of_x | |
FROM input | |
) | |
SELECT min(rand_within_p_percent_of_x) AS min, max(rand_within_p_percent_of_x) AS max | |
FROM calcs |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I'm aware that
p
is not a percentage, but I dislike multiplying things by 100 just to divide them by 100 later.