Веб-заметки и Веб-подсказки
SQL: Как выбрать только уникальные (неповторяющиеся) записи
Пример использования:
Допустим у нас есть таблица «winners» с данными:
| id | winner_name |
|---|---|
| 1 | Nick |
| 2 | Ann |
| 3 | Ann |
| 4 | David |
| 5 | Nick |
| 6 | Nick |
| 7 | Natali |
Допустим нам нужно получить список всех уникальный имён победителей.
Выполнив следующий запрос, мы получим все неповторяющиеся имена:
SELECT DISTINCT winner_name FROM winners;
Результат запроса:
| winner_name |
|---|
| Nick |
| Ann |
| David |
| Natali |
15 комментариев на «SQL: Как выбрать только уникальные (неповторяющиеся) записи»
Спасибо огромное, помогло!)
как в php сделать вывод данных?
Как я искал подобное решение. Огромное вам спасибо!
А как удалить неуникальные??
а еще можно использовать group by :
SELECT Filed FROM Table GROUP BY Field;
А если нужен и столбец id? подходит любое из значений… например
id winner_name
1 Nick
2 Ann
4 David
5 Nick
7 Natali
как такой запрос может выглядеть?
Так же есть задача, похожая на предыдущую. Есть таблица с вопросами из разных тем для экзамена. Нужно выбрать по одному случайному вопросу из 2-х любых тем (например так).
id theme question
1 1 question1
2 1 question2
3 1 question3
4 2 question4
5 2 question5
6 3 question6
7 3 question7
Тут решение из составного запроса в котором случайно сортируются все вопросы и берутся только 2 различные темы и потом как сделать, чтобы вопросов было по одному… Если кто знает — помогите, пожалуйста
Какой оператор используется для получения уникальных значений
Structured Query Language (SQL) — язык структурированных запросов, с помощью него пишутся специальные запросы (SQL инструкции) к базе данных с целью получения этих данных из базы и для манипулирования этими данными.
С точки зрения реализации язык SQL представляет собой набор операторов, которые делятся на определенные группы и у каждой группы есть свое назначение. В сокращенном виде эти группы называются DDL, DML, DCL и TCL.
DDL – Data Definition Language
Data Definition Language (DDL) – это группа операторов определения данных. Другими словами, с помощью операторов, входящих в эту группы, мы определяем структуру базы данных и работаем с объектами этой базы, т.е. создаем, изменяем и удаляем их.
В эту группу входят следующие операторы:
DML – Data Manipulation Language
Data Manipulation Language (DML) – это группа операторов для манипуляции данными. С помощью этих операторов мы можем добавлять, изменять, удалять и выгружать данные из базы, т.е. манипулировать ими.
В эту группу входят самые распространённые операторы языка SQL:
DCL – Data Control Language
Data Control Language (DCL) – группа операторов определения доступа к данным. Иными словами, это операторы для управления разрешениями, с помощью них мы можем разрешать или запрещать выполнение определенных операций над объектами базы данных.
TCL – Transaction Control Language
Transaction Control Language (TCL) – группа операторов для управления транзакциями. Транзакция – это команда или блок команд (инструкций), которые успешно завершаются как единое целое, при этом в базе данных все внесенные изменения фиксируются на постоянной основе или отменяются, т.е. все изменения, внесенные любой командой, входящей в транзакцию, будут отменены.
Базовый синтаксис SQL команды SELECT
Одна из основных функций SQL — получение данных из СУБД. Для построения всевозможных запросов к базе данных используется оператор SELECT. Он позволяет выполнять сложные проверки и обработку данных.
Общая структура запроса
В описанной структуре запроса необязательные параметры указаны в квадратных скобках.
Псевдонимы используются для представления столбцов или таблиц с именем отличным от оригинального. Это может быть полезно для улучшения читабельности имён и создания более короткого наименования столбца или таблицы.
Например, если в вашей таблице есть столбец good_type_id, вы можете переименовать его просто в id, для того, чтобы сделать его более коротким и удобным в использовании в будущем.
Для создания псевдонимов используется оператор AS:
Вы можете выводить любые строки и числа вместо столбцов:
Для того, чтобы вывести все данные из таблицы Company, вы можете использовать символ «*», который буквально означает «все столбцы»:
Вы можете вывести любой столбец, определённый в таблице, например, town_to из таблицы Trip:
Также вы можете вывести несколько столбцов. Для этого их нужно перечислить через запятую:
Иногда возникают ситуации, в которых нужно получить только уникальные записи. Для этого вы можете использовать DISTINCT. Например, выведем список городов без повторений, в которые летали самолеты:
Эта конструкция используется для формирования словарей, примеры рассмотрим в главе про команду INSERT
Условный оператор WHERE
Ситуация, когда требуется сделать выборку по определенному условию, встречается очень часто. Для этого в операторе SELECT существует параметр WHERE, после которого следует условие для ограничения строк. Если запись удовлетворяет этому условию, то попадает в результат, иначе отбрасывается.
Общая структура запроса с оператором WHERE
В описанной структуре запроса необязательные параметры указаны в квадратных скобках.
В условном операторе применяются операторы сравнения, специальные и логические операторы.
Операторы сравнения служат для сравнения 2 выражений, их результатом может являться ИСТИНА (1), ЛОЖЬ (0) и NULL.
Результат сравнения с NULL является NULL. Исключением является оператор эквивалентности.
| Оператор | Описание |
|---|---|
| = | Оператор равенство |
| Оператор эквивалентность Аналогичный оператору равенства, с одним лишь исключением: в отличие от него, оператор эквивалентности вернет ИСТИНУ при сравнении NULL NULL | |
| <> или != | Оператор неравенство |
| Оператор больше | |
| >= | Оператор больше или равно |
IS [NOT] NULL — позволяет узнать равно ли проверяемое значение NULL.
Для примера выведем всех членов семьи, у которых статус в семье не равен NULL:
[NOT] BETWEEN min AND max — позволяет узнать расположено ли проверяемое значение столбца в интервале между min и max.
Выведем все данные о покупках с ценой от 100 до 500 рублей из таблицы Payments:
[NOT] IN — позволяет узнать входит ли проверяемое значение столбца в список определённых значений.
Выведем имена членов семьи, чей статус равен «father» или «mother»:
[NOT] LIKE шаблон [ESCAPE символ] — позволяет узнать соответствует ли строка определённому шаблону.
Например, выведем всех людей с фамилией «Quincey»:
В шаблоне разрешается использовать два трафаретных символа:
ESCAPE-символ используется для экранирования трафаретных символов. В случае если вам нужно найти строки, содержащие проценты (а процент — это зарезервированный символ), вы можете использовать ESCAPE-символ.
Например, вы хотите получить идентификаторы задач, прогресс которых равен 3%:
Если бы мы не экранировали трафаретный символ, то в выборку попало бы всё, что начинается на 3.
Логические операторы необходимы для связывания нескольких условий ограничения строк.
Выведем все полёты, которые были совершены на самолёте «Boeing», но, при этом, вылет был не из Лондона:
Выборка сводных данных (из двух и более таблиц)
При формировании сводной выборки данные беруться из нескольких таблиц. В операторе FROM исходные таблицы перечисляются через запятую. Также им могут быть присвоены алиасы. Синтаксис запроса выглядит следующийм образом:
При выборке сводных таблиц нужно учитывать, что исходные таблицы перемножаются. Т.е. если на входе у нас были таблицы:
| id | Name |
|---|---|
| 1 | Иванов |
| 2 | Петров |
| id | Name | Phone |
|---|---|---|
| 1 | Иванов | 322223 |
| 2 | Петров | 111111 |
То при простом запросе без условий
Получим примерно следующее:
| id | Name | id2 | Name2 | Phone |
|---|---|---|---|---|
| 1 | Иванов | 1 | Иванов | 322223 |
| 1 | Иванов | 2 | Петров | 111111 |
| 2 | Петров | 1 | Иванов | 322223 |
| 2 | Петров | 2 | Петров | 111111 |
Чтобы выбрать уникальные значения, нам нужно использовать оператор WHERE для связи этих таблиц
Вложенные SQL запросы\
Вложенный запрос — это запрос на выборку, который используется внутри инструкции SELECT, INSERT, UPDATE или DELETE или внутри другого вложенного запроса. Подзапрос может быть использован везде, где разрешены выражения.
Пример структуры вложенного запроса
Здесь, SELECT поля_таблиц FROM список_таблиц WHERE конкретное_поле IN (. ) — внешний запрос, а SELECT поле_таблицы FROM таблица — вложенный (внутренний) запрос.
Каждый вложенный запрос, в свою очередь, может содержать один или несколько вложенных запросов. Количество вложенных запросов в инструкции не ограничено.
Подзапрос может содержать все стандартные инструкции, разрешённые для использования в обычном SQL-запросе: DISTINCT, GROUP BY, LIMIT, ORDER BY, объединения таблиц, запросов и т.д.
Подзапрос может возвращать скаляр (одно значение), одну строку, один столбец или таблицу (одну или несколько строк из одного или нескольких столбцов). Они называются скалярными, столбцовыми, строковыми и табличными подзапросами.
Подзапрос как скалярный операнд
Скалярный подзапрос — запрос, возвращающий единственное скалярное значение (строку, число и т.д.).
Следующий простейший запрос демонстрирует вывод единственного значения (названия компании). В таком виде он не имеет большого смысла, однако ваши запросы могут быть намного сложнее.
Таким же образом можно использовать скалярные подзапросы для фильтрации строк с помощью WHERE, используя операторы сравнения.
С помощью данного запроса возможно получить самого младшего члена семьи. Здесь используется подзапрос для получения максимальной даты рождения, которая затем используется для фильтрации строк.
Подзапросы с ANY, IN, ALL
ALL — ключевое слово, которое должно следовать за операцией сравнения, возвращающее TRUE, если все значения столбца подзапроса удовлетворяет обозначенному условию.
IN — ключевое слово, являющееся псевдонимом ключевому слову ANY с оператором сравнения = (эквивалентность), либо <> ALL для NOT IN. Например, следующие запросы равнозначны:
Строковый подзапрос — это подзапрос, возвращающий единственную строку с более чем одной колонкой. Например, следующий запрос получает в подзапросе единственную строку, после чего по порядку попарно сравнивает полученные значения со значениями во внешнем запросе.
Данную конструкцию удобно использовать для замены логических операторов. Так, следующие два запроса полностью эквивалентны:
Связанным подзапросом является подзапрос, который содержит ссылку на таблицу, которая была объявлена во внешнем запросе. Здесь вложенный запрос ссылается на внешюю таблицу «таблица_1»:
Подзапросы как производные таблицы
Производная таблица — выражение, которое генерирует временную таблицу в предложении FROM, которая работает так же, как и обычные таблицы, которые вы указываете через запятую. Так выглядит общий синтаксис запроса с использованием производных таблиц:
Обратите внимание на то, что для производной таблицы обязательно должен указываться её псевдоним, для того, чтобы имелась возможность обратиться к ней в других частях запроса.
Обработка вложенных запросов
Вложенные подзапросы обрабатываются «снизу вверх». То есть сначала обрабатывается вложенный запрос самого нижнего уровня. Далее значения, полученные по результату его выполнения, передаются и используются при реализации подзапроса более высокого уровня и т.д.
Добавление данных, оператор INSERT
Для добавления новых записей в таблицу предназначен оператор INSERT.
Общая структура запроса с оператором INSERT
В описанной структуре запроса необязательные параметры указаны в квадратных скобках. Вертикальной чертой обозначен альтернативный синтаксис.
Значения можно вставлять перечислением с помощью слова values, перечислив их в круглых скобках через запятую или c помощью оператора select. Таким образом, добавить новые записей можно следующими способами:
Первичный ключ при добавлении новой записи
Следует помнить, что первичный ключ таблицы является уникальным значением и добавление уже существующего значения приведет к ошибке.
При добавлении новой записи с уникальными индексами выбор такого уникального значения может оказаться непростой задачей. Решением может быть дополнительный запрос, направленный на выявление максимального значения первичного ключа для генерации нового уникального значения.
В SQL введен механизм его автоматической генерации. Для этого достаточно снабдить первичный ключ good_id атрибутом AUTO_INCREMENT. Тогда при создании новой записи в качестве значения good_id достаточно передать NULL или 0 — поле автоматически получит значение, равное максимальному значению столбца good_id, плюс единица.
Теперь, зная синткасис команд INSERT и SELECT, можем разобраться как создать из исходного набора данных словари и загрузить данные в БД с учетом внешних ключей
Допустим есть список агентов (данные полученные от заказчика в виде CSV-файла), у которых есть поля название, тип и т.д. (далее по тексту я её называю таблица импорта)
В структуре БД поле «тип агента» создано как внешний ключ на таблицу типов
Пишем инструкцию SELECT, которая выбирает уникальные записи из таблицы импорта:
Этот запрос можно выполнить отдельно, чтобы проверить что получится
После отладки запроса SELECT перед ним допишем запрос INSERT:
Если в таблице есть обязательные поля, а нем неоткуда взять для них данные, то мы можем в SELECT вставить фиксированные значения (в примере пустая строка):
Заполнение основной таблицы
Тоже сначала пишем SELECT запрос, чтобы проверить те ли данные получаются
напоминаю, что порядок и количество выбираемых и вставляемых полей должны быть одинаковыми
в поле AgentTypeID мы должны вставить ID соответсвующей записи из таблицы AgentType, поэтому выборка у нас из двух таблиц и чтобы не писать перед каждым полем полные названия таблиц мы присваиваем им алиасы
Т.е. мы выбираем перечисленные поля из таблицы agents_import и добавляем к ним ID агента у которого совпадает название.
Если же мы не укажем условие WHERE, то выберутся, к примеру, 100 * 10 = 1000 записей (каждый агент будет в каждой категории). Поэтому важно, чтобы условие WHERE выбирало уникальные значения.
Естественно, количество внешних ключей в таблице может быть больше одного, в таком случае в секции FROM перечисляем все используемые словари и в секции WHERE перечисляем условия для всех таблиц объединив их логическим выражением AND
Написав и проверив работу выборки (она должна возвращать чтолько же записей, сколько в таблице импорта) дописываем команду вставки данных:
Необходимо во-первых, восстановить структуру БД из скрипта, во-вторых, импортировать исходные данные в Excel, в-третьих исправить данные (смотрите на структуру таблиц в БД, где-то надо явно указать тип данных, где-то вырезать лишние данные. ) и в-четвёртых загрузить исправленные данные в БД (сначала просто импорт во временные таблицы, потом разнести SQL-запросами по нужным таблицам)
Эффективный count distinct
Конечно, есть очень простое решение на основе обычного SQL. Достаточно сохранять в простую таблицу уникальные id пользователей и время их входа:
Тогда решение будет выглядеть так:
SELECT count(distinct(id)) FROM visits WHERE time > :start AND time
Обычная выборка count distinct в Mysql
Однако это решение работает довольно медленно даже на небольших таблицах (миллионы записей).
Очень медленно, и это на 2.5млн записей в таблице
Суммарное количество аудитории, которое мы считаем — около 50 млн человек в сутки, поэтому нас это не устроило.
MySQL и count(distinct)
Mysql не очень хорошо подходит для решения задач. Но попробовать стоило. Чтобы все заработало быстрее, можно сделать несколько оптимизаций.
1. Правильный индекс
Mysql все равно будет сканировать все строки из запроса для определения уникальных id. Колонка времени поможет их существенно сократить. Поэтому она должна идти первой в индексе:
CREATE INDEX time_id ON visits(time, id)
2. Квант времени
В нашем случае минимальный промежуток времени для подсчета значений не может быть меньше дня. Нет смысла сохранять в один день больше, чем одну запись для каждого пользователя. Значит колонку time нужно делать типа date и ставить уникальный индекс:
CREATE UNIQUE INDEX time_id ON visits(time, id)
Колонка time имеет тип date, теперь каждый день будет только одна запись с уникальным id
Vertica и count(distinct)
Эта векторная база данных хорошо оптимизирована под агрегатные выборки. Однако подсчет количества по выбранному промежутку времени работает всего в 2-3 раза быстрее, чем Mysql.
Незначительно быстрее, чем Mysql
approximate_count_distinct()
Vertica поддерживает агрегатную функцию approximate_count_distinct(), которая считает количество уникальных значений приблизительно. Она работает на порядок быстрее обычного запроса, однако имеет ошибку около 1%:
Redis и HyperLogLog
Redis имеет специальное хранилище HyperLogLog. Оно позволяет сохранять туда ключи, а затем получать количество уникальных ключей в этом хранилище. Ограничение в том, что список сохраненных ключей достать невозможно. Преимущество в том, что одно такое хранилище занимает всего 12 Кб, способно сохранять 2 64 элементов и возвращает результат с погрешностью всего 0.8%.
Устройство HyperLogLog
HyperLogLog — это вероятностный алгоритм для подсчета уникальных элементов.
Принцип алгоритма можно объяснить простым примером из жизни. Представьте, что вы некоторое время подбрасывали монетку, подсчитывая количество непрерывных последовательных выпадений решки. Если у вас получилась последовательность длиной всего в 3 решки подряд, то можно предположить, что вы подбрасывали монетку не очень долго. С другой стороны, если у вас получилось 15, то вероятно вы потратили большую часть дня.
Но если вам повезло, и с первого раза получилась последовательность из 10 решек, после чего вы прекратили это бессмысленное занятие, то приблизительное суждение о потраченном времени будет крайне неверным. Чтобы увеличить точность апроксимации, нужно будет повторить экспреимент, но в этот раз использовать 10 монет и 10 листов бумаги, на которых вы будете записывать самые длинные последовательности выпавших решек. Таким образом сужденее о потраченном времени будет намного точнее.
HyperLogLog вычисляет хэш каждого нового элемента. Часть этого хэша (в двоичном представлении) используется для индекса регистра (разделяем множество нулей и единиц на m-число подмножеств, наши пары монета+лист бумаги). А другая часть используется для подсчета длины последовательности первых нулей и максимального значения этой последовательности (длина последовательности выпавших решек). Вероятность последовательности из n+1 нулей равна половине вероятности последовательности длиной n.
Поэтому используя значения различных регистров, которые привязаны к максимальным последовательностям нулей для данного подмножества, HyperLogLog способен обеспечить приближенную мощность множества с высокой точностью. Если имеется m-подгрупп и n-элементов, тогда в каждой группе в среднем будет n/m уникальных элементов, а среднее по всем подгруппам дает достаточно точную оценку значения log2(n/m).
Подсчет уникальных значений
В самом простом случае достаточно сохранять все значения в HLL элемент:
Функция pfcount вернет количество уникальных значений в ключике hll, увидим 1
Тут мы сохранили в ключ “hll” 1 элемент (число 1) и вывели количество уникальных элементов. Добавим еще парочку элементов в этот же ключик:
Теперь на экране увидим 3
Как и следовало, мы получили результат 3 (т.е. всего 3 уникальных элемента записаны в этом ключе).
И самое главное — этот подход работает на 3-4 порядка быстрее, чем аналогичное решение на основе SQL.
Временные фильтры
И все же, стандартного решения недостаточно. Нам необходимо иметь возможность выбирать период для подсчета уникальных значений. На этот случай Redis умеет делать склеивание HLL ключей прямо во время выборки:
Выборка вернет 5 — количество уникальных элементов сразу в двух HLL ключах
Для выборки можно использовать любое количество hll ключей. А нам необходимо получать количество уникальных пользователей по дням.
Следовательно, для решения достаточно сохранять идентификатор пользователя в HLL ключ при посещении им страницы. Название ключа будет содержать суффикс — дату посещения:
Сохранение информации о посещение за день
Теперь нужно выбрать количества уникальных пользователей за любой промежуток времени. Для этого нужно склеить все ключи за даты внутри этого промежутка:
Результатом будет количество уникальных посетителей в промежутке с 10.06 по 13.06 2016 года
Самое главное
SQL средства плохо справляются с подсчетом уникальных значений. Особенно, если необходимо фильтровать список. На небольших объемах данных достаточно использовать правильные индексы в MySQL. На объемах больше 1 млн записей следует обдумать возможность использования хранилища HyperLogLog в Redis.
Этот текст был написан несколько лет назад. С тех пор упомянутые здесь инструменты и софт могли получить обновления. Пожалуйста, проверяйте их актуальность.
Что такое индексы в Mysql и как их использовать для оптимизации запросов
Как исправить ошибку доступа к базе 1045 Access denied for user
Основные понятия о шардинге и репликации
Примеры ad-hoc запросов и технологии для их исполнения
Настройка Master-Master репликации на MySQL за 6 шагов
Как создать и использовать составной индекс в Mysql
Рекомендации по настройке Redis для оптимизации ресурсов и повышения стабильности на производственном сервере
Анализ медленных запросов (профилирование) в MySQL с помощью Percona Toolkit
Синтаксис и оптимизация Mysql LIMIT
Типы и способы применения репликации на примере MySQL
Настройка Master-Slave репликации на MySQL за 6 простых шагов
Check-unused-keys для определения неиспользуемых индексов в базе данных
Запрос для определения версии Mysql: SELECT version()
Правильная настройка Mysql под нагрузки и не только. Обновлено.
3 примера установки индексов в JOIN запросах
И как правильно работать с длительными соединениями в MySQL
Анализ медленных запросов с помощью EXPLAIN
Описание, рекомендации и значение параметра query_cache_size
Что значит и как это починить
Использование партиций для ускорения сложных удалений
Правила выбора типов данных для максимальной производительности в Mysql
Включение и использование логов ошибок, запросов и медленных запросов, бинарного лога для проверки работы MySQL
