CbTD9s6p

1CREATE TABLE if not exists tmp AS 
2SELECT DISTINCT t2.data AS point
3      from test as t1, test AS t2, test AS t3
4      WHERE 
5          t1.data < t3.data
6          AND
7          (
8          	-- Первое
9            t2.data = (SELECT data FROM test LIMIT 1)
10            -- Последнее
11            OR t2.data = (SELECT data FROM test ORDER BY data DESC LIMIT 1)
12            -- Локальный минимум или максимум
13            OR (SELECT COUNT(data) FROM test WHERE data < t3.data AND data > t1.data) = 1
14            AND (t2.data < t3.data AND t2.data > t1.data)
15           	AND (
16            	t2.cost > t1.cost AND t2.cost > t3.cost
17              	OR t2.cost < t1.cost AND t2.cost < t3.cost
18            )
19          )
20	order BY 1 ASC;
21
22SELECT DISTINCT START_DOWN, END_DOWN, START_UP, END_UP 
23from (
24    select DISTINCT t1.point AS START_DOWN, t2.point AS END_DOWN 
25    from tmp AS t1, tmp AS t2
26    WHERE 
27      END_DOWN > START_DOWN
28      AND (SELECT cost FROM test WHERE data = END_DOWN) < (SELECT cost FROM test WHERE data = START_DOWN)
29      AND END_DOWN = (
30        SELECT point 
31        FROM tmp 
32        WHERE 
33          point > START_DOWN 
34          and (point - START_DOWN) = (
35            select MIN(point - START_DOWN) 
36            FROM tmp 
37            WHERE point > START_DOWN)
38      )
39    ) as REQ_MIN,(
40    select DISTINCT t1.point AS START_UP, t2.point AS END_UP
41    from tmp AS t1, tmp AS t2
42    WHERE 
43      END_UP > START_UP
44      AND (SELECT cost FROM test WHERE data = END_UP) > (SELECT cost FROM test WHERE data = START_UP)
45      AND END_UP = (
46        SELECT point 
47        FROM tmp 
48        WHERE 
49          point > START_UP 
50          and (point - START_UP) = (
51            select MIN(point - START_UP) 
52            FROM tmp 
53            WHERE point > START_UP)
54      )
55    ) as REQ_MAX
56WHERE
57	END_DOWN = START_UP
58ORDER BY start_down ASC;
59
60drop table tmp;
61
62      
63