Расчет границ растущих значений в последовательности

Question

У меня есть записи с непрерывным id и колеблющиеся значения давления druck, Моя цель - выяснить, между какими значениями происходит повышение давления, и, соответственно, получить самый низкий и самый высокий id этого диапазона. У меня уже есть решение с использованием классического SQL, но я бы заинтересовался более эффективным решением.

Вот несколько типичных записей:

create table produktion ( id int, druck numeric (5, 2 ) );
insert into produktion
values (1, 1.35), (2, 1.37), (3, 1.45), ( 4, 1.48), ( 5, 1.51), ( 6, 1.39),
       (7, 1.53), (8, 1.55), (9, 1.62), (10, 1.39), (11, 1.32), (12, 1.28);

Я ожидаю этого результата:

========================
| erste_id | letzte_id |
|=======================
|        1 |         5 |
|        6 |         9 |
========================

Вот текущий используемый запрос:

SELECT p1.id AS erste_id,  -- first id
       p2.id AS letzte_id  -- last id
FROM   produktion AS p1,
       produktion AS p2
WHERE  p1.id < p2.id AND
       NOT EXISTS( SELECT *
                   FROM   produktion AS p3,
                          produktion AS p4
                   WHERE  p3.druck <= p4.druck AND
                          p4.id = p3.id - 1 AND
                          p3.id BETWEEN p1.id + 1 AND p2.id OR
                          p3.id = p1.id - 1 AND p3.druck < p1.druck OR
                          p3.id = p2.id + 1 AND p3.druck > p2.druck )

Обновить

Я забыл упомянуть, что при равных значениях последовательность считается прерванной.

Обновление 2

Я внес небольшие изменения в запрос FatFreddy, чтобы он соответствовал моим требованиям.

WITH
     find_boundaries AS (
   SELECT id,
          CASE WHEN lag(  druck, 1, druck ) over ( ORDER BY id ) <  druck AND
                    druck <  lead( druck, 1, druck ) OVER ( ORDER BY id ) THEN NULL
               WHEN lag(  druck, 1, druck ) OVER ( ORDER BY id ) <  druck AND
                    druck >= lead( druck, 1, druck ) OVER ( ORDER BY id ) THEN 'end_run'
               WHEN lag(  druck, 1, druck ) OVER ( ORDER BY id ) >= druck AND 
                    druck <  lead( druck, 1, druck ) OVER ( ORDER BY id ) THEN 'start_run'
          END AS row_type
   FROM produktion ),

     start_boundary AS (
   SELECT id,
          row_number() OVER ( ORDER BY id) AS correlated_start_row
   FROM   find_boundaries
   WHERE  row_type = 'start_run' ),

     end_boundary AS (
   SELECT id,
          row_number() OVER ( ORDER BY id ) AS correlated_end_row
   FROM   find_boundaries
   WHERE  row_type = 'end_run' )

SELECT s.id AS anfang,
       e.id AS ende
FROM   start_boundary AS s
       JOIN end_boundary AS e
         ON s.correlated_start_row = e.correlated_end_row
ORDER  BY 1

Answer 1

Я делаю EXPLAIN ANALYZE по вашему запросу (12 записей)

Planning time: 0.184 ms
Execution time: 3.525 ms

я могу дать вам запрос с большим временем планирования, но меньшим временем выполнения

Planning time: 0.290 ms
Execution time: 0.269 ms

100 записей

yours
    Planning time: 0.193 ms
    Execution time: 10457.269 ms
mine
    Planning time: 0.342 ms
    Execution time: 1.175 ms

1000 записей

yours
    no result after 5 minutes
mine
    Planning time: 0.343 ms
    Execution time: 5.866 ms

1000000 записей

yours
    no result after 5 minutes
mine
    Planning time: 0.348 ms
    Execution time: 5217.038 ms

замена некоторых ваших вложенных циклов хеш-соединениями. используя некоторые оконные функции https://www.postgresql.org/docs/9.5/static/functions-window.html мой запрос выглядит так:

with temp_flow as
(
select
id,
case when 
    id = 1 and lead(produktion.druck,1, 0::numeric) over(order by id)   <=  produktion.druck then 'fall' 
when 
    id = 1 and lead(produktion.druck,1, 0::numeric) over(order by id)  >  produktion.druck then 'start raise' 
when 
    lag(produktion.druck,1, 0::numeric) over(order by id)  < produktion.druck  and    lead(produktion.druck,1, 0::numeric) over(order by id)   > produktion.druck then 'raise'
when 
   lag(produktion.druck,1, 0::numeric) over(order by id)  < produktion.druck  and    lead(produktion.druck,1, 0::numeric) over(order by id)   <=  produktion.druck then 'end raise'
when 
   lag(produktion.druck,1, 0::numeric) over(order by id)  = produktion.druck  and    lead(produktion.druck,1, 0::numeric) over(order by id)   <=  produktion.druck then 'fall'
when 
   lag(produktion.druck,1, 0::numeric) over(order by id)  >=  produktion.druck  and    lead(produktion.druck,1, 0::numeric) over(order by id)   >  produktion.druck then 'start raise'
else 'fall'
end as way,

lag(produktion.druck,1, 0::numeric) over(order by id) as beforelag,
produktion.druck,
lead(produktion.druck,1, 0::numeric) over(order by id)  as afterlead
from produktion
order by id
),

temp_start as  
(
select
id,
row_number() over () as xrow
 from temp_flow
where temp_flow.way = 'start raise'
order by id
),

temp_end as 
(
select
id,
row_number() over () as xrow
 from temp_flow
where temp_flow.way = 'end raise'
order by id
)

select
temp_start.id as first,
temp_end.id as last
from 
temp_start
join temp_end on temp_end.xrow = temp_start.xrow 

в вашей настройке не объявлено, что должно быть сделано с равным druck, так что вы можете поместить где-то a >= вместо a> и вместо этого вы можете использовать цифры, например, "начать повышение", которое я использовал для удобства чтения, которое должно быть быстрее при сравнении текста.

счастливого тестирования с большими данными в вашей системе.

edit: исправить результат, сделав еще несколько случаев, выбирая некоторые странные случаи