/*-- Задача
--Существует таблица котировок финансовых инструментов
*/
create table Rate(
id int not null,
code varchar(50) not null,
date datetime not null,
value float not null
);
/*
- Дата в поле date хранится без времени.
- Записи в таблицу добавляются в день смены курса (не каждый день).
Курс действует с даты установки по дату следующей установки (не включительно), см. ниже.
1. Необходимо сгенерировать календарь по рабочим дням,
поместить в таблицу Calendar за период c 05.06.2009 по 18.06.2009.
2. Вывести значение курса по каждой валюте за каждый день периода с c 05.06.2009 по 18.06.2009 по каждой валюте.
*/
/*
Рабочие дни перечислены в таблице ниже.
Схема действия курса:
*/
insert into Rate (id, code, date, value) values (1,'EUR','20090605', 1.149);
insert into Rate (id, code, date, value) values (2,'EUR','20090615', 1.161);
insert into Rate (id, code, date, value) values (3,'EUR','20090617', 1.177);
insert into Rate (id, code, date, value) values (4,'USD','20090605', 1.625);
insert into Rate (id, code, date, value) values (5,'USD','20090615', 1.639);
insert into Rate (id, code, date, value) values (6,'USD','20090617', 1.644);
/*
Рабочие дни:
*/
create table Calendar (
code VARCHAR(3) not null,
dDay datetime not null,
value float not null,
weekday VARCHAR(10) not null
);
/*
Например:
code dDay value weekday
-------- ----------------------- --------- -------
EUR 2009-06-05 00:00:00.000 1.149 Friday
EUR 2009-06-08 00:00:00.000 1.149 Monday
EUR 2009-06-09 00:00:00.000 1.149 Tuesday
EUR 2009-06-10 00:00:00.000 1.149 Wednesday
EUR 2009-06-11 00:00:00.000 1.149 Thursday
EUR 2009-06-15 00:00:00.000 1.161 Monday
EUR 2009-06-16 00:00:00.000 1.161 Tuesday
EUR 2009-06-17 00:00:00.000 1.177 Wednesday
EUR 2009-06-18 00:00:00.000 1.177 Thursday
EUR 2009-06-19 00:00:00.000 1.177 Friday
USD 2009-06-03 00:00:00.000 1.625 Wednesday
USD 2009-06-04 00:00:00.000 1.625 Thursday
USD 2009-06-05 00:00:00.000 1.625 Friday
USD 2009-06-08 00:00:00.000 1.625 Monday
USD 2009-06-09 00:00:00.000 1.625 Tuesday
USD 2009-06-10 00:00:00.000 1.625 Wednesday
USD 2009-06-11 00:00:00.000 1.639 Thursday
USD 2009-06-15 00:00:00.000 1.639 Monday
USD 2009-06-16 00:00:00.000 1.639 Tuesday
USD 2009-06-17 00:00:00.000 1.644 Wednesday
USD 2009-06-18 00:00:00.000 1.644 Thursday
USD 2009-06-19 00:00:00.000 1.644 Friday
*/
/*
SET @start_dt := TIMESTAMP('20090605');
SET @end_dt := TIMESTAMP('20090619');
SELECT code, date,
IFNULL(LEAD(date) OVER (), @max_dt) as nextdt,
value
FROM Rate
WHERE code = "EUR"
UNION
SELECT code, date,
IFNULL(SUBDATE(LEAD(date) OVER (PARTITION BY code ORDER BY date), INTERVAL 1 DAY), @max_dt) as nextdt,
value
FROM Rate
WHERE code = "USD"
ORDER BY code, date;
SET @row_num := 0;
SELECT *, (@max_dt := DATE_ADD(@max_dt, INTERVAL 1 DAY)) AS str_num
FROM Rate;
*/
/* Вариант 1 с CROSS JOIN*/
WITH RECURSIVE dates(dt) AS (
SELECT '2009-06-05'
UNION ALL
SELECT DATE_ADD(dt, INTERVAL 1 DAY)
FROM dates
WHERE dt < '2009-06-19'
), intervals(code, stdt, spdt, val) AS (
SELECT code, date,
IFNULL(
SUBDATE(LEAD(date) OVER (PARTITION BY code ORDER BY date), INTERVAL 1 DAY), @max_dt) as nextdt,
value
FROM Rate
)
SELECT code, TIMESTAMP(dt) AS dDay, val as value, DAYNAME(dt) AS weekday
FROM dates, intervals
WHERE DAYNAME(dt) NOT IN ('Saturday', 'Sunday') AND dt BETWEEN stdt AND spdt
ORDER BY code, dt;
/* Вариант 2 *//*
SET @start_dt := TIMESTAMP('20090605');
SET @end_dt := TIMESTAMP('20090619');
WITH RECURSIVE dates(dt) AS (
SELECT @start_dt
UNION ALL
SELECT DATE_ADD(dt, INTERVAL 1 DAY)
FROM dates
WHERE dt < @end_dt
), intervals(code, stdt, spdt, val) AS (
SELECT code, date,
IFNULL(
SUBDATE(LEAD(date) OVER (PARTITION BY code ORDER BY date), INTERVAL 1 DAY), @end_dt) as nextdt,
value
FROM Rate
)
SELECT code, TIMESTAMP(dt) AS dDay, val as value, DAYNAME(dt) AS weekday
FROM dates d LEFT JOIN intervals i ON d.dt BETWEEN i.stdt AND i.spdt
WHERE DAYNAME(dt) NOT IN ('Saturday', 'Sunday')
ORDER BY code, dt;
*/
/* Вариант 3 */
/*
WITH RECURSIVE dates(dt) AS (
SELECT '2009-06-05'
UNION ALL
SELECT DATE_ADD(dt, INTERVAL 1 DAY)
FROM dates
WHERE dt < '2009-06-19'
)
SELECT code, TIMESTAMP(dt) AS dDay, value as value, DAYNAME(dt) AS weekday
FROM dates d LEFT JOIN Rate r ON d.dt >= MAX(r.date)
WHERE DAYNAME(dt) NOT IN ('Saturday', 'Sunday')
ORDER BY code, dt;
*/
/*INSERT INTO Calendar(code, dDay, value, weekday)
WITH RECURSIVE dates(dt) AS (
SELECT @start_dt
UNION ALL
SELECT IF(dayofweek(dt) <> 6, DATE_ADD(dt, INTERVAL 1 DAY), DATE_ADD(dt, INTERVAL 3 DAY) )
FROM dates
WHERE dt < @end_dt
), intervals(code, stdt, spdt, val) AS (
SELECT code, date,
IFNULL(
SUBDATE(LEAD(date) OVER (PARTITION BY code ORDER BY date), INTERVAL 1 DAY), @end_dt) as nextdt,
value
FROM Rate
)
SELECT code, TIMESTAMP(dt), val, DAYNAME(dt)
FROM dates d LEFT JOIN intervals i ON d.dt BETWEEN i.stdt AND i.spdt
ORDER BY code, dt;
SELECT * FROM Calendar;*/
Telegram: DB developers channel