/*-- Задача
--Существует таблица котировок финансовых инструментов
*/
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
;
*
-------- ----------------------- --------- -------
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;
*/
Telegram: DB developers channel