-1

I needed to generate 1000+ random dates over the period of 2 years and the code found here did the job well. However, since I'm visualizing this data, I would prefer if every date in the chosen time period appeared in the dataset at least once, otherwise the line chart keeps jumping too much. How would I go about it? Here the initial code used:

WITH parameters AS (
  SELECT 100 ids_count, DATE '2010-01-01' start_date, DATE '2020-12-31' finish_date
)
SELECT id, DATE_FROM_UNIX_DATE(CAST(start + (finish - start) * RAND() AS INT64)) random_date
FROM parameters, 
UNNEST(GENERATE_ARRAY(1, ids_count)) id,
UNNEST([STRUCT(UNIX_DATE(start_date) AS start, UNIX_DATE(finish_date) AS finish)])
-- ORDER BY id
ppp147
  • 61
  • 4

1 Answers1

0

I do not understand exactly what you mean.

'generate 1000+ random dates over the period of 2 years' and 'without skipping any'.

If I am not mistaken, that would be equivalent to generating every date once, which is k=2*365 or k=365+366 dates, which would be the 2 years in question, and after that generate randomly another N-k dates with the given code.

Basically you just require an SQL code that generates ALL days from the specified two years, and then the rest generate randomly using that code above.

It is rather simple, select each of two years, then all months and all days for each...

sophontosaphes
  • 21
  • 1
  • 5