时间戳

首页 > 解决方案 > 如何遍历 SQL 表并选择数据中的峰值？

sql - 如何遍历 SQL 表并选择数据中的峰值？

问题描述

可以说我有下表：

Id|name|spike|timestamp
1 |John|15   |111
2 |Jim |12   |112
3 |Jeff|13   |113
4 |Joe |4    |114
5 |Jess|0    |115
6 |Jill|0    |116
7 |Jey |13   |117
8 |Joy |15   |118
9 |Jess|14   |119
10|Joe |0    |120

我需要遍历表并选择数据，spike > 10并将行分成不同的数据集。对顶层表的可接受查询应导致：

Id|name|spike|timestamp
1 |John|15   |111
2 |Jim |12   |112
3 |Jeff|13   |113

和

Id|name|spike|timestamp
7 |Jey |13   |117
8 |Joy |15   |118
9 |Jess|14   |119

我需要处理表中的所有尖峰。

编辑：我不知道表格中的岛屿数量或它们相距多远。

标签： sqlpostgresql

解决方案

如果您不知道岛屿的数量，请使用我的动态方法解决方案。这是演示。

with cte as
(
    select
        *,
        id - row_number() over (order by id) as rnk
    from myTable
    where spike > 10
),
islands as
(
  select
      *,
      (select count(distinct rnk)::int from cte) as total_islands
  from cte
),
buckets as
(
  select
    Id,
    name,
    spike,
    timestamp,
    NTILE(total_islands) Over (Order by id) as island
  from islands
)  

select *
from buckets
where island = 2

对于您的情况，您可以直接使用窗口函数NTILE，它将您的数据分成两部分。这是演示。

首先创建一个cte 

with cte as
(
  select
    Id,
    name,
    spike,
    timestamp,
    NTILE(2) Over (Order by id) as nums
  from myTable
  where spike > 10
)

然后运行第一个查询以获取第一部分

select  
    Id,
    name,
    spike,
    timestamp
from cte
where nums = 1;

输出：

| id  | name | spike | timestamp |
| --- | ---- | ----- | --------- |
| 1   | John | 15    | 111       |
| 2   | Jim  | 12    | 112       |
| 3   | Jeff | 13    | 113       |

现在运行第二个查询以获取第二部分

select  
    Id,
    name,
    spike,
    timestamp
from cte
where nums = 2;

输出：

| id  | name | spike | timestamp |
| --- | ---- | ----- | --------- |
| 7   | Jey  | 13    | 117       |
| 8   | Joy  | 15    | 118       |
| 9   | Jess | 14    | 119       |

推荐阅读