Блог

У меня есть база данных 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 для самостоятельного объединения.

 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 строк данных для работы. Я знаю, что это длинный вопрос, но я просто хотел быть настолько подробным, насколько мог. Я работал над этим несколько дней и действительно хотел бы это сделать.

Спасибо за вашу помощь.

 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

Единственный известный мне способ сделать то, о чем вы просите, — использовать BEFORE INSERT триггер. Это должно быть BEFORE INSERT потому, что вы хотите обновить значение в вставляемой строке, что может быть сделано только в BEFORE триггере. К сожалению, это также означает, что ему еще не был присвоен идентификатор, поэтому, надеюсь, можно с уверенностью предположить, что на момент вставки новой записи последние 10 записей в таблице — это те, которые вас интересуют. Вашему триггеру необходимо будет получить значения последних 10 идентификаторов и использовать GROUP_CONCAT функцию, чтобы объединить их в единую строку, упорядоченную по COUNT . В основном я использую SQL 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