sql - 如何在某个值差距之前识别每组的行？

Question

我想根据 PostgreSQL 中相邻行之间另一列值的差异来更新表中的某一列。

这是一个测试设置：

CREATE TABLE test(
   main INTEGER,
   sub_id INTEGER,
   value_t INTEGER);

INSERT INTO test (main, sub_id, value_t)
VALUES
    (1,1,8),
    (1,2,7),
    (1,3,3),
    (1,4,85),
    (1,5,40),
    (2,1,3),
    (2,2,1),
    (2,3,1),
    (2,4,8),
    (2,5,41);

我的目标是main通过按. 在达到阈值之前，我想通过用值填充列来标记每个通过的行和条件所在的行，例如。sub_id 1diffsub_idFALSEnewval1

我应该使用循环还是有更智能的解决方案？

伪代码中的任务描述：

FOR i in GROUP [PARTITION BY main ORDER BY sub_id]:
    DO until diff > 10 OR diff <-10
        SET newval = 1 AND LEAD(newval) = 1

标签： sqlpostgresqlsql-updatewindow-functionsgaps-and-islands

Answer 1

基本的SELECT

尽可能快：

SELECT *, bool_and(diff BETWEEN -10 AND 10) OVER (PARTITION BY main ORDER BY sub_id) AS flag
FROM (
   SELECT *, value_t - lag(value_t, 1, value_t) OVER (PARTITION BY main ORDER BY sub_id) AS diff
   FROM   test
   ) sub;

细点

• 你的思维模型是围绕窗口函数演化的lead()。但它的对应物lag()在此目的上更有效，因为在包含大间隙之前的行时没有一个错误。或者，使用lead()反向排序顺序 ( ORDER BY sub_id DESC)。

• 为了避免NULL分区中的第一行，value_t默认提供第三个参数，这使得差异0而不是 NULL。两者都lead()具有lag()这种能力。

• diff BETWEEN -10 AND 10比@diff < 11（更清晰，更灵活）略快。（@作为“绝对值”运算符，相当于abs()函数。）

• bool_or()或者bool_and()在外部窗口中，将所有行标记为最大差距可能是最便宜的。

您的UPDATE

在达到阈值之前，我想通过用值FALSE填充列来标记每个通过的行和条件所在的一行，newval例如1。

再次，尽可能快。

UPDATE test AS t
SET    newval = 1
FROM  (
   SELECT main, sub_id
        , bool_and(diff BETWEEN -10 AND 10) OVER (PARTITION BY main ORDER BY sub_id) AS flag
   FROM (
      SELECT main, sub_id
           , value_t - lag(value_t, 1, value_t) OVER (PARTITION BY main ORDER BY sub_id) AS diff
      FROM   test
      ) sub
   ) u
WHERE (t.main, t.sub_id) = (u.main, u.sub_id)
AND    u.flag;

细点

• 在单个查询中计算所有值通常比相关子查询快得多。

• 添加的 WHERE 条件AND u.flag确保我们只更新实际需要更新的行。
  如果某些行在 中可能已经具有正确的值newval，请添加另一个子句以避免那些空更新：AND t.newval IS DISTINCT FROM 1 请参阅：

  • 我如何（或我可以）在多列上选择 DISTINCT？

• SET newval = 1分配一个常数（即使在这种情况下我们可以使用实际计算的值），这有点便宜。

db<>在这里摆弄