每小时从一张桌子上随机归档5条记录，并使重量为1/4

Question

我有两个表，一个是InvoiceFull，另一个是TestInvoiceFull。

在我基于php和mysql的web应用程序中，每小时有100条记录被输入到InvoiceFull中。我需要在MySQL中创建一个事件，以每小时从InvoiceFull中获取5条记录和1/4的权重，并将这些记录插入TestInvoiceFull中。TestInvoiceFull是我的存档表。

我已经编写了如下查询，它每小时插入1条记录，但我每小时需要5条记录。

INSERT INTO TestInvoiceFull (Weight,Status,StatusChangedTime)
SELECT a.Weight/4 AS ApprovedWeight,
       a.Status,
       a.StatusChangedTime
FROM InvoiceFull a
INNER JOIN
  (SELECT DATE(StatusChangedTime) date, HOUR(StatusChangedTime) hour,
                                        MIN(StatusChangedTime) min_date
   FROM InvoiceFull
   WHERE status='Approved'
   GROUP BY DATE(StatusChangedTime),
            HOUR(StatusChangedTime)) b ON DATE(a.StatusChangedTime) = b.date
AND HOUR(a.StatusChangedTime) = b.hour
AND a.StatusChangedTime = b.min_date;

我在小时组中选择了最短时间，因此每小时只获取1行。我如何才能每小时获取5条或一般情况下的N条记录。

提前谢谢。

Answer 1

您的要求是：

1. 随机选择的记录
2. 前一小时
3. 已插入并进行了修改
4. 按日期时间升序编辑

让我们一次吃一个。要随机选择五条记录，您需要

SELECT whatever
  FROM table
 ORDER BY RAND()
 LIMIT 5

它不快，但会起作用的。

其次，您需要在前一个小时内用StatusChangedTime选取记录。要做到这一点，你需要一种方法来开始当下。

STR_TO_DATE(NOW(), '%Y-%m-%d %H:00:00')

这样做。它占用时间NOW()，并将分钟和秒更改为零。我们可以使用相同的想法来获得前一小时的开始：

STR_TO_DATE(NOW(), '%Y-%m-%d %H:00:00') - INTERVAL 1 HOUR

因此，让我们在WHERE子句中使用这些表达式。

SELECT whatever
  FROM InvoiceFull a
 WHERE a.StatusChangedTime >= STR_TO_DATE(NOW(), '%Y-%m-%d %H:00:00') - INTERVAL 1 HOUR
   AND a.StatusChangedTime  < STR_TO_DATE(NOW(), '%Y-%m-%d %H:00:00')

这会选择从上一行开始直到的所有行，但不包括当前小时的开始。

接下来，您需要修改结果集中的一列。你做得对。如果Weight列是浮点值，则可能应该使用Weight * 0.25而不是除法。

INSERT INTO TestInvoiceFull
       (Weight,Status,StatusChangedTime)
SELECT (a.Weight * 0.25) AS ApprovedWeight,
       a.Status,
       a.StatusChangedTime  ...

最后，您需要按日期时间戳的顺序随机排列五条记录。因此，将您的基本SELECT查询放入子查询中。。。

INSERT INTO TestInvoiceFull
       (Weight,Status,StatusChangedTime)
SELECT *
  FROM (
  SELECT (a.Weight * 0.25) AS ApprovedWeight,
         a.Status,
         a.StatusChangedTime
    FROM InvoiceFull a
   WHERE a.StatusChangedTime >= STR_TO_DATE(NOW(), '%Y-%m-%d %H:00:00') - INTERVAL 1 HOUR
     AND a.StatusChangedTime  < STR_TO_DATE(NOW(), '%Y-%m-%d %H:00:00')
   ORDER BY RAND()
   LIMIT 5
  ) b ORDER BY StatusChangedTime

这是你的五张随机记录，按日期顺序排列。在每小时的某个时间运行此项，您将获得最后一小时的记录样本。我一直等到每小时过了五到十分钟才得到最好的结果，以防系统的某个部分落后。

请注意，您在问题的查询中滥用了GROUP BY。GROUP BY只有在使用像SUM()这样的聚合操作时才有帮助。