Как вывести уникальные значения sql

SQL-Урок 11. Выборка уникальных данных (SELECT DISTINCT)

Оператор SQL DISTINCT используется для указания на то, что следует работать только с уникальными значениями столбца.

Оператор SQL DISTINCT нашел широкое применение в операторе SQL SELECT, для выборки уникальных значений. Так же используется в агрегатных функциях.

Синтаксис

Примеры

Все примеры будут по этой таблице workers, если не сказано иное:

idnameagesalary
1Дима23400
2Петя25500
3Вася23500
4Коля301000
5Иван27500
6Кирилл281000

Пример

Давайте выберем все уникальные значения зарплат из таблицы workers:

SQL запрос выберет следующие строки:

salary
400
500
1000

Пример

Давайте подсчитаем все уникальные значения зарплат из таблицы workers (их будет 3 штуки: 400, 500 и 1000):

SQL запрос выберет следующие строки:

Пример

Давайте подсчитаем одновременно все уникальные значения зарплат и уникальные значения возрастов и запишем их в разные поля:

SQL запрос выберет следующие строки:

salary_countage_count
35

Пример

Давайте просуммируем все уникальные значения зарплат из таблицы workers:

Источник

Веб-заметки и Веб-подсказки

SQL: Как выбрать только уникальные (неповторяющиеся) записи

Пример использования:
Допустим у нас есть таблица «winners» с данными:

idwinner_name
1Nick
2Ann
3Ann
4David
5Nick
6Nick
7Natali

Допустим нам нужно получить список всех уникальный имён победителей.
Выполнив следующий запрос, мы получим все неповторяющиеся имена:

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 различные темы и потом как сделать, чтобы вопросов было по одному… Если кто знает — помогите, пожалуйста

Источник

Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

Базы данных

SQL оператор DISTINCT

В этом учебном материале вы узнаете, как использовать SQL оператор DISTINCT с синтаксисом и примерами.

Описание

SQL оператор DISTINCT используется для удаления дубликатов из результирующего набора оператора SELECT.

Синтаксис

Синтаксис для оператора DISTINCT в SQL:

Параметры или аргументы

Примечание

Давайте посмотрим, как использовать оператор DISTINCT для поиска уникальных значений в одном столбце таблицы.

В этом примере у нас есть таблица suppliers со следующими данными:

supplier_idsupplier_namecitystate
100YandexMoscowRussia
200GoogleLansingMichigan
300OracleRedwood CityCalifornia
400BingRedmondWashington
500YahooSunnyvaleWashington
600DuckDuckGoPaoliPennsylvania
700QwantParisIle de France
800FacebookMenlo ParkCalifornia
900Electronic ArtsSan FranciscoCalifornia

Будет выбрано 6 записей. Вот результаты, которые вы должны получить:

state
Russia
Ile de France
Pennsylvania
California
Washington
Michigan

В этом примере возвращаются все уникальные значения состояния из таблицы поставщиков и удаляются все дубликаты из набора результатов. Как видите, штат Калифорния в наборе результатов отображается только один раз, а не четыре раза.

Далее давайте рассмотрим, как использовать SQL DISTINCT для удаления дубликатов из более чем одного поля в операторе SELECT.

Используя ту же таблицу suppliers из предыдущего примера, введите следующий SQL оператор:

Будет выбрано 8 записей. Вот результаты, которые вы получите:

citystate
MoscowRussian
LansingMichigan
Redwood CityCalifornia
RedmondWashington
SunnyvaleWashington
PaoliPennsylvania
ParisFrance
Menlo ParkCalifornia

В этом примере у нас есть таблица products со следующими данными:

product_idproduct_namecategory_id
1Pear50
2Banana50
3Orange50
4Apple50
5Bread75
6Sliced Ham25
7KleenexNULL

Источник

SQL — урок 4. Выборка данных из базы

Базы данных › SQL — урок 4. Выборка данных из базы

Выборка записей

Выбор данных выполняется командой SELECT.
Ниже приведен примерный список используемых ею конструкций и ключевых слов, полный же список зависит от реализации СУБД:

Это наверно самая сложная команда, поэтому ее изучение лучше разбить на несколько частей. Здесь рассмотрим эту команду в общем, а соединения, группировку данных и подобное более детально чуть позже. Пусть имеется следующая таблица с указанными данными.

выборка констант

Для выбора констант может указываться любой источник. Однако, если мы хотим просто
подсчитать значение какого-то выражения, то указывать источник имеющий сотню тысяч записей затратно по ресурсам. Поэтому многие СУБД позволяют выбрать константы без указания источника. Oracle не поддерживает синтаксис SELECT без FROM, а для выбора констант используется специальная системная таблица dual.

выборка по столбцам таблиц

Если источники данных указаны, то кроме констант можно выбирать столбцы или строить выражения с их использованием. Столбец определяется как имя_источника.имя_столбца. Если источник данных один, то его имя можно опустить. Для выбора всех столбцов всех источников используется символ *. Аналогично можно выбрать все столбцы указанного источника: имя_источника.*. Ниже приведен пример выборки всех записей нашей таблицы.

синонимы (алиасы)

При выборе данных можно назначать временные синонимы источникам данных и используемым столбцам. А в некоторых случаях необходимо. Например, когда источник подзапрос соединяется с другим источником, именование подзапроса обязательно. Другой пример, это объединение нескольких выборок, имена столбцов которых должны совпадать. Ключевое слово AS как правило необязательно, а в Oracle разрешено только для столбцов.

уникальные записи

Записи выборки считаются одинаковыми, если значения соответствующих полей одинаковы. Поэтому для демонстрации distinct на нашей таблице нужно исключить первичный ключ (поле id) из выборки.

Конструкция ORDER BY позволяет последовательно отсортировать сразу по нескольким столбцам. Столбцы, по которым происходит сортировка, желательно проиндексировать.

выборка по условию

Конструкция WHERE позволяет ограничить множество выбираемых записей. Ниже приведено несколько примеров.

выборка по группам

И напоследок пару примеров группировки данных.

Соединения (Join)

Этот раздел написан на основе материалов сайта Javenue.

Ключевое слово join в SQL используется при построении select выражений. Инструкция Join позволяет объединить колонки из нескольких таблиц в одну. Объединение происходит временное и целостность таблиц не нарушается. Существует три типа join-выражений:

В свою очередь, outer join может быть left, right и full (слово outer обычно опускается).

В первой таблице будет хранится ID пользователя и его nick-name, а во второй — ID ресурса, имя ресурса и ID пользователя, который может этот ресурс администрировать.

Содержимое таблиц пусть будет таким:

Конструкция join выглядит так:

Где join_type — тип join-выражения, table_name — имя таблицы, которая присоединяется к результату, condition — условие объединения таблиц.

Кострукция join располагается сразу после select-выражения. Можно использовать несколько таких конструкций подряд для объединения соответствующего кол-ва таблиц. Логичнее всего использовать join в том случае, когда таблица имеет внешний ключ ( foreign key ).

Inner join необходим для получения только тех строк, для которых существует соответствие записей главной таблицы и присоединяемой. Иными словами условие condition должно выполняться всегда. Пример:

Результат будет таким:

В случае с left join из главной таблицы будут выбраны все записи, даже если в присоединяемой таблице нет совпадений, то есть условие condition не учитывает присоединяемую (правую) таблицу. Пример:

Результат выполнения запроса:

Результат показывает все ресурсы и их администраторов, вне зависимотсти от того есть они или нет.

А результат будет следующим:

Результирующая таблица показывает ресурсы и их администраторов. Если адмнистратор не задействован, эта запись тоже будет отображена. Такое может случиться, например, если ресурс был удален.

А результат будет таким:

Некоторые СУБД не поддерживают такую функциональность (например, MySQL), в таких случаях обычно используют объединение двух запросов:

Наконец, cross join. Этот тип join еще называют декартовым произведением (на английском — cartesian product). Настоятельно рекомендую использовать его с умом, так как время выполнения запроса с увеличением числа таблиц и строк в них растет нелинейно. Вот пример запроса, который аналогичен cross join :

Агрегатные функции, группировка данных

Для группировки данных в запросе select используется конструкция group by,
в которой должны быть перечислены те же столбцы, что и после select. Ниже приведен
пример вывода данных по группам для таблицы bills.

Сами по себе группы редко используются, и предыдущий пример выборки можно заменить
сортировкой. Другое дело, если необходимо воспользоваться одной из групповых функций,
называемых агрегатными:

Ключевое слово DISTINCT позволяет игнорировать повторные значения в столбце, ALL
обрабатывает все значения в столбце (по умолчанию), * позволяет включить в обработку поля с null значением.
В MySQL между именем функции и скобкой не должно быть пробелов.
Ниже приведен пример использования агрегатных функций в качестве выбираемых данных. Если
агрегатная функция используется в выборке без group by, то она применяется ко всем записям
выборки, иначе для каждой группы в отдельности. И в любом случае в перечислении select нельзя
смешивать групповые столбцы с не групповыми.

Агрегатные функции можно использовать в выражениях условия в конструкции having для
отбора группы.

Операции над выборками

Так как выборка по сути является множеством, то и доступные операции над ними
соответствующие:

Запросы участвующие в таких операциях должны следовать нескольким условиям.
Иметь одинаковое число столбцов, соответствующие столбцы должны быть одного типа.
Тип данных столбца должен быть простым, т.е. не разрешаются типы подобные blob.
MySQL 5 поддерживает только UNION, в Oracle EXCEPT для других целей,
а для исключения используется MINUS.

По умолчанию в результирующую выборку попадают только уникальные записи.
Для включения всех записей используется ключевое слово ALL после имени операции.
Например, в следующем примере будет две записи со значением 2.

Добавление итогов в SQL

Еще раз рассмотрим таблицу bills созданную в пункте об агрегатных функциях.
Предположим мы хотим вывести все суммы, а в конце выборки добавить итоговую сумму.
Наиболее универсальным способом является объединение двух запросов.

Для решения подобных задач в стандарте введена конструкция ROLLUP генерирующая
дополнительную строку. Если в определении столбца агрегатная функция не используется,
то соответствующее поле в этой строке заполняется значением null. В противном случае
заполняется значением выражения столбца, причем агрегатная функция выполняется ко
всем записям основной выборки.

А теперь предположим мы хотим вывести все суммы с итогами по каждой группе и в конце выборки
общий итог. Ниже приведен пример с использованием объединений. Чтобы общий итог был точно в
конце выборки, задаем в поле d максимальню дату. В Oracle и Postgre можно оставить значение
null.

Подобную задачу можно решить с помощью стандартной конструкции CUBE, если она уже
реализована в СУБД. Куб генерирует не только общий итог, но и все возможные под итоги.
Ниже приведен пример использования куба. Для упрощения кода пустые значения не заменяются.

Нумерация записей

В стандарт SQL2003 уже добавлена функция row_number(), если она еще не реализована
в вашей версии БД, используйте следующие методы.

Oracle

В Oracle для нумерации записей введен псевдостолбец rownum.

MySQL

В MySQL для этого надо воспользоваться переменной. Чтобы увидеть результат следующего
примера в MySQLQueryBrowser, необходимо начать транзакцию (на панели кнопка после слова
Transaction). Далее выполняем приведенные в примере команды и затем завершаем транзакцию
(соседняя кнопка с галочкой).

PostgreSQL

В PostgreSQL для этих целей можно выделить последовательность и сбрасывать ее перед новой
выборкой.

Источник

Эффективный count distinct

Как вывести уникальные значения sql. Смотреть фото Как вывести уникальные значения sql. Смотреть картинку Как вывести уникальные значения sql. Картинка про Как вывести уникальные значения sql. Фото Как вывести уникальные значения sql Как вывести уникальные значения sql. Смотреть фото Как вывести уникальные значения sql. Смотреть картинку Как вывести уникальные значения sql. Картинка про Как вывести уникальные значения sql. Фото Как вывести уникальные значения sql Как вывести уникальные значения sql. Смотреть фото Как вывести уникальные значения sql. Смотреть картинку Как вывести уникальные значения sql. Картинка про Как вывести уникальные значения sql. Фото Как вывести уникальные значения sql

Конечно, есть очень простое решение на основе обычного 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

Основные понятия о шардинге и репликации

Настройка Master-Master репликации на MySQL за 6 шагов

Примеры ad-hoc запросов и технологии для их исполнения

Типы и способы применения репликации на примере MySQL

Анализ медленных запросов (профилирование) в MySQL с помощью Percona Toolkit

Как создать и использовать составной индекс в Mysql

Настройка Master-Slave репликации на MySQL за 6 простых шагов

Синтаксис и оптимизация Mysql LIMIT

Рекомендации по настройке Redis для оптимизации ресурсов и повышения стабильности на производственном сервере

Правильная настройка Mysql под нагрузки и не только. Обновлено.

И как правильно работать с длительными соединениями в MySQL

Запрос для определения версии Mysql: SELECT version()

Check-unused-keys для определения неиспользуемых индексов в базе данных

3 примера установки индексов в JOIN запросах

Анализ медленных запросов с помощью EXPLAIN

Что значит и как это починить

Описание, рекомендации и значение параметра query_cache_size

Использование партиций для ускорения сложных удалений

Правила выбора типов данных для максимальной производительности в Mysql

Включение и использование логов ошибок, запросов и медленных запросов, бинарного лога для проверки работы MySQL

Источник

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *