#mysql #sql
Есть запрос:
SELECT *, DATE_FORMAT(`timestamp`,'%d.%m.%Y') AS `date`
FROM testresults
WHERE
`timestamp` > '2011-08-01' AND
`timestamp` NOT LIKE ('2011-08-04%') AND
`publish` = '1'
ORDER BY
SUBSTRING(`timestamp`, 1, 10) DESC,
`green` DESC,
`red` ASC,
`white` ASC,
`timestamp` DESC
Запрос вытаскивает записи результатов тестов за текущий месяц и сортирует их по дням
таким образом, чтобы тесты с лучшими результатами "внутри" дня шли первыми.
Но мне, на самом деле, нужно выбрать только первую строчку "внутри" каждого дня,
т.е. только одну строчку с самым лучшим результатом за каждый день текущего месяца.
Можно ли это сделать в данном запросе? И если можно, то каким образом нужно модифицировать
запрос?
upd: Результат теста лежит в трех колонках: green (количество правильных ответов),
red (количество неправильных) и white (количество вопросов без ответа). Если количество
green, red и white у двух тестов одинаковое, то лучшим из них (в этот день) считается
тот, который сделан позже по времени (поле timestamp, формат этого поля YYYY-MM-DD
HH:MM:SS). В ORDER BY видно как сортируются результаты.
Если бы поле в котором лежит результат было бы одно, то можно было бы сделать MAX()
по этому полю, сгруппировав записи по дням. Но здесь фактически четыре поля определяющих
лучший результат и я не вижу варианта как можно по ним сделать МАX() или что-то подобное ...
т.е. по сути надо сделать что-то типа "LIMIT 1 внутри каждого дня" ... вот только
как и можно ли это сделать вообще?
Ответы
Ответ 1
К сожалению, в mysql нет аналитических функций, поэтому придёться немного поизвращаться. Примерно так. select * from testresults t, (select max(a.id) as id from testresults a, (select max(result) res, date(day) d from testresults group by date(day)) b where a.result = b.res and date(a.day) = b.d group by a.result, date(a.day)) x where t.id = x.id; Хороший, кстати, вопрос) Тут таких мало.Ответ 2
Сгруппировать по дням и выбрать MAX(). Напишите структуру таблицы - в какой колонке лежит результат решительно непонятно. Теперь ясно. Предложение такое: раз результат не из одного поля, то максимум можно собирать по искусственно построенному выражению. Допустим, в тесте не может быть больше 100 вопросов. Тогда максимум ищем по выражению MAX( ((green*100) + white) * (60*60*24) - ((HOUR(timestamp)*60+MINUTE(timestamp))*60+SECOND(timestamp)) ) Исправлено. Конечно же не на 24 надо умножать а на 60, потому как в часу 60 минут Дополнено. SELECT t.* FROM testresults t JOIN (SELECT DAY(timestamp) AS md, MAX( ((green*100) + white) * (60*60*24)- ((HOUR(timestamp)*60+MINUTE(timestamp))*60+SECOND(timestamp)) ) AS mr FROM testresults WHERE ... GROUP BY DAY(timestamp)) tm ON tm.md = DAY(timestamp) AND tm.mr = ((green*100) + white) * (60*60*24)- ((HOUR(timestamp)*60+MINUTE(timestamp))*60+SECOND(timestamp)) ORDER BY DAY(timestamp)Ответ 3
А нет ли у Вас возможности добавить в таблицу еще одно поле, которое будет представлять общую оценку и вычисляться и вноситься в базу одновременно с конкретными результатами (правильно/не правильно/нет ответа). Можно предложить такую функцию: пусть red+green+white - число вопросов в тесте. Считаем оценку: note = (green - red)/(red + green + white) + 1 (если не подходит, можете составить свою функцию, возможно, лучше соответствующую вашей сортировке Ж) ) оценка будет в области 0...1...2, где 0 означает "все неправильно", 1 - "ни одного отвеченного либо число правильных и неправильных одинаково", 2 - "все правильно". (единицу добавили чтобы оценка всегда была неотрицательна, см. ниже) Для включения времени в оценку можно воспользоваться побитовым OR скомпоновав например, 64-битное целое таким образом, чтобы в старших разрядах (можно не во всех, мне кажется, достаточно, например 11 бит - значения от 0 до 2048 - помножим note на 1000 и получим нужное значение с сохранением точности 0.1%), сохранять note, а в младших - timestamp.... - таким образом, при сравнении главную роль будет играть оценка, а при одинаковых оценках - величина timestamp... Если бы поле в котором лежит результат было бы одно, то можно было бы сделать MAX() по этому полю, сгруппировав записи по дням. Но здесь фактически четыре поля определяющих лучший результат и я не вижу варианта как можно по ним сделать МАX() или что-то подобное ... Вот у Вас получится "одно поле". кстати Вы можете и не создавать это поле, а вычислять его значение в условии ORDER BY (...), но при большом количестве тестов - это дооолго...Ответ 4
Лучший результат - это какая-то функция от green, red, white, так и сделайте функцию, которая будет подсчитывать это значение, напр.: points = green * n * n - red * n - white + time где n - максимально допустимое количество вопросов, а time - фунция от времени. Добавите поле в таблицу и считайте значение при вставке. Тогда все сводится к банальной выборке: SELECT t.id, t.green, t.white, trunc (t.test_date) as test_date FROM result_table t, ( SELECT trunc(test_date) as test_date, max (points) as points FROM result_table WHERE green = green -- тут добавить нужный вам критерий group by trunc(test_date) ) x WHERE t.points = x.points p.s. название таблицы, полей и использование oracle функции trunc заменить на нужные вам Может я не все правильно понял, вы храните timestamp в строке? Еще и не в том формате, в которым выводите, может это действительно необходимо, но при каждом запросе вызываете две функции необязательно: DATE_FORMAT(`timestamp`,'%d.%m.%Y') AS `date` SUBSTRING(`timestamp`, 1, 10) DESC второе нам уже не надо из-за points, а первое можно сделать дополнительным полем - display_date, и тоже забивать значение при вставке. Итого таблица будет иметь вид: id, points, green, red, white, display_date, timestamp (+ другие ваши столбики) В случае, если таблица testresults слишком большая, то можно разбить ее на две: testresults_for_search: id, points, date (для фильтрации по дате) testresults: id, green, red, white, display_date, timestamp (ваш стамп в строке) Предполагается, что первая будет сканироватся полностью, а вторая по индексу - id.
Комментариев нет:
Отправить комментарий