Skip to content

Instantly share code, notes, and snippets.

@davidtedfordholt
Created October 29, 2020 14:11
Show Gist options
  • Save davidtedfordholt/a0c50134e699d7b8d91011ad9d1cf1e8 to your computer and use it in GitHub Desktop.
Save davidtedfordholt/a0c50134e699d7b8d91011ad9d1cf1e8 to your computer and use it in GitHub Desktop.
BigQuery - "random" number within a specified percentage of a number
-- 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
@davidtedfordholt
Copy link
Author

I'm aware that p is not a percentage, but I dislike multiplying things by 100 just to divide them by 100 later.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment