Как вернуть ранки по колонке?
Вот смотрите. Я могу отсортировать табличку, например так:
SELECT * FROM BAProtocols
WHERE StartID = 123
ORDER BY TimeAtFinishПолучится какая-то такая таблица результатов гонки (запрос сильно упрощён, конечно, потому что все остальное — не суть).
Могу отсортировать табличку иначе:
SELECT * FROM BAProtocols
WHERE StartID = 123
ORDER BY BibТогда получится старт-лист, типа такого.
Вчера на «Биатлонтайме» появилась возможность сортировать индивидуальные гонки по колонке Ski time, это типа чистое время. Поскольку в базе я храню только итоговое время, то чистое приходится вычислять как итоговое минус штрафы за промахи минус судейские корректировки:
SELECT
<много всего>,
(
BAProtocols.TimeAtFinish
- IF (BAProtocols.S1Misses > 0, BAProtocols.S1Misses * 60000, 0)
- IF (BAProtocols.S2Misses > 0, BAProtocols.S2Misses * 60000, 0)
- IF (BAProtocols.S3Misses > 0, BAProtocols.S3Misses * 60000, 0)
- IF (BAProtocols.S4Misses > 0, BAProtocols.S4Misses * 60000, 0)
- BAProtocols.Adjustment
) _skitime
FROM BAProtocols
WHERE StartID = 123
ORDER BY _skitimeПредвосхищая вопрос «Нахрена иф?»: потому что я храню −1 в стрельбе, если человек вообще не стрелял (бывает, люди не доезжают до финиша, это нужно отражать в протоколах), а 0 означает «чисто отстрелялся».
Получается такое. Кстати, любопытно посмотреть, какое колоссальное значение в индивидуальной гонке имеет качество стрельбы: например, пятая по чистому времени Мириам Гёсснер, в итоговом протоколе даже не попала в очки и оказалась 53-й. Но сейчас не об этом речь.
Дело в том, что при сортировке по чистому времени можно сказать, что каждый спорсмен «занял какое-то место», то есть имеет какой-то rank (не знаю, как по-русски) именно по этому критерию, например, Магдалена Нойнер — 1-я по чистому времени, а Ольга Зайцева — 10-я. Можно ли как-то заставить базу данных вернуть мне именно эти ранки по интересующим меня колонкам, не сортируя таблицу по ним?
Собственно, сейчас сам нужный эффект вы видите в самой первой колонке, когда сортировка не по ней. Например, когда результаты отсортированы по чистому времени, ранки слева продолжают означать место в итоговом протоколе. Но я это делаю вручную, что муторно, хочется, чтобы база сама считала.
То есть, короче, мне нужно по нескольким колонкам вернуть не только их значения, но и натуральные числа, которые означали бы, какой по счёту была бы эта колонка, если бы я отсортировал по ней. Если в колонке одинаковые значения у нескольких строчек, то их ранки должны быть равными, а ранк следующих должен быть таким, как если бы они про повторы ничего не знали (то есть, 1, 2, 3, 3, 3, 6, 6, 8, 9, 10).
Технические ограничения, если это важно: MySQL, MyISAM. Помогайте, пожалуйста.
Есть вариант с промежуточной таблицей — создаешь пустую таблицу с уникальным именем, делаешь там первичным ключом rank, с автоинкрементом.
Даллее insert into новая таблица select from старая таблица.
Так у тебя записи вставятся с одним этим дополнительным полем. А дальше делаешь select from новая таблица order by какое-то новое условие
Вместо -1 тебе было бы очень логично хранить NULL
Здравый смысл и характер данных (данные по гонке формируются один раз) подсказывает, что проще всего эти порядковые номера вычислять и хранить там же в таблице.
Это нужно будет делать один раз, когда приезжает итоговый протокол.
Так и сделаю, если ловкого решения никто не предложит, конечно.
Илья, быть может, вместо дополнительных запросов к БД с перегрузкой страницы сделать сортировку на клиенте средствами JavaScript?
Теоретически — правильно. Практически, нагрузка на клиент у меня сейчас уже запредельная, в Опере страницы еле скролятся. А на сервере все страницы считаются по одному разу, после чего полный ХТМЛ кладётся в кеш, поэтому их выдача — более-менее бесплатна для меня.
Я солидарен с А. Беловым.
Что-то в этом роде должно сработать:
SELECT
@rank := @rank + 1 _rank,
<много всего>,
(
BAProtocols.TimeAtFinish
) _skitime
FROM BAProtocols, (SELECT @rank := 0) tmp
WHERE StartID = 123
ORDER BY _skitime
А вы можете объяснить, как это работает? Я чё-то совсем не понимаю синтаксис с собакой. И как мне посчитать ранки по нескольким колонкам сразу, оставив сортировку BY TimeAtFinish?
Синтаксис с собакой — это пользовательские переменные. Почитать можно тут: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html.
Работает это примерно так: через пустой вложенный селект (SELECT @rank := 0) мы объявляем переменную @rank, которой задаем значение равное нулю. Далее, при каждой выборке увеличиваем значение этой переменной на один и присваиваем результат к текущей выборке в колонку _rank.
Чтобы заработало условие «Если в колонке одинаковые значения у нескольких строчек, то их ранки должны быть равными, а ранк следующих должен быть таким, как если бы они про повторы ничего не знали (то есть, 1, 2, 3, 3, 3, 6, 6, 8, 9, 10)», нужно, по всей видимости, слегка усложнить выборку, добавив еще одну переменную и работая еще и с ней для подсчета «провалов» в позициях.
То, что вам нужно, это аналитические функции, в оракле это было бы так:
%%
select id, a, b,
rank () over (order by b)
from my_table
order by a
%%
в MySql такого счастья нет, поэтому можно выкрутится, пристегнув подзапросами к исходной таблице саму себя же, но сотрированную по тем полям, по которым нужно считать ранки (собаки обозначают переменные, которые создаются на время выполнения запроса):
%%
set @rank:=0;
SELECT t1.*, ranked_by_a.rank
FROM my_table t1
inner join (select id, @rank:=@rank+1 as rank from my_table order by a) ranked_by_a on ranked_by_a.id = t1.id
order by t1.b;
%%
У таблицы наверняка есть уникальный ключ, по нему строчки из таблицы, сортированной по a станут рядом со строчками этой же таблицы, сортированной по b.
Если нужно добавить ранки по другим полям, еще раз пристегнуть по ключу ту же самую таблицу, сортированную по нужным полям:
%%
set @rank_a:=0;
set @rank_c:=0;
SELECT t1.*, ranked_by_a.rank, ranked_by_с.rank
FROM my_table t1
inner join (select id, @rank_a:=@rank_a+1 as rank from my_table order by a) ranked_by_a on ranked_by_a.id = t1.id
inner join (select id, @rank_c:=@rank_c+1 as rank from my_table order by с) ranked_by_c on ranked_by_с.id = t1.id
order by t1.b;
%%
и так далее.
Я вот смотрю на это всё, да и вообще по специфике работы приходится часто смотреть на код сложных запросов и всегда говорю программистам одно и то же:
Сложный запрос нужно писать только там, где нельзя изменить структуру БД (заранее добавив нужные столбцы). И где нельзя обойтись несколькими простыми запросами.
В данном случае, как уже было сказано выше, если есть возможность сразу хранить и чистое время и занятое место — то это самое ловкое решение. Почему?
Всё это правильно, но когда данные не меняются и можно кешировать ХТМЛ целиком, оптимальность устройства базы вообще не важна, важна скорость решения конкретных задач. Если можно это сделать даже очень тяжёлым, но всё же запросом, то зачем усложнять базу?
Если ранк можно вычислить однозначно для каждой строки в таблице, то почему бы не использовать ORDER BY a, b, (SELECT … AS rank), c, d ?
@Сергей Соляник:
Сам по себе order by не создаст новых вычисленных полей с ранками.
А в том варианте, что вы написали, сортировка по b будет не по всей таблице, а только внутри каждой группы значений а
«но когда данные не меняются и можно кешировать ХТМЛ целиком, оптимальность устройства базы вообще не важна, важна скорость решения конкретных задач. Если можно это сделать даже очень тяжёлым, но всё же запросом, то зачем усложнять базу?»
@Александр Чиченин:
Плохая практика — это преждевременная оптимизация. Как можно предлагать предлагать меры оптимизации прежде реализации? Для этого, по крайней мере, нужно знать объем данных, пропорции чтения и записи в обращении к таблице.
Выбрать все, а потом (в цикле?) тысячи раз обращаться к базе для записи вычисленных данных вместо одного обращения для чтения?
Может быть, если sql сложен для понимания, кому-то это будет нагляднее, но не быстрее и проще точно.
@Павел Малинников:
Ну в данном случае задача вроде очевида? Результатов при выборке будет максимум 120-130. Обращений явно больше чем операций записи. Вы же вроде в проблематике разобрались раз предлагали запросы, а раз предлагали — должны представлять и объёмы и количество запросов.
@Александр Чиченин:
В реальных задачах бывает не все так очевидно, как на первый вгляд.
Что касается обращений к базе, то в случае выполнения предложенного запроса оно одно, вне зависимости от количества строк, содержащихся в результате выборки.
@Павел Малинников:
Вот конкретная реальная задача, обойдёмся без демагогии.
Обращение то одно, а вот если разбор запроса посмотреть будет не так всё шоколадно. Ещё раз: лучше 5 простых запросов на выборку, чем 1 сложный запрос, который внутри будет преобразован в 25. Но это всё тоже бла бла бла. В конкретно описанной ситуации потребности в сложном запросе нет.
Для того, чтобы не было демагогии, нужно просто привести здесь реализацию того решения, которое вы имеете в виду.
Тогда не будет бла-бла и можно будет что-то обсудить.
Павел Малинников: Спасибо, Кэп.
Я имею в виду, что если какое-то значение (rank в виде натурального числа) можно получить через вычисление, то по нему можно сделать ORDER BY. В указанном мной примере a, b, c, d лишние, ага.
Илья пишет:
То есть, короче, мне нужно по нескольким колонкам вернуть не только их значения, но и натуральные числа, которые означали бы, какой по счёту была бы эта колонка, если бы я отсортировал по ней.
Тут я запутался — надо узнать какой по счёту будет колонка? Или таки строка?
Опять же, если надо действительно просто получить значение, а не сортировать по нему, то вот:
http://stackoverflow.com/questions/3614666/mysql-get-row-position-in-order-by/3614741#3614741
@Сергей Соляник
Вы меня успокоили, в примере a, b, c, d действительно лишние.
А то я на линкедине видел карточку с таким же именем, как у вас, так начал немного беспокоиться за майкрософт.
Павел Малинников: ничего не понял, где-где вы что там видели и при чём тут микрософт?
Если решать задачу теоретически, не подразумевая использования на практике, можно сделать так. Пусть в столбце a хранятся какие-то действительные числа. Тогда следующий запрос выведет именно ранги по столбцу a:
SELECT a, (SELECT 1 + count(*) FROM table AS t2 WHERE t2.a < t1.a AND ...) as rank FROM table AS t1 WHERE ...
Нужно поисследовать производительность такого запроса. Не думаю, что она будет приемлемой. Ситуацию можно немного спасти, если сделать a индексом. Тогда подзапрос (в зависимости от внутреннего WHERE, конечно) сможет выполниться только на индексе, без непосредственного обращения к таблице. Однако у тебя ORDER BY идет не по обычному столбцу, а по вычисляемому значению.
В одном подзапросе, скорее всего, можно вычислять сразу несколько рангов, по разным полям.
Таблицы, кстати, действительно проще всего сортировать на клиентской стороне. Вот, например: http://htmlcssjs.ru/JavaScript/?22. Нагрузка на клиент не имеет особого значения, так как скрипт работает только в момент щелчка по заголовку, когда идет сортировка.
@Роман Парпалак:
Роман, я попробовал проранжировать таблицу приблизительно в 50 записей по вашему методу, он работает. Но на таблице в 5000 записей не дождался результата выполнения, даже по индексированному столбцу.
Это, в общем, ожидаемо. Как вы сами понимаете, количество подзапросов тут пропорционально количеству строк, а не количеству нужных ранков, как хотелось бы.
А почему количество подзапросов пропорционально количеству строк в таблице? Оно пропорционально количеству выбираемых строк, которое вроде совпадает с количеством рангов.
Кстати, а что выводит EXPLAIN SELECT ...? Точно индексы используются?
Напишите, какая структура у вашей таблицы. Может я попробую на досуге оптимизировать это дело.
@Роман Парпалак:
А где вы у меня прочитали «пропорционально количеству строк в таблице»? Пропорционально количеству строк в селекте, вы же в селекте делаете подзапросы.
Если кроме сортировки нужно еще вычислить ранк по другому полю, то должен быть один подзапрос.
По 2-м полям — два и так далее, пропорционально количеству необходимых дополнительных ранков. Но не пропорционально количеству строк, это грустно.
Я погонял на реальной табличке 2 запроса. Один по вашей методике:
%%
SELECT t1.bid, t1.author, t1.genre, t1.bookname,
(select 1+count(*) from books t2 where t2.author < t1.author) rank
FROM books as t1
order by genre;
SQL-запрос: EXPLAIN SELECT t1.bid, t1.author, t1.genre, t1.bookname, ( SELECT 1 + count( * ) FROM books t2 WHERE t2.author < t1.author )rank FROM books AS t1 ORDER BY genre;
Ряды: 2
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 5764 Using filesort
2 DEPENDENT SUBQUERY t2 index author author 4 NULL 5764 Using where; Using index
%%
5764 всего, Запрос занял 28.9287 сек
и по своему варианту:
%%
SELECT t1.bid, t1.author, t1.genre, t1.bookname,
t2.rank
FROM books as t1, (select @rank1:=0) tmp1, (select @last_author:=0) tmp2
inner join (select bid, @rank1:=@rank1+if(@last_author = author, 0, 1) as rank, @last_author := author as lastval from books order by author) as t2 on t2.bid = t1.bid
order by genre;
%%
5764 всего, Запрос занял 0.0740 сек (!)
Кроме того, метод с count(*) возвратит не порядковый номер значения в сортировке, а количество строк перед ним. Например:
%%
Знач. Ранг count(*)
1 1 1
5 2 2
5 2 2
6 3 4
6 3 4
6 3 4
%%
А это не всегда приемлемо.
С быстродействием понятно, я так и думал.
Из таблицы можно выбирать не все строки, а, например, только 100 строк. Тогда 29 секунд превратятся в полсекунды.
По поводу последнего замечания. Илья написал, какой должен быть результат: 1, 2, 3, 3, 3, 6, 6, 8, 9, 10. Именно эти числа будут выведены в запросе 1 + count(*).
По поводу последнего замечания вы правы, Илье нужен именно такой ранг. В этом замечании, кстати, не утверждается, что Илье нужно что-то другое.
Попробовал ограничить выборку, в результате 80 строк. Даже в таких условиях count — 0.3869 сек, а с джойном все те же 0.0705 сек.
Разница в пять с половиной раз. Много ли это, мало ли, кто знает? Это зависит от данных, от конкретных условий.
А от нас, программистов, зависит то, какой алгоритм будет применяться для обработки этих данных. Думаю, вы согласитесь со мной, что он должен быть максимально отвязан от количества строк.