BigQuery quantiles into Data Studio

Question

I have some timeseries IOT that is collected minutely, but Data Studio only seems to support Daily aggregations. This would be fine if I could also get min/max/quantile in a timeseries chart. I cannot seem to find a way to get quantile data into a chart though. Is there a way to get Big Query quantile results into a Data Studio timeseries chart?

Elliott Brossard Elliott Brossard · Accepted Answer · 2017-07-08T18:16:18

Perhaps something like this would work? You would need to input a custom query and uncheck "Use Legacy SQL":

SELECT
  date,
  min_value,
  max_value,
  quantile_value,
  quantile
FROM (
  SELECT
    date,
    MIN(measurement) AS min_value,
    MAX(measurement) AS max_value,
    APPROX_QUANTILES(measurement, 100) AS quantiles
  FROM YourTable
  GROUP BY date
)
CROSS JOIN UNNEST(quantiles) AS quantile_value
WITH OFFSET quantile;

This gives you per-day min, max, and quantiles based on the assumption that you have some kind of column with measurements.

BigQuery quantiles into Data Studio

1 Answers