SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
/*-- Задача --Существует таблица котировок финансовых инструментов */ 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;*/
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear