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

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

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

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

Синтаксис

Примеры

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

Пример

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

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

Пример

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

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

Пример

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

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

Пример

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

Источник

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

Базы данных

SQL функция COUNT

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

Описание

SQL функция COUNT используется для подсчета количества строк, возвращаемых в операторе SELECT.

Синтаксис

Синтаксис для функции COUNT в SQL.

Или синтаксис для функции COUNT при группировке результатов по одному или нескольким столбцам.

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

Не все это понимают, но функция COUNT будет подсчитывать только те записи, в которых expressions НЕ равно NULL в COUNT( expressions ). Когда expressions является значением NULL, оно не включается в вычисления COUNT. Давайте рассмотрим это дальше.

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

Введите следующий запрос SELECT, которая использует функцию COUNT.

Источник

Функция COUNT (Transact-SQL)

Эта функция возвращает количество элементов, найденных в группе. Функция COUNT работает подобно функции COUNT_BIG. Эти функции различаются только типами данных в возвращаемых значениях. Функция COUNT всегда возвращает значение типа данных int. Функция COUNT_BIG всегда возвращает значение типа данных bigint.

Синтаксические обозначения в Transact-SQL

Синтаксис

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

ALL
Применяет агрегатную функцию ко всем значениям. Аргумент ALL используется по умолчанию.

DISTINCT
Указывает, что функция COUNT возвращает количество уникальных значений, не равных NULL.

expression
Выражение любого типа, кроме image, ntext и text. Обратите внимание, что функция COUNT не поддерживает агрегатные функции и вложенные запросы в выражении.

*
Указывает, что функция COUNT должна учитывать все строки, чтобы определить общее количество строк таблицы для возврата. Функция COUNT(*) не принимает параметры и не поддерживает использование аргумента DISTINCT. Для функции COUNT(*) не требуется параметр expression, так как по определению она не использует сведения о конкретном столбце. Функция COUNT(*) возвращает количество строк в указанной таблице с учетом повторяющихся строк. Она подсчитывает каждую строку отдельно. При этом учитываются и строки, содержащие значения NULL.

Типы возвращаемых данных

Remarks

Функция COUNT(*) возвращает количество элементов в группе. Сюда входят значения NULL и повторяющиеся значения.

Функция COUNT(ALL expression) вычисляет expression для каждой строки в группе и возвращает количество значений, не равных NULL.

Функция COUNT(DISTINCT expression) вычисляет expression для каждой строки в группе и возвращает количество уникальных значений, не равных NULL.

COUNT — это детерминированная функция, если она используется без _ предложений OVER и ORDER BY. Она не детерминирована при использовании _ с предложениями OVER и ORDER BY. Дополнительные сведения см. в статье Детерминированные и недетерминированные функции.

Примеры

A. Использование функции COUNT и параметра DISTINCT

В этом примере функция возвращает количество различных должностей, которые может иметь сотрудник Компания Adventure Works Cycles.

Б. Использование функции COUNT(*)

В этом примере функция возвращает общее количество сотрудников Компания Adventure Works Cycles.

В. Использование функции COUNT(*) совместно с другими статистическими функциями

Г. Использование предложения OVER

Примеры: Azure Synapse Analytics и Система платформы аналитики (PDW)

Д. Использование функции COUNT и параметра DISTINCT

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

Е. Использование функции COUNT(*)

Ж. Использование функции COUNT(*) совместно с другими статистическими функциями

З. Использование функции COUNT с предложением HAVING

И. Использование функции COUNT с предложением OVER

Источник

Эффективный 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 уникальных элементов, а среднее по всем подгруппам дает достаточно точную оценку значения log₂(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 для определения неиспользуемых индексов в базе данных

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

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

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

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

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

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

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

Источник

COUNT DISTINCT и оконные функции

Мы без проблем можем посчитать общее количество ПК для каждого производителя, а также количество уникальных моделей данного производителя в таблице PC:

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

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

Использование ключевого слова DISTINCT не допускается с предложением OVER.

Сообщение об ошибке ясно описывает проблему. Вопрос в том, как её обойти.

Использование подзапроса

Использование DENSE_RANK

Источник