Блог

Я пытаюсь получить средневзвешенное значение по 5 столбцам в таблице mysql. Я успешно делаю это, извлекая все строки и вычисляя их в php.

Однако, поскольку в моей таблице теперь более 2.000.000 строк, а запрос не редко составляет 20.000 строк, он выполняется медленно.

В принципе, если строка имеет weight_index of 1 it should the row:column-worth is 0.5 и если она имеет weight_index=2, row:column-worth is 1.

Затем вычислите среднее значение для столбцов a, b, c, d и e.

  ---- ---------- --- --- --- ---- --- -------------- 
| id | store_id | a | b | c |  d | e | weight_index |
 ---- ---------- --- --- --- ---- --- -------------- 
|  1 |     1    | 2 | 3 | 4 |  5 | 6 |       1      |
 ---- ---------- --- --- --- ---- --- -------------- 
|  2 |     1    | 3 | 4 | 1 |  2 | 0 |       2      |
 ---- ---------- --- --- --- ---- --- -------------- 
|  3 |     1    | 4 | 3 | 1 | 10 | 5 |       3      |
 ---- ---------- --- --- --- ---- --- -------------- 
|  4 |     1    | 2 | 3 | 4 |  5 | 6 |       2      |
 ---- ---------- --- --- --- ---- --- -------------- 

$weight_index = [1 => 0.5, 2 => 1, 3 => 1, 4 => 1, 5 => 1, 6 => 0.7];
 

В настоящее время я использую Mysql.

РЕДАКТИРОВАТЬ 1:

То, что я пытаюсь получить, — это взвешенное среднее значение для каждого столбца (a-e) (не строк). Значение ячеек определяется с помощью weight_index .

Аналогично среднему значению (a), но строки с weight_index 1 оцениваются в 0,5…

 CREATE TABLE `items`(
    `id` INT NOT NULL AUTO_INCREMENT,
    `user_id` INT NOT NULL,
    `a` INT NOT NULL,
    `b` INT NOT NULL,
    `c` INT NOT NULL,
    `d` INT NOT NULL,
    `e` INT NOT NULL,
    `weight_index` INT NOT NULL,
    PRIMARY KEY(`id`)
); 

INSERT INTO `items`(
    `id`,
    `user_id`,
    `a`,
    `b`,
    `c`,
    `d`,
    `e`,
    `weight_index`
)
VALUES(NULL, '1', '2', '3', '6', '3', '4', '1'),(NULL, '1', '4', '5', '6', '7', '3', '2'),(NULL, '2', '3', '4', '5', '6', '7', '2'),(NULL, '1', '2', '3', '4', '5', '6', '1');
 

Создайте другую таблицу со значениями веса. Объединитесь с ним и умножьте средние значения на вес.

 CREATE TABLE weights (
    id int PRIMARY KEY NOT NULL,
    weight FLOAT
);
INSERT INTO weights (id, weight) VALUES
(1, 0.5), (2, 1), (3, 1), (4, 1), (5, 1), (6, 0.7);

SELECT t.store_id, 
    SUM(w.weight * t.a)/SUM(w.weight) AS a, 
    SUM(w.weight * t.b)/SUM(w.weight) AS b, 
    SUM(w.weight * t.c)/SUM(w.weight) AS c, 
    SUM(w.weight * t.d)/SUM(w.weight) AS d, 
    SUM(w.weight * t.e)/SUM(w.weight) AS e
FROM your_table AS t
JOIN weights AS w ON t.weight_index = w.id
GROUP BY t.store_id;