sql - 选择匹配模式 : greater_than, less_than, greater_than 的行

Question

获得了一个数据库，其中的条目指示员工赚取的单位。我试图找到一个可以为我选择条目的查询，其中 units_earned工作人员遵循以下模式:>30然后<30然后 >30

在this SQL Fiddle ，我希望查询返回:

对于 John，Rows:

2, 4, 6

9, 10, 11

对于 Jane，Rows:

3, 5, 8

12, 13, 14

这里是相关的SQL:

CREATE TABLE staff_units(
   id integer,
   staff_number integer,
   first_name varchar(50),
   month_name varchar(3),
   units_earned integer,
   PRIMARY KEY(id)
);

INSERT INTO staff_units VALUES (1, 101, 'john', 'jan', 32);
INSERT INTO staff_units VALUES (2, 101, 'john', 'jan', 33);
INSERT INTO staff_units VALUES (3, 102, 'jane', 'jan', 39);
INSERT INTO staff_units VALUES (4, 101, 'john', 'feb', 28);
INSERT INTO staff_units VALUES (5, 102, 'jane', 'feb', 28);
INSERT INTO staff_units VALUES (6, 101, 'john', 'mar', 39);
INSERT INTO staff_units VALUES (7, 101, 'john', 'mar', 34);
INSERT INTO staff_units VALUES (8, 102, 'jane', 'mar', 40);
INSERT INTO staff_units VALUES (9, 101, 'john', 'mar', 36);
INSERT INTO staff_units VALUES (10, 101, 'john', 'apr', 18);
INSERT INTO staff_units VALUES (11, 101, 'john', 'may', 32);
INSERT INTO staff_units VALUES (12, 102, 'jane', 'jun', 31);
INSERT INTO staff_units VALUES (13, 102, 'jane', 'jun', 28);
INSERT INTO staff_units VALUES (14, 102, 'jane', 'jun', 32);

Answer 1

使用 window function lead 您可以引用当前记录的下两个前导记录，然后将这三个记录与您想要的模式进行比较。

with staff_units_with_leading as (
select id, staff_number, first_name, units_earned, 
  lead(units_earned) over w units_earned_off1,    -- units_earned from record with offset 1
  lead(units_earned, 2) over w units_earned_off2, -- units_earned from record with offset 2
  lead(id) over w id_off1,    -- id from record with offset 1
  lead(id, 2) over w id_off2  -- id from record with offset 2
from staff_units
window w as (partition by first_name order by id)
)
, ids_wanted as (
select  unnest(array[id, id_off1, id_off2]) id  -- 
from staff_units_with_leading
where 
  id_off1 is not null      -- Discard records with no two leading records
  and id_off2 is not null  -- Discard records with no two leading records
  and units_earned > 30       -- Match desired pattern
  and units_earned_off1 < 30  -- Match desired pattern
  and units_earned_off2 > 30  -- Match desired pattern
)
select * from staff_units
where id in (select id from ids_wanted)
  order by  staff_number, id;

要生成三元组，只需去掉 unnest

with staff_units_with_leading as (
select id, staff_number, first_name, units_earned, 
  lead(units_earned) over w units_earned_off1,    -- units_earned from record with offset 1
  lead(units_earned, 2) over w units_earned_off2, -- units_earned from record with offset 2
  lead(id) over w id_off1,    -- id from record with offset 1
  lead(id, 2) over w id_off2  -- id from record with offset 2
from staff_units
window w as (partition by first_name order by id)
)
select  staff_number, array[id, id_off1, id_off2] id, array[units_earned , units_earned_off1 , units_earned_off2 ] units_earned  -- 
from staff_units_with_leading
where 
  id_off1 is not null      -- Discard records with no two leading records
  and id_off2 is not null  -- Discard records with no two leading records
  and units_earned > 30       -- Match desired pattern
  and units_earned_off1 < 30  -- Match desired pattern
  and units_earned_off2 > 30  -- Match desired pattern