sql группировка по дням

Группа запросов MySQL по дням / месяцам / годам

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

13 ответов

Примечание (в первую очередь, для потенциальных downvoters). В настоящее время это может оказаться не столь эффективным, как другие предложения. Тем не менее, я оставляю его в качестве альтернативы, которая также может помочь увидеть, насколько быстрее другие решения. (Ибо нельзя отличить быстрое от медленного, пока не увидишь разницу.) Кроме того, с течением времени могут быть внесены изменения в движок MySQL в отношении оптимизации, чтобы сделать это решение в некоторых (возможно, не так далеко) точка в будущем, чтобы стать вполне сопоставимыми по эффективности с большинством других.

Я попытался использовать оператор » где » выше, я думал, что это правильно, так как никто не исправил его, но я был неправ; после некоторых поисков я узнал, что это правильная формула для оператора WHERE, поэтому код становится таким:

извлечение (единица измерения от даты) функция лучше, поскольку используется меньше группировки, и функция возвращает числовое значение.

условие сравнения при группировании будет быстрее, чем функция DATE_FORMAT (которая возвращает строковое значение). Попробуйте использовать функцию / поле, которые возвращают нестроковое значение для условия сравнения SQL (где, имея, заказ по, Группа по).

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

Версия № 1:

версия #2 (более эффективный):

Я сравнил эти версии на большой таблице с 1,357,918 строками (InnoDB в), и 2-я версия, похоже, имеет лучшие результаты.

version1 (в среднем выполняется 10): 1.404 секунд
version2 (в среднем выполняется 10): 0.780 секунд

( SQL_NO_CACHE ключ добавлен, чтобы предотвратить MySQL от кэширования запросов.)

Если вы хотите сгруппировать по дате в MySQL, используйте код ниже:

надеюсь, это сэкономит некоторое время для тех, кто собирается найти этот поток.

если вы хотите фильтровать записи за определенный год (например, 2000), оптимизируйте WHERE пункт такой:

результаты были сгенерированы для таблицы, содержащей 300k строк и столбец index on date.

Что касается GROUP BY предложение, я протестировал три варианта против вышеупомянутой таблицы; вот результаты:

последний является победителем.

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

полное и простое решение с аналогичным выполнением, но более короткой и гибкой альтернативой в настоящее время активен:

следующий запрос работал для меня в Oracle Database 12c Release 12.1.0.1.0

вы можете сделать это просто Mysql DATE_FORMAT () функция в группе BY. Может потребоваться добавить дополнительный столбец для большей ясности в некоторых случаях, например, когда записи охватывают несколько лет, а затем один и тот же месяц в разные годы.Здесь так много вариантов, которые вы можете настроить. Пожалуйста, прочитайте это для начала. Надеюсь, это будет очень полезно для вас. Вот пример запроса для вашего понимания

Я предпочитаю оптимизировать выбор группы на один год следующим образом:

Источник

Как сделать быструю группировку по дням и по часам в mysql?

Верно, просто индексы не помогут — любая операция над полем сразу блокирует использование индекса по нему. Добавлять ещё поля и там хранить данных в часах и днях (денормализация)

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

быстро можно только если сделать хранилище отдельное
дата | кол-во
и инкриментить с общим инсертом. (update order_counter set count=count+1 where date=date_format(date,’и какой типо групперовки вам нужен’), надо проверить что будет делать replace или insert с on dublicate key для самой первой встаки в идеале вместо update replace тогда и запись не надо будет проверять что есть.

либо опишите задачу подробнее, если в выборки еще фильтр какой-то есть,
то решение со счетчиком отдельным не поможет.

Попробуйте следующий вариант — 1. Это для группировки по дням
SELECT…
FROM…
GROUP BY UNIX_TIMESTAMP( поле с датой и временем ) DIV 24 * 3600

2. Это для группировки по часам
SELECT…
FROM…
GROUP BY UNIX_TIMESTAMP( поле с датой и временем ) DIV 3600

Если все еще будет тормозить (скорее всего так и будет), то эти два поля нужно добавлять к таблице и строить по ним индекс

Источник

Группировки и оконные функции в Oracle

Привет, Хабр! В компании, где я работаю, часто проходят (за мат извините) митапы. На одном из них выступал мой коллега с докладом об оконных функциях и группировках Oracle. Эта тема показалась мне стоящей того, чтобы сделать о ней пост.

С самого начала хотелось бы уточнить, что в данном случае Oracle представлен как собирательный язык SQL. Группировки и методы их применения подходят ко всему семейству SQL (который понимается здесь как структурированный язык запросов) и применимы ко всем запросам с поправками на синтаксис каждого языка.

Всю необходимую информацию я постараюсь кратко и доступно объяснить в двух частях. Пост скорее будет полезен начинающим разработчикам. Кому интересно — добро пожаловать под кат.

Часть 1: предложения Order by, Group by, Having

Здесь мы поговорим о сортировке — Order by, группировке — Group by, фильтрации — Having и о плане запроса. Но обо всем по-порядку.

Order by

Оператор Order by выполняет сортировку выходных значений, т.е. сортирует извлекаемое значение по определенному столбцу. Сортировку также можно применять по псевдониму столбца, который определяется с помощью оператора.

Преимущество Order by в том, что его можно применять и к числовым, и к строковым столбцам. Строковые столбцы обычно сортируются по алфавиту.

Сортировка по возрастанию применяется по умолчанию. Если хотите отсортировать столбцы по убыванию — используйте дополнительный оператор DESC.

SELECT column1, column2, … (указывает на название)
FROM table_name
ORDER BY column1, column2… ASC|DESC;

Давайте все рассмотрим на примерах:

В первой таблице мы получаем все данные и сортируем их по возрастанию по столбцу ID.

Во второй мы также получаем все данные. Сортируем по столбцу ID по убыванию, используя ключевое слово DESC.

В третьей таблице используется несколько полей для сортировки. Сначала идет сортировка по отделу. При равенстве первого оператора для полей с одинаковым отделом применяется второе условие сортировки; в нашем случае — это зарплата.

Все довольно просто. Мы можем задать более одного условия сортировки, что позволяет более грамотно сортировать выходные списки.

Group by

В SQL оператор Group by собирает данные, полученные из базы данных в определенных группах. Группировка разделяет все данные на логические наборы, что дает возможность выполнять статистические вычисления отдельно в каждой группе.

Этот оператор используется для объединения результатов выборки по одному или нескольким столбцам. После группировки будет только одна запись для каждого значения, использованного в столбце.

С использованием оператора SQL Group by тесно связано использование агрегатных функций и оператор SQL Having. Агрегатная функция в SQL — это функция, возвращающая какое-либо одно значение по набору значений столбца. Например: COUNT(), MIN(), MAX(), AVG(), SUM()

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

Group by стоит после условного оператора WHERE в запросе SELECT. По желанию можно использовать ORDER BY, чтобы отсортировать выходные значения.

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

Решение 1 (без использования группировки):

Решение 2 (с использованием группировки):

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

Второй пример вышел короче и читабельнее, хотя выполняет такие же функции, что и первый.

Как у нас работает Group by: сначала разбивает два отдела на группы qa и dev. Потом для каждого из них ищет максимальную зарплату.

Having

Having это инструмент фильтрации. Он указывает на результат выполнения агрегатных функций. Предложение Having используется в SQL там, где нельзя применить WHERE.

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

Сначала мы выводим отделы со средней зарплатой больше 4000. Затем выводим максимальную зарплату с применением фильтрации.

Решение 1 (без использования GROUP BY и HAVING):

Решение 2 (с использованием GROUP BY и HAVING):

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

План запроса

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

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

Исполнение любого SQL предложения в Oracle извлекает так называемый “план исполнения”. Этот план исполнения запроса является описанием того, как Oracle будет осуществлять выборку данных, согласно исполняемому SQL предложению. План представляет собой дерево, которое содержит порядок шагов и связь между ними.

К средствам, позволяющим получить предполагаемый план выполнения запроса, относятся Toad, SQL Navigator, PL/SQL Developer и др. Они выдают ряд показателей ресурсоемкости запроса, среди которых основными являются: cost — стоимость выполнения и cardinality (или rows) — кардинальность (или количество строк).

Чем больше значение этих показателей, тем менее эффективен запрос.

Ниже можно увидеть анализ плана запроса. В первом решении используется подселект, во втором — группировка. Обратите внимание, что в первом решении обработано 22 строки, во втором — 15.

Анализ плана запроса:

Ещё один анализ плана запроса, в котором применяется два подселекта:

Этот пример приведен как вариант нерационального использования средств SQL и я не рекомендую вам его использовать в своих запросах.

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

Часть 2: Оконные функции

Оконные функции появились ещё в Microsoft SQL Server 2005. Они осуществляют вычисления в заданном диапазоне строк внутри предложения Select. Если говорить кратко, то “окно” — это набор строк, в рамках которого происходит вычисление. “Окно” позволяет уменьшить данные и более качественно их обработать. Такая функция позволяет разбивать весь набор данных на окна.

Оконные функции обладают огромным преимуществом. Нет необходимости формировать набор данных для расчетов, что позволяет сохранить все строки набора с их уникальными ID. Результат работы оконных функций добавляется к результатирующей выборке в еще одно поле.

SELECT column_name(s)
Агрегирующая функция (столбец для вычислений)
OVER ([PARTITION BY столбец для группировки]
FROM table_name
[ORDER BY столбец для сортировки]
[ROWS или RANGE выражение для ограничения строк в пределах группы])

OVER PARTITION BY — это свойство для задания размеров окна. Здесь можно указывать дополнительную информацию, давать служебные команды, например добавить номер строки. Синтаксис оконной функции вписывается прямо в выборку столбцов.

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

В первом поле мы берем имя, во втором — зарплату. Дальше мы применяем оконную функцию over(). Используем её для получения максимальной зарплаты по всей организации, так как не указаны размеры “окна”. Over() с пустыми скобками применяется для всей выборки. Поэтому везде максимальная зарплата — 10 000. Результат действия оконной функции добавляется к каждой строчке.

Если убрать из четвертой строки запроса упоминание оконной функции, т.е. остается только max (salary), то запрос не сработает. Максимальную зарплату просто не удалось бы посчитать. Так как данные обрабатывались бы построчно, и на момент вызова max (salary) было бы только одно число текущей строки, т.е. текущего работника. Вот тут и можно заметить преимущество оконной функции. В момент вызова она работает со всем окном и со всеми доступными данными.

Давайте рассмотрим еще один пример, где нужно вывести максимальную з/п каждого отдела:

Фактически мы задаем рамки для “окна”, разбивая его на отделы. В качестве ранжирующего примера мы указываем department. У нас есть три отдела: dev, qa и sales.

“Окно” находит максимальную зарплату для каждого отдела. В результате выборки мы видим, что оно нашло максимальную зарплату сначала для dev, затем для qa, потом для sales. Как уже упоминалось выше, результат оконной функции записывается в результат выборки каждой строки.

В предыдущем примере в скобках после over не было указано. Здесь мы использовали PARTITION BY, которое позволило задать размеры нашего окна. Здесь можно указывать какую-то доп информацию, передавать служебные команды, например, номер строки.

Заключение

SQL не так прост, как кажется на первый взгляд. Все описанное выше — это базовые возможности оконных функций. С их помощью можно “упростить” наши запросы. Но в них скрыто намного больше потенциала: есть служебные операторы (например ROWS или RANGE), которые можно комбинировать, добавляя больше функциональности запросам.

Надеюсь, пост был полезен для всех интересующихся данной темой.

Источник

Оператор SQL GROUP BY для группировки в запросах

Оператор GROUP BY имеет следующий синтаксис:

Группировка по одному столбцу без агрегатных функций

Если в результате запроса требуется вывести один столбец и по этому же столбцу производится группировка, то оператор GROUP BY просто выбирает уникальные значения и убирает дубликаты, то есть выполняет те же задачи, что и ключевое слово DISTINCT.

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке .

В примерах работаем с базой данных библиотеки и ее таблицей «Книга в пользовании» (Bookinuse). Отметим, что оператор GROUP BY ведёт себя несколько по-разному в MySQL и в MS SQL Server. Эти различия будут показаны на примерах.

Пример 1. Вывести авторов выданных книг, сгруппировав их. Пишем следующий запрос:

Этот запрос вернёт следующий результат:

Как видим, в таблице стало меньше строк, так как фамилии авторов остались каждая по одной.

В следующем примере увидим, что оператор GROUP BY не следует путать с оператором ORDER BY и поймём, чем эти операторы отличаются друг от друга.

Пример 2. Вывести авторов и названия выданных книг, сгруппировав по авторам. Пишем следующий запрос, который допустим в MySQL:

Этот запрос вернёт следующий результат:

Как видим, в таблице каждому автору соответствует лишь одна книга, причём та, которая в таблице BOOKINUSE является первой по порядку записей.

Если бы нам требовалось вывести все книги, причём авторы должны были бы следовать не «вразброс», а по порядку: сначала Гоголь и все его книги, затем другие авторы и все их книги, то мы применили бы не оператор GROUP BY, а оператор ORDER BY.

Группировка по нескольким столбцам без агрегатных функций

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

Пример 3. Вывести авторов, названия выданных книг, ID пользователя и инвентарный номер выданной книги. Сгруппировать по авторам, ID пользователя и инвентарному номеру. На MySQL запрос будет следующим:

Этот запрос вернёт следующий результат:

По-другому ведёт себя оператор GROUP BY в MS SQL Server и в случае этого запроса.

Группировка с агрегатными функциями

Пример 4. Вывести количество выданных книг каждого автора. Запрос будет следующим:

Результатом выполнения запроса будет следующая таблица:

Пример 5. Вывести количество книг, выданных каждому пользователю. Запрос будет следующим:

Результатом выполнения запроса будет следующая таблица:

Примеры запросов к базе данных «Библиотека» есть также в уроках по оператору IN, предикату EXISTS и функциям CONCAT, COALESCE.

Источник

Руководство по предложению GROUP BY в SQL

Перевод статьи « SQL Group By Tutorial: Count, Sum, Average, and Having Clauses Explained».

Предложение GROUP BY это очень мощный параметр, но и непростой. Даже спустя восемь лет его использования я все еще каждый раз задумываюсь, что, собственно, я делаю.

Мы рассмотрим следующие темы:

Подготовительный этап с описанием программного обеспечения и созданием базы данных мы рассматривали в двух предыдущих статьях:

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

Создание таблицы с данными

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

Давайте создадим нашу таблицу и внесем в нее кое-какие данные о продажах:

У нас были продажи сегодня, вчера и позавчера.

Как работает GROUP BY?

Представьте, что у нас есть комната, в которой находится много людей. Эти люди родились в разных странах.

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

Когда люди сгруппируются по месту рождения, мы сможем высчитать средний рост в каждой группе.

Множественные группы

Мы можем группировать данные в любое количество групп и подгрупп.

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

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

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

Предложения GROUP BY часто используются в случаях, когда можно использовать обороты по чему-то или в каждом(ой):

Написание предложений GROUP BY

Предложение GROUP BY пишется очень просто. Мы используем ключевые слова GROUP BY и указываем поля, по которым должна происходить группировка:

Очевидно, что нам нужно сделать выборку локации. Мы группируем данные по этому столбцу и как минимум хотим увидеть имена созданных групп:

Результатом будут три наши локации:

А как насчет остальных столбцов таблицы?

мы получим вот такую ошибку:

Проблема в том, что мы взяли восемь строк и попытались втиснуть их в три.

Мы не можем просто возвращать оставшиеся столбцы, как обычно, потому что раньше у нас было восемь строк, а теперь их только три.

Что делать с оставшимися пятью строками данных? Какие данные из восьми строк должны быть отображены в трех строках?

На эти вопросы нет четкого и ясного ответа.

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

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

Агрегатные функции (COUNT, SUM, AVG)

Если мы решили сгруппировать данные, мы можем агрегировать данные оставшихся столбцов. например, мы можем посчитать число строк в каждой группе, суммировать отдельные значения в группе или вывести некое среднее значение (тоже по группе).

Для начала давайте найдем количество продаж по каждой локации.

Поскольку каждая запись в таблице sales это запись об одной продаже, число продаж по локации будет равно числу строк в каждой группе (при группировке по локациям).

Чтобы получить нужный результат, нам нужно применить агрегатную функцию COUNT() — так мы вычислим количество строк в каждой группе.

( COUNT() также работает с выражениями, но при этом имеет несколько другое поведение).

Вот как база данный выполняет наш запрос:

Локация 1st Street имеет две продажи, HQ — четыре, а Downtown — две.

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

Вместо подсчета числа строк в группе мы могли бы суммировать информацию по группе. Например, получить общее количество вырученных денег по каждой локации.

Для этого мы будем использовать функцию SUM() :

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

Функция AVG() позволяет находить среднее значение (AVG от Average — среднее). Давайте найдем среднюю сумму выручки по локациям. Для этого просто заменим функцию SUM() на функцию AVG() :

Работа с несколькими группами

Пока что мы работали с одной группировкой — по локациям. Что, если нам нужно разбить полученные группы на подгруппы?

Для этого нам нужно добавить к нашему предложению GROUP BY второе группирующее условие:

(Для облегчения чтения я добавил в запрос также предложения ORDER BY ).

В результатах нашего нового группирования мы видим уникальные комбинации локаций и продуктов:

Ну хорошо, у нас есть наши группы, а что мы будем делать с данными остальных столбцов?

Мы можем найти число продаж определенного продукта в каждой локации, используя все те же агрегатные функции:

(Задание «со звездочкой»: найдите общую выручку (сумму) за каждый продукт в каждой локации).

Использование функций в GROUP BY

Давайте попытаемся найти общее число продаж в день.

мы можем ожидать, что каждая группа будет уникальным днем, но вместо этого видим следующее:

Похоже, наши данные вообще не сгруппировались: мы получили каждую строку отдельно.

Но на самом деле наши данные сгруппированы! Проблема в том, что sold_at каждой строки является уникальным значением, поэтому каждая строка образует собственную группу!

GROUP BY работает правильно, однако это не тот результат, который нам нужен.

Виной всему уникальная информация временной метки (часы/минуты/секунды).

Все эти временные метки разные, поэтому записи разбрасываются по разным группам.

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

После этого все записи о продажах, сделанных в один день, будут иметь одинаковое значение даты и, следовательно, попадут в одну группу.

Для этого мы сведем значение временной метки sold_at к дате:

В запросе SELECT мы возвращаем то же выражение и даем ему псевдоним для более красивого вывода.

Вот результат числа продаж за день, который мы хотели увидеть:

Фильтрация групп при помощи HAVING

Давайте теперь разберем, как можно фильтровать наши сгруппированные строки. Например, попробуем найти дни, в которые у нас было больше одной продажи.

Имея группы, мы можем попробовать отфильтровать наши группы по числу строк…

К сожалению, это не сработало и мы получили ошибку:

Это предложение HAVING отфильтровывает все строки, если число строк в группе не больше одной. Вот результат:

Чисто для полноты картины вот вам порядок выполнения всех предложений SQL:

Агрегации со скрытым группированием

Последняя тема, которую мы затронем, это агрегации без GROUP BY или, если выражаться более точно, агрегации со скрытым группированием.

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

Мы могли бы найти общую выручку по всем локациям, просто выбрав сумму по всей таблице:

Еще один полезный сценарий — запросить первое или последнее что-нибудь.

Например, дату самой первой продажи.

Чтобы ее найти, мы моем применить функцию MIN() :

(Для поиска даты последней продажи нужно всего лишь заменить MIN() на MAX() ).

Использование MIN / MAX

Хотя эти простые запросы могут быть полезны сами по себе, они часто являются составляющими более длинных запросов.

Например, давайте попробуем найти общую выручку в последний день, когда у нас вообще были продажи.

Мы можем написать запрос так:

Этот запрос сработает, но мы захардкодили дату 2020-09-01. А ведь дата последней продажи будет постоянно меняться. Нам нужно динамическое решение.

Для этого нам нужно скомбинировать этот запрос с функцией MAX() в подзапросе:

Затем мы использовали эту «максимальную» дату в качестве значения, по которому фильтруется таблица, и суммировали выручку по каждой продаже.

Скрытое группирование

Я назвал это скрытым группированием, потому что если мы попытаемся вот так выбрать агрегированное значение с не-агрегированным столбцом —

мы получим уже знакомую ошибку:

GROUP BY — это инструмент

Как и многие другие вещи в сфере разработки, GROUP BY — это инструмент.

Источник