Comments 83
спасибо, полезно — добавил пост в избранное
Интересные решения. Конечно не для рабочего варианта базы, а скорее как единичные запросы запускаемые вручную.
Мне кажется, в подсчете количества различных записей в таблице вы предлагаете не оптимальный путь, потому что в нем не будут использоваться индексы. Другой способ — попробовать разбить один запрос на три и подобрать индексы. В конкретной задаче EXPLAIN SELECT подскажет, какой из способов быстрее.
За что минусанули человека? Мне тоже кажется, что
SUM(IF(f1 = 2, f2, 0)) индексы использовать если и будет, то гораздо менее оптимально, т.к. IF — это функция, и в неё нужно будет подать каждое существующее значение f1. В то же время, SELECT SUM(f2) WHERE f1 = 2 прекрасно использует индекс по условию, а потом уже просуммирует отфильтрованные данные.
Вы правы.
При наличии индексов у полей f1 и f2 запрос предложенный автором в реальности (за исключением специально подогнанных случаев) будет работать заметно медленнее, чем запрос, который автор называет не оптимальным.
При наличии индексов у полей f1 и f2 запрос предложенный автором в реальности (за исключением специально подогнанных случаев) будет работать заметно медленнее, чем запрос, который автор называет не оптимальным.
Поиск данных за последнюю дату
…
SELECT p.uid, p.amount FROM payments p JOIN (SELECT uid, MAX(pay_date) AS max_dt FROM payments GROUP BY uid) sel ON p.uid = sel.uid AND p.pay_date = sel.max_dt;
В результатах будут дубликаты, если один пользователь совершил несколько платежей в один день.
По моему GROUP BY не допустит этого.
Вы ошибаетесь. Будут там дубликаты, при условии, что пользователь совершил несколько платежей в последний день.
pastebin.com/7scfDNrq Минусуйте
Вы структуру таблицы предложенную автором для этого запроса внимательно рассмотрели?
У него поле pay_date типа DATE.
У него поле pay_date типа DATE.
payments (id INT, uid INT, pay_date DATETIME, amount DECIMAL(15, 2))
Я один вижу в этой таблице тип DATETIME?
Единственное, что я не воспроизвёл в своей таблице, так это тип поля amount, но оно в данном случае не играет роли.
Я один вижу в этой таблице тип DATETIME?
Единственное, что я не воспроизвёл в своей таблице, так это тип поля amount, но оно в данном случае не играет роли.
>> payments (id INT, uid INT, pay_date DATETIME, amount DECIMAL(15, 2))
Я один вижу в этой таблице тип DATETIME?
Автор поступил очень не красиво и поправил структуру таблицы в статье, после того как появились большинство комментариев на эту тему (примерно 15.08.2012 00:15).
Но даже поле типа DATETIME в данном случае Вас не спасет. Все равно будут дубликаты, но с точнотью до секунды, а не до дня.
Я один вижу в этой таблице тип DATETIME?
Автор поступил очень не красиво и поправил структуру таблицы в статье, после того как появились большинство комментариев на эту тему (примерно 15.08.2012 00:15).
Но даже поле типа DATETIME в данном случае Вас не спасет. Все равно будут дубликаты, но с точнотью до секунды, а не до дня.
distinct не допустил бы этого, group by группирует тупо
Дело не в group by, а в типе pay_date. Если он timestamp, то и дубликатов не будет. Ну они возможны, если юзер умудрился совершить 2 операции подряд с разницей в 1 секунду.
>> Дело не в group by, а в типе pay_date.
Нет, тут дело в не правильно построенном запросе.
Нет, тут дело в не правильно построенном запросе.
SELECT uid, MAX(pay_date) FROM payments GROUP BY uid;
Данный запрос гарантирует уникальные значения, но соединение значений с таблицей — нет. Еще раз, если тип будет timestamp, а при добавлении строки в поле pay_date будет добавляться current_timestamp, то запрос отработает как надо, даже если пользователь совершит несколько платежей в один день. Добавление записи с разницей < 1 секунду маловероятно. Niga доказал правильность запроса.
>> Добавление записи с разницей < 1 секунду маловероятно.
Вы это серьезно?
>> Niga доказал правильность запроса.
Он ошибся со структурой таблицы, и я ему это сразу пояснил. Не верите? возьмите и проверьте. Всё увидите своими глазами.
Вы это серьезно?
>> Niga доказал правильность запроса.
Он ошибся со структурой таблицы, и я ему это сразу пояснил. Не верите? возьмите и проверьте. Всё увидите своими глазами.
Вы это серьезно?
Всё упирается лишь в точность поля pay_date.
Он ошибся со структурой таблицы, и я ему это сразу пояснил. Не верите? возьмите и проверьте. Всё увидите своими глазами.
Так дело в запросе или в точности поля, хранящего время запроса? Вы уж определитесь.
В конце концов, можно решить это параноидальным запросом:
SELECT uid, amount
FROM payments
WHERE (id,uid) IN (SELECT MAX(id), uid
FROM payments p
GROUP BY uid, pay_date
HAVING pay_date = (SELECT max(pay_date) FROM payments WHERE uid=p.uid)
);
Ага, вот только бОльший id не означает, что платёж был проведён позже. В наших бизнес-требованиях об это ничего не сказано. Знаете ли, бывают распределённые системы, карточки с чипами, и т.д., когда платёж совершается сегодня, а в БД попадает через неделю.
В общем, проблема задачи — в том, что типичной её назвать сложно. Слишком разные бывают требования.
Нужно показать данные о последнем платеже, а вот как выяснить, какой платёж на самом деле был последним, — не сказано.
В общем, проблема задачи — в том, что типичной её назвать сложно. Слишком разные бывают требования.
Нужно показать данные о последнем платеже, а вот как выяснить, какой платёж на самом деле был последним, — не сказано.
Ага, вот только бОльший id не означает, что платёж был проведён позже.
Внимательно посмотрите на HAVING.
Вы, наверное, не поняли, что я имел в виду. ВНУТРИ одной даты (наибольшей для данного uid), бОльший id не означает, что платёж был проведён позже. И неважно, какая точность у payment_date — день, или секунда. Всё равно может быть несколько платежей, совершённых за одну секунду.
А ваш запрос выдаст точно тот же результат, что и мой, который я написал на час раньше. Критерии выбора те же самые, хотя структура запросов совсем разная.
Проблема в том, что неизвестно, как выяснить, какой платёж выбирать из тех, что имеют одинаковое значение payment_date. Бизнес-требования недостаточно проработаны.
А ваш запрос выдаст точно тот же результат, что и мой, который я написал на час раньше. Критерии выбора те же самые, хотя структура запросов совсем разная.
Проблема в том, что неизвестно, как выяснить, какой платёж выбирать из тех, что имеют одинаковое значение payment_date. Бизнес-требования недостаточно проработаны.
либо повышать точность времени, либо принять, что с одинаковым временем более поздний платеж с бОльшим id. Мой запрос, удовлетворяющий этому условию, ниже вашего)
Ну да. Я просто хотел подчеркнуть, что ничего непонятно без чётких бизнес-требований. Возможно, что задача вообще нерешаема.
Ага, а hello world стоит писать так.
>> Так дело в запросе или в точности поля, хранящего время запроса? Вы уж определитесь.
Вы понимаете, что результат запроса зависит от структуру таблицы?
Вы понимаете, что результат запроса зависит от структуру таблицы?
SELECT p1.uid, p1.amount
FROM payments p1
where not exists (select uid from payments p2 where p2.uid=p1.uid and p2.id>p1.id)
FROM payments p1
where not exists (select uid from payments p2 where p2.uid=p1.uid and p2.id>p1.id)
Сработает только для случаев, когда больший id означает более поздний платёж. Что совсем не обязательно истинно.
Делов-то, p2.pay_date>p1.pay_date вместо id. Главное — смысл.
select uid в подзапросе тоже возможно не оптимально, если индекс по uid отсутствует, а по id индекс есть.
select uid в подзапросе тоже возможно не оптимально, если индекс по uid отсутствует, а по id индекс есть.
pay_date вместо idТогда опять дубликаты появятся ;-)
Тогда задача не решаема? Максимум могу предложить суммировать все платежи в последний день, если их больше одного.
Да, я думаю, что в описанном варианте задача действительно нерешаема. Наиболее близким к искомому, наверное, будет вот такой вариант:
(онлайн-пример)
SELECT
p.uid,
p.amount
FROM
payments p
WHERE
p.id = (
SELECT p2.id
FROM payments p2
WHERE p.uid = p2.uid
ORDER BY p2.pay_date DESC, p2.id DESC
LIMIT 1
)
(онлайн-пример)
Ну или еще полухакерский вариант:
Думаю план у него будет плохим, да и 1000000 надо заменить на «очень большое значение». Короче, просто такой себе вариант
SELECT p.uid, p.amount
FROM payments p
JOIN
(SELECT uid, MAX(pay_date*1000000 + id) AS max_dt
FROM payments
GROUP BY uid) sel ON p.uid = sel.uid
AND (p.pay_date*1000000 + p.id) = sel.max_dt;Думаю план у него будет плохим, да и 1000000 надо заменить на «очень большое значение». Короче, просто такой себе вариант
Ну, если говорить о «хаках», можно и вот так :)
SELECT
uid,
CAST(
SUBSTRING(
MAX(CONCAT(pay_date, ',', LPAD(id, 11, '0'), ',', amount)),
24
) AS SIGNED) amount
FROM
payments
GROUP BY
uid
Да, вы правы, спасибо, моя ошибка. Тут конечно же DATETIME.
На самом деле, это мало что меняет.
И что Вам даст DATETIME?
Он Вам даст те же проблемы, но с точностью до секунды, а не до дней, что собственно ничего не меняет. Ваш запрос все равно будет возвращать дубликаты.
Он Вам даст те же проблемы, но с точностью до секунды, а не до дней, что собственно ничего не меняет. Ваш запрос все равно будет возвращать дубликаты.
Согласен с вами, был выбран неудачный пример, сейчас немного конкретизирую задачу.
Только обязательно укажите как Вы поставили задачу с самого начала, а потом новую постановку, а то получается, что мы здесь ерунду пишем в комментариях.
Вы привели какое-то абсолютно нереальное ограничение. Лучше бы написали, что если платежи произошли в одну секунду, то можно выбирать любой из них (например, тот, у которого больший id) — пользователю это подойдет. Ах да, хотя тогда бы ваш запрос не работал :)
Column 'id' in group statement is ambiguous
Я не понял «о чудо» этого абзаца. Очевидно же что для GROUP, id двусмысленный.
Так добавьте ему смысла.
Я не понял «о чудо» этого абзаца. Очевидно же что для GROUP, id двусмысленный.
Так добавьте ему смысла.
SELECT t1.id, t2.id
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id_t1
GROUP BY t1.id;
Плохо разбираюсь в MySQL, не подскажите, что за ключевое слово ON? Гугл плохо ищет по коротким словам.
ON используется в совокупности с JOIN'ами: dev.mysql.com/doc/refman/5.0/en/join.html
После ON следует условие для выборки из связанных таблиц в случае JOIN.
ON — аналог WHERE, используемый при JOIN.
Всем спасибо, все понял.
Спасибо, хорошие рецепты.
> Column 'id' in group statement is ambiguous
Для этого случая есть еще более простое решение:
Цифры в
Для этого случая есть еще более простое решение:
GROUP BY 1Цифры в
GROUP BY или ORDER BY обозначают номер колонки. Это плохая практика для рабочего кода. Но очень удобно, когда вам надо просто извлечь информацию из таблицы (особенно если вы пишете запрос руками).
Шикарно, наконец-то сбылась мечта идиота, я понял как сделать одно поле в выборке со счетчиком строк =)
Дополню Ваш пост: Common MySQL Queries
Вы пизданулись что ли все? Кто эти люди кто добавил статью в избранное? Вы хотите открыть что-то новое для себя в повторном чтении этих обычных запросов?
Если я узнал после прочтения статьи новое и добавил эту статью в избранное, то я «плохое слово»? Почему?
Про использование переменных не знал, спасибо. Решал такую задачу более громоздким запросом.
SELECT *
FROM address
WHERE num + 0 = 1;
Жесть.
Это запрос из разряда «прощай индексы».
Хотя даже LIKE их будет использовать (хотя и плохенько).
SELECT t1.id, t2.id
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id_t1
GROUP BY 1;
Лучше стараться не использовать специфичные для mysql фишки — легче будет мигрировать на другую СУБД, если вдруг понадобится
>> Лучше стараться не использовать специфичные для mysql фишки
Может Вы еще посоветуете не использовать специцифичные для СУБД ORACLE фишки?
Может Вы еще посоветуете не использовать специцифичные для СУБД ORACLE фишки?
Я ожидал, что в пример будет приведен эталон Оракл. Речь идет о нарушении стандартов. Например, group by мускула позволяет делать выборку полей, которые не участвуют в группировке. Это отход от стандарта. В большинстве случаев можно обойтись и без специфичных хаков
>> Речь идет о нарушении стандартов.
Вы знаете сколько дополнений к стандартам практически в любой СУБД (MySQL, MSSQL, PostgreSQL, ORACLE, ...)?
Если говорить о миграции, то речь скорее надо поднимать об использовании уровня абстракции над СУБД, но никак не отказываться от всяких вкусностей, которые предлагают различные СУБД.
Вы знаете сколько дополнений к стандартам практически в любой СУБД (MySQL, MSSQL, PostgreSQL, ORACLE, ...)?
Если говорить о миграции, то речь скорее надо поднимать об использовании уровня абстракции над СУБД, но никак не отказываться от всяких вкусностей, которые предлагают различные СУБД.
Насколько все эти трюки совместимы с PostgreSQL?
Вот ещё один хороший способ удалить дубликаты, не используя ключей
DELETE t1 FROM t1, t2 WHERE t1.id > t2.id AND t1.name = t2.name;
DELETE t1 FROM t1, t2 WHERE t1.id > t2.id AND t1.name = t2.name;
Начало статьи реально порадовало
На счет последнего примера я бы написал так:
ALTER IGNORE TABLE table1 ADD UNIQUE..., но в продолжении описаны методы, которые реально ломают использование индексов — огорчили.На счет последнего примера я бы написал так:
SELECT ua.uid, ua.amount
FROM (
SELECT p.uid, p.amount
FROM payments p
ORDER BY pay_date DESC
) ua
GROUP BY ua.uid
Нужно написать запрос, который бы вывел для каждого юзера дату и сумму последнего платежа.
UPD. Считаем, что юзер не может провести больше одного платежа за секунду. (Без этого условия постановка задачи некорректна). Тип pay_date изменен с DATE на DATETIME.
Возможно я открою вам глаза:
SELECT SELECT p.uid, p.amount
FROM payments p
GROUP BY p.uid
ORDER BY p.pay_date DESCРаботает в разы быстрее чем джойны виртуальных таблиц, честно.
Sign up to leave a comment.
Несколько интересных приемов и особенностей работы с MySQL