使用 SQL 来处理某个瞬发的数据，想整理每行是每小时的输出的列表，如果当前小时没数据就用最后一次有数据的时间

[uil330]

比如数据是这样子的：

id	data	time
1	10	00:03
1	50	04:23
1	20	07:03

输出的表格是：

id	data	time
1	10	01:00
1	10	02:00
1	10	03:00
1	10	04:00
1	50	05:00
1	50	06:00
1	50	07:00
1	20	08:00

现在能做到用 join ，当前小时数据不存在的时候添加 0 进去：

id	data	time
1	10	01:00
1	0	02:00
1	0	03:00
1	0	04:00
1	50	05:00
1	0	06:00
1	0	07:00
1	20	08:00

但是我想要的是添加上一个值而不是 0 。

求问怎么解？

找到方法了:
1 先生成那个包含所有时间，对应时间不存在的项为 NULL 的表
2 使用 last_value ，抽出最后一个非 null 的值

具体的 SQL 我有时间补上

不过这样的话感觉执行效率可能会低，请问有什么高效点的方法么

[debuggerx]

这个活儿非要用 sql 实现？程序里处理那不就是一行的事

[uil330]

@debuggerx 1.50TB 放在 cloud 的数据我想不出比 sql 更有效率的方法了

[sun522198558]

一个变量记录下
```sql
SET @prev = 0;
SELECT
IF
( `data` IS NOT NULL, @prev := `data`, @prev)
```

[zbinlin]

这里是每个小时只能有一条数据还是可以多条？如果第 1 个小时没有数据，那 data 是什么？

[wxf666]

@sun522198558 对于 `MySQL`，官方不建议这样使用 *（两周前就有人 [发帖]( /t/883301 ) 说赋值异常的）*

- 『在同一条语句内赋值和读取一个用户变量』的行为未定义
- 『在 `SELECT` 中赋值用户变量』已被弃用，未来会移除


@uil330 你是啥数据库啊？

我看 `MySQL` 的 `LAG()`、`LAST_VALUE()` 等窗口函数，都还不支持 `IGNORE NULLS`

`SQLite` 也还不支持在 `LAG()` 等窗口函数上使用 `FILTER (WHERE xxx IS NOT NULL)`，但能用在聚合函数上

所以用 `SQLite` 写了试试：

*（ V 站排版原因，开头有全角空格。若要复制运行，记得删除）*

```sqlite
WITH
　 data(id, data, time) AS (
　　 VALUES
　　　(1, 10, '00:03'),
　　　(1, 50, '04:23'),
　　　(1, 20, '07:03')
　),
　
　 ids(id) AS (
　　 SELECT DISTINCT id
　　　 FROM data
　),
　
　 time(hour) AS (
　　 SELECT value
　　　 FROM json_each('[1, 2, 3, 4, 5, 6, 7, 8]')
　),
　
　 formated(id, hour, data) AS (
　　 SELECT id, strftime('%H', time) + 1, data
　　　 FROM data
　)

-- 按 id 分组，hour 为顺序，窗口范围为 [组内第一行, 当前行]，并过滤掉 NULL 值，
-- 剩余值合并成 json 数组，然后取数组最后一位（这个数组至多 24 个数嘛，不算大）
SELECT id,
　　　 json_group_array(data) FILTER(WHERE data IS NOT NULL) OVER win ->> '$[#-1]' data,
　　　 format('%02d:00', hour) time
　 FROM ids
　 JOIN time
　 LEFT JOIN formated USING(id, hour)
WINDOW win AS (PARTITION BY id ORDER BY hour);
```

[shiyanfei5]

1.建议不要变为 0 ，变为 null
2.思路：可通过 pg 语法
2.1.select 里新增一个字段，使用 lag(data,1,-999999) over(partition by id order by time) 永远取上一条记录的值 为字段 last_val
2.2 你已实现添加值为 0 ，此处建议把 0 改为 null ，case when 一下 如果 data 字段当前值为 null 或，则取 last_val ，否则就是当前值
。。懒得写了，要么就上存储过程

[wxf666]

@shiyanfei5 我查了查 `PostgreSQL` 关于窗口函数的 [文档]( https://www.postgresql.org/docs/current/functions-window.html )，pg 也不支持使用 `IGNORE NULLS` 取上一个非 `NULL` 值呀：

> The SQL standard defines a RESPECT NULLS or IGNORE NULLS option for lead, lag, first_value, last_value, and nth_value. This is not implemented in PostgreSQL: the behavior is always the same as the standard's default, namely RESPECT NULLS

你是咋做的呢？

[uil330]

@wxf666 用的是 bigquery

sql 每个数据库支持的特性都不一样，真让人头大。。。

[sun522198558]

@wxf666 #5 你说这么多，新版本的 mysql 是不支持的窗口。
只要当前版本能用就行

[sun522198558]

@sun522198558 #9 错了，是旧版本不支持

[wxf666]

@uil330

> 1.50TB 放在 cloud 的数据我想不出比 sql 更有效率的方法了

这是要取回本地嘛？还是更新 /追加云端的数据？

若取回本地的话，写个脚本应该会更快吧？*（至少，稀疏数据传得会快些？）*



@sun522198558 数据库新手，好奇想问下，连 1~2 MB 的 `SQLite` 功能都开始丰富多样起来了，为啥还坚持旧版本 `MySQL` 呢？

未定义行为，摸透了也确实能用 *（反正我没摸透，不知为啥那个帖子里的赋值是异常的）*

不支持窗口函数，那就用基础 `SQL` 吧：*（感觉会比窗口函数慢）*

```sqlite
WITH
　 data(id, data, time) AS (
　　 VALUES
　　　(1, 10, '00:03'),
　　　(1, 50, '04:23'),
　　　(1, 20, '07:03')
　),

　 ids(id) AS (
　　 SELECT DISTINCT id
　　　 FROM data
　),

　 time(time) AS (
　　 SELECT format('%02d:00', value)
　　　 FROM json_each('[1, 2, 3, 4, 5, 6, 7, 8]')
　)

-- 方法一
SELECT i.id, t.time,
　　　 (SELECT data
　　　　　 FROM data
　　　　 WHERE id = i.id
　　　　　 AND time < t.time
　　　　 ORDER BY time DESC
　　　　 LIMIT 1) data,
　　　 NULL useless
　 FROM ids i
　 JOIN time t

UNION ALL
VALUES ('----', '----', '----', '----')
UNION ALL

-- 方法二
SELECT i.id, t.time, d.data, MAX(d.time) useless
　 FROM ids i
　 JOIN time t
　 LEFT JOIN data d ON i.id = d.id AND t.time > d.time
GROUP BY i.id, t.time;
```

[shiyanfei5]

@wxf666 仔细看了一下，我写的思路还有点问题。 用 sum over()把，首先分区有序累加，按照 id 为粒度获取其累加的值。。原则上累加的值 如果相同且 id 相同，那么他们划分为同一个组里。
然后只要 id 相同且在同一个组，由于其 data 为 0 ，所以直接 再 sum over 即可

select
id,
data,
time,
sum(data) over( partition by id,group_sign order by time) as group_sign

from
(
select
id,
data,
time,
sum(data) over( partition by id order by time) as group_sign
from ta
) t1