I’m having a question on how to duplicate rows in a table that generates dates between StartDate and EndDate. Actually, I have a same question as this Duplicating records to fill gap between dates in Google BigQuery but with a different type of table.
So, my table looks like this:
╔═══════════╦══════════════╦══════════════╗ ║ user_name ║ start_date ║ end_date ║ ╠═══════════╬══════════════╬══════════════╣ ║ A ║ '2019-07-01' ║ '2019-07-31' ║ ╚═══════════╩══════════════╩══════════════╝
and I would like the output to be like this:
╔═══════════╦══════════════╦══════════════╦══════════════╗ ║ user_name ║ start_date ║ end_date ║ date ║ ╠═══════════╬══════════════╬══════════════╬══════════════╣ ║ A ║ '2019-07-01' ║ '2019-07-31' ║ '2019-07-01' ║ ╠═══════════╬══════════════╬══════════════╬══════════════╣ ║ A ║ '2019-07-01' ║ '2019-07-31' ║ '2019-07-02' ║ ╠═══════════╬══════════════╬══════════════╬══════════════╣ ║ A ║ '2019-07-01' ║ '2019-07-31' ║ '2019-07-03' ║ ╠═══════════╬══════════════╬══════════════╬══════════════╣ ║ A ║ '2019-07-01' ║ '2019-07-31' ║ '2019-07-04' ║ ╠═══════════╬══════════════╬══════════════╬══════════════╣ ║ ... ║ ... ║ ... ║ ... ║ ╠═══════════╬══════════════╬══════════════╬══════════════╣ ║ A ║ '2019-07-01' ║ '2019-07-31' ║ '2019-07-31' ║ ╚═══════════╩══════════════╩══════════════╩══════════════╝
How should I put it in a query? Thanks in advance.
Advertisement
Answer
Below is for BigQuery Standard SQL
#standardSQL WITH `project.dataset.table` AS ( SELECT 'A' user_name, DATE '2019-07-01' start_date, DATE '2019-07-31' end_date ) SELECT user_name, start_date, end_date, day FROM `project.dataset.table`, UNNEST(GENERATE_DATE_ARRAY(start_date, end_date)) day ORDER BY user_name, day
with result
Row user_name start_date end_date day 1 A 2019-07-01 2019-07-31 2019-07-01 2 A 2019-07-01 2019-07-31 2019-07-02 3 A 2019-07-01 2019-07-31 2019-07-03 . . . 29 A 2019-07-01 2019-07-31 2019-07-29 30 A 2019-07-01 2019-07-31 2019-07-30 31 A 2019-07-01 2019-07-31 2019-07-31