sql - 如何在sql中的运行序列中找到峰值和谷值

Question

所以我在 athena 中有一个数据集，因此出于此目的，您可以将其视为 postgres 数据库。数据样本可以在这个 sql fiddle 中看到.

这是一个示例:

  create table vals (
  timestamp int,
  type varchar(25),
  val int
  );

  insert into vals(timestamp,type, val) 
  values      (10, null, 1),
              (20, null, 2),
              (39, null, 1),
              (40,'p',1),
              (50,'p',2),
              (60,'p',1),
              (70,'v',5),
              (80,'v',6),
              (90,'v',6),
              (100,'v',3),
              (110,null,3),
              (120,'v',6),
              (130,null,3),
              (140,'p',10),
              (150,'p',8),
              (160,null,3),
              (170,'p',1),
              (180,'p',2),
              (190,'p',2),
              (200,'p',1),
              (210,null,3),
              (220,'v',1),
              (230,'v',1),
              (240,'v',3),
              (250,'v',41)               

我想要得到的是一个包含所有值但突出显示“p”的最高值和连续“v”的最低值的数据集。

所以最终我会得到:

   timestamp, type, value, is_peak
    (10, null, 1, null),
    (20, null, 2, null),
    (39, null, 1, null),
    (40,'p',1, null),
    (50,'p',2, 1),
    (60,'p',1, null),
    (70,'v',5, null),
    (80,'v',6, null),
    (90,'v',6, null),
    (100,'v',3, 1),
    (110,null,3, null),
    (120,'v',6, 1),
    (130,null,3, null),
    (140,'p',10, 1),
    (150,'p',8, null),
    (160,null,3, null),
    (170,'p',1, null),
    (180,'p',2, 1),
    (190,'p',2, null), -- either this record or 180 would be fine
    (200,'p',1, null),
    (210,null,3, null),
    (220,'v',1, 1), -- again either this or 230
    (230,'v',1, null),
    (240,'v',3, null),
    (250,'v',41, null) 

is peak 有很多类型的选择，如果它是某种 denserank 或递增的数字就好了。这样我就可以确信在连续的集合中，“标记”的值是最高值或最低值。

祝你好运

注意:峰值的最大值或谷值的最小值可以在连续集合中的任意位置，但一旦类型发生变化，我们就会重新开始。

Answer 1

有一个小技巧可以用来解决像这个这样的间隙和孤岛问题。

通过从一个值的 row_number 中减去一个 row_number，您可以获得一些排名。

对于某些用途，此方法有一些缺点。
但它适用于这种情况。

一旦计算出排名，外部查询中的其他窗口函数就可以使用它。
为此，我们可以再次使用 row_number。但是根据需要，您可以改用 DENSE_RANK 或 MIN & MAX 的窗口函数。

然后我们将它们包装在一个 CASE 中，以根据类型实现不同的逻辑。

select timestamp, type, val, 
(case 
 when type = 'v' and row_number() over (partition by (rn1-rn2), type order by val, rn1) = 1 then 1
 when type = 'p' and row_number() over (partition by (rn1-rn2), type order by val desc, rn1) = 1 then 1
 end) is_peak
-- , rn1, rn2, (rn1-rn2) as rnk
from
(
  select timestamp, type, val,
   row_number() over (order by timestamp) as rn1,
   row_number() over (partition by type order by timestamp) as rn2
  from vals
) q
order by timestamp;

您可以测试 SQL Fiddle here

返回:

timestamp   type    val     is_peak
---------   ----    ----    -------
10          null    1       null
20          null    2       null
39          null    1       null
40          p       1       null
50          p       2       1
60          p       1       null
70          v       5       null
80          v       6       null
90          v       6       null
100         v       3       1
110         null    3       null
120         v       6       1
130         null    3       null
140         p       10      1
150         p       8       null
160         null    3       null
170         p       1       null
180         p       2       1
190         p       2       null
200         p       1       null
210         null    3       null
220         v       1       1
230         v       1       null
240         v       3       null
250         v       41      null