SQL - 查询帮助:查找局部最大值 [英] SQL - Query Help: Finding a Local Maximum

问题描述

我有一个表格，其中包含图表中的数据.

I have a table which has data from a graph.

例如

index  value
0      3
1      5
2      7
3      6
4      8
5      9
6      12
7      11
8      10
9      14
10     13

我需要一个查询，该查询返回值处于局部最大值的结果，即特定索引处的值大于 index+1 和 index-1 处的值.

I need to a query that returns the results where the value is at a local maximum, i.e. the value at a particular index is greater than the value at index+1 and index-1.

因此对于示例集，它应该返回索引列表:2、6、9 对应于值 7、12、14.

So for the example set, it should return the list of indexes: 2, 6, 9 corresponding to values 7, 12, 14.

我在 SQLite 中使用 PHP.我可以用 php 中的 foreach 循环来完成，但想知道是否有一种简单的方法可以只使用 SQL 命令来完成.

I'm using PHP with SQLite. I can do it with a foreach-loop in php, but was wondering if there's an easy way to do it using just SQL commands.

任何输入将不胜感激.

推荐答案

在 PHP 中使用单个循环执行此操作可能比将其硬塞到 SQL 查询中要快得多，但如果您真的想要，您可以自行加入带有类似内容的表格:

Doing this with a single loop in PHP is likely to be much faster than shoehorning this into an SQL query, but if you really want to you could self-join the table with itself with something like:

SELECT b.index
FROM   points AS a, points AS b, points AS c
WHERE  a.index = b.index-1 AND c.index = b.index+1
   AND a.value < b.value   AND c.value < b.value 

(未经测试，所以*交叉手指*.)

(Untested, so *cross fingers*.)

这篇关于SQL - 查询帮助:查找局部最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！