CODEWITHSUNDEEP
google-bigquery
user1311888
user1311888

Reputation: 793

With daily tables on BigQuery, how can I query rolling 12 months?

I have daily tables in BigQuery, (table_name_yyyymmdd). How can I write a view that will always query the rolling 12 months of data?

Upvotes: 2

Views: 1095

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

As an example:

Save below query as a view (let's name it - view_test - I assume it in the same dataset as tables) 

#standardSQL
SELECT PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) as table, COUNT(1) as rows_count
FROM `yourProject.yourDataset.table_name_*`
WHERE _TABLE_SUFFIX 
         BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 13 DAY) ) 
             AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) )
GROUP BY 1

Now you can use it as below for example: 

#standardSQL
SELECT * 
FROM `yourProject.yourDataset.view_test`

So, this views referencing last 12 full days
You can change DAY to MONTH to have 12 months instead

Hope you got an idea

If needed this can easily be "translated" to Legacy SQL (make sure the view and query that calls that view are using the same SQL version/dialect)
Note: Google recommends migrate to Standard SQL whenever it is possible!

Upvotes: 2

rtbf
rtbf

Reputation: 1587

You could use function TABLE_DATE_RANGE which according to doc (https://cloud.google.com/bigquery/docs/reference/legacy-sql#table-date-range) :

Queries multiple daily tables that span a date range.

like below:

SELECT *FROM TABLE_DATE_RANGE(data_set_name.table_name,
                    TIMESTAMP('2016-01-01'),
                    TIMESTAMP('2016-12-31'))

as there is currently no option to parametrise your view programatically you need to generate your queries/views by some other tool/code

Upvotes: 1

Related Questions