#php #mysql
#php #mysql
Вопрос:
У меня есть единственная таблица данных (упрощенная ниже)
| id | date | product |
|----|-------------|---------|
| 1 | 2019-01-02 | prod 1 |
| 2 | 2019-01-02 | prod 2 |
| 3 | 2019-01-22 | prod 1 |
| 4 | 2019-02-02 | prod 1 |
| 5 | 2019-02-02 | prod 1 |
| 6 | 2019-03-02 | prod 1 |
Я бы хотел, чтобы конечный результат выглядел следующим образом…
| product | 2019-01 | 2019-02 | 2019-03 |
|---------|---------|---------|---------|
| prod 1 | 2 | 3 | 0 |
| prod 2 | 1 | 0 | 0 |
Короче говоря, я хотел бы перебрать все продукты в диапазоне дат (все даты содержатся в одной таблице). Когда нет результата за месяц, возвращает 0. Я считаю, что COALESCE может быть способом решения этой проблемы.
Я пробовал это двумя разными способами… возвращает все данные в одном запросе и перебирает эти данные, но I, если данных нет, он никогда не возвращает строку или что-то, что я могу использовать (null).
SELECT
product_name,
count(product_name) AS count
DATE_FORMAT(date,'%Y-%m') AS date
FROM products
GROUP BY DATE_FORMAT(date,'%Y-%m'), company_name
ORDER BY date;
| product | date | count |
|---------|---------|-------|
| prod 1 | 2019-01 | 2 |
| prod 2 | 2019-01 | 1 |
| prod 1 | 2019-02 | 3 |
| prod 2 | 2019-02 | 0 | <--- this row doesn't return
В качестве альтернативы я пробовал перебирать все продукты, а затем по отдельности перебирать даты, но снова данные не возвращаются, если в таблице нет даты, поэтому моя таблица стала бы искаженной.
Я видел, как другие хранят даты в другой таблице, а затем перебирают их, это казалось излишеством, поскольку у меня есть все даты в этой таблице, поэтому я попытался объединить таблицу с самой собой, но это тоже не сработало.
Заранее спасибо за любую помощь.
Комментарии:
1. ваш желаемый результат (с использованием данных в качестве имен столбцов) не может быть получен mysql, afaik. (это своего рода поворот), за исключением случаев, когда вы генерируете запрос, который явно называет столбцы таким образом (
select ... as `2019-01`...). при этом вы не можете создавать данные из ничего. либо вы предоставляете это вместе с запросом, либо у вас есть таблица со всеми (включая пустые) датами. ;o / Включение дат в запрос может сработать, но обычно это некрасиво, и вам приходится перепрыгивать через некоторые препятствия.
Ответ №1:
Чтобы получить второй результат, который вы хотите, сначала вам нужно сделать CROSS JOIN между всеми различными значениями date и product_name в таблице. Затем это можно LEFT JOIN снова отредактировать в таблице, чтобы получить все значения для каждого продукта за каждый месяц, а результаты сгруппировать по product_name и month:
SELECT
p.product_name,
count(p2.product_name) AS count,
d.date
FROM (SELECT DISTINCT DATE_FORMAT(date,'%Y-%m') AS date
FROM products) d
CROSS JOIN (SELECT DISTINCT product_name
FROM products) p
LEFT JOIN products p2 ON DATE_FORMAT(p2.date,'%Y-%m') = d.date AND p2.product_name = p.product_name
GROUP BY p.product_name, d.date
ORDER BY p.product_name, d.date
Вывод
product_name count date
prod 1 2 2019-01
prod 1 3 2019-02
prod 2 1 2019-01
prod 2 0 2019-02
Получение первого результата выполнимо, но требует использования хранимой процедуры для динамического создания запроса сводной таблицы. Намного проще взять приведенный выше результат и обработать его в PHP для достижения того же результата.
Ответ №2:
По сути, это сводная таблица.
Хороший учебник о том, как этого добиться, можно найти здесь: http://www.artfulsoftware.com/infotree/qrytip.php?id=78
Вот ваше динамическое решение для сводной таблицы. Просто используйте запрос, предложенный @Nick, и создайте временную таблицу из этого
create temporary table tmp as
SELECT
p.product_name,
count(p2.product_name) AS count,
d.date
FROM (SELECT DISTINCT DATE_FORMAT(date,'%Y-%m') AS date
FROM products) d
CROSS JOIN (SELECT DISTINCT product_name
FROM products) p
LEFT JOIN products p2 ON DATE_FORMAT(p2.date,'%Y-%m') = d.date AND p2.product_name = p.product_name
GROUP BY p.product_name, d.date
ORDER BY p.product_name, d.date;
Теперь вот dynamic SP, который преобразует строку в столбец с учетом любого нового продукта, который появится в будущем.
delimiter $$
DROP PROCEDURE IF EXISTS pivot $$
CREATE PROCEDURE pivot(IN schema_name VARCHAR(64)
, IN table_name VARCHAR(64)
, IN id_name VARCHAR(64)
, IN key_name VARCHAR(64)
, IN value_name VARCHAR(64))
pivot:BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND SET @error := 1;
SET @error := 0;
SELECT MAX(character_maximum_length)
INTO @maxlen
FROM information_schema.columns
WHERE table_schema = schema_name
AND table_name = table_name
AND column_name = key_name
AND data_type IN ('char', 'varchar');
SET @maxlen = IFNULL(@maxlen,500);
IF @error OR !@maxlen OR @maxlen IS NULL THEN
SELECT '@error OR @maxlen=0 OR @maxlen IS NULL', @error, @maxlen;
LEAVE pivot;
END IF;
DROP TEMPORARY TABLE IF EXISTS temp_pivot;
SET @sql := CONCAT('CREATE TEMPORARY TABLE temp_pivot (key_name VARCHAR(',
@maxlen,
')) ENGINE=Memory SELECT DISTINCT `',
key_name,
'` key_name FROM `',
schema_name,
'`.`',
table_name,
'`;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
SELECT GROUP_CONCAT(CONCAT( ', MAX(CASE `',
key_name,
'` WHEN ''',
temp_pivot.key_name,
''' THEN `',
value_name,
'` else 0 END) `',
temp_pivot.key_name,
'`') SEPARATOR '')
INTO @sql
FROM temp_pivot;
DROP TEMPORARY TABLE IF EXISTS Pivot_Check;
SET @sql := CONCAT('CREATE TEMPORARY TABLE Pivot_Check AS ','SELECT `',
id_name,
'`',
@sql,
' FROM `',
schema_name,
'`.`',
table_name,
'` GROUP BY `',
id_name,
'`;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
SET @error := NULL;
SET @maxlen := NULL;
SET @sql := NULL;
END $$
delimiter
и просто вызовите SP
call pivot('schema_name','table_name','product','count','date');
И ваша сводная таблица готова, просто используйте
select * from Pivot_Check