Блог

У меня есть простая таблица следующим образом:

 DROP TABLE IF EXISTS table_name;

CREATE TABLE table_name 
(symbol CHAR(4)
,time DATE 
,bid DECIMAL (7,2)
,ask DECIMAL (7,2)
,PRIMARY KEY(symbol,time)
);

insert into table_name 
(symbol,`time`,bid,ask)
 values 
 ('CN50','2020-09-25',15077.5, 15087.5),
 ('CN50','2020-09-28',15255  , 15265  ),
 ('CN50','2020-09-29',15257  , 15267  ),
 ('CN50','2020-09-30',15258  , 15268  ),
 ('CN50','2020-10-01',15259  , 15269  );

SELECT *, DATE_FORMAT(time,'%a') dow FROM table_name;
  -------- ------------ ---------- ---------- ------ 
 | symbol | time       | bid      | ask      | dow  |
  -------- ------------ ---------- ---------- ------ 
 | CN50   | 2020-09-25 | 15077.50 | 15087.50 | Fri  |
 | CN50   | 2020-09-28 | 15255.00 | 15265.00 | Mon  |
 | CN50   | 2020-09-29 | 15257.00 | 15267.00 | Tue  |
 | CN50   | 2020-09-30 | 15258.00 | 15268.00 | Wed  |
 | CN50   | 2020-10-01 | 15259.00 | 15269.00 | Thu  |
  -------- ------------ ---------- ---------- ------ 
 

Чего я хочу добиться, так это того, что если в течение недели нет значения для воскресенья, вставьте новую строку между этой пятницей и следующим понедельником с датой воскресенья в столбце time и скопируйте значения bid / ask с пятницы.

В этом примере 2020-09-25 — пятница, а 2020-09-28 — понедельник, я бы ожидал вставить строку с 2020-09-27 как time и сохранить значение bid и ask с прошлой пятницы, которое равно 2020-09-25.

До того, как:

После:

Как уже упоминалось, я бы справился с подобными вещами в коде приложения, но если вы знаете, что пятница будет там (и, что важно, воскресенье — нет!), Тогда чего-то подобного будет достаточно…

 SELECT * 
  FROM table_name
 UNION
SELECT symbol
     , time   INTERVAL 2 DAY
     , bid
     , ask 
  FROM table_name 
 WHERE DATE_FORMAT(time   INTERVAL 2 DAY,'%a') = 'sun' 
 ORDER 
    BY symbol
     , time;
 -------- ------------ ---------- ---------- 
| symbol | time       | bid      | ask      |
 -------- ------------ ---------- ---------- 
| CN50   | 2020-09-25 | 15077.50 | 15087.50 |
| CN50   | 2020-09-27 | 15077.50 | 15087.50 |
| CN50   | 2020-09-28 | 15255.00 | 15265.00 |
| CN50   | 2020-09-29 | 15257.00 | 15267.00 |
| CN50   | 2020-09-30 | 15258.00 | 15268.00 |
| CN50   | 2020-10-01 | 15259.00 | 15269.00 |
 -------- ------------ ---------- ---------- 
 

Если есть вероятность, что воскресенье может быть уже там, тогда вы можете слева присоединить этот результат к исходному набору данных и использовать COALESCE для фильтрации правильного значения — но к этому моменту вы, вероятно, захотите вернуться к коду приложения или использовать инструменты CTE / Windowing, доступные в MySQL 8.0