Использование 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 

Можете ли вы загрузить его в таблицу с помощью IDENTITY() (с сохранением порядка, как в Excel)?

HoneyBadger 18.12.2020 16:56

@HoneyBadger - мне удалось это успешно реализовать! Спасибо за предложение!

Depth of Field 21.12.2020 17:49

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

HoneyBadger 21.12.2020 18:05

@HoneyBadger Готово!

Depth of Field 21.12.2020 19:42
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
4
148
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 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

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