V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
uil330
V2EX  ›  数据库

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

  •  
  •   uil330 · 2022-10-12 17:54:06 +08:00 · 1553 次点击
    这是一个创建于 776 天前的主题,其中的信息可能已经有所发展或是发生改变。

    比如数据是这样子的:

    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 条附言  ·  2022-10-12 18:34:55 +08:00
    找到方法了:
    1 先生成那个包含所有时间,对应时间不存在的项为 NULL 的表
    2 使用 last_value ,抽出最后一个非 null 的值

    具体的 SQL 我有时间补上

    不过这样的话感觉执行效率可能会低,请问有什么高效点的方法么
    12 条回复    2022-10-14 22:28:47 +08:00
    debuggerx
        1
    debuggerx  
       2022-10-12 18:33:30 +08:00
    这个活儿非要用 sql 实现?程序里处理那不就是一行的事
    uil330
        2
    uil330  
    OP
       2022-10-12 18:35:44 +08:00
    @debuggerx 1.50TB 放在 cloud 的数据我想不出比 sql 更有效率的方法了
    sun522198558
        3
    sun522198558  
       2022-10-12 19:13:51 +08:00
    一个变量记录下
    ```sql
    SET @prev = 0;
    SELECT
    IF
    ( `data` IS NOT NULL, @prev := `data`, @prev)
    ```
    zbinlin
        4
    zbinlin  
       2022-10-12 21:36:19 +08:00
    这里是每个小时只能有一条数据还是可以多条?如果第 1 个小时没有数据,那 data 是什么?
    wxf666
        5
    wxf666  
       2022-10-12 21:46:38 +08:00   ❤️ 1
    @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
        6
    shiyanfei5  
       2022-10-12 21:51:35 +08:00
    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
        7
    wxf666  
       2022-10-12 23:17:04 +08:00
    @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
        8
    uil330  
    OP
       2022-10-13 08:55:33 +08:00
    @wxf666 用的是 bigquery

    sql 每个数据库支持的特性都不一样,真让人头大。。。
    sun522198558
        9
    sun522198558  
       2022-10-13 11:09:35 +08:00
    @wxf666 #5 你说这么多,新版本的 mysql 是不支持的窗口。
    只要当前版本能用就行
    sun522198558
        10
    sun522198558  
       2022-10-13 11:09:49 +08:00
    @sun522198558 #9 错了,是旧版本不支持
    wxf666
        11
    wxf666  
       2022-10-14 02:12:39 +08:00
    @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
        12
    shiyanfei5  
       2022-10-14 22:28:47 +08:00
    @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
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   3640 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 04:28 · PVG 12:28 · LAX 20:28 · JFK 23:28
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.