Скажем, у меня есть таблица с этими столбцами:
Я хотел бы получить самую раннюю дату покупки автомобиля между определенным интервалом. Если за этот промежуток времени человеком не было куплено ни одного транспортного средства, то эта дата будет нулевой. Если в этом интервале кем-то куплено два автомобиля, следует выбрать самый ранний из них.
Другими словами, следующий набор результатов является желаемым результатом, если интервал, скажем, между 20.01.22 и 30.01.22 (включительно):
Шаблон, который я думал использовать для этого:
SELECT person, date, car_type
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY person ORDER BY date ASC) AS rn,
person,
date,
car_type
FROM car_table
) sub
WHERE sub.rn = 1;
Но есть два столбца даты, и я хотел бы знать, как я могу объединить их в один для этой цели; или если есть просто лучший способ добиться этого.
Примечание: ваша попытка не является допустимым синтаксисом. Вы написали PARTITION
неправильно, в вашей производной таблице нет FROM
, поэтому столбцы person
, date
и car_type
не имеют контекста, а функция ROW_NUMBER
, а не ROW
.
Спасибо за советы и извините за ошибки. К сожалению, я не могу изменить дизайн таблицы, и данные должны обрабатываться как есть. Разве вы не имели в виду создание cte и объединение его там?
Как я упоминал в комментариях, если вы используете последнюю версию SQL Server (2022 на момент написания), вы можете использовать LEAST
, чтобы получить меньшее значение двух столбцов даты и времени. LEAST
также игнорирует NULL
значения. Неясно, должна ли логика для дат основываться на том, должна ли одна из дат быть между датами или самой низкой, поэтому это может не дать требуемых результатов для разных данных, но должно дать вам достаточно, чтобы добраться туда:
SELECT ID
INTO dbo.Person
FROM (VALUES(1),(3),(4))V(ID);
SELECT *
INTO dbo.BoughtVehicle
FROM (VALUES(1,'green','1/10/22',NULL,'11','suv'), --d/M/yy or M/d/yy are TERRIBLE date formats. Use a date and time data type!
(1,'blue',NULL,'1/11/22','16','sedan'),
(3,'red','1/23/22',NULL,'17','suv'),
(3,'yellow',NULL,'1/28/22','26','sedan'),
(4,'purple','1/27/22',NULL,'14','suv'),
(4,'black',NULL,'1/21/22','17','sedan'))V(person,color,date1,date2,aux,car_type);
GO
WITH RNs AS(
SELECT person,
LEAST(date1,date2) AS date,
car_type,
ROW_NUMBER() OVER (PARTITION BY Person ORDER BY LEAST(date1,date2)) AS RN
FROM dbo.BoughtVehicle
WHERE (date1 >= '1/20/22' AND date1 <= '1/30/22') --This uses ambiguous varchar literals, you should be using an unambiguous date format like yyyyMMdd against a date and time value
OR (date2 >= '1/20/22' AND date2 <= '1/30/22'))
SELECT P.ID AS Person,
RN.date,
RN.car_type
FROM dbo.Person P
LEFT JOIN RNs RN ON P.ID = RN.Person
AND RN.RN = 1;
GO
DROP TABLE dbo.BoughtVehicle;
DROP TABLE dbo.Person;
Сначала разверните свои данные, а затем получите «1 лучших в группе». В качестве альтернативы, если вы используете SQL Server 2022, у вас есть доступ к
LEAST
. Или (возможно, лучше всего) нормализуйте свой дизайн и не используйте 2 столбца даты, которые представляют одно и то же.