mysql - INSERT 插入行到现有表中

Question

我有一个类似于这个简化示例的 MySQL 表:

          orders table
--------------------------------
orderid stockid rem_qty reported
--------------------------------
1000000     100     500 00:01:00
1000000     100     200 01:10:00
1000000     100     200 03:20:00
1000000     100     100 04:30:00
1000000     100      50 11:30:00
:
1000010     100     100 00:01:00
1000010     100     100 01:10:00
1000010     100      20 03:20:00
:
1000020     200    1000 03:20:00
1000020     200     995 08:20:00
1000020     200     995 11:50:00
--------------------------------

该表来自第 3 方，每天约有 80-100M 行，格式固定。这会很好，但它缺少显示 rem_qty 何时达到零的行。好消息是，我可以估计它们，至少是一个好的上限/下限:

第 3 方在一天中基本随机的时间扫描每个不同的 stockid，并在那个时候为每个打开的 orderid 返回一行。例如，stockid = 100 在 (00:01, 01:10, 03:20, 04:30, 11:30) 被扫描。每次，每个 当前 orderid 和 stockid 都会有一行。因此，可以看到 orderid = 1000000 在 11:30(我们数据中的最后一次扫描)仍然开放，但在 03:20 和 04:30 之间的某个时间，orderid = 1000010 售罄。 (stockid = 200 的时间与 stockid = 100 无关)。

所以，我想做的是INSERT 为每个售罄的订单添加 rem_qty = 0 的内插行。在这种情况下，我们(只能)说 orderid = 1000010 在 AVG('03:20:00','04:30:00') 处变为 0 ，所以我想INSERT以下行:

      orders table INSERT
--------------------------------
orderid stockid rem_qty reported
--------------------------------
1000010     100       0 03:55:00
--------------------------------

问题是，我的 SQL 生锈了，我无法弄清楚这个复杂的查询。在其他失败的尝试中，我尝试了各种JOIN，创建了一个TEMPORARY TABLE stock_report(stockid,last_report)，我可以这样做:

SELECT      orders.stockid,
            orderid,
            MAX(reported),
            TIMEDIFF(last_report,MAX(reported)) as timediff
FROM        orders
INNER JOIN  stock_report
        ON  orders.stockid = stock_report.stockid
GROUP BY    orderid
HAVING      timediff > 0
ORDER BY    orderid

这将显示每个售罄的订单，以及上次报告 orderid 与最后 < em>stockid 被报告了。这可能是一个好的开始，但我需要能够计算 next_report 列(特定于此 orderid，而不是 last_report，这将基本上是:

SELECT      MIN(reported) AS next_report
FROM        orders
WHERE       reported > @order_max_reported
ORDER BY    reported
LIMIT       1

但这只是徒劳地试图说明我所追求的部分内容。同样，我真正需要的是在订单的 AVG() 时间INSERT 新行到 orders() 表中的方法>rem_qty 变为 0，如上面的 orders table INSERT 示例表中所示。或者，也许是 64,000 GFLOP 问题:将此逻辑转移到我的主要(应用程序)语言中会更好吗？我每天处理 1 亿行，因此效率是一个问题。 p>

为冗长的描述道歉。为了简洁起见，这真的是我能做的最好的编辑!任何人都可以提供任何有用的建议吗？

Answer 1

可以做到。有一个子查询，获取每个订单 ID/股票 ID 的最大报告时间，并将其与股票 ID 相同且最新时间小于订单时间的订单表连接起来。这将为您提供该股票 ID 的所有报告时间，这些时间大于该股票 ID 和订单 ID 的最新时间。

使用 MIN 获取最短报告时间。将 2 时间转换为秒，将它们相加并除以 2，然后从秒转换回时间。

像这样:-

SELECT orderid, stockid, 0, SEC_TO_TIME((TIME_TO_SEC(next_poss_order_report) + TIME_TO_SEC(last_order_report)) / 2)
FROM
(
    SELECT a.orderid, a.stockid, last_order_report, MIN(b.reported) next_poss_order_report
    FROM 
    (
        SELECT orderid, stockid, MAX(reported) last_order_report
        FROM orders_table
        GROUP BY orderid, stockid
    ) a
    INNER JOIN orders_table b
    ON a.stockid = b.stockid
    AND a.last_order_report < b.reported
    GROUP BY a.orderid, a.stockid, a.last_order_report
) sub0;

SQL fiddle 在这里:-

http://www.sqlfiddle.com/#!2/cf129/17

可以将其简化为:-

SELECT a.orderid, a.stockid, 0, SEC_TO_TIME((TIME_TO_SEC(MIN(b.reported)) + TIME_TO_SEC(last_order_report)) / 2)
FROM 
(
    SELECT orderid, stockid, MAX(reported) last_order_report
    FROM orders_table
    GROUP BY orderid, stockid
) a
INNER JOIN orders_table b
ON a.stockid = b.stockid
AND a.last_order_report < b.reported
GROUP BY a.orderid, a.stockid, a.last_order_report;

这些查询可能需要一段时间，但可能比从脚本代码运行许多查询更有效。