从MySQL数据库的前N行获得运行频率分布

Question

我有一个MySQL数据库，其中一个列包含状态代码。该列为int类型，其值永远只为100,200,300,400。如下所示:为了清晰起见，删除了其他列。

id   |  status
----------------
 1      300
 2      100
 3      100
 4      200
 5      300
 6      300
 7      100
 8      400
 9      200
10      300
11      100
12      400
13      400
14      400
15      300
16      300

id字段是自动生成的，并且始终是顺序的。我希望有第三列显示前10行状态代码的频率分布的逗号分隔的字符串。应该是这样的。

id   |  status  |  freq
-----------------------------------
 1      300
 2      100
 3      100
 4      200
 5      200
 6      300
 7      100
 8      400
 9      300
10      300
11      100       300,100,200,400    -- from rows 1-10
12      400       100,300,200,400    -- from rows 2-11
13      400       100,300,200,400    -- from rows 3-12
14      400       300,400,100,200    -- from rows 4-13
15      300       400,300,100,200    -- from rows 5-14
16      300       300,400,100        -- from rows 6-15

我想先列出最常用的代码。如果两个状态代码的频率相同，那么首先列出的对我来说并不重要，但在示例中，我确实在较大的代码之前列出了较小的代码。最后，如果代码在前十行中根本没有出现，那么它也不应该在freq列中列出。

为了非常清楚，频率字符串出现在的行号是否而不是考虑了该行的状态代码；它只是前面的行。

那我做了什么？我对SQL非常熟悉。我是一个程序员，我觉得这种SQL语言习惯起来有点奇怪。我管理了以下自联接选择语句。

select *, avg(b.status) freq
from sample a
join sample b
on (b.id < a.id) and (b.id > a.id - 11)
where a.id > 10
group by a.id;

使用聚合函数avg，我至少可以演示这个概念。派生表b为avg函数提供了正确的行，但我无法计算出从b中计数和分组行以获得频率分布的多步过程，然后将频率行折叠为单个字符串值。

此外，我还尝试使用标准存储函数和过程来代替内置的聚合函数，但似乎b派生表超出了范围或其他方面。我似乎无法访问它。据我所理解，编写自定义聚合函数对我来说是不可能的，因为它似乎需要用C开发，这是我没有受过训练的。

下面是加载示例的sql。

create table sample (
    id int NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    status int
);

insert into sample(status) values(300),(100),(100),(200),(200),(300)
  ,(100),(400),(300),(300),(100),(400),(400),(400),(300),(300),(300)
  ,(100),(400),(100),(100),(200),(500),(300),(100),(400),(200),(100)
  ,(500),(300);

该示例有30行数据可供处理。我知道这是个很长的问题，但我只想尽可能的详细。我已经做了几天了，我真的很想把它做好。

谢谢你的帮助。

Answer 1

我所知道的执行您要求的唯一方法是使用BEFORE INSERT触发器。它必须是BEFORE INSERT，因为您希望更新要插入的行中的值，该值只能在BEFORE触发器中执行。不幸的是，这也意味着它还没有被分配一个ID，所以希望能够安全地假设在插入新记录时，表中的最后10个记录就是您感兴趣的记录。您的触发器将需要获取最后10个ID的值，并使用GROUP_CONCAT函数将它们连接到由COUNT命令的单个字符串中。我一直在使用Server，目前还无法访问MySQL服务器来测试这一点，但希望我的语法足够接近，至少可以让您朝着正确的方向前进：

create trigger sample_trigger BEFORE INSERT ON sample 
FOR EACH ROW
BEGIN
    DECLARE _freq varchar(50);

    SELECT GROUP_CONCAT(tbl.status ORDER BY tbl.Occurrences) INTO _freq
    FROM (SELECT status, COUNT(*) AS Occurrences, 1 AS grp FROM sample ORDER BY id DESC LIMIT 10) AS tbl
    GROUP BY tbl.grp

    SET new.freq = _freq;
END

Answer 2

SELECT id, GROUP_CONCAT(status ORDER BY freq desc) FROM
    (SELECT a.id as id, b.status, COUNT(*) as freq
    FROM 
        sample a
    JOIN 
        sample b ON (b.id < a.id) AND (b.id > a.id - 11)
    WHERE 
        a.id > 10
    GROUP BY a.id, b.status) AS sub
GROUP BY id;

SQL Fiddle