时间戳

首页 > 解决方案 > 将值替换为标志设置为 1 的最后一个值

sql - 将值替换为标志设置为 1 的最后一个值

问题描述

我有一个表，其中每一行包含在某个事件期间更改的所有字段，以及与每个字段关联的标志，以标记该字段是否已更新。为简单起见，我在这里只显示“状态”字段，但它们也是其他几个字段。如果事件未修改给定字段，则该字段设置为空，标志也是如此。

+----+---------------------+--------+---------------------+
| id |        date         | status | flag_changed_status |
+----+---------------------+--------+---------------------+
|  1 | 2020-01-03 19:32:17 | TODO   |                   1 |
|  1 | 2020-01-08 15:46:07 | WIP    |                   1 |
|  1 | 2020-01-08 15:53:53 |        |                     | //this line was generated because another field changed
|  1 | 2020-01-08 15:56:53 |        |                     | //this line was generated because another field changed
|  1 | 2020-01-08 16:02:31 | Done   |                   1 |
+----+---------------------+--------+---------------------+

我的目标是将字段未更改的行的字段值替换为标志等于 1 时的最后一个值，例如 get ：

+----+---------------------+--------+---------------------+
| id |        date         | status | flag_changed_status |
+----+---------------------+--------+---------------------+
|  1 | 2020-01-03 19:32:17 | TODO   |                   1 |
|  1 | 2020-01-08 15:46:07 | WIP    |                   1 |
|  1 | 2020-01-08 15:53:53 | WIP    |                     |
|  1 | 2020-01-08 15:56:53 | WIP    |                     |
|  1 | 2020-01-08 16:02:31 | Done   |                   1 |
+----+---------------------+--------+---------------------+

我知道我想last_value在 Bigquery 中使用分析功能，我尝试了：

SELECT ID_DEMANDE, date, status, 
last_value(status) OVER (ORDER BY flag_changed_status, DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as current_status, flag_changed_status 
FROM table ORDER BY  id, DATE

这个想法是，通过按函数顺序使用标志，标志设置为 null 的行将首先放入，然后 last_value(status) 将是 flag_changed_status 设置为 1 的最后一个值

但这只有在我使用 ROWS BETWEEN UNBOUNDED PRECEDING 和 UNBOUNDED FOLLOWING 时才有效，因为该ORDER BY子句将在窗口框架子句（...之间的行）之前处理，因此对于 flag_changed_status 为空的行，在处理 order by 之后，当前行号为0，因此无界前行和当前行之间的最后一个值始终为空。

有什么方法可以先运行 ROWS BETWEEN UNBOUNDED PRECEDING 和 UNBOUNDED FOLLOWING 然后运行 ​​ORDER BY，以便 last_value(status) 将返回标志设置为 1 的当前行之前的最后一个值？还是有更简单的东西，仍然使用分析功能让我在一个查询中完成所有不同的字段？

编辑：我真的想复制上次设置标志时设置的状态，即使此状态为空，这就是为什么我试图按顺序使用标志。也就是说，如果初始表是：

+----+---------------------+--------+---------------------+
| id |        date         | status | flag_changed_status |
+----+---------------------+--------+---------------------+
|  1 | 2020-01-03 19:32:17 | TODO   |                   1 |
|  1 | 2020-01-08 15:46:07 | null   |                   1 |
|  1 | 2020-01-08 15:53:53 | null   |                null |
|  1 | 2020-01-08 15:56:53 | null   |                null |
|  1 | 2020-01-08 15:57:53 | WIP    |                   1 |
|  1 | 2020-01-08 15:58:53 | null   |                null |
|  1 | 2020-01-08 16:02:31 | Done   |                   1 |
+----+---------------------+--------+---------------------+

我会需要：

+----+---------------------+--------+---------------------+
| id |        date         | status | flag_changed_status |
+----+---------------------+--------+---------------------+
|  1 | 2020-01-03 19:32:17 | TODO   |                   1 |
|  1 | 2020-01-08 15:46:07 | null   |                   1 |
|  1 | 2020-01-08 15:53:53 | null   |                null | // we copy the last status where the flag was 1, and it is null
|  1 | 2020-01-08 15:56:53 | null   |                null |
|  1 | 2020-01-08 15:57:53 | WIP    |                   1 |
|  1 | 2020-01-08 15:58:53 | WIP    |                null | //only this line changes
|  1 | 2020-01-08 16:02:31 | Done   |                   1 |
+----+---------------------+--------+---------------------+

但这似乎太复杂了，所以我将使用自定义状态替换标志设置为 1 的所有空值，然后 @gordon-linoff 建议的简单 last_value(status IGNORE NULLS) 将提供几乎所需的结果

标签： sqlgoogle-bigquery

解决方案

以下是 BigQuery 标准 SQL 

#standardSQL
SELECT * EXCEPT(grp),
  LAST_VALUE(status IGNORE NULLS) OVER (PARTITION BY grp ORDER BY date) AS updated_status
FROM (
  SELECT *,
    COUNTIF(flag_changed_status = 1) OVER(ORDER BY `date`) grp
  FROM `project.dataset.table`
)

如果适用于您的问题的样本数据 - 结果是 

Row id  date                status  flag_changed_status updated_status   
1   1   2020-01-03 19:32:17 TODO    1                   TODO     
2   1   2020-01-08 15:46:07 null    1                   null     
3   1   2020-01-08 15:53:53 null    null                null     
4   1   2020-01-08 15:56:53 null    null                null     
5   1   2020-01-08 15:57:53 WIP     1                   WIP  
6   1   2020-01-08 15:58:53 null    null                WIP  
7   1   2020-01-08 16:02:31 Done    1                   Done

推荐阅读