如何在BigQuery的特定行下获得第一个值?

Question

我有以下数据集在BigQuery: dataset

当类型为V时，count总是等于零。当类型为V时，我希望列计数获得类型为T的这一行下的第一个值。根据group_id和position列对行进行排序。

这是我想要的最终结果:所需数据集

I tried this

FIRST_VALUE( count )
OVER ( 
PARTITION BY id_group,id_person
ORDER BY
CASE WHEN type LIKE "T" THEN 1 ELSE 0 END DESC,
position
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) as NEW_count

但它总是给我第一个类型为T的计数，当我想要第一个值低于行

Answer 1

我不认为这种情况可以使用导航函数来解决，因为最近的T值位置是不确定的(领先，滞后，第一，最后，第n个值)。

你需要查询相同的数据集，以找到最近的t_value使用子查询。

这里有一个工作示例:

WITH source_data AS (
SELECT 'A1' AS id_group, 'a' AS id_perso, 1 AS position, 'V' AS type, 0 AS count
UNION ALL
SELECT 'A1', 'b', 2, 'V', 0
UNION ALL
SELECT 'A1', 'c', 3, 'T', 13
UNION ALL
SELECT 'A1', 'd', 4, 'V', 0
UNION ALL
SELECT 'A1', 'e', 5, 'T', 5
UNION ALL
SELECT 'A1', 'f', 6, 'T', 7
UNION ALL
SELECT 'A1', 'g', 7, 'V', 0
UNION ALL
SELECT 'A1', 'h', 8, 'V', 0
UNION ALL
SELECT 'A1', 'i', 9, 'V', 0
UNION ALL
SELECT 'A1', 'j', 10,' 'T, 0
)

SELECT *, 
(SELECT count FROM source_data counts WHERE counts.position = 
(SELECT MIN(t_values.position) FROM source_data t_values WHERE t_values.type='T' and t_values.position > source.position)) 
FROM source_data source

如果你需要0而不是null，你可以合并t_value

Answer 2

您可以考虑下面的查询来满足您的需求。

with cte as (
select 'A1' id_group, 'a' id_person, 1 position, 'V'type, 0 count union all 
select 'A1','b',2,'V',0 union all 
select 'A1','c',3,'T',13 union all 
select 'A1','d',4,'V',0 union all 
select 'A1','e',5,'T',5 union all 
select 'A1','f',6,'T',7 union all 
select 'A1','g',7,'V',0 union all 
select 'A1','h',8,'V',0 union all 
select 'A1','i',9,'V',0 union all 
select 'A1','j',10,'T',0 
)
select *,last_value(count_1 ignore nulls) over (order by position desc) new_count,
from (select *,case when type='V' and count=0 then null else count 
end count_1
from cte
)
order by position