Sql как выбрать первую запись за день

На рисунке ниже показан план выполнения этого запроса:

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

Однако при высокой плотности столбца секционирования (небольшое число уникальных клиентов, у каждого их которых много заказов) план, предусматривающий поиск по индексу для каждого клиента, становится предпочтительнее полного просмотра конечной страницы индекса. Чтобы получить такой план, надо запрашивать таблицу, которая содержит уникальные значения, по которым выполняется секционирование, (в нашем случае Sales.Customers) и применить оператор APPLY, чтобы инициировать запрос с параметром OFFSET/FETCH или TOP для каждого клиента:

SELECT C.custid, A.* FROM Sales.Customers AS C CROSS APPLY (SELECT orderdate, orderid, empid FROM Sales.Orders AS O WHERE O.custid = C.custid ORDER BY orderdate DESC, orderid DESC OFFSET 0 ROWS FETCH FIRST 3 ROWS ONLY) AS A;

План этого запроса показан на следующем рисунке:

Обратите внимание, что в плане индекс таблицы Customers просматривается для получения идентификаторов всех клиентов. Затем для каждого клиента в плане предусмотрена операция поиска в POC-индексе (переход на начало секции текущего клиента в концевой странице индекса), а затем на концевой странице индекса считываются три строки с самыми последними заказами.

Как вы помните, параметр OFFSET/FETCH появился в SQL Server 2012. В более ранних версиях SQL Server вместо него можно задействовать параметр TOP:

SELECT C.custid, A.* FROM Sales.Customers AS C CROSS APPLY (SELECT TOP (3) orderdate, orderid, empid FROM Sales.Orders AS O WHERE O.custid = C.custid ORDER BY orderdate DESC, orderid DESC) AS A;

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

А сейчас удалим POC-индекс:

DROP INDEX idx_cid_odD_oidD_i_empid ON Sales.Orders;

В третьем способе реализована методика, которую можно назвать параллельной сортировкой. Я представил эту методику ранее при обсуждении функций смещения. Идея заключается в том, чтобы создать в каждой секции по строке, в которую конкатенируются сначала упорядочивающие элементы, а затем все элементы, которые необходимо возвратить. Важно использовать именно конкатенацию, в результате которой получается строка, которая сортируется так же, как должна выполняться сортировка по элементам упорядочения. В нашем случае, упорядочение выполняется по orderdate DESC и orderid DESC.

Первый элемент является датой. Чтобы получить строковое представление даты, которая сортируется так же, как исходная дата, нужно перевести дату в форму YYYYMMDD, где YYYY - год, MM — месяц и DD - день. Для этого воспользуемся функцией CONVERT со стилем 112. Что касается элемента ordered, то это положительное целое. Чтобы символьное строковое представление числа сортировалось так же, как исходное целое, нужно отформатировать его как строку фиксированной ширины с ведущими пробелами или нулями. Отформатировать целую величину как строку фиксированной длины можно с помощью функции STR.

Решение предусматривает группировку строк по столбцу секционирования и вычисления верхней (максимальной) конкатенированной строки в группе. Верхняя строка содержит конкатенированные элементы строки, которую нужно возвратить. Затем на основе последнего запроса нужно создать CTE-выражение. Затем во внешнем запросе используем функции SUBSTRING для извлечения отдельных ранее конкатенированных элементов и преобразования их в исходные типы. Вот как выглядит готовое решение:

WITH C AS ( SELECT custid, MAX(CONVERT(CHAR(8), orderdate, 112) + STR(orderid, 10) + STR(empid, 10) COLLATE Latin1_General_BIN2) AS mx FROM Sales.Orders GROUP BY custid ) SELECT custid, CAST(SUBSTRING(mx, 1, 8) AS DATETIME) AS orderdate, CAST(SUBSTRING(mx, 9, 10) AS INT) AS custid, CAST(SUBSTRING(mx, 19, 10) AS INT) AS empid FROM C;

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

DATEPART (Transact-SQL)

Эта функция возвращает целое число, представляющее указанную часть datepart заданного типа date.

Обзор всех типов данных и функций даты и времени в языке Transact-SQL см. в статье Типы данных и функции даты и времени (Transact-SQL).

Синтаксис

DATEPART ( datepart , date ) 

Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.

Аргументы

datepart
Определенная часть аргумента date, для которого функция DATEPART вернет значение типа integer. В приведенной ниже таблице перечислены все допустимые аргументы datepart.

DATEPART не принимает эквивалентные переменные, определяемые пользователем, для аргументов datepart.

date
Выражение, которое разрешается в один из следующих типов данных:

Для date DATEPART будет принимать столбец выражения, выражение, строковый литерал или определяемую пользователем переменную. Во избежание неоднозначности используйте четырехзначную запись года. Сведения о двузначном обозначении года см. в статье Настройка параметра конфигурации сервера two digit year cutoff.

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

INT

Возвращаемое значение

Каждое выражение datepart и его краткие формы возвращают одно и то же значение.

Возвращаемое значение зависит от языка среды, задаваемого инструкцией SET LANGUAGE, и от параметра конфигурации сервера "язык по умолчанию" для имени входа. Если значение date является строковым литералом некоторого формата, то возвращаемое значение зависит от функции SET DATEFORMAT. SET DATEFORMAT не изменяет возвращаемое значение, если дата является выражением столбца с типом данных "дата" или "время".

Ниже представлена таблица соответствия аргументов функции datepart и значений, возвращенных выражением SELECT DATEPART(datepart,'2007-10-30 12:15:32.1234567 +05:10') . Аргумент date имеет тип данных datetimeoffset(7). Последние две позиции значения, возвращаемого функцией nanoseconddatepart, всегда 00 , а масштаб этого значения составляет 9.

.123456700

Аргументы функции datepart, содержащие информацию о номере недели и дня

Для week (wk, ww) или weekday (dw) datepart возвращаемое значение DATEPART зависит от значения, заданного функцией SET DATEFIRST.

1 января любого года определяет начальное число для раздела даты weekdatepart. Например:

DATEPART (wk, 'Jan 1, xxxx') = 1,

где xxxx — любой год.

В этой таблице представлены возвращаемые значения параметров week и weekdaydatepart даты 21.04.2007 с каждым аргументом функции SET DATEFIRST. 1 января 2007 г. приходится на понедельник. 21 апреля 2007 г. приходится на субботу. Для английского языка США,

SET DATEFIRST 7 -- ( Sunday )

SET DATEFIRST 7 (воскресенье). После задания DATEFIRST используйте предлагаемую инструкцию SQL для табличных значений datepart:

SELECT DATEPART(week, '2007-04-21 '), DATEPART(weekday, '2007-04-21 ')

Аргументы функции datepart, отображающие год, месяц и день даты

Значения, возвращаемые в результате выполнения инструкций DATEPART (year, date), DATEPART (month, date) и DATEPART (day, date), совпадают с результатами выполнения функций YEAR, MONTH и DAY, соответственно.

iso_week datepart

Стандарт ISO 8601 включает в себя систему отсчета дней и недель ISO. Каждая неделя приписывается тому году, в котором находится ее четверг. Например, первая неделя 2004 г. (2004W01) считается с понедельника 29 декабря 2003 г. по воскресенье 4 января 2004 г. Европейские страны или регионы обычно используют этот стиль нумерирования. Неевропейские страны и регионы обычно не используют его.

Примечание. Наибольшее число недель в году может составлять 52 или 53.

Система отсчета недель в разных странах и регионах может не совпадать со стандартом ISO. В таблице ниже представлено шесть возможных вариантов.

tzoffset

DATEPART возвращает значение tzoffset (tz) как количество минут (со знаком). В результате выполнения этой инструкции возвращается смещение часового пояса 310 минут:

SELECT DATEPART (tzoffset, '2007-05-10 00:00:01.1234567 +05:10'); 

Функция DATEPART отображает значение tzoffset описанным ниже образом.

• Для datetimeoffset и datetime2 значение tzoffset возвращает временное смещение в минутах, причем для datetime2 смещение всегда равно 0 минут.
• Для типов данных, которые могут быть неявно преобразованы в datetimeoffset или datetime2, функция DATEPART возвращает временное смещение в минутах. Для остальных типов даты и времени этого не происходит.
• Для параметров любых других типов возвращается ошибка.

Аргумент даты типа smalldatetime

Для значения date типа smalldatetime функция DATEPART возвращает количество секунд 00.

Возвращается значение по умолчанию для аргумента функции datepart, который отличен от даты

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

Например, значение "год-месяц-день" по умолчанию для любого типа данных date равно 1900-01-01. Приведенная ниже инструкция содержит аргументы компонентов даты для datepart, аргумент времени для date и возвращает 1900, 1, 1, 1, 2 .

SELECT DATEPART(year, '12:10:30.123') ,DATEPART(month, '12:10:30.123') ,DATEPART(day, '12:10:30.123') ,DATEPART(dayofyear, '12:10:30.123') ,DATEPART(weekday, '12:10:30.123'); 

Если аргумент date указан как переменная или столбец таблицы и тип данных этой переменной или столбца не содержит указанной части datepart, функция DATEPART возвращает ошибку 9810. В этом примере переменная @t имеет тип данных time. Этот пример завершается ошибкой, потому что год даты не является допустимым для типа данных time:

DECLARE @t time = '12:10:30.123'; SELECT DATEPART(year, @t); 

Доли секунды

Эти инструкции демонстрируют, что функция DATEPART возвращает доли секунды:

SELECT DATEPART(millisecond, '00:00:01.1234567'); -- Returns 123 SELECT DATEPART(microsecond, '00:00:01.1234567'); -- Returns 123456 SELECT DATEPART(nanosecond, '00:00:01.1234567'); -- Returns 123456700 

Замечания

Функция DATEPART может использоваться в предложениях выбора из списка, WHERE, HAVING, GROUP BY и ORDER BY.

DATEPART неявно приводит строковые литералы в качестве типа datetime2 в SQL Server 2008 (10.0.x) и более поздних версий. Это означает, что DATENAME не поддерживает формат ГЧМ (год, число, месяц) при передаче даты в виде строки. Для использования формата ГЧМ (год, число, месяц) необходимо явно привести строку к типу datetime или smalldatetime.

Примеры

В этом примере возвращается базовый год. Его значение используется при расчетах, связанных с датами. В примере дата указана как число. Обратите внимание, что SQL Server интерпретирует 0 с 1 января 1900 г.

SELECT DATEPART(year, 0), DATEPART(month, 0), DATEPART(day, 0); -- Returns: 1900 1 1 

В приведенном ниже примере возвращается часть даты 12/20/1974 , представляющая день.

-- Uses AdventureWorks SELECT TOP(1) DATEPART (day,'12/20/1974') FROM dbo.DimCustomer; -- Returns: 20 

В приведенном ниже примере возвращается часть даты 12/20/1974 , представляющая год.

-- Uses AdventureWorks SELECT TOP(1) DATEPART (year,'12/20/1974') FROM dbo.DimCustomer; -- Returns: 1974 

Выбрать несколько записей из каждой группы.

Судя по сообщениям на форуме SQLinfo, выбор более одной строки из группы - часто встречающаяся задача (например, несколько популярных/новых товаров из каждой категории, последние новости для каждой рубрики, и т.д.), которая вызывает сложности при попытке решить её средствами SQL. В статье объясняется несколько способов как одним запросом получить N первых, последних или случайных строк из группы и дана оценка их эффективности с точки зрения производительности.

Разберем решения на примере таблицы сообщений, имеющей поля (post_id, user_id, date_added, post_text), в которой хранится id сообщения, id пользователя, дата добавления и текст поста. Предполагается, что комбинация (user_id, date_added) уникальна, т.е. пользователь не может разместить 2 сообщения в один момент времени.

Код создания и заполнения таблицы

CREATE TABLE `posts` (
`post_id` int ( 11 ) NOT NULL AUTO_INCREMENT ,
`user_id` int ( 11 ) NOT NULL ,
`date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`post_text` text NOT NULL ,
PRIMARY KEY ( `post_id` ) ,
UNIQUE KEY `user_id` ( `user_id`,`date_added` )
) ENGINE = InnoDB DEFAULT CHARSET =utf8;

INSERT INTO `posts` VALUES ( 1 , 1 , '2018-04-17 05:37:15' , 'Есть таблица товаров. Каждый товар относится к какой-то категории. Как вывести по 3 товара из каждой категории?' ) ;
INSERT INTO `posts` VALUES ( 2 , 1 , '2018-04-17 05:43:26' , 'В идеале хочется получить последние N товаров из каждой категории одним запросом. Может есть какой-нибудь элегантный способ?' ) ;
INSERT INTO `posts` VALUES ( 3 , 2 , '2018-04-17 11:13:22' , 'Выборка по N последних товаров каждой категории. Просто не хочется делать запрос на каждую категорию.' ) ;
INSERT INTO `posts` VALUES ( 4 , 1 , '2018-04-17 15:38:04' , 'Как вывести по N строк из каждой группы? Например, последние новости для каждой рубрики или несколько популярных товаров из каждой категории' ) ;
INSERT INTO `posts` VALUES ( 5 , 1 , '2018-04-17 15:52:18' , 'Помогите написать запрос. Выбор по несколько строк из каждой группы в запросе' ) ;
INSERT INTO `posts` VALUES ( 6 , 4 , '2018-04-18 12:17:05' , 'MySQL не поддерживает LIMIT внутри IN подзапросов. Как написать запрос с лимитом строк внутри каждой группы?' ) ;
INSERT INTO `posts` VALUES ( 7 , 4 , '2018-04-18 14:55:36' , 'К сожалению, в MySQL нет windows function. Используйте user variables для их эмуляции.' ) ;
INSERT INTO `posts` VALUES ( 8 , 1 , '2018-04-23 02:17:14' , 'Помогите ускорить выборку случайных строк из группы.' ) ;
INSERT INTO `posts` VALUES ( 9 , 4 , '2018-04-25 17:22:02' , 'TOP в подзапросе выдает ошибку.' ) ;
INSERT INTO `posts` VALUES ( 10 , 4 , '2018-04-26 22:28:45' , 'Нужно выбрать из каждой группы по 2 последних элемента и 2 случайных.' ) ;

Пусть нам требуется выбрать 3 последних сообщения каждого пользователя. Некоторые из предложенных ниже вариантов после незначительных изменений могут быть использованы для выборки 3 случайных сообщений каждого пользователя (о чем будут даны соответствующие пояснения).

1. зависимый подзапрос

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

select t1.* from posts t1
where ( select count ( * ) from posts t2 where t1.user_id=t2.user_id and t2.date_added > t1.date_added ) < 3 ;

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

2. join + group by

Та же идея, что и в предыдущем случае, только реализована через самообъединение таблицы и группировку. Каждой строке сопоставляется набор строк с тем же user_id и большей или равной date_added, после группировки мы получаем для каждой строки (количество сообщений того же пользователя с большей датой добавления) + 1. Иными словами, если мы пронумеруем сообщения пользователя по убыванию date_added, то полученное число будет порядковым номером строки в этой нумерации.

select t1.* from
posts t1 join posts t2 on t1.user_id=t2.user_id and t2.date_added >= t1.date_added
group by t1.post_id
having count ( * )

Этот способ часто рекомендуют в интернете в качестве решения задачи (встречаются вариации с left join). Однако его производительность не самая оптимальная в сравнении с другими методами, рассмотренными в этой статье. Вероятно, причина популярности этого решения в том, что join многим интуитивно представляется более простым решением.

Обратите внимание: в режиме ONLY_FULL_GROUP_BY придется усложнять запрос: сначала выбрать нужные post_id, затем по ним дополнительным join извлечь остальные поля (подробнее см статью Группировка в MySQL). Простое перечисление всех полей в части group by в разы увеличивает время выполнения запроса.

• в старых версиях они действительно не применимы 🙂
• в новых их производительность будет существенно хуже по сравнению с иными доступными вариантами решений (см способы 4 и 6)

3. group_concat()

Для каждого пользователя с помощью group_concat() составляется список идентификаторов его сообщений, отсортированный по убыванию даты. Используя substring_index(), вырезаем первые 3 значения post_id, и по ним извлекается вся строка.

select t1.* from posts t1 join
( select user_id, substring_index ( group_concat ( post_id order by date_added desc ) , ',' , 3 ) x from posts t2 group by user_id ) t
on t.user_id = t1.user_id and find_in_set ( t1.post_id,x ) ;

К сожалению, MySQL не умеет решать уравнения, поэтому для поиска по условию с find_in_set будет просканирована вся таблица сообщений. Есть обходной путь: используя строковые функции и union all, вырезать id сообщений из списка и объединить их в один столбец. Тогда оптимизатор сможет использовать их для поиска нужных строк в таблице сообщений, а не наоборот.

with cte as (
select substring_index ( group_concat ( post_id order by date_added desc ) , ',' , 3 ) x from posts group by user_id
)
select posts.* from (
select substring_index ( x, ',' , 1 ) post_id from cte
union all
select substring_index ( substring_index ( x, ',' , 2 ) , ',' , -1 ) from cte
union all
select substring_index ( x, ',' , -1 ) from cte ) t
join posts on t.post_id = posts.post_id;

• сколько строк из группы нужно выбрать
• есть ли возможность использовать with (доступны с MariaDB 10.2 / MySQL 8). Если в явном виде дублировать from-подзапрос, то каждый из них будет материализован в отдельную временную таблицу.

Этот способ можно применять для выборки 3 случайных сообщений каждого пользователя. Для этого достаточно указать иной вид сортировки внутри group_concat: order by rand ( ) вместо order by date_added desc .

4. оконные функции

Начиная с MariaDB 10.2 / MySQL 8 добавлена поддержка оконных функций. С помощью row_number() можно для каждого пользователя сделать отдельную нумерацию сообщений в порядке убывания даты. После чего выбрать те записи, у которых № меньше или равен 3.

select post_id, user_id, date_added, post_text from
( select posts.*,
row_number ( ) over ( partition by user_id order by date_added desc ) i
from posts ) t where i

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

Для случайных сообщений пользователя достаточно заменить сортировку по убыванию даты order by date_added desc на случайную - order by rand ( ) .

5. пользовательские переменные

Та же идея, что и в предыдущем варианте, только реализована с помощью пользовательских переменных (user variables). Актуально для версий, в которых нет оконных функций.

select post_id, user_id, date_added, post_text from
( select posts.*, if ( @gr=user_id, @i:=@i +1 , @i:= 1 + least ( @gr:=user_id, 0 ) ) x
from posts, ( select @i:= 0 , @gr:= 0 ) t order by user_id, date_added desc ) t1 where x

Как и в примере с row_number(), мы нумеруем сообщения каждого пользователя в порядке убывания даты добавления (только делаем это с помощью пользовательских переменных), затем оставляем только те строки, у которых № меньше или равен 3.

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

select post_id, user_id, date_added, post_text from
( select t2.*, if ( @gr=user_id, @i:=@i +1 , @i:= 1 + least ( @gr:=user_id, 0 ) ) x
from ( select posts.*, rand ( ) q, @z:= 1 from posts ) t2, ( select @i:= 0 , @gr:= 0 ) t order by user_id, q ) t1 where x

Обратите внимание на добавление ещё одной переменной @z:=1, которая более нигде не применяется. С некоторых пор оптимизатор научился упрощать тривиальные с его точки зрения from-подзапросы, перенося условия из них во внешний запрос. Однако, если в подзапросе используются переменные, то пока оптимизатор материализует такие подзапросы.

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

6. подзапросы lateral

В MySQL 8.0.14 добавлена поддержка зависимых подзапросов в части FROM, с помощью которых наша задача решается оптимальным образом. Сначала формируется список идентификаторов пользователей (производная таблица t1) и для каждого из выбираются нужные строки (коррелированный from-подзапрос t2).

select t2.* from ( select user_id from posts group by user_id ) as t1,
lateral ( select * from posts where t1.user_id=posts.user_id order by date_added desc limit 3 ) as t2;

К удивлению, при выборе строк в подзапросе t2 сервер читает все строки группы и делает файловую сортировку вместо нахождения 3 нужных строк по уникальному индексу (user_id, date_added). Возможно в будущих версиях это поведение будет исправлено.

На сегодняшний день можно применить следующий трюк, благодаря которому MySQL будет использовать индекс - расширить выражение сортировки следующим образом:

.. order by user_id desc , date_added desc limit 3
Спасибо за совет Гильяму Бишоту.

Для выборки случайных строк из группы достаточно в lateral подзапросе заменить сортировку на случайную - order by rand().

Заключение

Сводная таблица, показывающая среднее время выполнения изложенных выше способов для нахождения трёх последних и трёх случайных сообщений каждого пользователя на тестовых данных в 16000 строк, равномерно распределенных среди count(distinct user_id) = 20.

Если ваша СУБД поддерживает подзапросы lateral, то используйте их. Вообще, каждый раз, когда есть необходимость "для каждого значения выбрать . " - возможно вы сможете эффективно решить задачу, используя LATERAL производные таблицы. Подробнее об этой функциональности можно прочитать в статье В MySQL 8.0.14 добавлена поддержка производных таблиц типа LATERAL.

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

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

P.S.При выборе подходящего варианта проводите тестирование в своем окружении.
Если после прочтения статьи ваш вопрос остался нерешенным, задавайте его на форуме SQLinfo.

Дата публикации: 15.09.2019

© Все права на данную статью принадлежат порталу SQLInfo.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.

Как вывести первые 10 строк sql

Самым лучшим способом это сделать будет использование оператора LIMIT .

SELECT * FROM users LIMIT 10; 

Такой запрос вернёт первые 10 записей из таблицы users.