Bigquery Lag navigation function ignore nulls

user_4893099 0
Viewed 1086

I have a table as below:

WITH
  table1 AS (
  SELECT
    TIMESTAMP '2016-10-18 2:51:45' AS ts,
    'AB4' AS packId,
    FALSE AS stat,
    'x' AS position
  UNION ALL
  SELECT
    TIMESTAMP '2016-10-18 2:54:11',
    'AB4',
    FALSE,
    'x'
  UNION ALL
  SELECT
    TIMESTAMP '2016-10-18 3:01:17',
    'AB4',
    FALSE,
    'x'
  UNION ALL
  SELECT
    TIMESTAMP '2016-10-18 3:05:42',
    'AB4',
    TRUE,
    NULL
  UNION ALL
  SELECT
    TIMESTAMP '2016-10-18 3:06:24',
    'AB4',
    FALSE,
    'x'
  UNION ALL
  SELECT
    TIMESTAMP '2016-10-18 3:06:36',
    'AB4',
    TRUE,
    'y'
  UNION ALL
  SELECT
    TIMESTAMP '2016-10-18 3:07:41',
    'AB4',
    FALSE,
    'x')
SELECT
  ts,
  packId,
  stat,
  position,
  LAG(position) OVER prev_pos AS previous_position1,
  LAG(position,2) OVER prev_pos AS previous_position2
FROM
  table1
WINDOW
  prev_pos AS (
  PARTITION BY
    packId
  ORDER BY
    ts)

And results look like this:

enter image description here

I would like to get previous value (from column position) which is not null , as shown in the result table, I want those null values be the previous non-null which is 'x' for both of them in this example. (There are thousands of rows with null positions so last non-null position for current row could be thousand rows before it )

In summary: how to ignore nulls for Lag in bigquery?

google-bigquery window-functions sql
2 Answers

Below is for BigQuery Standard SQL

WITH table1 AS (
  SELECT TIMESTAMP '2016-10-18 2:51:45' AS ts, 'AB4' AS packId, FALSE AS stat, 'x3' AS position UNION ALL
  SELECT TIMESTAMP '2016-10-18 2:54:11', 'AB4', FALSE, 'x2' UNION ALL
  SELECT TIMESTAMP '2016-10-18 3:01:17', 'AB4', FALSE, 'x1' UNION ALL
  SELECT TIMESTAMP '2016-10-18 3:05:42', 'AB4', TRUE, NULL UNION ALL
  SELECT TIMESTAMP '2016-10-18 3:06:24', 'AB4', FALSE, 'x' UNION ALL
  SELECT TIMESTAMP '2016-10-18 3:06:36', 'AB4', TRUE, 'y' UNION ALL
  SELECT TIMESTAMP '2016-10-18 3:07:41', 'AB4', FALSE, 'x'
)
SELECT
  ts,
  packId,
  stat,
  position,
  NTH_VALUE(position, 1 IGNORE NULLS) OVER prev_pos AS previous_position1,
  NTH_VALUE(position, 2 IGNORE NULLS) OVER prev_pos AS previous_position2
FROM  table1
WINDOW prev_pos AS 
  (PARTITION BY packId ORDER BY ts DESC ROWS BETWEEN 1 FOLLOWING and UNBOUNDED FOLLOWING)
-- ORDER BY ts

with output

enter image description here

As you can see no subqueries needed - and the ONLY change to your original query is

  1. replacing LAG(position) and LAG(position,2) with respectively - NTH_VALUE(position, 1 IGNORE NULLS) and NTH_VALUE(position, 2 IGNORE NULLS) and

  2. adding proper framing clause to prev_pos window

user_52219440

BigQuery makes this a bit hard. One method is to enumerate the groups where you have position values. Then use a window frame specification to get the value from the previous group and the group before that.

The trick is assigning the groups. The simplest method is to do a count of the non-NULL values on or after each row. This assigns each row with a NULL value to the next group. However, the group numbers are in reverse order.

Here is the resulting query:

SELECT t1.*,
       MAX(position) OVER (PARTITION BY packId ORDER BY grp DESC RANGE BETWEEN 1 preceding and 1 preceding) as prev1,
       MAX(position) OVER (PARTITION BY packId ORDER BY grp DESC RANGE BETWEEN 2 preceding and 2 preceding) as prev2,
FROM (SELECT t1.*,
             COUNT(position) OVER (PARTITION BY packid ORDER BY ts DESC) as grp
      FROM table1 t1 
     ) t1
user_11440350
Related