У меня есть таблица ниже -
Здесь я создал столбец «Порядок», используя функцию ранжирования, разделенную case_identifier, упорядоченную по audit_date.
Теперь я хочу создать новый столбец, как показано ниже:
Логика для нового столбца будет такой:
select *,
case when [order] = '1' then [days_diff]
else (val of [days_diff] in rank 2) - (val of [days_diff] in rank 1) ...
end as '[New_Col]'
from TABLE
Не могли бы вы помочь мне с синтаксисом? Спасибо.
МЕТОД ЗАДЕРЖКИ
SELECT
CASE_IDENTIFIER
,AUDIT_DATE
,[order]
,days_diff
,days_diff - ISNULL(LAG(days_diff,1) OVER (PARTITION BY CASE_IDENTIFIER ORDER BY [order]),0) AS New_Column
FROM @Table
САМОСТОЯТЕЛЬНОЕ ПРИСОЕДИНЕНИЕ
SELECT
t1.CASE_IDENTIFIER
,AUDIT_DATE
,t1.[order]
,t1.days_diff
,t1.days_diff - ISNULL(t2.days_diff,0) AS New_Column
FROM
@Table t1
LEFT JOIN @Table t2
ON t1.CASE_IDENTIFIER = t2.CASE_IDENTIFIER
AND t1.[order] - 1 = t2.[order]
Я чувствую, что многие другие ответы находятся на правильном пути, но есть некоторые нюансы или более простые способы написания некоторых из них. Или также некоторые ответы указывают направление записи, но что-то не так с их соединением или синтаксисом. В любом случае, вам не нужен CASE STATEMENT независимо от того, используете ли вы LAG метода SELF JOIN. Далее COALESCE() отлично, но вы сравниваете только 2 значения, поэтому ISNULL() тоже отлично работает для sql-сервера, но подойдет и то, и другое.
Взгляните на функцию LAG. Он предоставит вам то, что вы хотите.
что-то типа:
declare @temptable TABLE (case_id varchar(2), row_order int, days_diff float)
INSERT INTO @temptable values ('A',1,5)
INSERT INTO @temptable values ('A',2,3)
INSERT INTO @temptable values ('A',3,2)
INSERT INTO @temptable values ('B',1,5)
INSERT INTO @temptable values ('B',2,1)
--select * from @temptable
SELECT case_id,row_order, LAG(days_diff,1) OVER (PARTITION BY case_id ORDER BY row_order) AS prev_row,days_diff,
CASE
WHEN row_order = 1 THEN days_diff
ELSE LAG(days_diff,1) OVER (PARTITION BY case_id ORDER BY row_order) - days_diff
END AS newcolumn
FROM @temptable
order by case_id,row_order asc
SELECT case_id,row_order,LAG(days_diff,1) OVER (PARTITION BY case_id ORDER BY row_order) AS prev_row, days_diff,
COALESCE(LAG(days_diff,1) OVER (PARTITION BY case_id ORDER BY row_order) - days_diff , days_diff)
FROM @temptable
order by case_id,row_order asc
Другие ответы будут использовать объединение вместо оператора CASE. Это, вероятно, быстрее, но я чувствую, что это яснее.
Если вы запустите оба и посмотрите на планы выполнения, они будут одинаковыми.
Я считаю, что следующий запрос даст вам то, что вы хотите.
SELECT a.*,
'NEW DAYS DIFF' =
CASE
WHEN a.[order] = 1 THEN a.days_diff
ELSE a.days_diff - b.days_diff
END
FROM dbo.tblCaseDaysDiff a
INNER JOIN dbo.tblCaseDaysDiff b
ON
(b.CASE_ID = a.CASE_ID AND b.[order] + 1 = a.[order] ) -- Get the current row and compare with the next highest order
OR (b.CASE_ID = a.CASE_ID AND b.[order] = 1 AND a.[order] = 1) --WHEN ORDER = 1 Get days_diff value
ORDER BY a.CASE_ID, a.[order]
Как оказалось, вы уже по уши в оконных функциях, и, как уже отмечали другие, LAG сделает свое дело. Однако в общем случае вы всегда можете получить разницу между двумя строками, составив одну строку: соединив таблицу с самой собой.
with T (CASE_IDENTIFIER, AUDIT_DATE, order, days_diff)
as (
... your query ...
)
select a.*,
a.days_diff - coalesce(b.days_diff, 0) as delta_days_diff
from T as a left join T as b
on a.CASE_IDENTIFIER = b.CASE_IDENTIFIER
and b.days_diff = a.days_diff - 1
