时间戳

首页 > 解决方案 > 多次选择最近期货到期的利率 SQL

mysql - 多次选择最近期货到期的利率 SQL

问题描述

你能帮我解决以下问题吗？

我有一个有 4 列的表，以下列内容为例：

maturity        date        ticker              rate
21/09/2021 0:00 9/07/2021   FUTPVOLUSD10YSEP21  4.476
22/03/2022 0:00 9/07/2021   FUTPVOLUSD10YMAR22  3.352
21/12/2021 0:00 9/07/2021   FUTPVOLUSD10YDEC21  4.856
21/12/2021 0:00 12/07/2021  FUTPVOLUSD10YDEC21  4.844
22/03/2022 0:00 12/07/2021  FUTPVOLUSD10YMAR22  3.352
21/09/2021 0:00 12/07/2021  FUTPVOLUSD10YSEP21  4.372
21/09/2021 0:00 13/07/2021  FUTPVOLUSD10YSEP21  4.323
22/03/2022 0:00 13/07/2021  FUTPVOLUSD10YMAR22  3.352
21/12/2021 0:00 13/07/2021  FUTPVOLUSD10YDEC21  4.633
21/12/2021 0:00 14/07/2021  FUTPVOLUSD10YDEC21  4.174
22/03/2022 0:00 14/07/2021  FUTPVOLUSD10YMAR22  3.352
21/09/2021 0:00 14/07/2021  FUTPVOLUSD10YSEP21  4.324

我如何提取一个表格，其中每个日期我都会有最早到期的利率。

例如，截至 2021 年 7 月 14 日，它是 4.324，因为 2021 年 9 月 21 日是该日期可用的三个到期日中最早的到期日。

非常感谢

标签： mysql

解决方案

DROP TABLE IF EXISTS n;

CREATE TABLE n
(maturity DATE 
,date DATE
,ticker VARCHAR(12) NOT NULL
,rate DECIMAL(5,3)
,PRIMARY KEY(ticker,date)
);

INSERT INTO n VALUES
(20210921, 20210709,  'SEP21' , 4.476),
(20220322, 20210709,  'MAR22' , 3.352),
(20211221, 20210709,  'DEC21' , 4.856),
(20211221, 20210712,  'DEC21' , 4.844),
(20220322, 20210712,  'MAR22' , 3.352),
(20210921, 20210712,  'SEP21' , 4.372),
(20210921, 20210713,  'SEP21' , 4.323),
(20220322, 20210713,  'MAR22' , 3.352),
(20211221, 20210713,  'DEC21' , 4.633),
(20211221, 20210714,  'DEC21' , 4.174),
(20220322, 20210714,  'MAR22' , 3.352),
(20210921, 20210714,  'SEP21' , 4.324);

SELECT * FROM n;
+------------+------------+--------+-------+
| maturity   | date       | ticker | rate  |
+------------+------------+--------+-------+
| 2021-09-21 | 2021-07-09 | SEP21  | 4.476 |
| 2022-03-22 | 2021-07-09 | MAR22  | 3.352 |
| 2021-12-21 | 2021-07-09 | DEC21  | 4.856 |
| 2021-12-21 | 2021-07-12 | DEC21  | 4.844 |
| 2022-03-22 | 2021-07-12 | MAR22  | 3.352 |
| 2021-09-21 | 2021-07-12 | SEP21  | 4.372 |
| 2021-09-21 | 2021-07-13 | SEP21  | 4.323 |
| 2022-03-22 | 2021-07-13 | MAR22  | 3.352 |
| 2021-12-21 | 2021-07-13 | DEC21  | 4.633 |
| 2021-12-21 | 2021-07-14 | DEC21  | 4.174 |
| 2022-03-22 | 2021-07-14 | MAR22  | 3.352 |
| 2021-09-21 | 2021-07-14 | SEP21  | 4.324 |
+------------+------------+--------+-------+

SELECT date,MIN(maturity) maturity FROM n GROUP BY date;
+------------+------------+
| date       | maturity   |
+------------+------------+
| 2021-07-09 | 2021-09-21 |
| 2021-07-12 | 2021-09-21 |
| 2021-07-13 | 2021-09-21 |
| 2021-07-14 | 2021-09-21 |
+------------+------------+
4 rows in set (0.01 sec)

...

SELECT a.* 
  FROM n a 
  JOIN 
     ( SELECT date,MIN(maturity) maturity FROM n GROUP BY date)b 
    ON b.date = a.date 
   AND b.maturity = a.maturity;
+------------+------------+--------+-------+
| maturity   | date       | ticker | rate  |
+------------+------------+--------+-------+
| 2021-09-21 | 2021-07-09 | SEP21  | 4.476 |
| 2021-09-21 | 2021-07-12 | SEP21  | 4.372 |
| 2021-09-21 | 2021-07-13 | SEP21  | 4.323 |
| 2021-09-21 | 2021-07-14 | SEP21  | 4.324 |
+------------+------------+--------+-------+

推荐阅读