Использование LAG/LEAD с отсутствующим PK в чередующихся строках
Я пытаюсь работать со следующей таблицей в SQL Server 2012:
EMPID JOB_TITLE SALARY
------------------------------------
1234 SALES 56000
NULL NULL 54000
1235 MARKETING 72000
NULL NULL 71000
Таблица происходит из экстракта Excel, для которого я импортирую с помощью OPENROWSET.
Мне нужно получить «предыдущее» значение зарплаты, которое, как вы можете предположить, всегда находится в следующей записи в естественном порядке в таблице, и отобразить его как новый столбец.
Ожидаемый результат:
EMPID JOBTITLE SALARY PREV_SALARY
--------------------------------------------------
1234 BUSINESS ANALYST 56000 54000
1235 SALES MANAGER 72000 71000
Очевидно, это немного сложно, потому что в следующей строке нет PK, я не могу точно использовать функцию LEAD, если я не могу определить значение для «разделения» записи. Есть идеи?
Я включил те же значения/код:
INSERT INTO #EMP
VALUES (1234, 'Sales', 56000),
(NULL, NULL, 54000),
(1235, 'Marketing', 72000),
(NULL, NULL, 70500)
SELECT
EMPID,
JOBTITLE,
SAL,
LEAD(SAL,1,0) OVER (ORDER BY NEWID()) AS PREV_SAL
FROM
#EMP
WHERE
EMPID IS NOT NULL
@HoneyBadger - мне удалось это успешно реализовать! Спасибо за предложение!
Вы можете сами ответить на свой вопрос, если считаете, что это может быть полезно другим в подобных обстоятельствах.
@HoneyBadger Готово!
Ответы 2
Если нет столбца, определяющего порядок строк, вопрос не может быть решен - строки таблицы SQL неупорядочены по замыслу.
Если у вас есть столбец заказа, вы можете сделать:
select *
from (
select e.*, lead(salary) over(order by id) as prev_salary
from employee e
) t
where empid is not null
Это предполагает, что ненулевые и нулевые строки всегда правильно чередуются. Если это не так, то я бы порекомендовал некоторую технику пробелов и островов, например:
select max(empid) as empid,
max(case when rn = 1 then job_title end) as job_title,
max(case when rn = 1 then salary end) as salary,
max(case when rn = 2 then salary end) as prev_salary
from (
select e.*, row_number() over(partition by grp order by empid) rn
from (
select e.*, count(empid) over(order by id) as grp
from employee e
) t
) t
group by grp
По предложению @Honeybadger я реализовал атрибут IDENTITY(1,1) при создании временной целевой таблицы. Это зафиксировало естественную последовательность строк, когда я загружал данные. Работает как шарм!
CREATE TABLE #EMP
(
EMPID INT
, JOBTITLE NVARCHAR(19)
, SAL INT
, ROW_SEQ INT IDENTITY(1,1)
)
INSERT INTO #EMP
VALUES (1234, 'Sales', 56000),
(NULL, NULL, 54000),
(1235, 'Marketing', 72000),
(NULL, NULL, 70500)
SELECT * FROM (
SELECT
EMPID,
JOBTITLE,
SAL,
LEAD(SAL,1,0) OVER (ORDER BY ROW_SEQ ) AS PREV_SAL
FROM #EMP ) T WHERE EMPID IS NOT NULL
Выходы:
EMPID JOBTITLE SAL PREV_SAL
1234 Sales 56000 54000
1235 Marketing 72000 70500
Можете ли вы загрузить его в таблицу с помощью IDENTITY() (с сохранением порядка, как в Excel)?