Отсутствие даты в Min() при использовании Over и Partition by в T-SQL
Я использую OVER и Partition by, чтобы получить разум и максимальную дату набора данных.
|ResdetId | bookingdate | Amount | AmountExcl |
-----------------------------------------------
|120106 | 2018-02-04 | 75.00 | 70.7547 |
|120106 | 2018-02-05 | 75.00 | 70.7547 |
|120106 | 2018-02-06 | 90.00 | 84.9057 |
|120106 | 2018-02-08 | 75.00 | 70.7547 |
|120106 | 2018-02-09 | 75.00 | 70.7547 |
Я использую этот запрос
select distinct ResDetId, Amount, AmountExcl,
min(Bookingdate) OVER(Partition by ResDetId, Amount, AmountExcl) as Mindate,
max(Bookingdate) OVER(Partition by ResDetId, Amount, AmountExcl) as MaxDate
from @Cumulatedbookingdetails
И я получаю этот результат
|ResdetId | Amount | AmountExcl | MinDate | MaxDate |
------------------------------------------------------------
|120106 | 75.00 | 70.7547 | 2018-02-04 | 2018-02-09 |
|120106 | 90.00 | 84.9057 | 2018-02-06 | 2018-02-06 |
Как видим дата 2018-02-07 запись отсутствует в наборе данных. Итак, мне нужен такой результат
|ResdetId | Amount | AmountExcl | MinDate | MaxDate |
------------------------------------------------------------
|120106 | 75.00 | 70.7547 | 2018-02-04 | 2018-02-05 |
|120106 | 75.00 | 70.7547 | 2018-02-08 | 2018-02-09 |
|120106 | 90.00 | 84.9057 | 2018-02-06 | 2018-02-06 |
4 ответа
Один из способов решения проблемы "островов и разрывов", такой как эта, заключается в использовании рекурсивного CTE для создания островков. Мы делаем нерекурсивную часть (выше union) найдите строку, которая отмечает начало каждого острова, и рекурсивная часть выращивает каждый остров по одному совпадению за раз.
К сожалению, окончательные результаты CTE содержат все промежуточные ряды, используемые при построении островов, поэтому вам нужен окончательный GROUP чтобы выбрать последний остров:
declare @t table (ResdetId int, bookingdate date, Amount decimal(9,3), AmountExcl decimal (9,3))
insert into @t(ResdetId,bookingdate,Amount,AmountExcl) values
(120106,'20180204',75.00,70.7547),
(120106,'20180205',75.00,70.7547),
(120106,'20180206',90.00,84.9057),
(120106,'20180208',75.00,70.7547),
(120106,'20180209',75.00,70.7547)
;With Islands as (
select ResdetId, Amount, AmountExcl,bookingdate as MinDate,bookingDate as MaxDate
from @t t
where not exists (select * from @t t2
where t2.ResdetId = t.ResdetId
and t2.Amount = t.Amount
and t2.AmountExcl = t.AmountExcl
and t2.bookingdate = DATEADD(day,-1,t.BookingDate))
union all
select i.ResdetId, i.Amount,i.AmountExcl,i.MinDate,t.bookingDate
from Islands i
inner join
@t t
on t.ResdetId = i.ResdetId
and t.Amount = i.Amount
and t.AmountExcl = i.AmountExcl
and t.bookingdate = DATEADD(day,1,i.MaxDate)
)
select
ResdetId, Amount, AmountExcl,MinDate,MAX(MaxDate) as MaxDate
from
Islands
group by ResdetId, Amount, AmountExcl,MinDate
Результаты:
ResdetId Amount AmountExcl MinDate MaxDate
----------- --------- ------------ ---------- ----------
120106 75.000 70.755 2018-02-04 2018-02-05
120106 75.000 70.755 2018-02-08 2018-02-09
120106 90.000 84.906 2018-02-06 2018-02-06
Попробуйте это, он использует технику различия номеров строк:
declare @tbl table(ResdetId int, bookingdate date, Amount float, AmountExcl float);
insert into @tbl values
(120106 , '2018-02-04' , 75.00 , 70.7547 ),
(120106 , '2018-02-05' , 75.00 , 70.7547 ),
(120106 , '2018-02-06' , 90.00 , 84.9057 ),
(120106 , '2018-02-08' , 75.00 , 70.7547 ),
(120106 , '2018-02-09' , 75.00 , 70.7547 );
select MIN(bookingDate), MAX(bookingDate), Amount, AmountExcl
from (
select *,
ROW_NUMBER() over (order by bookingDate) -
ROW_NUMBER() over (partition by amount, AmountExcl order by bookingDate) rn
from @tbl
) a group by Amount, AmountExcl, rn
Это было бы намного проще сделать с GROUP BY, OVER а также DISTINCT Есть много более сложных способов сделать тот же запрос:
WITH VTE AS(
SELECT ResdetId,
CONVERT(date,bookingdate) AS bookingdate,
Amount,
AmountExcl
FROM (VALUES (120106,'20180204',75.00,70.7547),
(120106,'20180205',75.00,70.7547),
(120106,'20180206',90.00,84.9057),
(120106,'20180208',75.00,70.7547),
(120106,'20180209',75.00,70.7547)) V(ResdetId,bookingdate,Amount,AmountExcl))
SELECT ResdetId,Amount,AmountExcl,
MIN(bookingdate) AS MinBookingDate,
MAX(bookingdate) AS MaxBookingDate
FROM VTE
GROUP BY ResdetId,Amount,AmountExcl;
Как отметил мой саам, я неправильно прочитал результаты, это вопрос о пробелах и островах:
WITH VTE AS(
SELECT ResdetId,
CONVERT(date,bookingdate) AS bookingdate,
Amount,
AmountExcl
FROM (VALUES (120106,'20180204',75.00,70.7547),
(120106,'20180205',75.00,70.7547),
(120106,'20180206',90.00,84.9057),
(120106,'20180208',75.00,70.7547),
(120106,'20180209',75.00,70.7547)) V(ResdetId,bookingdate,Amount,AmountExcl)),
Grps AS(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ResdetId ORDER BY V.bookingdate) -
ROW_NUMBER() OVER (PARTITION BY ResdetId, Amount ORDER BY V.bookingdate) AS Grp
FROM VTE V)
SELECT ResdetId,
Amount,
AmountExcl,
MIN(bookingdate) AS MinBookingDate,
MAX(bookingdate) AS MaxBookingDate
FROM Grps
GROUP BY ResdetId,
Amount,
AmountExcl,
Grp
ORDER BY ResdetId,
Amount,
MinBookingDate;
Вы не видели 2018-02-07, потому что дата бронирования не в вашем разделе, поэтому
|ResdetId | Amount | AmountExcl
--------------------------------
|120106 | 75.00 | 70.7547
|120106 | 90.00 | 84.9057
уникальны вашим разделом. Так что это как Ключ. Вам нужен еще один атрибут, чтобы различать те же данные:
|ResdetId | Amount | AmountExcl
--------------------------------
|120106 | 75.00 | 70.7547