mysql - 汇总日内数据

Question

我在每帧 1 分钟内存储了股票数据，因此 1 行 = 1 分钟，我正在使用此查询来获取每天的最小值、最大值、总和、第一个和最后一个记录。

我每天有 511 行 1 分钟(从 0900 到 1730)。

SELECT  MAX(high), MIN(low), SUM(volume) from a2a where date = 20131202; 
SELECT open FROM a2a ORDER BY time LIMIT 1;
SELECT close FROM a2a  WHERE time = 1730 ORDER BY time DESC LIMIT 1; 

SELECT  MAX(high), MIN(low), SUM(volume) from a2a where date = 20131203; 
SELECT open FROM a2a ORDER BY time LIMIT 1;
SELECT close FROM a2a  WHERE time = 1730 ORDER BY time DESC LIMIT 1;

SELECT  MAX(high), MIN(low), SUM(volume) from a2a where date = 20131204; 
SELECT open FROM a2a ORDER BY time LIMIT 1;
SELECT close FROM a2a  WHERE time = 1730 ORDER BY time DESC LIMIT 1;

在 Netbeans 中，这将返回 9 个选项卡:

我的问题是:

1) 有没有办法在单个选项卡中获得这些结果？

2) 我需要每天执行 3 次不同的选择，还是有办法在单个查询中完成？

3) 有没有比这更快的方法来计算这些聚合？贴出来的只是例子，我每天需要获取数百条数据。

编辑表a2a图像。日期和时间类型为 CHAR，打开到关闭为 DOUBLE，交易量为 INTEGER。

Answer 1

试试这个高/低/音量

 SELECT symbol,
        date,
        MAX(high)    AS dailyhigh,
        MIN(low)     AS dailylow,
        SUM(volume)  AS dailyvolume
  GROUP BY symbol, date

每个符号每天一行。

很难说您是如何处理开盘价的。当然，即使没有日期，整个表中的第一个时间戳也不是正确的方法。因此，让我们找出每个日期的最早时间戳。

 SELECT symbol,
        date,
        MIN(time) AS opentime
   FROM a2a
  GROUP BY symbol, date

现在，我们需要查找该时间戳的开盘价。怎么做？它变得有点复杂，因为对于该问题的特定时间和日期，您可能有不止一行。

 SELECT a.symbol, 
        a.date,
        AVG (a.open) AS dailyopen
   FROM a2a a
       JOIN (
         SELECT symbol,
                date,
                MIN(time) AS opentime
           FROM a2a
          GROUP BY symbol, date 
        ) AS b   ON a.symbol = b.symbol 
                AND a.date = b.date
                AND a.time = b.opentime
  GROUP BY a.symbol, a.date

这会提取每个交易品种和日期的开盘记录(最低时间记录)。

现在，在我看来，您的关闭记录是时间戳为 1730 的记录，或者时间戳小于 1730 的最新记录。这正在从活泼的 SQL 转变为实际工作，但这些是财务记录和 Madoff在 jail 里，所以让我们做对吧。这是每个交易品种在每个日期的收盘时间。

 SELECT symbol,
        date,
        MAX(time) AS closetime
   FROM a2a
  WHERE time <= 1730
  GROUP BY symbol, date

现在我们需要每个交易品种在每个日期的收盘价。我们将以与检索开盘价相同的方式检索它。

 SELECT a.symbol, 
        a.date,
        AVG (a.close) AS dailyclose
   FROM a2a a
       JOIN (
         SELECT symbol,
                date,
                MAX(time) AS closetime
           FROM a2a
          WHERE time <= 1730
          GROUP BY symbol, date
        ) AS b   ON a.symbol = b.symbol
                AND a.date = b.date
                AND a.time = b.closetime
  GROUP BY a.symbol, a.date

下一步是将这三个摘要查询连接在一起，并限制您处理的日期范围。

SELECT d.symbol, d.date, d.dailyhigh, d.dailylow, d.dailyvolume,
       o.dailyopen,
       c.dailyclose
  FROM (
      /* the first summary query, daily high low volume */
       ) AS d
  JOIN (
      /* the second summary query, daily open */ 
       ) AS o ON d.symbol = o.symbol AND d.date = o.date
  JOIN (
      /* the third summary query, daily close */ 
       ) AS c ON d.symbol = c.symbol AND d.date = c.date
 WHERE d.date >= DATE_FORMAT(NOW() - INTERVAL 3 DAY, '%Y%m%d') 
   AND d.date  < DATE_FORMAT(NOW(), '%Y%m%d')
 ORDER BY d.symbol, d.date

这将为您提供三天的数据，从昨天开始。您必须将三个摘要查询剪切并粘贴到这个大连接中。我有点希望你会这样做，因为像这样格式化深度嵌套的查询并不是很有趣。

这并不简单，但它对您的业务规则非常稳健和明确。