postgreSQL 查询以按差异隐藏坏行



>我有一个表格s_log

如下所示
id | parent_id | log_time         | s_value
 1 | 1         | 2013-10-09 09:01 | 2.1
 2 | 2         | 2013-10-09 09:02 | 9.1 --> hide this
 3 | 1         | 2013-10-09 09:04 | 5.2 --> hide this
 4 | 2         | 2013-10-09 09:05 | 4.1
 5 | 1         | 2013-10-09 09:06 | 2.3
 6 | 2         | 2013-10-09 09:07 | 4.2
 7 | 1         | 2013-10-09 09:09 | 2.2
 8 | 2         | 2013-10-09 09:10 | 4.9
 9 | 2         | 2013-10-09 09:11 | 5.7
10 | 2         | 2013-10-09 09:12 | 6.3
11 | 2         | 2013-10-09 09:13 | 2.3 --> hide this
12 | 2         | 2013-10-09 09:14 | 5.8 
13 | 2         | 2013-10-09 09:15 | 6.5 
14 | 2         | 2013-10-09 09:16 | 9.5 --> hide this

要求是:

  • 不显示与上一行和下一行相差超过 1 的任何值parent_id
  • 错误数据仅出现在 2 个数据之前和之后,除了第一条和最后一条记录

例如,当 parent_id = 1

 2.1 - 5.2 - 2.3 - 2.2
   3.1  2.9  0.1
    BAD   BAD   OK

所以5.2必须隐藏

另一个例子,当 parent_id = 2

 9.1 - 4.1 - 4.2 - 4.9 - 5.7 - 6.3 - 2.3 - 5.8 - 6.5 - 9.6
   5.0  0.1  0.7  0.8  0.6  4.0  2.5  0.7  3.0
    BAD   OK    OK    OK    OK    BAD   BAD   OK    BAD

我老板问的完全不可能。

编辑:如果可能,请使用PostgreSQL 9.3

使用递归查询,我们可以将每一行与上一行进行比较,即使它没有以前的 ID(少一个)。

尝试这样的事情:

DECLARE @tbl TABLE 
  ( 
     [ID]        INT, 
     [PARENT_ID] INT, 
     [LOG_TIME]  DATETIME, 
     [S_VALUE]   NUMERIC(4, 2), 
     RN          INT 
  ) 
INSERT @tbl 
SELECT *, 
       ROW_NUMBER() 
         OVER ( 
           PARTITION BY PARENT_ID 
           ORDER BY ID) RN 
FROM   TABLE1 
;WITH CTE 
     AS (SELECT *, 
                CAST(0 AS NUMERIC(4, 2)) AS diff 
         FROM   @tbl 
         WHERE  RN = 1 
         UNION ALL 
         SELECT T2.*, 
                ABS(CAST(T1.S_VALUE - T2.S_VALUE AS NUMERIC(4, 2))) diff 
         FROM   CTE T1 
                INNER JOIN @tbl T2 
                        ON T1.RN = T2.RN - 1 
                           AND T1.PARENT_ID = T2.PARENT_ID) 
SELECT ID, 
       PARENT_ID, 
       LOG_TIME, 
       S_VALUE 
FROM   CTE 
WHERE  DIFF < 1 

可以在SQL Fiddle上找到一个工作示例。

如果您使用的是 SQL 2012,则有一个更简单的解决方案.
使用 LAGLEAD 函数以更短、更简单的代码获得相同的结果。

最新更新