SQLize Online / PHPize Online  /  SQLtest Online

/*-- Задача --Существует таблица котировок финансовых инструментов */ create table Rate( id int not null, code varchar(50) not null, date datetime not null, value float not null ); select * from Rate; /* - Дата в поле 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); select * from Rate; /* Рабочие дни: */ create table Calendar (dDay datetime not null, weekday VARCHAR(10) not null); select * from Calendar; WITH RECURSIVE nrows(date) AS ( SELECT '2009-06-05' UNION ALL SELECT DATE_ADD(date,INTERVAL 1 day) FROM nrows WHERE date<='2009-06-18' ) SELECT date, DAYNAME(date) FROM nrows WHERE DAYNAME(date) NOT LIKE 'Saturday' AND DAYNAME(date) NOT LIKE 'Sunday' ; INSERT INTO Calendar(dDay, weekday) WITH RECURSIVE nrows(date) AS ( SELECT '2009-06-05' UNION ALL SELECT DATE_ADD(date,INTERVAL 1 day) FROM nrows WHERE date<='2009-06-18' ) SELECT date, DAYNAME(date) FROM nrows WHERE DAYNAME(date) NOT LIKE 'Saturday' AND DAYNAME(date) NOT LIKE 'Sunday' ; SELECT * FROM Calendar; WITH ntable (code, dDay, value, weekday) AS ( SELECT code, dDay, value, weekday FROM Calendar LEFT JOIN (SELECT * FROM Rate WHERE code = 'EUR') as RateE ON Calendar.dDay = RateE.date UNION ALL SELECT code, dDay, value, weekday FROM Calendar LEFT JOIN (SELECT * FROM Rate WHERE code = 'USD') as RateU ON Calendar.dDay = RateU.date ) SELECT LAG(code) OVER(ORDER BY dDay), dDay FROM ntable group by code, dDay ; WITH nt1 (code, dDay, value, weekday) AS ( SELECT 'EUR' as code, dDay, value, weekday FROM Calendar LEFT JOIN (SELECT * FROM Rate WHERE code LIKE 'EUR') as RateE ON Calendar.dDay = RateE.date ), nt2 (code, dDay, value, weekday) AS ( SELECT 'USD' as code, dDay, value, weekday FROM Calendar LEFT JOIN (SELECT * FROM Rate WHERE code LIKE 'USD') as RateU ON Calendar.dDay = RateU.date ), nt3 (code, dDay, value, weekday) AS ( SELECT code, dDay, value, weekday FROM nt1 UNION ALL SELECT code, dDay, value, weekday FROM nt2 ) SELECT code, dDay, value, LAG(value) OVER(PARTITION BY code ORDER BY dDay), weekday FROM nt3 ; /*select ic.item-number, ic.country_code, b.date_ym, (select g.budget from g where g.item_number = ic.item_number and g.country_code = ic.country_code and g.budget_set_date >= b.date_ym order by b.date_ym limit 1 ) as budget from (select distinct item_number, country_code from g) ic cross join b ; */ SELECT (SELECT Rate.code FROM Rate WHERE Rate.code LIKE('EUR') AND Rate.date < Calendar.dDay ORDER by Calendar.dDay LIMIT 1) as code, Calendar.dDay, (SELECT Rate.value FROM Rate WHERE Rate.code LIKE('EUR') AND Rate.date < Calendar.dDay ORDER by Calendar.dDay LIMIT 1) as value, Calendar.weekday FROM (SELECT DISTINCT code, value FROM Rate) as tmp CROSS JOIN Calendar ; /* Например: 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 @max_dt = '20090618' SELECT code, date, LEAD(date) OVER () as nextdt, value, DAYNAME(date) FROM Rate WHERE code = "EUR" UNION SELECT code, date, IFNULL(LEAD(date) OVER () as nextdt, @max_dt), value, DAYNAME(date) FROM Rate WHERE code = "USD" ORDER BY code, date; */