1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
| SET SQL DIALECT 3;
SET NAMES ISO8859_1;
CREATE DATABASE 'C:\Users\EG\Documents\test_sqlpro1.fdb'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 16384
DEFAULT CHARACTER SET ISO8859_1 COLLATION ISO8859_1;
CREATE GENERATOR GEN_T_INTERVAL_ITV_ID;
SET GENERATOR GEN_T_INTERVAL_ITV_ID TO 1001;
CREATE TABLE T_INTERVAL_ITV (
ITV_ID INTEGER NOT NULL,
ITV_ITEM VARCHAR(16) NOT NULL,
ITV_DEBUT TIMESTAMP NOT NULL,
ITV_FIN TIMESTAMP NOT NULL
);
ALTER TABLE T_INTERVAL_ITV ADD CONSTRAINT CHK_ITV_FIN_DEBUT CHECK (itv_fin >= itv_debut);
ALTER TABLE T_INTERVAL_ITV ADD CONSTRAINT T_INTERVAL_ITV_PKEY PRIMARY KEY (ITV_ID);
CREATE INDEX X_ITV_ITM_DEB_FIN ON T_INTERVAL_ITV (ITV_ITEM, ITV_DEBUT, ITV_FIN);
CREATE INDEX X_ITV_ITM_FIN_DEB ON T_INTERVAL_ITV (ITV_ITEM, ITV_FIN, ITV_DEBUT);
CREATE TRIGGER T_INTERVAL_ITV_BI FOR T_INTERVAL_ITV
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new.itv_id is null) then
new.itv_id = gen_id(gen_t_interval_itv_id,1);
end;
CREATE PROCEDURE P_GENERATE_ROWS (
DEBUT_INTERVAL TIMESTAMP,
NUM_ITEMS INTEGER,
INTERVALS_PAR_ITEM INTEGER,
DUREE_MAX_S INTEGER)
AS
DECLARE VARIABLE Fin_interval TIMESTAMP;
DECLARE VARIABLE I INTEGER;
DECLARE VARIABLE J INT;
DECLARE VARIABLE Itm VARCHAR(16);
DECLARE VARIABLE D TIMESTAMP;
DECLARE VARIABLE F TIMESTAMP;
DECLARE VARIABLE Duree INT;
BEGIN
Fin_interval = DATEADD(SECOND, :Duree_max_s,:Debut_interval );
I = 1;
J = 1;
WHILE (I <= :Num_items) DO
BEGIN
WHILE (J <= :Intervals_par_item) DO
BEGIN
D = DATEADD(SECOND, RAND() * :Duree_max_s * 10, :Debut_interval);
Duree = FLOOR(RAND() * :Duree_max_s);
F = DATEADD(SECOND, :Duree, :D);
Itm = 'Item' || I;
INSERT INTO T_interval_itv (Itv_item,
Itv_debut,
Itv_fin)
VALUES (:Itm,
:D,
:F);
J = :J + 1;
END
J = 1;
I = :I + 1;
END
END;
EXECUTE PROCEDURE P_GENERATE_ROWS ('2001-01-01', 10, 100, 3600);
-- solution 1 classique
WITH
T0 AS
(SELECT PRE.ITV_ITEM,
PRE.ITV_DEBUT AS D1, PRE.ITV_FIN AS F1,
DER.ITV_DEBUT AS D2, DER.ITV_FIN AS F2
FROM T_INTERVAL_ITV PRE
INNER JOIN T_INTERVAL_ITV DER
ON PRE.ITV_DEBUT <= DER.ITV_FIN
AND PRE.ITV_ITEM = DER.ITV_ITEM)
SELECT DISTINCT ITV_ITEM, D1 AS ITV_DEBUT, F2 AS ITV_FIN
FROM T0 AS I
WHERE NOT EXISTS (SELECT *
FROM T_INTERVAL_ITV SI1
WHERE (SI1.ITV_DEBUT < I.D1
AND I.D1 <= SI1.ITV_FIN
AND I.ITV_ITEM = SI1.ITV_ITEM )
OR (SI1.ITV_DEBUT <= I.F2
AND I.F2 < SI1.ITV_FIN
AND I.ITV_ITEM = SI1.ITV_ITEM))
AND NOT EXISTS (SELECT *
FROM T_INTERVAL_ITV SI2
WHERE D1 < SI2.ITV_DEBUT
AND SI2.ITV_DEBUT <= F2
AND I.ITV_ITEM = SI2.ITV_ITEM
AND NOT EXISTS (SELECT *
FROM T_INTERVAL_ITV SI3
WHERE SI3.ITV_DEBUT < SI2.ITV_DEBUT
AND SI2.ITV_DEBUT <= SI3.ITV_FIN
AND SI2.ITV_ITEM = SI3.ITV_ITEM ));
-- solution 2 SnodGrass
WITH T
AS (SELECT F.ITV_DEBUT, L.ITV_FIN, F.ITV_ITEM
FROM T_INTERVAL_ITV AS F
JOIN T_INTERVAL_ITV AS L
ON F.ITV_FIN <= L.ITV_FIN
AND F.ITV_ITEM = L.ITV_ITEM
INNER JOIN T_INTERVAL_ITV AS E
ON F.ITV_ITEM = E.ITV_ITEM
GROUP BY F.ITV_DEBUT, L.ITV_FIN, F.ITV_ITEM
HAVING COUNT(CASE
WHEN (E.ITV_DEBUT < F.ITV_DEBUT AND F.ITV_DEBUT <= E.ITV_FIN)
OR (E.ITV_DEBUT <= L.ITV_FIN AND L.ITV_FIN < E.ITV_FIN)
THEN 1
END) = 0)
SELECT ITV_ITEM, ITV_DEBUT, MIN(ITV_FIN) AS ITV_FIN
FROM T
GROUP BY ITV_ITEM, ITV_DEBUT; |
