SQL Вычислить (временной) промежуток между появлениями в журнале

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

Образец данных:

Item   ReturnDate:
Item1, 20120101
Item1, 20120201
Item1, 20120301
Item2, 20120401
Item2, 20120601

Таким образом, в этом случае мы видим, что был месячный перерыв до того, как элемент 1 был возвращен в первый раз, и еще месяц до того, как он был возвращен во второй раз. Пункт 2 вернулся через 2 месяца.

Моя отправная точка:

Select r1.Item, r1.ReturnDate, r2.Item, r2.ReturnDate, DateDiff(m, r1.ReturnDate, r2.ReturnDate)
from Returns r1
inner join Returns r2 on r2.VehicleNo = r1.VehicleNo

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

Одним из решений является пометка каждого возврата счетчиком (сколько раз этот элемент был возвращен):

Item   ReturnDate, ReturnNo:
Item1, 20120101,   1
Item1, 20120201,   2
Item1, 20120301,   3
Item2, 20120401,   1
Item2, 20120601,   2

Это позволило бы мне использовать следующий T-SQL (или аналогичный):

Select r1.Item, r1.ReturnDate, r2.Item, r2.ReturnDate, DateDiff(m, r1.ReturnDate, r2.ReturnDate)
from Returns r1
inner join Returns r2 on r2.VehicleNo = r1.VehicleNo 
and (r1.ReturnNo + 1 = r2.ReturnNo)

Мой первый вопрос заключается в том, является ли это разумным/оптимальным подходом или есть ли лучший подход?

Во-вторых, каков самый простой/самый удобный способ расчета ReturnNo?


sql-server tsql sequences
person CJM    schedule 12.06.2012    source источник
comment
Не могли бы вы рассказать нам немного о структуре вашего стола? И нужен ли вам обратный номер для чего-то еще?   -  person Nicole Castle    schedule 12.06.2012
comment
@NicoleCastle Я пытался сохранить общее объяснение, а не предоставлять живой DDL с более конкретной и менее полезной терминологией. Скажем так, есть таблица, в которой фиксируется поступление товаров извне (обычно от заказчика) и что мы хотим проанализировать интервалы между каждым поступлением одних и тех же товаров. Item и ReturnDate — это все, что имеет значение в данном случае.   -  person CJM    schedule 12.06.2012
comment
@NicoleCastle ... и нет, мне не нужен ReturnNo ни для чего другого - это просто помогло мне сравнить следующий возврат для каждого элемента.   -  person CJM    schedule 12.06.2012
comment
Спасибо Доминику и Ричарду не только за их ответы (никогда раньше не использовали ROWNUMBER/PARTITION), но и за введение в SQLFiddle.com, который выглядит как фантастический ресурс.   -  person CJM    schedule 12.06.2012
comment
SQLFiddle великолепен. Убедитесь, что вы проверили кнопку «Текст в DDL» вверху — я просто скопировал в нее ваши образцы данных, а не сам придумал DDL.   -  person Richard    schedule 12.06.2012
comment
Вау, круто, я сам не знал об этой функции. Спасибо, @Richard!   -  person Dominic P    schedule 12.06.2012

Ответы (3)


arrow_upward
4
arrow_downward

Если вы используете SQL Server 2005+, используйте ROW_NUMBER(), чтобы сделать именно то, что вы хотите:

WITH RankedReturn AS
(
  SELECT Item, ReturnDate, 
    ROW_NUMBER() OVER (PARTITION BY Item ORDER BY ReturnDate DESC) AS ReturnNo
  FROM Returns
)
SELECT * FROM RankedReturn

Очевидно, что теперь, когда у вас есть CTE, вы можете поместить во внешний SELECT все, что вам нужно. Я бы использовал OUTER APPLY для этого:

WITH RankedReturn AS
(
  SELECT Item, ReturnDate, 
    ROW_NUMBER() OVER (PARTITION BY Item ORDER BY ReturnDate DESC) AS ReturnNo
  FROM Returns
)
SELECT rOuter.Item, rOuter.ReturnDate, DATEDIFF(month, prev.PrevDate, ReturnDate) AS Months
FROM RankedReturn rOuter
OUTER APPLY 
  (
    SELECT ReturnDate AS PrevDate 
    FROM RankedReturn rInner 
    WHERE rOuter.Item = rInner.Item AND rOuter.ReturnNo = rInner.ReturnNo - 1
  ) prev

К сожалению, SQL Fiddle здесь.

Отредактировано, так как вычисление разницы в месяцах было обратным; исправлено

person Dominic P    schedule 12.06.2012

arrow_upward
3
arrow_downward

Самый простой способ вычислить ReturnNo — использовать OVER:

SELECT [Item], [ReturnDate], 
    ROW_NUMBER() OVER (PARTITION BY [Item] ORDER BY [ReturnDate]) AS ReturnNumber
FROM Returns

http://sqlfiddle.com/#!3/e18ad/1/0

Вы также можете попытаться использовать оптимальный.aspx" rel="nofollow">методы расчета промежуточной суммы для расчета разницы между двумя строками.

person Richard    schedule 12.06.2012
comment
Я хотел бы дать вам дополнительную ссылку для «расчета промежуточной суммы», которая сама по себе интересна для чтения. - person CJM; 12.06.2012

arrow_upward
2
arrow_downward

Вот как бы я это сделал:

select  itemNo,
        dt,
        DATEDIFF(day, previousDt, dt) as daysSince
from    (select itemNo, 
                dt,
                (select top 1 dt from testTable where itemNo = outerTbl.itemNo and dt < outerTbl.dt order by dt desc) as previousDt
        from    testTable as outerTbl
        ) as x

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

create table testTable(
itemNo nvarchar(20),
dt datetime)
go

insert into testTable values('Item1', '2012-01-01');
insert into testTable values('Item1', '2012-02-01');
insert into testTable values('Item1', '2012-03-01');
insert into testTable values('Item2', '2012-04-01');
insert into testTable values('Item2', '2012-05-01');
go
person John MacIntyre    schedule 12.06.2012