时间戳

首页 > 解决方案 > GROUP BY 和 NULL (SQL / BigQuery) 的问题

sql - GROUP BY 和 NULL (SQL / BigQuery) 的问题

问题描述

我有一些不好的数据需要进行一些转换并且正在努力做。

下面是示例数据和所需的输出。

我希望的是，如果 PUNCHIN 为 NULL，但该 EmployeeNum 和 APPLY_DATE 有另一个 PUNCHIN 值，然后使用另一个值。否则，使用 SHIFTSTARTTIME。PUNCHOUT 和 SHIFTENDTIME 也是如此。

最初我想使用 MIN(PUNCHIN) 和 GROUP BY，但 EmployeeNum 和 APPLY_DATE 可以有多个条目，如 EmployeeNum 456 所示

| EmployeeNum |     APPLY_DATE      |       PUNCHIN       |      PUNCHOUT       |   SHIFTSTARTTIME    |    SHIFTENDTIME     |
+-------------+---------------------+---------------------+---------------------+---------------------+---------------------+
|         123 | 2019-06-29T00:00:00 | null                | 2019-06-30T00:55:00 | 2019-06-29T17:00:00 | 2019-06-30T01:00:00 |
|         123 | 2019-06-29T00:00:00 | 2019-06-29T16:54:00 | null                | 2019-06-29T17:00:00 | 2019-06-30T01:00:00 |
|         456 | 2019-12-09T00:00:00 | 2019-12-09T16:15:00 | 2019-12-09T21:15:00 | 2019-12-09T16:15:00 | 2019-12-09T21:15:00 |
|         456 | 2019-12-09T00:00:00 | 2019-12-09T22:00:00 | 2019-12-10T02:00:00 | 2019-12-09T22:00:00 | 2019-12-10T02:00:00 |
|         789 | 2019-12-22T00:00:00 | null                | null                | 2019-12-09T22:00:00 | 2019-12-10T02:00:00 |
+-------------+---------------------+---------------------+---------------------+---------------------+---------------------+ ```

And the output I'm looking for is:

```+-------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| EmployeeNum |     APPLY_DATE      |       PUNCHIN       |      PUNCHOUT       |     SHIFTSTART      |      SHIFTEND       |
+-------------+---------------------+---------------------+---------------------+---------------------+---------------------+
|         123 | 2019-06-29T00:00:00 | 2019-06-29T16:54:00 | 2019-06-30T00:55:00 | 2019-06-29T17:00:00 | 2019-06-30T01:00:00 |
|         456 | 2019-12-09T00:00:00 | 2019-12-09T16:15:00 | 2019-12-09T21:15:00 | 2019-12-09T16:15:00 | 2019-12-09T21:15:00 |
|         456 | 2019-12-09T00:00:00 | 2019-12-09T22:00:00 | 2019-12-10T02:00:00 | 2019-12-09T22:00:00 | 2019-12-10T02:00:00 |
|         789 | 2019-12-22T00:00:00 | 2019-12-09T22:00:00 | 2019-12-10T02:00:00 | 2019-12-09T22:00:00 | 2019-12-10T02:00:00 |
+-------------+---------------------+---------------------+---------------------+---------------------+---------------------+```

标签： sqlgoogle-bigquery

解决方案

嗯。. . 我认为这可以满足您的要求：

select distinct employeenum, apply_date,
       coalesce(punchin, max(punchin) over (partition by employeenum, apply_date), apply_date),
       coalesce(punchout, max(punchout) over (partition by employeenum, apply_date), apply_date)
from t;

推荐阅读