Получение последней записи в каждой группе — MySQL
Существует таблица messages , которая содержит данные, как показано ниже:
Если я выполню запрос select * from messages group by name , я получу результат в виде:
Какой запрос вернет следующий результат?
То есть последняя запись в каждой группе должна быть возвращена.
В настоящее время я использую этот запрос:
Но это выглядит крайне неэффективно. Есть ли другие способы достижения того же результата?
15 ответов
UPD: 2017-03-31, версия 5.7.5 MySQL сделал переключатель ONLY_FULL_GROUP_BY включенным по умолчанию (следовательно, недетерминированные запросы GROUP BY стали отключены). Более того, они обновили реализацию GROUP BY, и решение могло работать не так, как ожидалось, даже с отключенным коммутатором. Нужно проверить.
Приведенное выше решение Билла Карвина прекрасно работает, когда количество элементов в группах довольно мало, но производительность запросов становится плохой, когда группы довольно велики, поскольку для решения требуется примерно n*n/2 + n/2 только для сравнения IS NULL .
Я провел свои тесты в таблице InnoDB из 18684446 строк с 1182 группы. Таблица содержит результаты тестов для функциональных тестов и имеет (test_id, request_id) в качестве первичного ключа. Таким образом, test_id является группой, и я искал последнюю request_id для каждого test_id .
Решение Билла уже работало в течение нескольких часов на моем Dell E4310, и я не знаю, когда оно закончится, даже если оно работает с индексом покрытия (следовательно, using index в EXPLAIN).
У меня есть пара других решений, основанных на тех же идеях:
- если базовым индексом является индекс BTREE (который обычно имеет место), самая большая пара (group_id, item_value) является последним значением в каждой —- +: = 10 =: + —-, то есть первое для каждого group_id если мы пройдемся по индексу в порядке убывания;
- если мы читаем значения, охватываемые индексом, значения читаются в порядке индекса;
- каждый индекс неявно содержит столбцы первичного ключа, добавленные к нему (то есть первичный ключ находится в индексе покрытия). В приведенных ниже решениях я работаю непосредственно с первичным ключом, в вашем случае вам просто нужно добавить столбцы первичного ключа в результат.
- во многих случаях гораздо дешевле собрать требуемые идентификаторы строк в нужном порядке в подзапросе и присоединить результат подзапроса к идентификатору. Поскольку для каждой строки в результате подзапроса MySQL потребуется отдельная выборка на основе первичного ключа, подзапрос будет помещен первым в объединении, а строки будут выведены в порядке идентификаторов в подзапросе (если мы опускаем явное ORDER BY для объединения)
3 способа, которыми MySQL использует индексы , — это отличная статья, чтобы понять некоторые детали.
Решение 1
Этот невероятно быстрый, он занимает около 0,8 секунд на моих 18M + строках:
Если вы хотите изменить порядок на ASC, поместите его в подзапрос, верните только идентификаторы и используйте его в качестве подзапроса для присоединения к остальным столбцам:
На мои данные уходит около 1,2 секунды.
Решение 2
Вот еще одно решение, которое занимает около 19 секунд для моей таблицы:
Также возвращает тесты в порядке убывания. Это намного медленнее, так как он выполняет полное сканирование индекса, но это здесь, чтобы дать вам представление о том, как вывести N max строк для каждой группы.
Недостаток запроса в том, что его результат не может быть кэширован в кеше запроса.
Источник
Функция LAST_VALUE в MySQL
Описание: в этой статье вы узнаете, как использовать функцию LAST_VALUE() в MySQL для возврата последней строки в упорядоченном наборе строк.
Обзор функций LAST_VALUE() в MySQL
Функция LAST_VALUE() является оконной функцией, что позволяет выбрать последнюю строку в упорядоченном наборе строк.
Ниже показан синтаксис функции LAST_VALUE():
Функция LAST_VALUE() возвращает значение expression из последней строки отсортированного набора строк.
Пункт OVER имеет три положения: partition_clause, order_clause и frame_clause.
partition_clause
partition_clause имеет следующий синтаксис:
Раздел PARTITION BY распределяет наборы результатов на несколько разделов , указанных одного или более выражений expr1, expr2 и т.д. Функция LAST_VALUE() применяется к каждой секции независимо друг от друга.
order_clause
order_clause имеет следующий синтаксис:
Предложение ORDER BY определяет логические порядки строк в разделах, где применяется функция LAST_VALUE().
frame_clause
В frame_clause определяет подмножество текущего раздела, к которому применяется функция LAST_VALUE(). Для более подробной информации frame_clause, пожалуйста, ознакомьтесь с руководством по оконным функциям.
Примеры функций LAST_VALUE() в MySQL
Давайте настроим образец таблицы для демонстрации.
Ниже приведен сценарий для создания таблицы overtime и внесения данных в таблицу.
1) Пример LAST_VALUE() по всему результату запроса
Следующий оператор получает имя сотрудника, сверхурочную работу и сотрудника, который имеет наибольшее сверхурочное время:
В этом примере в предложении ORDER BY указан логический порядок строк в наборе результатов по часам от низкого до высокого.
Спецификация фрейма по умолчанию следующая:
Это означает, что кадр начинается в первой строке и заканчивается в текущей строке набора результатов.
Поэтому, чтобы получить сотрудника с самым высоким сверхурочным временем, мы изменили спецификацию фрейма на следующую:
Это означает, что кадр начинается в первой строке и заканчивается в последней строке набора результатов.
2) Пример LAST_VALUE() над разделом
Следующее утверждение находит сотрудника, который имеет самые высокие сверхурочные в каждом отделе:
В этом примере, во-первых, предложение PARTITION BY разделило сотрудников по отделам. Затем предложение ORDER BY сортирует сотрудников в каждом отделе сверхурочно от низкого до высокого.
Спецификация фрейма в этом случае – это весь раздел. В результате функция LAST_VALUE() выбрала последнюю строку в каждом разделе, в которой был сотрудник с наибольшим временем сверхурочной работы.
Из этой статьи вы узнали, как использовать функцию LAST_VALUE() в MySQL для получения последней строки в упорядоченном наборе строк.
Источник
Последняя запись из таблицы
Всем привет, какой вариант выборки последней записи лучше в плане производительности, особенно если много записей?
В таблице id — auto_increament и парочка текстовых полей.
Помощь в написании контрольных, курсовых и дипломных работ здесь.
Последняя запись в базу
Здравствуйте можно ли узнать в каких таблицах базы происходили последние изменения?
Почему только последняя запись
При поиске выводится только последняя запись, а не все возможные варианты. почему? require.
Последняя вставленная запись в транзакции
Добрый вечер, Уважаемые форумчане! Стоит задача сделать транзакцию, которая добавляет в таблицу.
Не вытаскивается последняя тысяча записей из таблицы
Добрый день. Сложилась очень неприятная ситуация. Есть таблица, в ней около 20000 записей, при.
Т.к. выборка идёт по PRIMARY KEY, то, мне кажется, что тут и первый и второй варианты будут одинаково быстры.
Добавлено через 57 секунд
Эм. а второй вариант в MySQL вообще нельзя использовать. он такой синтаксис не понимает.
Сообщение от gitarillo
Сообщение от Asgaroth
Можно делать SELECT *,MAX(id) FROM. но почему-то SELECT MAX(id),* FROM не работает. И в любом случае, MAX он показывает максимальный, а строку выводит первую. Так что второй вариант вообще нерабочий получается.
Как по правильнее и красивее не знаю. лично я делаю ORDER BY . DESC LIMIT 1 (первый вариант). Лично мне вообще не нравятся сложные конструкции с несколькими запросами. Вряд ли они смогут быстрее работать.
Сообщение от Humanoid
Если на id стоит индекс, то, по-моему, первая конструкция будет наиболее быстрой.
1300 записей — это не много, много — это когда выборка с «LIMIT N, 1» будет выполняться заметно дольше, чем с «LIMIT 1»
Могу еще такой вариант предложить:
Сообщение от epog333
Сообщение от epog333
Помощь в написании контрольных, курсовых и дипломных работ здесь.
Запрос с JOIN и алиасами при выборке подставляется последняя запись
Добрый вечер! Если честно минут 30 думал как правильно поставить вопрос. Есть две таблицы Таблица.
Нужна последняя запись из подчинённой таблицы
Доброго дня! До сего момента пользовался функцией Last и с проблемами не сталкивался. Но сейчас.
Как сделать запись в реестр win7 64. В реестр попадает только последняя запись
Добрый вечер! Не могу сделать запись в реестр win7 64. Microsoft.Win32.RegistryKey hklm =.
Последняя запись
Помогите пож. разобраться c запросом! Необходимо найти в таблице последнюю запись с наличием буквы.
Источник
Примеры SQL запросов к базе данных MySQL
Содержание статьи
1. Самые простые MySQL запросы
2. Простые SELECT (выбрать) запросы
3. Простые INSERT (новая запись) запросы
4. Простые UPDATE (перезаписать, дописать) запросы
5. Простые DELETE (удалить запись) запросы
6. Простые DROP (удалить таблицу) запросы
7. Сложные MySQL запросы
8. MySQL запросы и переменные PHP
1. Самые простые SQL запросы
1. Выведет список ВСЕХ баз.
2. Выведет список ВСЕХ таблиц в Базе Данных base_name.
2. Простые SELECT (выбрать) запросы к базе данных MySQL
SELECT – запрос, который выбирает уже существующие данные из БД. Для выбора можно указывать определённые параметры выбора. Например, суть запроса русским языком звучит так — ВЫБРАТЬ такие-то колонки ИЗ такой-то таблицы ГДЕ параметр такой-то колонки равен значению.
1. Выбирает ВСЕ данные в таблице tbl_name.
2. Выведет количество записей в таблице tbl_name.
3. Выбирает (SELECT) из(FROM) таблицы tbl_name лимит (LIMIT) 3 записи, начиная с 2.
4. Выбирает (SELECT) ВСЕ (*) записи из (FROM) таблицы tbl_name и сортирует их (ORDER BY) по полю id по порядку.
5. Выбирает (SELECT) ВСЕ записи из (FROM) таблицы tbl_name и сортирует их (ORDER BY) по полю id в ОБРАТНОМ порядке.
6. Выбирает (SELECT) ВСЕ (*) записи из (FROM) таблицы users и сортирует их (ORDER BY) по полю id в порядке возрастания, лимит (LIMIT) первые 5 записей.
7. Выбирает все записи из таблицы users, где поле fname соответствует значению Gena.
8. Выбирает все записи из таблицы users, где значение поля fname начинается с Ge.
9. Выбирает все записи из таблицы users, где fname заканчивается на na, и упорядочивает записи в порядке возрастания значения id.
10. Выбирает все данные из колонок fname, lname из таблице users.
| Внимание! Старайтесь указывать конкретные колонки (как в примере 10). Это важно для того, чтобы запросы обрабатывались намного быстрее! |
11. Допустим у Вас в таблице пользовательских данных есть страна. Так вот если Вы хотите вывести ТОЛЬКО список встречающихся значений (чтобы, например, Россия не выводилось 20 раз, а только один), то используем DISTINCT. Выведет, из массы повторяющихся значений Россия, Украина, Беларусь. Таким образом, из таблицы users колонки country будут выведены ВСЕ УНИКАЛЬНЫЕ значения
12. Выбирает ВСЕ данные строк из таблицы users где age имеет значения 18,19 и 21.
13. Выбирает МАКСИМАЛЬНОЕ значение age в таблице users. То есть если у Вас в таблице самое большее значение age(с англ. возраст) равно 55, то результатом запроса будет 55.
14. Выберет данные из таблицы users по полям name и age ГДЕ age принимает самое маленькое значение.
15. Выберет данные из таблицы users по полю name ГДЕ id НЕ РАВЕН 2.
3. Простые INSERT (новая запись) запросы
INSERT – запрос, который позволяет ПЕРВОНАЧАЛЬНО вставить запись в БД. То есть создаёт НОВУЮ запись (строчку) в БД.
1. Делает новую запись в таблице users, в поле name вставляет Сергей, а в поле age вставляет 25. Таким образом, в таблицу дописывается новая строки с данными значениями. Если колонок больше, то они оставшиеся останутся либо пустыми, либо с установленными по умолчанию значениями.
4. Простые UPDATE запросы к базе данных MySQL
UPDATE – запрос, который позволяет ПЕРЕЗАПИСАТЬ значения полей или ДОПИСАТЬ что-то в уже существующей строке в БД. Например, есть готовая строка, но в ней нужно перезаписать параметр возраста, так как он изменился со временем.
1. В таблице users ГДЕ id равно 3 значение поля age становится 18.
2. Всё то же самое, что и в первом запросе, просто показан синтаксис запроса, где перезаписываются два поля и более.
В таблице users ГДЕ id равно 3 значение поля age становится 18, а country Россия.
5. Простые DELETE (удалить запись) запросы к базе данных MySQL
DELETE – запрос, который удаляет строку из таблицы.
1. Удаляет строку из таблицы users ГДЕ id равен 10.
6. Простые DROP (удалить таблицу) запросы к базе данных MySQL
DROP – запрос, который удаляет таблицу.
1. Удаляет целиком таблицу tbl_name.
7. Сложные запросы к базе данных MySQL
Любопытные запросы, которые могут пригодиться даже опытным пользователям
Данный сложный запрос ВЫБИРАЕТ колонки id,name,country В ТАБЛИЦАХ users,admins ГДЕ registration_date (дата) не старше 14 дней И activation НЕ РАВНО , СОРТИРОВАТЬ по registration_date в обратном порядке (новое в начале).
Выше указан пример так называемого запроса в запросе в SQL. Обновить возраст среди пользователей на 18+, где пол — мужской. Подобные варианты запроса не рекомендую. По личному опыту скажу, лучше создать несколько отдельных — они будут прорабатываться быстрее.
8. Запросы к базе данных MySQL и PHP
В MySQL запросы в PHP странице можно вставлять переменные в качестве сравниваемых и тп значений. Пара примеров
1. Выбирает все записи из таблицы users, где поле fname соответствует значению переменной $name.
2. В таблице users ГДЕ id равно 3 значение поля age изменяется на значение переменной $age.
Внимание! Если Вам интересен какой-либо ещё пример, то пишите вопрос в комментарии!
Источник
Полезные команды MySQL
Авторизация на сервере (из консоли), -h при необходимости авторизации на удалённом сервере
mysql -h hostname -u root -p
Создание БД
mysql> create database `databasename`;
Создание БД с указанием необходимой кодировки
mysql> create database `databasename` default character set ‘utf8’ collate ‘utf8_unicode_ci’;
Получить список всех БД на сервере
mysql> show databases;
Переключится на БД
mysql> use `db name`;
Получить список таблиц в базе
mysql> show tables;
Посмотреть структуру таблицы
mysql> describe `table name`;
Ещё один вариант
mysql> show columns from `table name`;
Удалить БД
mysql> drop database `database name`;
Удалить таблицу
mysql> drop table `table name`;
Показать все данные в таблице
mysql> SELECT * FROM `table name`;
Показать строки, где поле `field name` имеет значение «whatever».
mysql> SELECT * FROM `table name` WHERE `field name` = ‘whatever’;
Показать строки с именем «Bob» и номеном «3444444»
mysql> SELECT * FROM `table name` WHERE name = ‘Bob’ AND phone_number = 3444444;
Показать строки с номером «3444444» не содержащие имени «Bob» отсортированные по номеру.
mysql> SELECT * FROM `table name` WHERE name != ‘Bob’ AND phone_number = 3444444 order by phone_number;
Показать записи с именем, начинающимся на «bob» и номером 3444444
mysql> SELECT * FROM `table name` WHERE name like ‘Bob%’ AND phone_number = 3444444;
Верннуть все данные с именем, начинающемся на «bob» и номером 3444444 ограничить вывод пятью первыми строками
mysql> SELECT * FROM `table name` WHERE name like ‘Bob%’ AND phone_number = 3444444 limit 0,5;
Используем регулярное выражение. Для регистрозависимого выбора используйте «REGEXP BINARY». Данный запрос найдёт все записи, начинающиеся на «a»
mysql> SELECT * FROM `table name` WHERE rec RLIKE ‘^a’;
Показать уникальные записи
mysql> SELECT DISTINCT `column name` FROM `table name`;
Показать выбранные колонки отсортированные от а до я (ASC) или от я до а (DESC)
mysql> SELECT `col1`,`col2` FROM `table name` ORDER BY `col2` DESC;
Вернуть количество строк в таблице.
mysql> SELECT COUNT(*) FROM `table name`;
Просуммировать все числовые поля таблицы
mysql> SELECT SUM(*) FROM `table name`;
Объединение таблиц. Как работает JOIN (в картинках)
mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;
Создание пользователя. Вход под root. Переключение на БД mysql. Создание пользователя и обновление привилегий.
mysql -u root -p mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES(‘%’,’username’,PASSWORD(‘password’));
mysql> flush privileges;
Смена пароля пользователя из консоли
mysqladmin -u username -h hostname -p password ‘new-password’
Смена пароля пользователя из консоли MySQL. Вход как root. Смена пароля. Обновление привелегий.
mysql -u root -p mysql> SET PASSWORD FOR ‘user’@’hostname’ = PASSWORD(‘passwordhere’);
mysql> flush privileges;
Восстановление пароля root пользователя. Остановить MySQL сервер. Запустить с пониженной безопасностью. Залогинится на MySQL как root. Установить новый пароль. Разлогинится и перезапустить MySQL сервер.
/etc/init.d/mysql stop
mysqld_safe —skip-grant-tables &
mysql -u root mysql> use mysql;
mysql> update user set password=PASSWORD(‘newrootpassword’) where User=’root’;
mysql> flush privileges;
mysql> quit /etc/init.d/mysql stop
/etc/init.d/mysql start
Установка пароля root если он ещё не задавался ранее
mysqladmin -u root password newpassword
Смена пароля root
mysqladmin -u root -p oldpassword newpassword
Разрешить пользователю «Bob» подключаться к серверу c локального адреса с паролем «passwd». Войти как root. Переключиться на БД mysql. Дать привилегии. Обновить привелегии.
mysql -u root -p mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by ‘passwd’;
mysql> flush privileges;
Предоставить пользователю привилегии на БД. Авторизоваться как root. Переключиться на БД mysql. Предоставить привилегии. Обновить кеш привилегий.
mysql -u root -p mysql> use mysql;
mysql> INSERT INTO user (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES (‘%’,’databasename’,’username’,’Y’,’Y’,’Y’,’Y’,’Y’,’N’);
mysql> flush privileges;
mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;
Обновить информацию для существующего пользователя
mysql> use mysql;
mysql> UPDATE `user` SET Select_priv = ‘Y’,Insert_priv = ‘Y’,Update_priv = ‘Y’ where `User` = ‘user’;
flush privileges;
Удалить строки из таблицы
mysql> DELETE from `table name` where `field_name` = ‘whatever’;
Обновить кеш привилегий
mysql> flush privileges;
Удалить колонку из таблицы
mysql> alter table `table name` drop column `column name`;
Добавить колонку в таблицу
mysql> alter table `table name` add column `new column name` varchar (20);
Переименовать колонку
mysql> alter table `table name` change `old column name` `new column name` varchar (50);
Сделать данные в колоке уникальными (если дублирующиеся уже есть — будет ошибка)
mysql> alter table `table name` add unique (`column name`);
Модифицировать колонку
mysql> alter table `table name` modify `column name` VARCHAR(3);
Удалить индекс
mysql> alter table `table name` drop index `colmn name`;
Загрузить данные в БД из CSV файла.
mysql> LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE `table name` FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (field1,field2,field3);
Сделать дамп всех БД для бэкапа. Бэкап это файл с SQL командами для воссоздания всех баз.
mysqldump -u root -p —opt > /tmp/alldatabases.sql
Сделать дамп одной базы.
mysqldump -u username -p —databases databasename > /tmp/databasename.sql
Сделать дамп одной таблицы
mysqldump -c -u username -p databasename tablename > /tmp/databasename.tablename.sql
Восстановить БД (или таблицу) из бэкапа
mysql -u username -p databasename CREATE TABLE `table name` (
`firstname` VARCHAR(20),
`middleinitial` VARCHAR(3),
`lastname` VARCHAR(35),
`suffix` VARCHAR(3),
`officeid` VARCHAR(10),
`userid` VARCHAR(15),
`username` VARCHAR(8),
`email` VARCHAR(35),
`phone` VARCHAR(25),
`groups` VARCHAR(15),
`datestamp` DATE,
`timestamp` time,
`pgpemail` VARCHAR(255)
);
Создание таблицы, пример 2.
mysql> CREATE TABLE `table name` (
personid int(50) not null auto_increment primary key,
firstname VARCHAR(35),
middlename VARCHAR(50),
lastname VARCHAR(50) default ‘bato’
);
Источник