clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 818532 fiddles created (9186 in the last week).

CREATE TABLE produktion ( id INTEGER PRIMARY KEY, druck NUMERIC( 5, 2 ) ); INSERT into produktion VALUES (1, 1.35), (2, 1.33), (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); CREATE INDEX produktion_druck_idx ON produktion USING btree ( druck );
12 rows affected
 hidden batch(es)


WITH finde_grenzen 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 'ende' WHEN lag( druck, 1, druck ) OVER ( ORDER BY id ) >= druck AND druck < lead( druck, 1, druck ) OVER ( ORDER BY id ) THEN 'anfang' END AS row_type FROM produktion ), nummeriere_anfang AS ( SELECT id, row_number() OVER ( ORDER BY id) AS num_start FROM finde_grenzen WHERE row_type = 'anfang' ), nummeriere_ende AS ( SELECT id, row_number() OVER ( ORDER BY id ) AS num_ende FROM finde_grenzen WHERE row_type = 'ende' ) SELECT s.id AS anfang, e.id AS ende FROM nummeriere_anfang AS s JOIN nummeriere_ende AS e ON s.num_start = e.num_ende ORDER BY 1
anfang ende
2 5
6 9
 hidden batch(es)


SELECT p1.id AS anfang, p2.id AS ende 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 )
anfang ende
2 5
6 9
 hidden batch(es)