mysql - 在 MySQL 中将月度 future 数据与前月时间序列相结合

Question

我有每月 VIX future 的每日数据，并试图将其转换为前月时间序列。
所以我编写了以下代码来提取特定日期的前一个月时间序列。

SELECT date, ticker_id, settle_price, volume FROM hist WHERE ticker_id IN ('Q (Aug 11)', 'U (Sep 11)', 'V (Oct 11)', 'X (Nov 11)') AND trade_dt='06/22/2011';

由于这些是月度 future ，我如何设置查询，以便确定该日期当前的前月 future ，然后返回前月和接下来 3 个日历月的选定字段。

Answer 1

这需要大量的预先检查，但本质上，我必须一次构建 SQL 变量，就像在“让 X = 某物”、“让 y = X + 某物”的程序中一样等。通过构建最内部的@SQLVars 变量，一旦声明第一个变量，它就可以用作下一个变量的基础，依此类推...首先，这是您可以应用于构建的数据的完整查询基于当前日期。您更好地了解您的数据可能需要对其进行一些调整，但我认为这会让您顺利进行。

select
      CONCAT( 'Q (', LEFT( MonthName( DateBasis.dMonth1 ), 3 ), ' ', RIGHT( Year( DateBasis.dMonth1 ), 2 ), ')' ) as FirstMonth,
      CONCAT( 'U (', LEFT( MonthName( DateBasis.dMonth2 ), 3 ), ' ', RIGHT( Year( DateBasis.dMonth2 ), 2 ), ')' ) as SecondMonth,
      CONCAT( 'V (', LEFT( MonthName( DateBasis.dMonth3 ), 3 ), ' ', RIGHT( Year( DateBasis.dMonth3 ), 2 ), ')' ) as ThirdMonth,
      CONCAT( 'X (', LEFT( MonthName( DateBasis.dMonth4 ), 3 ), ' ', RIGHT( Year( DateBasis.dMonth4 ), 2 ), ')' ) as FourthMonth
   from   
      ( select @FirstOfMonth dFirstOfMonth,
               @FDOM nWeekDay,
               @SWOM nSecondWedOfMonth,
               @SkipMonths nSkip,
               @Month1 dMonth1,
               @Month2 dMonth2,
               @Month3 dMonth3,
               @Month4 dMonth4
           from
              ( select @FirstOfMonth := CONCAT( year(curdate()), '-', month( curdate()), '-01' ),
                       @FDOW := DayOfWeek( @FirstOfMonth ),
                       @SWOM := if( @FDOW <= 4, 12, 19) - @FDOW,
                       @SkipMonths := if( day( CurDate()) <= @SWOM, 1, 2 ),
                       @Month1 := date_add( @FirstOfMonth, interval 0 +  @SkipMonths month ),
                       @Month2 := date_add( @Month1, interval 1 month ),
                       @Month3 := date_add( @Month2, interval 1 month ),
                       @Month4 := date_add( @Month3, interval 1 month )
                       ) sqlvars
      ) DateBasis

上面这个查询的结果将返回一条 SINGLE 记录(基于当前日期 1 月 31 日)以显示
FirstMonth SecondMonth ThirdMonth FourthMonth
Q(3 月 12 日) U(4 月 12 日) V(5 月 12 日) X(6 月 12 日)

现在，将其嵌套在查询的其余部分中，以获取代码 ID，例如

SELECT hist.date, 
       hist.ticker_id, 
       hist.settle_price, 
       hist.volume 
   FROM 
      hist,
      ( entire select statement above ) FinalDates

   WHERE 
          hist.ticker_id IN ( FinalDates.FirstMonth,
                              FinalDates.SecondMonth,
                              FinalDates.ThirdMonth,
                              FinalDates.FourthMonth )
      and hist.trade_dt = curdate()

如果你看前面提到的最里面的@SqlVariables 就像一堆“let x=something”。我总是需要一个基础才能开始，所以我首先将给定月份的第一天放入变量@FirstOfMonth，方法是将当前日期的年份+“-”+当前日期的月份+ "-01"总是从月初开始... 例如:今天是 2012 年 1 月 31 日，将构建一个字符串 '2012-01-01'，它的年/月/日格式会立即被 MySQL 识别作为日期格式，我们可以对其执行日期算术。所以现在，我有@FirstOfMonth = '2012-01-01'。现在，我们需要确定这个日期代表我们所在月份的第一天(因此@FDOW)。这将返回一个 1-7 的值(星期日 = 1，星期三 = 4，星期六 = 7)。

由此，我们现在需要计算每月的第二个星期三是什么时候。如果星期几是星期日到星期三(包括星期三)，则第二个星期三是 12 天减去星期几。例如:星期天 1 号是 4 号星期三，然后是 11 号星期三……所以 12 - 1(星期日)= 11。如果一个月的第一天是星期三，那将是一周中的某一天 = 4，但是每月 1 日 = 星期三，第二个星期三 = 8，因此 12 - 4 = 8。现在，如果日期是星期四、星期五或星期六作为本月的第一天，则星期几将是 5、6 或 7 . 第一个星期三的最小日期是 7，第二个星期三是 14，所以这是从 19 开始 - 无论是一周中的哪一天... 5, 6, 7... 例如:19 - 5(星期四Week) = 14, 19 - 6(Fri Day of Week) = 13, 19 - 7(Sat Day of Week) = 12.. 所以，我们知道第一个星期三将是整周，所以最早会be 是第 7 和第 14，而不是第 1 和第 8(月初)。

现在我们知道每月的第二个星期三是什么时候，将其与我们运行查询的日期进行比较(即: curdate() )。如果当前日期是 ON 或 BEFORE(通过 <=)MONTH 的第二个星期三(@SWOM)，那么我们只想跳过 1 个月......如果我们在这个月更远，我们需要跳过 2 个月。

现在，建立日期。第 1 个月的日期基础是当月的第一个月加上要跳过的几个月的间隔。第 2 个月是第 1 个月后的一个月，第 3 个月是第 2 个月后的一个月，第 4 个月是第 3 个月后的一个月。

@FirstOfMonth := CONCAT( year(curdate()), '-', month( curdate()), '-01' ),
@FDOW := DayOfWeek( @FirstOfMonth ),
@SWOM := if( @FDOM <= 4, 12, 19) - @FDOM,
@SkipMonths := if( day( CurDate()) <= @SWOM, 1, 2 ),
@Month1 := date_add( @FirstOfMonth, interval 0 +  @SkipMonths month ),
@Month2 := date_add( @Month1, interval 1 month ),
@Month3 := date_add( @Month2, interval 1 month ),
@Month4 := date_add( @Month3, interval 1 month )

所以我们终于有 4 个月的基础可以在单行 (select ...) sqlvars 结果集中使用，显示类似

@Month1     @Month2     @Month3     @Month4
2012-03-01  2012-04-01  2012-05-01  2012-06-01 ... the four months out

最后，一旦这个数据看起来没问题，我们现在可以用各自的“Q”、“U”、“V”和“X”前缀加上月份名称的左边 3 和 2 来构建您正在寻找的特定字符串数字年份。

因此，通过获取您期望的所有日期范围和字符串，请根据我在最初列出的其他表查询它。

我希望这对您有所帮助，并让您看到一个全新的上下文来欺骗 SQL...本质上是在做一个内联程序来创建许多变量并从中进行操作...非常酷呵呵...

老实说，这是我第一次专门尝试这种技术，尽管我过去使用 SQLVars 做过很多查询。