Reputation: 105
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
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