create table Rate(
id int not null,
code varchar(50) not null,
date datetime not null,
value float not null
);
select * from Rate;
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 (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
;
Telegram: DB developers channel