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

Скажем, у меня есть таблица с этими столбцами:

человек цвет дата1 дата2 вспомогательный тип автомобиля 1 зеленый 10.01.22 нулевой 11 внедорожник 1 синий нулевой 11.01.22 16 седан 3 красный 23.01.22 нулевой 17 внедорожник 3 желтый нулевой 28.01.22 26 седан 4 фиолетовый 27.01.22 нулевой 14 внедорожник 4 черный нулевой 21.01.22 17 седан

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

Другими словами, следующий набор результатов является желаемым результатом, если интервал, скажем, между 20.01.22 и 30.01.22 (включительно):

человек дата тип автомобиля 1 нулевой нулевой 3 23.01.22 внедорожник 4 21.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;

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

Сначала разверните свои данные, а затем получите «1 лучших в группе». В качестве альтернативы, если вы используете SQL Server 2022, у вас есть доступ к LEAST. Или (возможно, лучше всего) нормализуйте свой дизайн и не используйте 2 столбца даты, которые представляют одно и то же.

Larnu 20.01.2023 12:15

Примечание: ваша попытка не является допустимым синтаксисом. Вы написали PARTITION неправильно, в вашей производной таблице нет FROM, поэтому столбцы person, date и car_type не имеют контекста, а функция ROW_NUMBER, а не ROW.

Larnu 20.01.2023 12:19

Спасибо за советы и извините за ошибки. К сожалению, я не могу изменить дизайн таблицы, и данные должны обрабатываться как есть. Разве вы не имели в виду создание cte и объединение его там?

Jaigus 20.01.2023 12:27
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
1
3
52
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Как я упоминал в комментариях, если вы используете последнюю версию 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;

Другие вопросы по теме