Пронумеровать поле в MySQL таблице с сортировкой и группировкой?

Question

[Sekira]

Есть две таблицы article (article_id, sort) и category (article_id, category_id, number)

Нужно заполнить поле number от 1..., используя сортировку по полю sort и чтобы в каждой category_id счет опять начинался с 1, а не продолжался, то есть должно получится так:
category:

article_id — category_id — number

51 1 1

96 1 2

12 1 3

…

62 2 1

51 2 2

28 2 3

… и т.д.


Например, если делать отдельно для каждой категории, так как не придумал как можно в запросе сделать для всех категорий сразу со сбросом на 1 в новой, то так:

UPDATE `category`
JOIN `article` ON `article`.`article_id`=`category`.`article_id`
SET `number`=(select @n:=@n+1 from (select @n:=0) as t)
WHERE `category`.`category_id`=123
ORDER BY `sort` ASC

Но пишет, нельзя использовать order by к таблице из join


Хотелось бы не отдельно для каждой категории запрос делать, так как категорий очень много, и было бы быстрее одним запросом, чем для каждой категории свой

Answer 1

[pel]

Че-т не вышло сделать сходу без дополнительных телодвижений, поэтому предложу пока такой вариант (дальше скрипты с create'ами. Убирать их не стал — мне кажется, так «нагляднее» будет):

-- Ваша таблица article
CREATE TABLE a (
a_id INT( 10 ) NOT NULL ,
s INT( 10 ) NOT NULL
) ENGINE = MYISAM ;

INSERT INTO  a (a_id , s) VALUES 
(1, 3), 
(2, 5), 
(3, 7), 
(4, 2), 
(5, 4);

-- Ваша таблица category
CREATE TABLE c (
a_id INT( 10 ) NOT NULL ,
c_id INT( 10 ) NOT NULL ,
n INT( 10 ) NOT NULL DEFAULT 0
) ENGINE = MYISAM ;

INSERT INTO c (a_id , c_id , n) VALUES 
(1, 2, 0), 
(2, 2, 0), 
(3, 1, 0), 
(4, 2, 0), 
(5, 1, 0);

-- В таблицу категорий добавляем временное поле, 
ALTER TABLE c ADD s_tmp INT NOT NULL DEFAULT '0';
-- в которое закинем соответствующие значения sort из article
UPDATE c 
SET c.s_tmp = ( 
	SELECT s
	FROM a
	WHERE a.a_id = c.a_id 
);

-- Создадим временную таблицу, в которую с помощью как раз той магии, которая вам, собственно, нужна, проставим позиции внутри групп
DROP TABLE IF EXISTS c_tmp;

SET @cat:='', @num:=1;

CREATE TABLE c_tmp 
SELECT c_id, a_id,
	@num := IF( @cat = c_id, @num +1, 1 ) AS row_num, 
	@cat := c_id AS dummy
FROM c
ORDER BY c_id, s_tmp;

-- Теперь эти сгенерированные значения перекидываем в основную таблицу
UPDATE c 
SET c.n = ( 
	SELECT row_num
	FROM c_tmp AS ct
	WHERE c.a_id = ct.a_id AND c.c_id = ct.c_id 
);

-- Ну и убираем 
ALTER TABLE  c DROP  s_tmp;
-- за собой мусор
DROP TABLE  c_tmp;

Comments

[pel]

Хотя, знаете, похоже, я все-таки поторопился и можно обойтись без мусора.
Почитайте вот:
www.xaprb.com/blog/2006/12/15/advanced-mysql-user-variable-techniques/

[Sekira]

Большое спасибо! Жаль нет кармы для повышения Вашей.

Вы имеете ввиду использования фукнций?
Вот использовав это, убрал использование временной таблицы, а вот как поле сортировки убрать, не знаю:

SET @cat:='', @num:=1;
UPDATE `category`
SET `sort`=max(@num := IF( @cat = `category_id`, @num +1, 1 ),  (@cat:=`category_id`)-999999) order by `category_id`, `sort_temp` ;

Использовал max, а не coalesce, так как в coalesce не выполняется второй параметр, если первый не null, а другой функции не придумал.

[Sekira]

Ой, не max, а GREATEST

[pel]

Честно говоря, я сам не совсем понял, что имею в виду :)) Я просто полистал, почитал еще и мне показалось, что можно обойти временные таблицы. Хранимые процедуры? Возможно. Пробуйте, ищите :)

Answer 2

[Ivan Komarov]

UPDATE category SET number = 0;

SET @old_category = -1;
SET @num = -1;
UPDATE category AS c
INNER JOIN (
	SELECT 
	    t.article_id,
	    t.sort,
	    t.category_id,
	    @old_category,
	    @num := CASE
	    	WHEN @old_category <> t.category_id THEN 0
	        ELSE @num + 1
	    END AS number,
	    @old_category := t.category_id
	FROM (
		SELECT
			a.article_id, 
		    a.sort, 
		    c.category_id
		FROM category AS c
		INNER JOIN article AS a ON a.article_id = c.article_id
		ORDER BY c.category_id, a.sort
	) AS t
) AS t
ON t.article_id = c.article_id
SET c.number = t.number;

SELECT c.*, a.sort FROM category AS c 
INNER JOIN article AS a ON a.article_id = c.article_id
ORDER BY c.category_id, a.sort

Результат:

aricle_id | category_id  | number | sort
5         | 1            | 0      | 4
3         | 1            | 1      | 7
4         | 2            | 0      | 2
1         | 2            | 1      | 3
2         | 2            | 2      | 5

Comments

[Sekira]

Спасибо! Но что-то подвисает на select, у которого вложенный select, то есть отдельно самый первый изнутри select нормально, а следующий висит, минут 5 ждал. Убил процесс MySQL.
article где-то 100 000 строк, category 200 000, причем article_id может быть в нескольких категориях, поэтому в Вашем коде, нужно еще WHERE t.category_id = c.category_id в UPDATE добавить, но это всё ерунда. Жаль, что долго выполняется и непонятно почему, вроде индексы везде есть.

[pel]

Покажите результат запроса
EXPLAIN SELECT

[Ivan Komarov]

Explain у меня не работает на этом запросе, говорит, что ошибка синтаксиса.
Кстати, в Microsoft SQL Server такие вещи делаются гораздо проще. Там есть специальная функция row_number. А про where да, забыл.

[Ivan Komarov]

mb подзапрос в join выполняется для каждой записи, которую нужно апдейтить?

[Ivan Komarov]

А все же зачем там WHERE. может я еще не проснулся, но IMHO он там не нужен

[Ivan Komarov]

Если медленно, тогда я бы сделал через memory table:

UPDATE category SET number = 0;

SET @old_category = -1;
SET @num = -1;

DROP TABLE IF EXISTS temp;
CREATE TABLE temp (
	article_id int,
	sort int,
	category_id int,
	number integer
) ENGINE = MEMORY;

INSERT INTO temp
SELECT 
	t.article_id,
	t.sort,
	t.category_id,
	t.number
FROM(
	SELECT 
		t.article_id,
		t.sort,
		t.category_id,
		@old_category,
		@num := CASE
			WHEN @old_category <> t.category_id THEN 0
			ELSE @num + 1
		END AS number,
		@old_category := t.category_id
	FROM (
		SELECT
			a.article_id, 
			a.sort, 
			c.category_id
		FROM category AS c
		INNER JOIN article AS a ON a.article_id = c.article_id
		ORDER BY c.category_id, a.sort
	) AS t
) AS t;

UPDATE category AS c
INNER JOIN temp AS t ON t.article_id = c.article_id
SET c.number = t.number;

DROP TABLE temp;

SELECT c.*, a.sort FROM category AS c 
INNER JOIN article AS a ON a.article_id = c.article_id
ORDER BY c.category_id, a.sort

[Sekira]

pel
Самый внутренний SELECT:

1	SIMPLE	c	index	PRIMARY	PRIMARY	10	NULL	192137	Using index; Using temporary; Using filesort
1	SIMPLE	a	eq_ref	PRIMARY,all	PRIMARY	4	base.c.article_id	1

Для другого SELECT:

1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	192137	
2	DERIVED	c	index	PRIMARY	PRIMARY	10	NULL	192137	Using index; Using temporary; Using filesort
2	DERIVED	a	eq_ref	PRIMARY,all	PRIMARY	4	base.c.article_id	1

FreeTibet
Если без WHERE то сразу несколько записей будет обновляться при встречи с article_id и перезаписывать предыдущие обновления в других category_id.
Я пробовал делать limit 5000 в самом внутреннем запросе select, выполнялось минуты 2

[Ivan Komarov]

Я понял, у вас ключ составной. я исходил из того, что article_id уникально :)