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:
x
╔═══════════╦══════════════╦══════════════╗║ 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
#standardSQLWITH `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, dayFROM `project.dataset.table`, UNNEST(GENERATE_DATE_ARRAY(start_date, end_date)) dayORDER 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