Как составить SQL запрос для отчета с разным количеством строк по категории?

Question

[AlexeyL2552]

Есть таблица с данными в виде:


Нужно вывести отчет такого вида:


При группировке по подразделению необходимо учитывать, что количество строк в каждом типе (type_id) может быть разное.

Подскажите, какие методы (union или join, возможно частичная генерация SQL при помощи программного кода PHP) лучше применить, чтобы получить отчет указанного вида?
Используемая СУБД - MySQL 5.7.

Answer 1

[alexalexes]

При помощи SQL вы получаете исходные данные, в виде первой таблицы.
Далее работаете только кодом PHP.
Вторая таблица - это у вас несколько матриц, по количеству подразделений.
Одна матрица n*m это:
n - макс. индекс предмета 000n в пределах одного подразделения.
m - макс. индекс видов предметов во всей исходной выборке.
Позиция в матрице i,j:
i - это 000i
j - индекс вида предмета.

Для вывода результата вам нужно получить структуру:

$result =
[
  1 =>  // department_id
  [
     1 /* 000i индекс */ => [1 /*индекс вида предмета*/ => '1_1_0001', 2 => '1_2_0001', 3 => '1_3_0001' /* непосредственно данные */],
     2 /* 000i индекс */ => [1 /*индекс вида предмета*/ => '1_1_0002', 2 => '1_2_0002', 3 => '1_3_0002' /* непосредственно данные */],
     3 /* 000i индекс */ => [1 /*индекс вида предмета*/ => '1_1_0003', 3 => '1_3_0003' /* непосредственно данные */],
     ...
  ],
  2 =>
  ...
];

Вариант 2, динамически в PHP создать запрос:

select t.department_id,
          t1.item as type_1,
          t2.item as type_2,
          ...
from t
left join (select t.department_id, t.item from t where t.type_id = 1) t1 on  t1.department_id = t.department_id
left join (select t.department_id, t.item from t where t.type_id = 2) t2 on  t2.department_id = t.department_id
...
where 1 = 1
    and (SUBSTRING_INDEX(t.item, '_', -1) + 0 = SUBSTRING_INDEX(t1.item, '_', -1) + 0 or t1.item is null)
    and (SUBSTRING_INDEX(t.item, '_', -1) + 0 = SUBSTRING_INDEX(t2.item, '_', -1) + 0 or t2.item is null)
   ...
  -- тут нужно получить равенство всех субиндексов 0000i от t.item сравнить со всеми tn.item
order by  t.department_id, SUBSTRING_INDEX(t.item, '_', -1) + 0

Comments

[AlexeyL2552]

Подход неплохой.
Что-то похожее уже пробовал.

Однако если строк в базе достаточно много (например, несколько тысяч), то формирование такого отчета начинает заметно тормозить - у меня примерно полминуты (а то и больше) уходило на формирование.

Все это усложняется тем, что затем еще надо либо разбить по страницам и выдать нужную, либо выгрузить в Excel весь отчет.

Похоже на то, что нужно скомпонованные данные по каждому подразделению сохранять в какую-либо таблицу СУБД и уже оттуда делать выборку при запросе отчета.

[alexalexes]

Вы можете по количеству видов динамически можете создать такой текст запроса в PHP:

select t.department_id,
          t1.item as type_1,
          t2.item as type_2,
          ...
from t
left join (select t.department_id, t.item from t where t.type_id = 1) t1 on  t1.department_id = t.department_id
left join (select t.department_id, t.item from t where t.type_id = 2) t2 on  t2.department_id = t.department_id
...
where (SUBSTRING_INDEX(t.item, '_', -1) + 0 = SUBSTRING_INDEX(t1.item, '_', -1) + 0 or t1.item is null)
    and (SUBSTRING_INDEX(t.item, '_', -1) + 0 = SUBSTRING_INDEX(t2.item, '_', -1) + 0 or t2.item is null)
   ...
  -- тут нужно получить равенство всех субиндексов 0000i от t.item сравнить со всеми tn.item
order by  t.department_id, SUBSTRING_INDEX(t.item, '_', -1) + 0

Его выполнить, и сразу получить нужное форматирование, но нужно создать индекс по столбцу item, используя функцию SUBSTRING_INDEX(t1.item, '_', -1) + 0, чтобы выражение в where не было узким местом в скорости выполнения запроса. Либо добавить столбец в базе для 0000i и заполнять его и индексировать как есть.

Answer 2

[Михаил Ливач]

если исходную таблицу можно трансформировать, то я добавил столбец item_id и получил естественный первичный ключ (department_id, type_id, item_id). Столбец item идёт в довесок и, в общем-то, не нужен.
данные для воспроизведения:

CREATE TABLE `items` (
  `department_id` int(10) unsigned NOT NULL,
  `type_id` int(10) unsigned NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  `item` varchar(255) NOT NULL,
  PRIMARY KEY (`department_id`,`type_id`,`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `items` (`department_id`, `type_id`, `item_id`, `item`) VALUES
(1,	1,	1,	'1_1_0001'),
(1,	1,	2,	'1_1_0002'),
(1,	2,	1,	'1_2_0001'),
(1,	3,	1,	'1_3_0001'),
(1,	3,	2,	'1_3_0002'),
(1,	3,	3,	'1_3_0003'),
(1,	3,	4,	'1_3_0004'),
(1,	3,	5,	'1_3_0005'),
(2,	1,	1,	'2_1_0001'),
(2,	2,	1,	'2_2_0001'),
(2,	3,	1,	'2_3_0001');

Запрос для решения задачи:

SELECT t.department_id, 
IFNULL( t1.item, '' ) as type_1,
IFNULL( t2.item, '' ) as type_2,
IFNULL( t3.item, '' ) as type_3,
IFNULL( t4.item, '' ) as type_4
FROM `items` t
LEFT JOIN `items` t1 ON ( t.department_id=t1.department_id AND t.item_id=t1.item_id AND t1.type_id=1 )
LEFT JOIN `items` t2 ON ( t.department_id=t2.department_id AND t.item_id=t2.item_id AND t2.type_id=2 )
LEFT JOIN `items` t3 ON ( t.department_id=t3.department_id AND t.item_id=t3.item_id AND t3.type_id=3 )
LEFT JOIN `items` t4 ON ( t.department_id=t4.department_id AND t.item_id=t4.item_id AND t4.type_id=4 )
GROUP BY t.department_id, t.item_id

Я добавил 4-й столбец для иллюстрации NULL . Таким образом, есть 2 подхода:
1) перед конструированием знать уникальные значения столбца type_id. Тогда надо добавить в таблицу ещё один индекс ( просто INDEX) чисто по этому столбцу, для скорости предварительного запроса.
2) предполагать максимальный type_id и тупо строить по диапазону 1..type_id_max

Comments

[Михаил Ливач]

частично оффтопик: я сначала грохнул столбец item, а потом решил заполнить обратно, чтобы топикстартеру было проще. Заполнял таким запросом:

update items SET item = CONCAT(department_id, '_',type_id, '_', LPAD(item_id,4,'0'));

Поэтому и пишу, что этот столбец не нужен - его можно воспроизвести "на лету"

[AlexeyL2552]

Да, исходную таблицу можно трансформировать.
Кроме того, в исходную таблицу уже добавил поле id как автоинкрементное.

По результатам запроса у меня получилось следующее:


Насколько я понимаю, чтобы привести данную результирующую таблицу к виду, который я указывал как желательный, нужно либо уже при помощи кода (PHP) привести в соответствие, либо в SQL запросе использовать переменные для имитации номеров строк и затем объединять по номерам строк.

Кроме того, указанные в значения в столбце item сильно упрощенный пример - там может быть любое значение с regex вида: /[0-9A-Za-z_-]+/.
Поэтому ваш ответ наиболее близко подходит к тому результату, который требуется получить.

[Михаил Ливач]

AlexeyL2552, если Вы моё поле item_id заменили на id , то это было неправильно. У меня item_id одинаковый для 1_1_0001, 1_2_0001 и так далее.
Если Вы возьмёте мои запросы и попробуете где-то в тестовой базе, то увидите, что никакой "лесенки" нет.

[AlexeyL2552]

Михаил Ливач, да, я чуть позже увидел ошибку в своей логике.
Реализовал похожую логику (чтобы был одинаковый item_id) - все получилось, "лесенки" нет.
Обыграл как номер строки в отчете, т.к. мне она пригодится позже.
Поле id так и оставил, т.к. оно тоже нужно для дальнейшей обработки.

В любом случае спасибо за подробное объяснение!