Filling missing dates in BigQuery (SQL) without creating a new calendar

user_9640828 0
Viewed 5365

I am trying to create a SQL so I can make a time series chart in Google Data Studio with connection of BigQuery. You can see my SQL below.

WITH 
CTE_1 AS
(SELECT ID, Date, Min_Predict, Max_Predict, Interval
,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date) AS row_num
FROM
    table),
    CTE_2 AS
(SELECT Date, Min_Predict, Max_Predict,
SUM(Min_Predict) OVER (ORDER BY Date) AS Min,
SUM(Max_Predict) OVER (ORDER BY Date) AS Max
FROM CTE_1
WHERE
    row_num = 1 AND Interval = 'A')

SELECT Date, Min, Max
From CTE_2
GROUP BY Date, Min, Max
ORDER BY Date

I get this table as a result. 

Row ProgressDate            EstMin  EstMax  
1   2017-07-21T00:00:00Z    0.125   0.25
2   2017-07-24T00:00:00Z    5.125   5.375
3   2017-07-25T00:00:00Z    8.75    10.25
4   2017-07-26T00:00:00Z    10.0    12.0
5   2017-07-27T00:00:00Z    10.5    12.75
6   2017-08-01T00:00:00Z    15.25   19.125
7   2017-08-02T00:00:00Z    15.5    19.375
8   2017-08-05T00:00:00Z    16.25   20.625

As you can see I have missing dates e.g. between 21.07 and 24.07. How can I fill those missing dates with the data of previous day? Because in data studio, I have missing data on those days which I can equal them too 0 but I don't want this.

missing-data google-bigquery sql
1 Answers

Below is for BigQuery Standard SQL and built off of your current result 

#standardSQL
WITH your_current_result AS (
  ......
), days AS (
  SELECT day
  FROM (
    SELECT 
      MIN(DATE(TIMESTAMP(ProgressDate))) min_dt, 
      MAX(DATE(TIMESTAMP(ProgressDate))) max_dt
    FROM your_current_result
  ), UNNEST(GENERATE_DATE_ARRAY(min_dt, max_dt)) day
)
SELECT day, 
  LAST_VALUE(EstMin IGNORE NULLS) OVER(ORDER BY day) EstMin,
  LAST_VALUE(EstMax IGNORE NULLS) OVER(ORDER BY day) EstMax
FROM days
LEFT JOIN your_current_result
ON day = DATE(TIMESTAMP(ProgressDate))
-- ORDER BY day

you can test, play with above using example of output in your question 

#standardSQL
WITH your_current_result AS (
  SELECT '2017-07-21T00:00:00Z' ProgressDate, 0.125 EstMin, 0.25 EstMax UNION ALL
  SELECT '2017-07-24T00:00:00Z', 5.125, 5.375 UNION ALL
  SELECT '2017-07-25T00:00:00Z', 8.75, 10.25 UNION ALL
  SELECT '2017-07-26T00:00:00Z', 10.0, 12.0 UNION ALL
  SELECT '2017-07-27T00:00:00Z', 10.5, 12.75 UNION ALL
  SELECT '2017-08-01T00:00:00Z', 15.25, 19.125 UNION ALL
  SELECT '2017-08-02T00:00:00Z', 15.5, 19.375 UNION ALL
  SELECT '2017-08-05T00:00:00Z', 16.25, 20.625 
), days AS (
  SELECT day
  FROM (
    SELECT 
      MIN(DATE(TIMESTAMP(ProgressDate))) min_dt, 
      MAX(DATE(TIMESTAMP(ProgressDate))) max_dt
    FROM your_current_result
  ), UNNEST(GENERATE_DATE_ARRAY(min_dt, max_dt)) day
)
SELECT day, 
  LAST_VALUE(EstMin IGNORE NULLS) OVER(ORDER BY day) EstMin,
  LAST_VALUE(EstMax IGNORE NULLS) OVER(ORDER BY day) EstMax
FROM days
LEFT JOIN your_current_result
ON day = DATE(TIMESTAMP(ProgressDate))
ORDER BY day

with result 

Row day         EstMin  EstMax   
1   2017-07-21  0.125   0.25     
2   2017-07-22  0.125   0.25     
3   2017-07-23  0.125   0.25     
4   2017-07-24  5.125   5.375    
5   2017-07-25  8.75    10.25    
6   2017-07-26  10.0    12.0     
7   2017-07-27  10.5    12.75    
8   2017-07-28  10.5    12.75    
9   2017-07-29  10.5    12.75    
10  2017-07-30  10.5    12.75    
11  2017-07-31  10.5    12.75    
12  2017-08-01  15.25   19.125   
13  2017-08-02  15.5    19.375   
14  2017-08-03  15.5    19.375   
15  2017-08-04  15.5    19.375   
16  2017-08-05  16.25   20.625
user_52219440
Related