选择"step“记录

Question

给出下表

  grp |   ind |   val
----------------------
    a |     1 |     1
    a |     2 |     1
    a |     3 |     1
    a |     4 |     2
    a |     5 |     2
    a |     6 |     4
    a |     7 |     2
    b |     1 |     1
    b |     2 |     1
    b |     3 |     1
    b |     4 |     3
    b |     5 |     3
    b |     6 |     4

我需要选择以下内容：

  grp |   ind |   val
----------------------
    a |     1 |     1
    a |     4 |     2
    a |     6 |     4
    a |     7 |     2
    b |     1 |     1
    b |     4 |     3
    b |     6 |     4

也就是说，对于每个'grp'，其中'val‘与前面的'val’不同的每个记录(按‘index’排序)，所以每个'value‘处的记录都是“step”。

实现这一目标的最有效方法是什么？

谢谢。

以下是创建测试用例的脚本：

create temp table test_table
(
    grp character varying,
    ind numeric,
    val numeric
);
insert into test_table values
    ('a', 1 , 1),
    ('a', 2 , 1),
    ('a', 3 , 1),
    ('a', 4 , 2),
    ('a', 5 , 2),
    ('a', 6 , 4),
    ('a', 7 , 2),
    ('b', 1 , 1),
    ('b', 2 , 1),
    ('b', 3 , 1),
    ('b', 4 , 3),
    ('b', 5 , 3),
    ('b', 6 , 4);

Answer 1

select grp,
       ind,
       val
from (
   select grp, 
          ind, 
          val,
          lag(val,1,0::numeric) over (partition by grp order by ind) - val as diff
   from test_table
) t
where diff <> 0;

Answer 2

DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;

CREATE TABLE ztable
        ( zgroup CHAR(1)
        , zindex int
        , zvalue INTEGER
        );
INSERT INTO ztable(zgroup,zindex,zvalue) VALUES
    ('a',     1,      1)
    ,('a',     2,      1)
    ,('a',     3,      1)
    ,('a',     4,      2)
    ,('a',     5,      2)
    ,('a',     6,      4)
    ,('b',     1,      1)
    ,('b',     2,      1)
    ,('b',     3,      1)
    ,('b',     4,      3)
    ,('b',     5,      3)
    ,('b',     6,      4)
        ;

WITH agg AS (
        SELECT zgroup
        , zindex
        , zvalue
        , row_number() OVER (PARTITION BY zgroup ORDER BY zindex) AS zrank
        FROM ztable
        )
SELECT t1.zgroup,t1.zindex,t1.zvalue
FROM agg t1
LEFT JOIN agg t0 ON t0.zgroup = t1.zgroup AND 1+t0.zrank = t1.zrank
WHERE t0.zvalue <> t1.zvalue OR t0.zrank IS NULL
        ;

Answer 3

select group,min(index) as index,value from table
group by group,value