mysql - SQL - 基于日期时间查询多个聚合 - MySQL

Question

这是一个复杂的问题。但是我有一个表，其中有一个 DATETIME 字段，以及其他一些需要求和和平均的 int 和 float 字段。我们想根据时间戳对该表进行求和和平均，最终目标是开发 3 个查询，从某种意义上说，它们将相互构建。

所以能力看起来像这样

     TIMESTAMP      |subj_diff| SCR2  | SCR3
2011-09-20 09:01:37 |  1      | 0.02  | 1.6
2011-09-20 09:04:18 |  3      | 0.09  | 1.8
2011-09-20 14:24:55 |  5      | 0.21  | 1.2
2011-09-21 18:50:47 |  8      | 0.08  | 0.9
2011-09-21 18:54:21 |  9      | 0.12  | 2.1

我们想要生成的三个查询是:

1. 汇总从以前的数据到当前选择的记录的所有前面的项目。还应该有另一列包含总数所以举例来说，如果我们想要 20 日和 21 日之间的结果，返回的表将如下所示:

     TIMESTAMP      |subj_diff| SCR2  | SCR3  | COUNT
2011-09-20 09:01:37 |  1      | 0.02  | ...   |  1
2011-09-20 09:04:18 |  4      | 0.11  |       |  2
2011-09-20 14:24:55 |  9      | 0.32  |       |  3
2011-09-21 18:50:47 |  17     | ...
2011-09-21 18:54:21 |  26     |  

2. 以 5 分钟的时间间隔汇总结果 - 与上述类似，但查询将返回 3 行，因为第 1 行和第 2 行以及第 4 行和第 5 行将以与以下相同的方式汇总在一起多于。在此查询中，如果每 5 分钟间隔返回 0 且计数为 0，则可以。例如

     TIMESTAMP      |subj_diff| SCR2  | SCR3  | COUNT
2011-09-20 09:05:00 |  4      | 0.11  | 3.4   |   2
2011-09-20 14:25:00 |  5      | 0.21  | 1.2   |   1
2011-09-21 18:55:00 |  17     | 0.20  | 3.0   |   2

3. 在一天中的每 5 分钟间隔(即从 00:05:00 到 24:00:00)，对查询 2 的结果集在查询 1 中执行相同的操作).

这是一个相当棘手的问题，我不知道如何开始这个问题。谁能写 SQL 来解决这个问题？

这里有一些使用游标和存储过程的基本代码，但它并没有真正起作用。

DROP PROCEDURE curdemo;
DELIMITER $$ 
CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT 0;

DECLARE a datetime;
DECLARE b,c FLOAT;

DECLARE cur1 CURSOR FOR 
SELECT  msgDate, subj_diff FROM classifier_results
WHERE DATE(msgDate) >= DATE('2011-09-25');

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

CREATE TEMPORARY TABLE IF NOT EXISTS temp_scores (d datetime, acc float);

OPEN cur1;

read_loop: LOOP
    FETCH cur1 INTO a, b;
    IF done THEN
      LEAVE read_loop;
    END IF;
    INSERT temp_scores(d,acc)
    SELECT a, SUM(subj_diff) FROM classifier_results 
    WHERE DATE(msgDate) >= DATE('2011-09-25')
    AND msgDate <= a;

END LOOP;

CLOSE cur1;

SELECT * FROM temp_scores;

END;

干杯!

Answer 1

试试这段代码-

创建并填充表:

CREATE TABLE classifier_results(
  `TIMESTAMP` DATETIME NOT NULL,
  subj_diff INT(11) DEFAULT NULL,
  scr2 FLOAT(10, 5) DEFAULT NULL,
  scr3 FLOAT(10, 5) DEFAULT NULL
);

INSERT INTO classifier_results VALUES 
  ('2011-09-20 09:01:37', 1, 0.02000, 1.60000),
  ('2011-09-20 09:04:18', 3, 0.09000, 1.80000),
  ('2011-09-20 14:24:55', 5, 0.21000, 1.20000),
  ('2011-09-21 18:50:47', 8, 0.08000, 0.90000),
  ('2011-09-21 18:54:21', 9, 0.12000, 2.10000);

并执行这些查询:

-- 1 query
SET @subj_diff = 0;
SET @scr2 = 0;
SET @scr3 = 0;
SET @cnt = 0;
SELECT timestamp,
  @subj_diff:=IF(@subj_diff IS NULL, subj_diff, @subj_diff + subj_diff) subj_diff,
  @scr2:=IF(@scr2 IS NULL, scr2, @scr2 + scr2) scr2,
  @scr3:=IF(@scr3 IS NULL, scr3, @scr3 + scr3) scr3,
  @cnt:=@cnt+1 count
FROM classifier_results;

+---------------------+-----------+---------+---------+-------+
| timestamp           | subj_diff | scr2    | scr3    | count |
+---------------------+-----------+---------+---------+-------+
| 2011-09-20 09:01:37 |         1 | 0.02000 | 1.60000 |     1 |
| 2011-09-20 09:04:18 |         4 | 0.11000 | 3.40000 |     2 |
| 2011-09-20 14:24:55 |         9 | 0.32000 | 4.60000 |     3 |
| 2011-09-21 18:50:47 |        17 | 0.40000 | 5.50000 |     4 |
| 2011-09-21 18:54:21 |        26 | 0.52000 | 7.60000 |     5 |
+---------------------+-----------+---------+---------+-------+

-- 2 query
SELECT
  DATE(timestamp) + INTERVAL 5  * (12 * HOUR(timestamp) + FLOOR(MINUTE(timestamp) / 5)) MINUTE new_timestamp,
  SUM(subj_diff) subj_diff,
  SUM(scr2) scr2,
  SUM(scr3) scr3,
  COUNT(*) count
FROM classifier_results
GROUP BY new_timestamp;

+---------------------+-----------+---------+---------+-------+
| new_timestamp       | subj_diff | scr2    | scr3    | count |
+---------------------+-----------+---------+---------+-------+
| 2011-09-20 09:00:00 |         4 | 0.11000 | 3.40000 |     2 |
| 2011-09-20 14:20:00 |         5 | 0.21000 | 1.20000 |     1 |
| 2011-09-21 18:50:00 |        17 | 0.20000 | 3.00000 |     2 |
+---------------------+-----------+---------+---------+-------+

-- 3 query
SET @subj_diff = 0;
SET @scr2 = 0;
SET @scr3 = 0;
SET @cnt = 0;
SELECT new_timestamp timestamp,
  @subj_diff:=IF(@subj_diff IS NULL, subj_diff, @subj_diff + subj_diff) subj_diff,
  @scr2:=IF(@scr2 IS NULL, scr2, @scr2 + scr2) scr2,
  @scr3:=IF(@scr3 IS NULL, scr3, @scr3 + scr3) scr3,
  @cnt:=@cnt+1 count
FROM (
  SELECT
    DATE(timestamp) + INTERVAL 5  * (12 * HOUR(timestamp) + FLOOR(MINUTE(timestamp) / 5)) MINUTE new_timestamp,
    SUM(subj_diff) subj_diff,
    SUM(scr2) scr2,
    SUM(scr3) scr3,
    COUNT(*) count
  FROM classifier_results
  GROUP BY new_timestamp
) t;

+---------------------+-----------+---------+---------+-------+
| timestamp           | subj_diff | scr2    | scr3    | count |
+---------------------+-----------+---------+---------+-------+
| 2011-09-20 09:00:00 |         4 | 0.11000 | 3.40000 |     1 |
| 2011-09-20 14:20:00 |         9 | 0.32000 | 4.60000 |     2 |
| 2011-09-21 18:50:00 |        26 | 0.52000 | 7.60000 |     3 |
+---------------------+-----------+---------+---------+-------+

祝你好运!