search

runt18 / google-bigquery

Automatically exported from code.google.com/p/google-bigquery
0 stars 0 forks source link

Allow sub selects for TABLE_DATE_RANGE timestamps #394

Open GoogleCodeExporter opened 8 years ago

GoogleCodeExporter commented 8 years ago 
It would be wonderful if BigQuery supported sub-selects for determining table 
date ranges. Using this functionality we would be able to limit the scope of 
the data in our queries based on the input from the user (we use BigQuery a lot 
in Tableau dashboards).

So for instance, this doesn't work:

SELECT
  *
FROM
  (TABLE_DATE_RANGE(view_events.monthly,
    (SELECT USEC_TO_TIMESTAMP(UTC_USEC_TO_MONTH(sale_start_time)) FROM [sales.all] where sale_id = 6141),
    (SELECT USEC_TO_TIMESTAMP(UTC_USEC_TO_MONTH(sale_start_time)) FROM [sales.all] where sale_id = 4344)))

It would be even more useful if BigQuery also supported TABLE_DATE_LIST with 
support for specific dates in a list instead of ranges. Then we could query on 
data from March 2015 and September 2015 without having to include everything in 
between. Like this:

SELECT
  *
FROM
  (TABLE_DATE_LIST(view_events.monthly,
    (SELECT USEC_TO_TIMESTAMP(UTC_USEC_TO_MONTH(sale_start_time)) FROM [sales.all] where sale_id IN (6141, 4344),
   )

Original issue reported on code.google.com by wouter.c...@vente-exclusive.com on 15 Dec 2015 at 5:18

GoogleCodeExporter commented 8 years ago 
Thanks for the suggestions. I've opened a feature request internally.

You might also ask these questions on Stack Overflow -- someone more familiar 
with Tableau may have an existing workaround. For example, can you issue two 
queries (one to resolve the timestamp for the sale and another to fetch the 
associated view events)?

Original comment by ada...@google.com on 15 Dec 2015 at 7:14

GoogleCodeExporter commented 8 years ago 
Tableau allows for parameters in its custom queries. Unfortunately it currently 
only supports static parameters. We could let the user of a dashboard set the 
dates as parameters, but that brings extra complexity. The user would first 
have to find out himself which dates are relevant. Moreover, the date would 
need to be entered as a string in the correct format that BigQuery understands.

Anyway, thanks for investigating this!

Original comment by wouter.c...@vente-exclusive.com on 15 Dec 2015 at 8:30