| DML | //
// Demonstration of how to use the various MaxDB DML expressions
//
// -> Get some test data for time series
DROP TABLE time_series
//
CREATE TABLE time_series
(kyy CHAR(03) NOT NULL
,bgn_dt DATE NOT NULL
,end_dt DATE NOT NULL
,CONSTRAINT tsc1 CHECK (kyy <> '')
,CONSTRAINT tsc2 CHECK (bgn_dt <= end_dt)
)
//
INSERT INTO time_series VALUES ('AAA','1995-10-01','1995-10-04')
//
INSERT INTO time_series VALUES ('AAA','1995-10-06','1995-10-06')
//
INSERT INTO time_series VALUES ('AAA','1995-10-07','1995-10-07')
//
INSERT INTO time_series VALUES ('AAA','1995-10-15','1995-10-19')
//
INSERT INTO time_series VALUES ('BBB','1995-10-01','1995-10-01')
//
INSERT INTO time_series VALUES ('BBB','1995-10-03','1995-10-03')
//
// -> correlated subquery examples (find gaps in time series)
SELECT a.kyy
,a.bgn_dt
,a.end_dt
,b.bgn_dt
,b.end_dt
,DATEDIFF(b.bgn_dt,A.end_dt) as diff
FROM time_series a
,time_series b
WHERE a.kyy = b.kyy
AND NUM(a.end_dt) < NUM(b.bgn_dt) - 1
AND NOT EXISTS (SELECT *
FROM time_series z
WHERE z.kyy = a.kyy
AND z.kyy = b.kyy
AND z.bgn_dt > a.bgn_dt
AND z.bgn_dt < b.bgn_dt)
ORDER BY 1,2
//
// -> Put this into a view:
CREATE OR REPLACE VIEW find_gaps_in_time_series (kyy,gapdt,gsize) AS
SELECT a.kyy
,ADDDATE(a.end_dt,1)
,DATEDIFF(b.bgn_dt,a.end_dt) - 1
FROM time_series a
,time_series b
WHERE a.kyy = b.kyy
AND NUM(a.end_dt) < NUM(b.bgn_dt) - 1
AND NOT EXISTS (SELECT *
FROM time_series z
WHERE z.kyy = a.kyy
AND z.kyy = b.kyy
AND z.bgn_dt > a.bgn_dt
AND z.bgn_dt < b.bgn_dt)
//
// -> recursion example tp get each day in gap:
DECLARE c_each_day_in_gap CURSOR FOR WITH RECURSIVE td (kyy,gap_dt,gsize) AS
(SELECT kyy
,gapdt
,gsize
FROM find_gaps_in_time_series
UNION ALL
SELECT kyy
,ADDDATE(gap_dt,1)
,gsize - 1
FROM td
WHERE gsize > 1
)
SELECT *
FROM td
ORDER BY 1,2
//
// -> recursion examples (an example to generate application data (dimension data))
// (time serie to create rows for dimension table dim_time insert:
DECLARE c_dates_4_dim_table CURSOR FOR WITH RECURSIVE dim (id,dim_dt) AS
(
SELECT 1,MAKEDATE(2008,01) FROM dual
UNION ALL
SELECT id+1,ADDDATE(dim_dt,1)
FROM dim
WHERE ADDDATE(dim_dt,1) < '2008-04-01'
)
SELECT id AS id_time
,CHAR(dim_dt,ISO) AS "ISO Date"
,NUM(CHAR(dim_dt,INTERNAL)) AS "Numeric Date"
,YEAR(dim_dt) AS "Year"
,CASE WHEN MONTH(dim_dt) BETWEEN 1 AND 3 THEN 1
WHEN MONTH(dim_dt) BETWEEN 4 AND 6 THEN 2
WHEN MONTH(dim_dt) BETWEEN 7 AND 9 THEN 3
WHEN MONTH(dim_dt) BETWEEN 10 AND 12 THEN 4
END AS "Quarter"
,MONTH(dim_dt) AS "Month"
,WEEK(dim_dt) AS "Week"
,DAYOFYEAR(dim_dt) AS "Day of Year"
,DAYOFWEEK(dim_dt) AS "Day of Week"
,DAY(dim_dt) AS "Day"
,DAYNAME(dim_dt) AS "Dayname"
FROM dim
ORDER BY dim_dt ASC
//
|
| Result | > DROP TABLE time_series
> CREATE TABLE time_series (kyy CHAR(03) NOT NULL ,bgn_dt DATE NOT NULL ,end_dt DATE NOT NULL ,CONSTRAINT tsc1 CHECK (kyy <> '') ,CONSTRAINT tsc2 CHECK (bgn_dt <= end_dt) )
> INSERT INTO time_series VALUES ('AAA','1995-10-01','1995-10-04')
> INSERT INTO time_series VALUES ('AAA','1995-10-06','1995-10-06')
> INSERT INTO time_series VALUES ('AAA','1995-10-07','1995-10-07')
> INSERT INTO time_series VALUES ('AAA','1995-10-15','1995-10-19')
> INSERT INTO time_series VALUES ('BBB','1995-10-01','1995-10-01')
> INSERT INTO time_series VALUES ('BBB','1995-10-03','1995-10-03')
> SELECT a.kyy ,a.bgn_dt ,a.end_dt ,b.bgn_dt ,b.end_dt ,DATEDIFF(b.bgn_dt,A.end_dt) as diff FROM time_series a ,time_series b WHERE a.kyy = b.kyy AND NUM(a.end_dt) < NUM(b.bgn_dt) - 1 AND NOT EXISTS (SELECT * FROM time_series z WHERE z.kyy = a.kyy AND z.kyy = b.kyy AND z.bgn_dt > a.bgn_dt AND z.bgn_dt < b.bgn_dt) ORDER BY 1,2
> | KYY | BGN_DT | END_DT | BGN_DT | END_DT | DIFF |
> | --- | ---------- | ---------- | ---------- | ---------- | ------------ |
> | AAA | 1995-10-01 | 1995-10-04 | 1995-10-06 | 1995-10-06 | 2 |
> | AAA | 1995-10-07 | 1995-10-07 | 1995-10-15 | 1995-10-19 | 8 |
> | BBB | 1995-10-01 | 1995-10-01 | 1995-10-03 | 1995-10-03 | 2 |
> CREATE OR REPLACE VIEW find_gaps_in_time_series (kyy,gapdt,gsize) AS SELECT a.kyy ,ADDDATE(a.end_dt,1) ,DATEDIFF(b.bgn_dt,a.end_dt) - 1 FROM time_series a ,time_series b WHERE a.kyy = b.kyy AND NUM(a.end_dt) < NUM(b.bgn_dt) - 1 AND NOT EXISTS (SELECT * FROM time_series z WHERE z.kyy = a.kyy AND z.kyy = b.kyy AND z.bgn_dt > a.bgn_dt AND z.bgn_dt < b.bgn_dt)
> DECLARE c_each_day_in_gap CURSOR FOR WITH RECURSIVE td (kyy,gap_dt,gsize) AS (SELECT kyy ,gapdt ,gsize FROM find_gaps_in_time_series UNION ALL SELECT kyy ,ADDDATE(gap_dt,1) ,gsize - 1 FROM td WHERE gsize > 1 ) SELECT * FROM td ORDER BY 1,2
> | KYY | GAP_DT | GSIZE |
> | --- | ---------- | ---------------------------------------------- |
> | AAA | 1995-10-05 | 1 |
> | AAA | 1995-10-08 | 7 |
> | AAA | 1995-10-09 | 6 |
> | AAA | 1995-10-10 | 5 |
> | AAA | 1995-10-11 | 4 |
> | AAA | 1995-10-12 | 3 |
> | AAA | 1995-10-13 | 2 |
> | AAA | 1995-10-14 | 1 |
> | BBB | 1995-10-02 | 1 |
> DECLARE c_dates_4_dim_table CURSOR FOR WITH RECURSIVE dim (id,dim_dt) AS ( SELECT 1,MAKEDATE(2008,01) FROM dual UNION ALL SELECT id+1,ADDDATE(dim_dt,1) FROM dim WHERE ADDDATE(dim_dt,1) < '2008-04-01' ) SELECT id AS id_time ,CHAR(dim_dt,ISO) AS "ISO Date" ,NUM(CHAR(dim_dt,INTERNAL)) AS "Numeric Date" ,YEAR(dim_dt) AS "Year" ,CASE WHEN MONTH(dim_dt) BETWEEN 1 AND 3 THEN 1 WHEN MONTH(dim_dt) BETWEEN 4 AND 6 THEN 2 WHEN MONTH(dim_dt) BETWEEN 7 AND 9 THEN 3 WHEN MONTH(dim_dt) BETWEEN 10 AND 12 THEN 4 END AS "Quarter" ,MONTH(dim_dt) AS "Month" ,WEEK(dim_dt) AS "Week" ,DAYOFYEAR(dim_dt) AS "Day of Year" ,DAYOFWEEK(dim_dt) AS "Day of Week" ,DAY(dim_dt) AS "Day" ,DAYNAME(dim_dt) AS "Dayname" FROM dim ORDER BY dim_dt ASC
> | ID_TIME | ISO Date | Numeric Date | Year | Quarter | Month | Week | Day of Year | Day of Week | Day | Dayname |
> | ---------------------------------------------- | ---------- | ---------------------------------------------- | -------- | ------------ | ---------- | -------- | ------------------ | ------------------ | ------- | ------------ |
> | 1 | 2008-01-01 | 20080101 | 2008 | 1 | 1 | 1 | 1 | 2 | 1 | Tuesday |
> | 2 | 2008-01-02 | 20080102 | 2008 | 1 | 1 | 1 | 2 | 3 | 2 | Wednesday |
> | 3 | 2008-01-03 | 20080103 | 2008 | 1 | 1 | 1 | 3 | 4 | 3 | Thursday |
> | 4 | 2008-01-04 | 20080104 | 2008 | 1 | 1 | 1 | 4 | 5 | 4 | Friday |
> | 5 | 2008-01-05 | 20080105 | 2008 | 1 | 1 | 1 | 5 | 6 | 5 | Saturday |
> | 6 | 2008-01-06 | 20080106 | 2008 | 1 | 1 | 1 | 6 | 7 | 6 | Sunday |
> | 7 | 2008-01-07 | 20080107 | 2008 | 1 | 1 | 2 | 7 | 1 | 7 | Monday |
> | 8 | 2008-01-08 | 20080108 | 2008 | 1 | 1 | 2 | 8 | 2 | 8 | Tuesday |
> | 9 | 2008-01-09 | 20080109 | 2008 | 1 | 1 | 2 | 9 | 3 | 9 | Wednesday |
> | 10 | 2008-01-10 | 20080110 | 2008 | 1 | 1 | 2 | 10 | 4 | 10 | Thursday |
> | 11 | 2008-01-11 | 20080111 | 2008 | 1 | 1 | 2 | 11 | 5 | 11 | Friday |
> | 12 | 2008-01-12 | 20080112 | 2008 | 1 | 1 | 2 | 12 | 6 | 12 | Saturday |
> | 13 | 2008-01-13 | 20080113 | 2008 | 1 | 1 | 2 | 13 | 7 | 13 | Sunday |
> | 14 | 2008-01-14 | 20080114 | 2008 | 1 | 1 | 3 | 14 | 1 | 14 | Monday |
> | 15 | 2008-01-15 | 20080115 | 2008 | 1 | 1 | 3 | 15 | 2 | 15 | Tuesday |
> | 16 | 2008-01-16 | 20080116 | 2008 | 1 | 1 | 3 | 16 | 3 | 16 | Wednesday |
> | 17 | 2008-01-17 | 20080117 | 2008 | 1 | 1 | 3 | 17 | 4 | 17 | Thursday |
> | 18 | 2008-01-18 | 20080118 | 2008 | 1 | 1 | 3 | 18 | 5 | 18 | Friday |
> | 19 | 2008-01-19 | 20080119 | 2008 | 1 | 1 | 3 | 19 | 6 | 19 | Saturday |
> | 20 | 2008-01-20 | 20080120 | 2008 | 1 | 1 | 3 | 20 | 7 | 20 | Sunday |
> | 21 | 2008-01-21 | 20080121 | 2008 | 1 | 1 | 4 | 21 | 1 | 21 | Monday |
> | 22 | 2008-01-22 | 20080122 | 2008 | 1 | 1 | 4 | 22 | 2 | 22 | Tuesday |
> | 23 | 2008-01-23 | 20080123 | 2008 | 1 | 1 | 4 | 23 | 3 | 23 | Wednesday |
> | 24 | 2008-01-24 | 20080124 | 2008 | 1 | 1 | 4 | 24 | 4 | 24 | Thursday |
> | 25 | 2008-01-25 | 20080125 | 2008 | 1 | 1 | 4 | 25 | 5 | 25 | Friday |
> | 26 | 2008-01-26 | 20080126 | 2008 | 1 | 1 | 4 | 26 | 6 | 26 | Saturday |
> | 27 | 2008-01-27 | 20080127 | 2008 | 1 | 1 | 4 | 27 | 7 | 27 | Sunday |
> | 28 | 2008-01-28 | 20080128 | 2008 | 1 | 1 | 5 | 28 | 1 | 28 | Monday |
> | 29 | 2008-01-29 | 20080129 | 2008 | 1 | 1 | 5 | 29 | 2 | 29 | Tuesday |
> | 30 | 2008-01-30 | 20080130 | 2008 | 1 | 1 | 5 | 30 | 3 | 30 | Wednesday |
> | 31 | 2008-01-31 | 20080131 | 2008 | 1 | 1 | 5 | 31 | 4 | 31 | Thursday |
> | 32 | 2008-02-01 | 20080201 | 2008 | 1 | 2 | 5 | 32 | 5 | 1 | Friday |
> | 33 | 2008-02-02 | 20080202 | 2008 | 1 | 2 | 5 | 33 | 6 | 2 | Saturday |
> | 34 | 2008-02-03 | 20080203 | 2008 | 1 | 2 | 5 | 34 | 7 | 3 | Sunday |
> | 35 | 2008-02-04 | 20080204 | 2008 | 1 | 2 | 6 | 35 | 1 | 4 | Monday |
> | 36 | 2008-02-05 | 20080205 | 2008 | 1 | 2 | 6 | 36 | 2 | 5 | Tuesday |
> | 37 | 2008-02-06 | 20080206 | 2008 | 1 | 2 | 6 | 37 | 3 | 6 | Wednesday |
> | 38 | 2008-02-07 | 20080207 | 2008 | 1 | 2 | 6 | 38 | 4 | 7 | Thursday |
> | 39 | 2008-02-08 | 20080208 | 2008 | 1 | 2 | 6 | 39 | 5 | 8 | Friday |
> | 40 | 2008-02-09 | 20080209 | 2008 | 1 | 2 | 6 | 40 | 6 | 9 | Saturday |
> | 41 | 2008-02-10 | 20080210 | 2008 | 1 | 2 | 6 | 41 | 7 | 10 | Sunday |
> | 42 | 2008-02-11 | 20080211 | 2008 | 1 | 2 | 7 | 42 | 1 | 11 | Monday |
> | 43 | 2008-02-12 | 20080212 | 2008 | 1 | 2 | 7 | 43 | 2 | 12 | Tuesday |
> | 44 | 2008-02-13 | 20080213 | 2008 | 1 | 2 | 7 | 44 | 3 | 13 | Wednesday |
> | 45 | 2008-02-14 | 20080214 | 2008 | 1 | 2 | 7 | 45 | 4 | 14 | Thursday |
> | 46 | 2008-02-15 | 20080215 | 2008 | 1 | 2 | 7 | 46 | 5 | 15 | Friday |
> | 47 | 2008-02-16 | 20080216 | 2008 | 1 | 2 | 7 | 47 | 6 | 16 | Saturday |
> | 48 | 2008-02-17 | 20080217 | 2008 | 1 | 2 | 7 | 48 | 7 | 17 | Sunday |
> | 49 | 2008-02-18 | 20080218 | 2008 | 1 | 2 | 8 | 49 | 1 | 18 | Monday |
> | 50 | 2008-02-19 | 20080219 | 2008 | 1 | 2 | 8 | 50 | 2 | 19 | Tuesday |
> | 51 | 2008-02-20 | 20080220 | 2008 | 1 | 2 | 8 | 51 | 3 | 20 | Wednesday |
> | 52 | 2008-02-21 | 20080221 | 2008 | 1 | 2 | 8 | 52 | 4 | 21 | Thursday |
> | 53 | 2008-02-22 | 20080222 | 2008 | 1 | 2 | 8 | 53 | 5 | 22 | Friday |
> | 54 | 2008-02-23 | 20080223 | 2008 | 1 | 2 | 8 | 54 | 6 | 23 | Saturday |
> | 55 | 2008-02-24 | 20080224 | 2008 | 1 | 2 | 8 | 55 | 7 | 24 | Sunday |
> | 56 | 2008-02-25 | 20080225 | 2008 | 1 | 2 | 9 | 56 | 1 | 25 | Monday |
> | 57 | 2008-02-26 | 20080226 | 2008 | 1 | 2 | 9 | 57 | 2 | 26 | Tuesday |
> | 58 | 2008-02-27 | 20080227 | 2008 | 1 | 2 | 9 | 58 | 3 | 27 | Wednesday |
> | 59 | 2008-02-28 | 20080228 | 2008 | 1 | 2 | 9 | 59 | 4 | 28 | Thursday |
> | 60 | 2008-02-29 | 20080229 | 2008 | 1 | 2 | 9 | 60 | 5 | 29 | Friday |
> | 61 | 2008-03-01 | 20080301 | 2008 | 1 | 3 | 9 | 61 | 6 | 1 | Saturday |
> | 62 | 2008-03-02 | 20080302 | 2008 | 1 | 3 | 9 | 62 | 7 | 2 | Sunday |
> | 63 | 2008-03-03 | 20080303 | 2008 | 1 | 3 | 10 | 63 | 1 | 3 | Monday |
> | 64 | 2008-03-04 | 20080304 | 2008 | 1 | 3 | 10 | 64 | 2 | 4 | Tuesday |
> | 65 | 2008-03-05 | 20080305 | 2008 | 1 | 3 | 10 | 65 | 3 | 5 | Wednesday |
> | 66 | 2008-03-06 | 20080306 | 2008 | 1 | 3 | 10 | 66 | 4 | 6 | Thursday |
> | 67 | 2008-03-07 | 20080307 | 2008 | 1 | 3 | 10 | 67 | 5 | 7 | Friday |
> | 68 | 2008-03-08 | 20080308 | 2008 | 1 | 3 | 10 | 68 | 6 | 8 | Saturday |
> | 69 | 2008-03-09 | 20080309 | 2008 | 1 | 3 | 10 | 69 | 7 | 9 | Sunday |
> | 70 | 2008-03-10 | 20080310 | 2008 | 1 | 3 | 11 | 70 | 1 | 10 | Monday |
> | 71 | 2008-03-11 | 20080311 | 2008 | 1 | 3 | 11 | 71 | 2 | 11 | Tuesday |
> | 72 | 2008-03-12 | 20080312 | 2008 | 1 | 3 | 11 | 72 | 3 | 12 | Wednesday |
> | 73 | 2008-03-13 | 20080313 | 2008 | 1 | 3 | 11 | 73 | 4 | 13 | Thursday |
> | 74 | 2008-03-14 | 20080314 | 2008 | 1 | 3 | 11 | 74 | 5 | 14 | Friday |
> | 75 | 2008-03-15 | 20080315 | 2008 | 1 | 3 | 11 | 75 | 6 | 15 | Saturday |
> | 76 | 2008-03-16 | 20080316 | 2008 | 1 | 3 | 11 | 76 | 7 | 16 | Sunday |
> | 77 | 2008-03-17 | 20080317 | 2008 | 1 | 3 | 12 | 77 | 1 | 17 | Monday |
> | 78 | 2008-03-18 | 20080318 | 2008 | 1 | 3 | 12 | 78 | 2 | 18 | Tuesday |
> | 79 | 2008-03-19 | 20080319 | 2008 | 1 | 3 | 12 | 79 | 3 | 19 | Wednesday |
> | 80 | 2008-03-20 | 20080320 | 2008 | 1 | 3 | 12 | 80 | 4 | 20 | Thursday |
> | 81 | 2008-03-21 | 20080321 | 2008 | 1 | 3 | 12 | 81 | 5 | 21 | Friday |
> | 82 | 2008-03-22 | 20080322 | 2008 | 1 | 3 | 12 | 82 | 6 | 22 | Saturday |
> | 83 | 2008-03-23 | 20080323 | 2008 | 1 | 3 | 12 | 83 | 7 | 23 | Sunday |
> | 84 | 2008-03-24 | 20080324 | 2008 | 1 | 3 | 13 | 84 | 1 | 24 | Monday |
> | 85 | 2008-03-25 | 20080325 | 2008 | 1 | 3 | 13 | 85 | 2 | 25 | Tuesday |
> | 86 | 2008-03-26 | 20080326 | 2008 | 1 | 3 | 13 | 86 | 3 | 26 | Wednesday |
> | 87 | 2008-03-27 | 20080327 | 2008 | 1 | 3 | 13 | 87 | 4 | 27 | Thursday |
> | 88 | 2008-03-28 | 20080328 | 2008 | 1 | 3 | 13 | 88 | 5 | 28 | Friday |
> | 89 | 2008-03-29 | 20080329 | 2008 | 1 | 3 | 13 | 89 | 6 | 29 | Saturday |
> | 90 | 2008-03-30 | 20080330 | 2008 | 1 | 3 | 13 | 90 | 7 | 30 | Sunday |
> | 91 | 2008-03-31 | 20080331 | 2008 | 1 | 3 | 14 | 91 | 1 | 31 | Monday |
|