CODEWITHSUNDEEP
google-bigquery
pythonlearner
pythonlearner

Reputation: 105

SQL: Retain First Value Of the Group in other rows

Trying to retain value of Status in each group until status value changed. For Example: For Status L, Rows number 2,3 should populate L in expected Status Need Suggestion.

Rank Status  Expected_status
1     L      L
2     NULL   L
3     NULL   L
1     M      M
2     NULL   M
1     K      K
2     NULL   K
3     NULL   K

Upvotes: 0

Views: 136

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173210

Below is for BigQuery Standard SQL 

#standardSQL
SELECT *, LAST_VALUE(status IGNORE NULLS) OVER(ORDER BY ts) Expected_status
FROM `project.dataset.table`

If to apply to sample data from your question as in below example 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 ts, 1 rank, 'L' Status UNION ALL
  SELECT 2, 2, NULL UNION ALL
  SELECT 3, 3, NULL UNION ALL
  SELECT 4, 1, 'M' UNION ALL
  SELECT 5, 2, NULL UNION ALL
  SELECT 6, 1, 'K' UNION ALL
  SELECT 7, 2, NULL UNION ALL
  SELECT 8, 3, NULL 
)
SELECT ts, LAST_VALUE(status IGNORE NULLS) OVER(ORDER BY ts) Expected_status
FROM `project.dataset.table`
-- ORDER BY ts

result is 

Row ts  rank    Status  Expected_status  
1   1   1       L       L    
2   2   2       null    L    
3   3   3       null    L    
4   4   1       M       M    
5   5   2       null    M    
6   6   1       K       K    
7   7   2       null    K    
8   8   3       null    K

NOTE: you must have some column in your table that defines order of your rows - usually this is timestamp or day or position, etc. In my example I am assuming you have some timestamp column (I named it ts but yo might have different one).
Having rank column the way you have it in your example - does not help as it does not allow to distinguish rows 2, 5 and 7 between each other!

Upvotes: 1

Related Questions