mysql - 如何使用 MySQL 变量赋值来迭代行？

Question

我有一个包含日期、分数和 ID 的表，在本例中该表不是唯一的。

这是一个示例数据集和相同的 SQLFiddle:

create TABLE variable_test (
      date DATE
    , id INT
    , score INT
);

insert into variable_test
(date,id, score)
VALUES
('2018-01-01',123456,465),
('2018-01-02',123456,165),
('2018-01-03',123456,14),
('2018-01-04',123456,420),
('2018-01-05',123456,4152),
('2018-01-06',123456,1),
('2018-01-07',123456,5923),
('2018-01-08',123456,42),
('2018-01-09',123456,2154),
('2018-01-10',123456,1542),
('2018-01-11',123456,15183),
('2018-01-12',123456,15603),
('2018-01-13',123456,152),
('2018-01-14',123456,210),
('2018-01-15',123456,25),
('2018-01-16',123456,232),
('2018-01-17',123456,1),
('2018-01-18',123456,75),
('2018-01-19',123456,36),
('2018-01-20',123456,45),
('2018-01-21',123456,75212),
('2018-01-22',123456,111),
('2018-01-23',123456,2),
('2018-01-24',123456,2),
('2018-01-25',123456,65),
('2018-01-26',123456,511),
('2018-01-27',123456,210),
('2018-01-28',123456,25),
('2018-01-29',123456,232),
('2018-01-30',123456,1),
('2018-01-31',123456,75),
('2018-02-01',123456,36),
('2018-02-02',123456,45),
('2018-02-03',123456,75212),
('2018-02-04',123456,111),
('2018-02-05',123456,2),
('2018-02-06',123456,2),
('2018-02-07',123456,65),
('2018-02-08',123456,511),
('2018-02-09',123456,210),
('2018-02-10',123456,25),
('2018-02-11',123456,232),
('2018-02-12',123456,1),
('2018-02-13',123456,75),
('2018-02-14',123456,36),
('2018-02-15',123456,45),
('2018-02-16',123456,75212),
('2018-02-17',123456,111),
('2018-02-18',123456,2),
('2018-02-19',123456,2),
('2018-02-20',123456,65),
('2018-02-21',123456,511),
('2018-02-22',123456,210),
('2018-02-23',123456,25),
('2018-02-24',123456,232),
('2018-02-25',123456,1),
('2018-02-26',123456,75),
('2018-02-27',123456,36),
('2018-02-28',123456,45),
('2018-01-01',78910,75212),
('2018-01-02',78910,111),
('2018-01-03',78910,2),
('2018-01-04',78910,2),
('2018-01-05',78910,65),
('2018-01-06',78910,511),
('2018-01-07',78910,210),
('2018-01-08',78910,25),
('2018-01-09',78910,232),
('2018-01-10',78910,1),
('2018-01-11',78910,75),
('2018-01-12',78910,36),
('2018-01-13',78910,45),
('2018-01-14',78910,75212),
('2018-01-15',78910,111),
('2018-01-16',78910,2),
('2018-01-17',78910,2),
('2018-01-18',78910,65),
('2018-01-19',78910,511),
('2018-01-20',78910,210),
('2018-01-21',78910,25),
('2018-01-22',78910,232),
('2018-01-23',78910,1),
('2018-01-24',78910,75),
('2018-01-25',78910,36),
('2018-01-26',78910,45),
('2018-01-27',78910,75212),
('2018-01-28',78910,111),
('2018-01-29',78910,2),
('2018-01-30',78910,2),
('2018-01-31',78910,65),
('2018-02-01',78910,511),
('2018-02-02',78910,210),
('2018-02-03',78910,25),
('2018-02-04',78910,232),
('2018-02-05',78910,1),
('2018-02-06',78910,75),
('2018-02-07',78910,36),
('2018-02-08',78910,45),
('2018-02-09',78910,75212),
('2018-02-10',78910,111),
('2018-02-11',78910,2),
('2018-02-12',78910,2),
('2018-02-13',78910,65),
('2018-02-14',78910,511),
('2018-02-15',78910,210),
('2018-02-16',78910,25),
('2018-02-17',78910,232),
('2018-02-18',78910,1),
('2018-02-19',78910,75),
('2018-02-20',78910,36),
('2018-02-21',78910,45),
('2018-02-22',78910,75212),
('2018-02-23',78910,111),
('2018-02-24',78910,2),
('2018-02-25',78910,2),
('2018-02-26',78910,65),
('2018-02-27',78910,511),
('2018-02-28',78910,210),
('2018-01-01',11121314,25),
('2018-01-02',11121314,232),
('2018-01-03',11121314,1),
('2018-01-04',11121314,75),
('2018-01-05',11121314,36),
('2018-01-06',11121314,45),
('2018-01-07',11121314,75212),
('2018-01-08',11121314,111),
('2018-01-09',11121314,2),
('2018-01-10',11121314,2),
('2018-01-11',11121314,65),
('2018-01-12',11121314,511),
('2018-01-13',11121314,210),
('2018-01-14',11121314,25),
('2018-01-15',11121314,232),
('2018-01-16',11121314,1),
('2018-01-17',11121314,75),
('2018-01-18',11121314,36),
('2018-01-19',11121314,45),
('2018-01-20',11121314,75212),
('2018-01-21',11121314,111),
('2018-01-22',11121314,2),
('2018-01-23',11121314,2),
('2018-01-24',11121314,65),
('2018-01-25',11121314,511),
('2018-01-26',11121314,210),
('2018-01-27',11121314,25),
('2018-01-28',11121314,232),
('2018-01-29',11121314,1),
('2018-01-30',11121314,75),
('2018-01-31',11121314,36),
('2018-02-01',11121314,45),
('2018-02-02',11121314,75212),
('2018-02-03',11121314,111),
('2018-02-04',11121314,2),
('2018-02-05',11121314,2),
('2018-02-06',11121314,65),
('2018-02-07',11121314,511),
('2018-02-08',11121314,210),
('2018-02-09',11121314,25),
('2018-02-10',11121314,232),
('2018-02-11',11121314,1),
('2018-02-12',11121314,75),
('2018-02-13',11121314,36),
('2018-02-14',11121314,45),
('2018-02-15',11121314,75212),
('2018-02-16',11121314,111),
('2018-02-17',11121314,2),
('2018-02-18',11121314,2),
('2018-02-19',11121314,65),
('2018-02-20',11121314,511),
('2018-02-21',11121314,210),
('2018-02-22',11121314,25),
('2018-02-23',11121314,232),
('2018-02-24',11121314,1),
('2018-02-25',11121314,75),
('2018-02-26',11121314,36),
('2018-02-27',11121314,45),
('2018-02-28',11121314,75212);

http://sqlfiddle.com/#!9/cd3a81/4 。

我正在尝试遍历每个 id 的表并得出分数的 3 天移动平均值。

我有以下代码似乎可以实现此目的，但我不完全理解它是如何工作的。它按 id 和日期排序，并在 id 更改时重置。

我想要了解的是这些变量分配是如何工作的。

也许有更好的方法来做到这一点。谢谢。

 select date, id, AVG(three_day_total)
from (
SELECT
    date,
    id,
    score,
    CASE @i
    WHEN id
        THEN @i := id
    ELSE (@i := id)
             AND (@n := 0)
             AND (@a0 := 0) AND (@a1 := 0) AND (@a2 := 0)
    END               AS a,
    @a0 := @a1,
    @a1 := @a2,
    @a2 := score,
    (@a0 + @a1 + @a2) AS three_day_total
FROM qa_db.variable_test
ORDER BY id, date
 ) x
group by x.date, x.id

运行查询的内部部分会显示 case 语句的更多详细信息:

SELECT
    date,
    id,
    score,
    CASE @i
    WHEN id
        THEN @i := id
    ELSE (@i := id)
             AND (@n := 0)
             AND (@a0 := 0) AND (@a1 := 0) AND (@a2 := 0)
    END               AS a,
    @a0 := @a1,
    @a1 := @a2,
    @a2 := score,
    (@a0 + @a1 + @a2) AS three_day_total
FROM qa_db.variable_test
ORDER BY id, date

主要目标是获得每行中每个日期和 ID 的连续 3 天得分总计。我根据结果了解此方法的作用。我试图更清楚地理解案例状态中的变量分配是如何实现这一目标的，或者也许这是一个更好的方法。

Answer 1

我不明白结果是如何计算的 - 或者即使它们确实是按照您想要的方式计算的。

让我们减少数据集:

SELECT * FROM variable_test WHERE id IN(78910,123456) AND date < '2018-01-07';
+------------+--------+-------+
| date       | id     | score |
+------------+--------+-------+
| 2018-01-01 |  78910 | 75212 |
| 2018-01-01 | 123456 |   465 |
| 2018-01-02 |  78910 |   111 |
| 2018-01-02 | 123456 |   165 |
| 2018-01-03 |  78910 |     2 |
| 2018-01-03 | 123456 |    14 |
| 2018-01-04 |  78910 |     2 |
| 2018-01-04 | 123456 |   420 |
| 2018-01-05 |  78910 |    65 |
| 2018-01-05 | 123456 |  4152 |
| 2018-01-06 |  78910 |   511 |
| 2018-01-06 | 123456 |     1 |
+------------+--------+-------+

该数据集的预期结果应该是什么样的(显示计算)？