提问人：nerdygeek 提问时间：10/24/2023 更新时间：10/25/2023 访问量：33
如何在 Bigquery 中计算指定非 null 行之前的 null 行数
How to count the number of null rows preceding a specified non-null one in Bigquery
问：
我有如下数据：
我打算创建一个 BQ 查询，该查询将计算指定日期之前每个 ID 的“null”月数（在本例中，所有 ID 的指定日期为 2023-08-01;但我稍后希望这样做，因为该日期因 ID 而异，因此如果您有任何建议可以解释这一点，我将不胜感激）
期望的输出是这样的：例如，
在 2023-08-01 之前，ID 111 有 3 个 null 个月，222 有 5 个 null 个月，等等
尝试检查这些，但数据的格式似乎不同
- BigQuery：获取窗口中的第一个非空值？
- 如何从Big Query中的一列值中获取第一个非null值？
- https://www.appsloveworld.com/google-bigquery/24/count-number-of-null-rows-along-with-the-first-non-null-row-and-reset-the-count-a
我认为我得到的最接近的是这个 - 但所需的输出并不完全是我需要的（例如，我没有选项来提及我希望从哪个位置计算前面的空行的确切日期）
答：
1赞
Samuel
10/25/2023
#1
需要几个 CTE。这是示例的原始数据。
计算将来发生的条目，并在列中给出此值。查找 中的下一个非空项。接下来，在这些值之间进行减法。tblhelpernullidcount_nahelpercount_nanr
您只想显示具有非 null 的行。该子句仅筛选最新条目。nrqualify
请注释掉最后两行过滤器。此外，还要查询每个 CTE，以便更好地理解。
With tbl as (
Select *, case row_number() over (order by id, month) when 3 then 132 when 4 then 89 when 8 then 300 when 10 then 114 when 16 then 334 end as nr
from unnest(['111','222'])as id, unnest(generate_date_array(date "2023-01-01",date "2023-08-01",interval 1 month)) as month
)
, helper as (
Select *,
countif(nr is null) over fill count_na
from tbl
window fill as (partition by id order by month desc rows between unbounded preceding and 0 preceding)
order by 1,2
), helper2 as
(
Select *,
last_value(if(nr is null,null,count_na) ignore nulls) over fill as last_count
from helper
window fill as (partition by id order by month desc rows between unbounded preceding and 1 preceding)
order by 1,2
)
Select *,
count_na - last_count as nr_null_months
from helper2
where nr is not null and last_count is not null # comment this out
qualify month=max(month) over (partition by id) # comment this out
order by 1,2
评论
0赞
nerdygeek
10/25/2023
这创造了奇迹，非常感谢
评论