Блог

Главная - Вопросы по программированию - SQL-Server: продолжить последнее значение, отличное от NULL, неправильный раздел по

SQL-Server: продолжить последнее значение, отличное от NULL, неправильный раздел по

#sql #sql-server #datetime #sql-server-2012 #gaps-and-islands

#sql #sql-server #дата и время #sql-server-2012 #пробелы и острова

Вопрос:

Я хочу показать последнее EmployeeId значение в столбце Last_EmployeeId для каждого RoomNumber . Поэтому, когда есть NULL значение, я должен вернуть EmployeeId вместо NULL . Приведенный ниже запрос почти делает трюк Partition by value_partition , потому value_partition что показывает другое число для ненулевых записей. Однако в определенный момент value_partition отображается точно такое же число как для нулевых, так и для ненулевых записей (после ~ 3000 записей). Как следствие, он возвращает EmployeeId также для всех несвязанных комнат (как вы можете видеть во вставленном фрагменте данных ниже).

Я использую следующий код:

 ;WITH Rooms_Rank AS ( 
SELECT 
    Rooms.*
    ,ROW_NUMBER() OVER (PARTITION BY Rooms.RoomNumber ORDER BY Rooms.[Date]) -
    ROW_NUMBER() OVER (PARTITION BY Rooms.RoomNumber, Rooms.beginDate ORDER BY Rooms.[Date]) AS Rnk--[Services].beginDate ORDER BY RoomDate.[Date]) AS Rnk
FROM Rooms
)
SELECT
  [Date]
  ,RoomNumber
  ,EmployeeId
  ,value_partition
  ,first_value(EmployeeId) OVER (PARTITION BY value_partition ORDER BY [Date]) AS Last_EmployeeId
FROM (
    SELECT *,
    SUM(CASE WHEN EmployeeId is null THEN 0 ELSE 1 END) OVER (ORDER BY RoomNumber, CAST([Date] AS DATE)) AS value_partition
    FROM Rooms_Rank
  ) AS q
ORDER BY [Date] ASC, RoomNumber

Небольшая выборка набора данных (поскольку эта проблема возникает только после записей 3000 )

Дата Номер комнаты EmployeeID value_partition Last_EmployeeId
2020-10-12 33 607 133 607
2020-10-12 34 NULL 136 NULL
2020-10-12 401 NULL 136 NULL
2020-10-12 71 NULL 223 NULL
2020-10-13 33 607 134 607
2020-10-13 34 NULL 136 NULL
2020-10-13 401 NULL 136 NULL
2020-10-13 71 NULL 223 NULL
2020-10-14 33 607 135 607
2020-10-14 34 NULL 136 NULL
2020-10-14 401 NULL 136 NULL
2020-10-14 71 NULL 223 NULL
2020-10-15 33 607 136 607
2020-10-15 34 NULL 136 607
2020-10-15 401 NULL 136 607
2020-10-15 71 NULL 223 NULL
2020-10-16 33 NULL 136 607
2020-10-16 34 NULL 136 607
2020-10-16 401 NULL 136 607
2020-10-16 71 NULL 223 NULL

Как вы можете видеть, 607 продолжает отображаться не только RoomNumber для 33, но также для 34 и 401. Как я могу найти способ правильно отобразить последний ‘EmployeeID’?

Есть предложения?

Ответ №1:

Я понимаю, что вам нужно последнее значение, отличное null employeeid от значения для каждой комнаты. Это было бы прямолинейно, если бы SQL Server поддерживал функцию option ignore nulls to window lag() — увы, очень немногие базы данных поддерживают, и SQL Server не является одним из них.

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

 select t.*, 
    max(employeeid) over(partition by roomnumber, grp order by date) as lastemployeeid
from (
    select rr.*,
        count(employeeid) over(partition by roomnumber order by date) grp
    from rooms_rank rr
) rr