Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / MySQL Новый топик    Ответить
 FAQ: Нумерация строк и другие вопросы про использование переменных  [new]
mahoune
Member

Откуда: Moscow
Сообщений: 5663
Задача: Пронумеровать строки в запросе
Достаточно часто возникает необходимость пронумеровать возвращаемые MySQL строки средствами самой базы данных.

Решение:
SELECT @i := @i + 1 AS row_number, your_table.*
FROM your_table, (select @i:=0);

В ряде случаев вы можете получить следующий ответ от MySQL:
Every derived table must have its own alias

Тогда необходимо добавить алиас (псевдоним):
SELECT @i := @i + 1 AS row_number, your_table.*
FROM your_table, (select @i:=0) AS z;

или воспользоваться механизмом присвоением нулевого значения переменной @i перед запросом, если такая возможность присутствует. Т.к. в случае если эти два запроса буду выполнены в разных подключениях, толку будет ноль, а вернее NULL.

SET @id := 0;
SELECT @id := @id + 1 AS row_number, your_table.* FROM your_table;

.mahoune.
2 авг 09, 19:10    [7488722]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Нумерация строк и другие вопросы про использование перменных  [new]
mahoune
Member

Откуда: Moscow
Сообщений: 5663
Выборка нескольких последних записей в неких группах
(а.к.а TOP N queries in categories).

Допустим, имеется следуюшая задача:

Появилась необходимость получить несколько строк, отсортированных по какому-то признаку и сгруппированных по какому-либо полю. Например, поиск 5 последних сообщений по каждой теме. Пробовал получать вот таким запросом:

SELECT * FROM 
(SELECT DISTINCT type_a FROM table) AS t1 
INNER JOIN table AS t2 
ON t2.id IN 
(SELECT id FROM table WHERE type_a=t1.type_a ORDER BY date_a DESC LIMIT 5)

но почему-то он выдает ошибку, что LIMIT в подзапросах не поддерживается (вроде это очень простая операция, не понимаю почему ее нет?). Единственный выход какой я вижу - это UNION, но при большом количестве типов это будет не очень удобно. Как быть в данном случае, может есть какой-то вариант получше?

Даже если ЛИМИТ проходит, последний запрос будет выпонятся для каждого
значения колонки "type_a". О производительности здесь говорить не приходится.

Решение с использованием переменных:

1. отсортировать таблицу по "type_a" и по "date_a DESC"
2. пронумеровать записи отдельно для кaждого значения "type_a"
3. выбрать первые 5 записи в каждой группе

Реализация:

1. отсортировать таблицу по "type_a" и по "date_a DESC".

Добавим селект для задания переменных
@rownum -- для хранения текушего счетчика
@typex -- для хранения текушего значения "type_a"

select * from tableA ta, (select @rownum:=1, @typex:='_') zz
order by type_a, date_a DESC

2. пронумеровать записи отдельно для кaждого значения "type"

select ta.*,
if(@typex=ta.type_a, -- если "type_a" не изменился
@rownum:=@rownum+1, -- то увеличить счетчик
@rownum:=1+least(0,@typex:=ta.type_a)) AS rown -- иначе скинуть счетчик на 1 
-- и занести новое значение в @typex
from tableA ta, (select @rownum:=1, @typex:='_') zz
order by type_a, date_a DESC

3. выбрать первые 5 записи в каждой группе

select * from 
(
select ta.*,
if(@typex=ta.type_a, 
@rownum:=@rownum+1, 
@rownum:=1+least(0,@typex:=ta.type_a)) rown 
from tableA ta, (select @rownum:=1, @typex:='_') zz
order by type_a, date_a DESC
) yy
where rown < 6

Примечания:
@rownum:=1+least(0,@typex:=ta.type) гарантирует обновление @typex
после проверки "if(@typex=ta.type..."

Спасибо javajdbc
2 авг 09, 23:57    [7489069]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Нумерация строк и другие вопросы про использование перменных  [new]
Alex_Ustinov
Member

Откуда: Nickel
Сообщений: 2911
Пронумеровать поле таблицы "по-порядку"
update 
   your_table
set
   field1 =(select @a:= @a + 1 from (select @a:= 0) s)
order by 
   field2, field3 ...
3 авг 09, 09:17    [7489347]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Нумерация строк и другие вопросы про использование перменных  [new]
magicprinc
Member

Откуда:
Сообщений: 127
Подробный разбор всех техник работы с переменными в запросе

Advanced MySQL user variable techniques

http://www.xaprb.com/blog/2006/12/15/advanced-mysql-user-variable-techniques/

Финальный вариант X элементов из группы у него выглядит так:
select *, @num
from fruits
where
   (@num := if(type = @type, @num + 1, 1)) is not null
   and (@type := type) is not null
   and (@num <= 2);
26 ноя 09, 21:45    [7984809]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Нумерация строк и другие вопросы про использование перменных  [new]
Богдан Гоцкий
Member

Откуда: Львов
Сообщений: 499
magicprinc

Финальный вариант X элементов из группы у него выглядит так:
select *, @num
from fruits
where
   (@num := if(type = @type, @num + 1, 1)) is not null
   and (@type := type) is not null
   and (@num <= 2);


Где гарантия, что порядок присвоений переменных @num и @type будет именно в том порядке как написано в запросе? По-моему этот запрос вообще недетерминирован и возвращает нужную нумерацию чисто случайно.
22 фев 10, 12:07    [8378615]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Нумерация строк и другие вопросы про использование переменных  [new]
an0nym
Member

Откуда:
Сообщений: 7078
Богдан Гоцкий,

в принципе любая (за очень редким исключением) работа с переменными в резалт-сете, содержащем более одной записи, недетерминированна.
22 фев 10, 12:27    [8378678]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
javajdbc
Member

Откуда: Montreal
Сообщений: 17141
Богдан Гоцкий

Где гарантия, что порядок присвоений переменных @num и @type будет именно в том порядке как написано в запросе? По-моему этот запрос вообще недетерминирован и возвращает нужную нумерацию чисто случайно.


Действительно, порядок выполнения в СЕЛЕКТ и в WHERE групе не определен -- теоретически.
Практически я не видел доказаного случая неверного порядка.
Етот вопрос известен и решается, правда через задницу, но все же решается.
Посмотри второй пост в етой ветке, в самом конце:

автор

Примечания:
@rownum:=1+least(0,@typex:=ta.type) гарантирует обновление @typex
после проверки "if(@typex=ta.type..."
22 фев 10, 17:23    [8379530]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Нумерация строк и другие вопросы про использование переменных  [new]
javajdbc
Member

Откуда: Montreal
Сообщений: 17141
an0nym
Богдан Гоцкий,

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


Странная сентенция....
если делать правильно, то все будет детерминировано.... если неправильно делать,
то можно и пенис сломать...
22 фев 10, 17:29    [8379549]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Нумерация строк и другие вопросы про использование переменных  [new]
an0nym
Member

Откуда:
Сообщений: 7078
javajdbc,

я примерно это и имел в виду. :)
22 фев 10, 17:50    [8379605]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Нумерация строк и другие вопросы про использование переменных  [new]
miksoft
Member

Откуда:
Сообщений: 36326
Модератор: Это FAQ-овый топик. Просьба не засорять его своими частными вопросами, для этого создавайте отдельные топики.
Все, что не относится непосредственно к сабжу, зачищено.
27 окт 11, 12:51    [11508269]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Нумерация строк и другие вопросы про использование переменных  [new]
tanglir
Member

Откуда:
Сообщений: 30154
miksoft
Это FAQ-овый топик.
Так может, в "важные" его?
27 окт 11, 15:31    [11509914]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Нумерация строк и другие вопросы про использование переменных  [new]
Arhat109
Member

Откуда: из СССР
Сообщений: 3282
tanglir,

Вот понадобилось... а оно... :)

Подход с переменными работает только при отсутствии сортировки в запросе, отличной от "по умолчанию" и нумерации только одной таблицы (без join!)

Столкнулся с тем, что при задании сортировки в режиме DESC - нумерация переменными или совсем не производится, или пересортировывается также по DESC.

Задача: оставить для каждого пользователя форума только по 10 ПОСЛЕДНИХ сообщений... то есть сообщения надо сгруппировать по пользователям, отсортировать в обратном порядке по дате и пронумеровать в прямом порядке (обратно дате!) и те, которые имеют номер в группе больше 10 - удалить нафиг...

ну и ещё повеселило:
Если таблица сообщений - сборка джойном из нескольких, то управление переменными в блоке where делается столько раз, сколько джойнов в сборке. То есть при двух таблицах (один джойн) - получаем только четные значения, при двух джойнах - кратные трем... и т.д.

лечится включением всей сборки и сортировки во вложенный подселект. Но проблема в том, что у меня при записях "сообщений" больше 1млн и с десяток тысяч "пользователей"... внутренний подселект без временной таблицы - валится напрочь "табличка повреждена..." или как оно там... запись во временную табличку - около 2 минут...
4 апр 12, 19:36    [12367029]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Нумерация строк и другие вопросы про использование переменных  [new]
javajdbc
Member

Откуда: Montreal
Сообщений: 17141
Arhat109,

да... переменные в мускл-е (и вообше в СКЛ) -- дело
идеологически инородное... и требует осторожности в использовании...

1. было показано, что в зависимости от использовании-или-неиспользовании
индексов на джоинте переменные евалюировались по разному.
Т.е. для чистого СКЛ-а работа оптимизатора не влияет на результат,
но может повлиять на вычисление переменные

2. единственый прямой способ добится однозначного порядка --
как вы отметили выше -- использование подселектов -- соединять таблицы
внутри под-селекта но работать с переменными в следуюшем селекте.
типа:

2a select ....переменные....from (select * from A,B where ...ORDER BY ...)
2b select ....переменные....from (select * from A,B where ....) ORDER BY ...

наверное 2б будет правильнее

3. В сложных случаях -- подселекты являются проблемой ибо на
подселекты нет индексов -- т.е. лефт джоинт на подселект будет медленым.

4. Выборка ТОП-Н всегда предпологает двойной проход: нумерация а потом
отсечение -- я наблюдал случаи когда по нехватки памяти такие квери
падали на хард-диск. А ето тормозило запрос на несколько порядков...
На милионах записей это реально (я видел срыв на 400-600К на домашнем компе)

5. рекомендую создать новый топик по теме, поставить задачу,
выдать пример данных, описать таблицы и индексы

6. Переменные -- не панацея, вполне может случится что
работа через промежуточнуе таблицы будет быстрее... или
с переменными но по 100К за раз (что б все в памяти)...
...или...надо посмотреть.. :-)
8 апр 12, 04:16    [12382612]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Нумерация строк и другие вопросы про использование переменных  [new]
Arhat109
Member

Откуда: из СССР
Сообщений: 3282
javajdbc,

не вижу смысла создавать отдельную тему. Надо оставить мой пост и ваш коммент "для ограничения применения", дабы народ не впадал в маразм, наступая на одни грабли. Этот пост можно потереть за ненадобностью.

Решается всё достаточно легко предварительным созданием временной таблицы на базе подселектов только с нужными полями и навешиванием нужного количества индексов для последующей быстрой выборки с переменными... больше миллиона записей пролетает на удаление (суммарно с созданием временной таблицы) за 30-60 секунд... для разового запроса на чистку базы - мелочь. Даже если делать раз в день.

Просто табличку мне надо было сразу создавать правильную, а не SELECT * FROM... :) Вполне достаточно только id,fkey,last_date
8 апр 12, 15:01    [12383180]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: FAQ: Нумерация строк и другие вопросы про использование переменных  [new]
alex564657498765453
Member

Откуда:
Сообщений: 1935
+
mahoune
Выборка нескольких последних записей в неких группах
(а.к.а TOP N queries in categories).

Допустим, имеется следуюшая задача:

Появилась необходимость получить несколько строк, отсортированных по какому-то признаку и сгруппированных по какому-либо полю. Например, поиск 5 последних сообщений по каждой теме. Пробовал получать вот таким запросом:

SELECT * FROM 
(SELECT DISTINCT type_a FROM table) AS t1 
INNER JOIN table AS t2 
ON t2.id IN 
(SELECT id FROM table WHERE type_a=t1.type_a ORDER BY date_a DESC LIMIT 5)


но почему-то он выдает ошибку, что LIMIT в подзапросах не поддерживается (вроде это очень простая операция, не понимаю почему ее нет?). Единственный выход какой я вижу - это UNION, но при большом количестве типов это будет не очень удобно. Как быть в данном случае, может есть какой-то вариант получше?

Даже если ЛИМИТ проходит, последний запрос будет выпонятся для каждого
значения колонки "type_a". О производительности здесь говорить не приходится.

Решение с использованием переменных:

1. отсортировать таблицу по "type_a" и по "date_a DESC"
2. пронумеровать записи отдельно для кaждого значения "type_a"
3. выбрать первые 5 записи в каждой группе

Реализация:

1. отсортировать таблицу по "type_a" и по "date_a DESC".

Добавим селект для задания переменных
@rownum -- для хранения текушего счетчика
@typex -- для хранения текушего значения "type_a"

select * from tableA ta, (select @rownum:=1, @typex:='_') zz
order by type_a, date_a DESC


2. пронумеровать записи отдельно для кaждого значения "type"

select ta.*,
if(@typex=ta.type_a, -- если "type_a" не изменился
@rownum:=@rownum+1, -- то увеличить счетчик
@rownum:=1+least(0,@typex:=ta.type_a)) AS rown -- иначе скинуть счетчик на 1 
-- и занести новое значение в @typex
from tableA ta, (select @rownum:=1, @typex:='_') zz
order by type_a, date_a DESC


3. выбрать первые 5 записи в каждой группе

select * from 
(
select ta.*,
if(@typex=ta.type_a, 
@rownum:=@rownum+1, 
@rownum:=1+least(0,@typex:=ta.type_a)) rown 
from tableA ta, (select @rownum:=1, @typex:='_') zz
order by type_a, date_a DESC
) yy
where rown < 6


Примечания:
@rownum:=1+least(0,@typex:=ta.type) гарантирует обновление @typex
после проверки "if(@typex=ta.type..."

Спасибо javajdbc


select ta.*,
if(@typex=ta.type_a, -- если "type_a" не изменился
@rownum:=@rownum+1, -- то увеличить счетчик
@rownum:=1+least(0,@typex:=ta.type_a)) AS rown -- иначе скинуть счетчик на 1 
-- и занести новое значение в @typex
from tableA ta, (select [color=red]@rownum:=1[/color], @typex:='_') zz
order by type_a, date_a DESC


и что никого не смутила ошибка??? первая група начнёт нумероваться с 2 а не с 1!!!
и логически ясно, и лично только что проверил на мускле.
22 май 13, 14:02    [14332883]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Нумерация строк и другие вопросы про использование переменных  [new]
javajdbc
Member

Откуда: Montreal
Сообщений: 17141
alex564657498765453,

по идее, первая група пройдет на строке:
@rownum:=1+least(0,@typex:=ta.type_a)) AS rown -- иначе скинуть счетчик на 1 

...и нумерация пойдет с единицы.
Двойка будет если самая первая група будет иметь название '_'.

Если у вас по другому, приведите полный тест-кейс.
Бывают случаи когда жоинты сбивали порядок вывода на
блок СЕЛЕКТ.
22 май 13, 16:06    [14334014]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Нумерация строк и другие вопросы про использование переменных  [new]
alex564657498765453
Member

Откуда:
Сообщений: 1935
имеется ТАБЛИЦА - записи покупок

CREATE TABLE `sales` (
	`idsale` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'PRIMARY KEY',

	`fk_idgood` INT(11) NOT NULL COMMENT 'FOREIGN KEY goods',

	`fk_iduser` INT(11) NOT NULL COMMENT 'FOREIGN KEY users'

--    ..... other information fields

);


ЗАДАЧА

Получить список самых активных покупателей и число их покупок.

(если есть несколько человек, купивших больше всех, вывести нужно каждого)

ожидаемый результат

№юзер Кол-во покупок

23 120
41 120
77 120

при этом, не существует пользователя купившего больше чем 120.

РЕШЕНИЕ без переменных

SELECT t1.fk_iduser, t1.total

FROM (

-- получим список всех пользователей с числом покупок каждого
--
--№юзер     Кол-во покупок
--
--   1             32
--   2             12
--          ...
--  23            120
--  24             95
--  25            100
--          ...
--  41            120
--          ...
--  77            120
--          ...

    SELECT fk_iduser, COUNT(fk_idgood) as 'total'

    FROM sales

    GROUP BY fk_iduser

) t1 JOIN (

-- получим любого одного активного покупателя
-- нам нужно лишь определить это самое число - 120, 
-- максимальное кол-во покупок
--
--№юзер     Кол-во покупок
--
--  23            120
--

    SELECT fk_iduser, COUNT(fk_idgood) as 'total'

    FROM sales

    GROUP BY fk_iduser

    ORDER BY COUNT(fk_idgood) DESC
-- самой первой окажется запись с максимальным значением COUNT(fk_idgood)

    LIMIT 1
-- только её и возьмём

-- благодаря соединению, из общего списка отберём только те, у 
-- кого число покупок равно максимальному - 120
)  t2 ON t1.total=t2.total

-- результат
--     23            120
--     41            120
--     77            120


РЕШЕНИЕ с использованием ПЕРЕМЕННЫХ

SELECT id_shopper, total 

FROM (
-- по сути если убрать все переменные, получаемая таблица идентична t1
-- из первого решения
--
    SELECT id_shopper,@m:=COUNT(idgood) AS 'total',IF(@k<@m,@k:=@m,@k)

    FROM sales join (SELECT @k:=0) k
-- инициализируем значение переменой @k
-- псевдоним используем лишь для того, что бы избежать
-- возможного ругательства об обязательности каждому подзапросу дать свой псевдоним

    GROUP BY id_shopper

--сначала будет обработана секция from, что даёт исходную таблицу + инициализированную
--переменную
--
--затем отработает группировка и выполняться будет секция select
-- на каждой строке, будет посчитано значение COUNT(idgood),
-- также это значение будет присвоено переменой @m
-- так же будет проверено, если текущее значение @k меньше @m, то 
-- обновляем значение @k 
--
--таким образом после полной отработки вложенного запроса, 
--@k будет хранить максимальное значение COUNT(idgood)
-- в первом решении мы это получали отдельным под-запросом с группировкой - t2

) t1

WHERE
-- єто запрос просто отфильтрует из t1, все записи
-- где сумма покупок не равна максимальной, 120
total = @k;

-- итого получим тот же результат. 


ПРЕИМУЩЕСТВА
быстродействие - мы не делаем лишний запрос с группировкой и сортировкой.
даже сама сортировка более медленный процесс, чем определение максимального значения
пробежав по записям и обновляя значение переменной.
6 июн 14, 16:54    [16134068]     Ответить | Цитировать Сообщить модератору
Все форумы / MySQL Ответить