Блог

Главная - Вопросы по программированию - ОБРАЩЕНИЕ с Row_Num

ОБРАЩЕНИЕ с Row_Num

#sql #sql-server #greatest-n-per-group #common-table-expression #sql-null

#sql #sql-сервер #наибольшее число на группу #общее-табличное-выражение #sql-null

Вопрос:

У меня есть таблица в SQL Server 2016, где для каждого уникального номера партнера есть несколько значений ConnectorId и несколько CommissionDate

Мое требование заключается в том, что когда я запрашиваю таблицу в конечном результате, я должен получать только строки, имеющие rn = 1. это работало с использованием приведенного ниже запроса, но в этом примере P1 в соответствии с текущей логикой он выбирает строку, где rn = 1, но я дополнительно хочу, чтобы если CommissionDate для rn = 1 равно NULL, то в этом случае отобразите значение rn = 2, если не null, то продолжайте с rn = 1

Сценарий таблицы:

 CREATE table #Final_Data
(
  CommissionDate date,
  PartnerNumber varchar(50),
  Connector_Id varchar(50),
 )

GO

insert into #Final_Data (CommissionDate,PartnerNumber,Connector_Id)
VALUES (NULL,'P1','C1'), ('2017-12-27','P1','C2')
,('2015-09-14','P2','C3'),('2011-09-13','P2','C4') 
,(NULL,'P3','C5'),(NULL,'P3','C6') 

GO

Запрос:

 ;WITH CTE
AS
(
SELECT CommissionDate,PartnerNumber,Connector_Id,
ROW_NUMBER() OVER (Partition by PartnerNumber  ORDER BY CommissionDate asc) AS rn
FROM #Final_Data
)

SELECT TOP  9999999 * FROM CTE where rn = 1 
ORDER BY PartnerNumber

Фактический объем производства:

 CommissionDate  PartnerNumber   Connector_Id    rn
NULL                 P1              C1         1
2011-09-13           P2              C4         1
NULL                 P3              C5         1

Ожидаемый результат:

 CommissionDate  PartnerNumber   Connector_Id       rn
    2017-12-27           P1              C2         2
    2011-09-13           P2              C4         1
    NULL                 P3              C5         1

Ответ №1:

Вы можете использовать условную сортировку в order by предложении функции window:

 ROW_NUMBER() OVER (
    PARTITION BY PartnerNumber  
    ORDER BY CASE WHEN CommissionDate IS NOT NULL THEN 0 ELSE 1 END,
    CommissionDate
) AS rn

Ответ №2:

Ответ GMB правильный. Однако часто это более кратко выражается как:

 ROW_NUMBER() OVER (
    PARTITION BY PartnerNumber  
    ORDER BY COALESCE(CommissionDate, '9999-01-01')
) AS rn

Это позволяет ORDER BY иметь только один ключ. И это, по сути, сокращение стандартного синтаксиса SQL:

 ROW_NUMBER() OVER (
    PARTITION BY PartnerNumber  
    ORDER BY CommissionDate NULLS LAST
) AS rn

какой SQL Server этого не делает (пока ???) Поддержка.