krXsNBpD

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    
22CREATE TABLE if not exists REQ_MIN as
23select DISTINCT t1.point AS START_DOWN, t2.point AS END_DOWN 
24    from tmp AS t1, tmp AS t2
25    WHERE 
26      START_DOWN = NULL AND END_DOWN = NULL
27      OR
28      END_DOWN > START_DOWN
29      AND (SELECT cost FROM test WHERE data = END_DOWN) < (SELECT cost FROM test WHERE data = START_DOWN)
30      AND END_DOWN = (
31        SELECT point 
32        FROM tmp 
33        WHERE 
34          point > START_DOWN 
35          and (point - START_DOWN) = (
36            select MIN(point - START_DOWN) 
37            FROM tmp 
38            WHERE point > START_DOWN)
39      );
40
41CREATE TABLE if not exists REQ_MAX as    
42select DISTINCT t1.point AS START_UP, t2.point AS END_UP
43    from tmp AS t1, tmp AS t2
44    WHERE 
45      START_UP = NULL AND END_UP = NULL
46      OR
47      END_UP > START_UP
48      AND (SELECT cost FROM test WHERE data = END_UP) > (SELECT cost FROM test WHERE data = START_UP)
49      AND END_UP = (
50        SELECT point 
51        FROM tmp 
52        WHERE 
53          point > START_UP 
54          and (point - START_UP) = (
55            select MIN(point - START_UP) 
56            FROM tmp 
57            WHERE point > START_UP)
58      );
59
60INSERT INTO REQ_MAX VALUES(NULL, NULL);
61INSERT INTO REQ_MIN VALUES(NULL, NULL);
62
63
64SELECT START_DOWN, END_DOWN, START_UP, END_UP 
65from REQ_MIN, REQ_MAX
66WHERE
67	END_DOWN = START_UP
68    OR
69   	START_UP NOTNULL AND END_UP NOTNULL AND START_DOWN ISNULL AND END_DOWN ISNULL 
70    AND (SELECT COUNT(*) FROM REQ_MIN WHERE REQ_MIN.END_DOWN = START_UP) = 0
71    OR
72   	START_DOWN NOTNULL AND END_DOWN NOTNULL AND START_UP ISNULL AND END_UP ISNULL 
73    AND (SELECT COUNT(*) FROM REQ_MAX WHERE REQ_MAX.START_UP = END_DOWN) = 0
74ORDER BY start_down ASC;
75
76drop table tmp;
77DROP TABLE REQ_MAX;
78DROP TABLE REQ_MIN;
79      
80