Замена NULL в SQL Server динамическим значением

Question

Я ищу, чтобы заменить значения NULL на значение режима на основе данных в таблице.

В следующем примере я хотел бы заменить NULL InDate для EquipmentID значением режима InDates для этого ProcessID. Я рассчитал режим InDate для ProcessID, я просто не могу понять, как использовать это значение для замены значения NULL для EquipmentID с помощью ProcessID

Вот пример настройки:

 CREATE TABLE dbo.Table_basic (
                InDate INT,
                EquipmentID INT,
                ProcessID nvarchar(50),
                SiteID INT
                )

INSERT INTO Table_basic (InDate, EquipmentID, ProcessID, SiteID)
VALUES (2001, 1,'1PAA',1),
        (2001,2,'1PAA',1),
        (NULL, 3,'1PAA',1),
        (2001,4,'1PAA',1),
        (1999, 5,'1PAA',1),
        (2001,6,'1PAB',1),
        (2001,7,'1PAC',1),
        (2001, 8,'2AA',2),
        (1999,9,'2AB',2),
        (NULL, 10,'2AB',2),
        (1999,11,'2AB',2),
        (1998,12,'2AB',2),
        (2001, 13,'2AB',2),
        (1999,14,'2AB',2),
        (2001, 15,'2AC',2),
        (2001,16,'2AC',2),
        (1986, 17,'3AA',3),
        (1985,18,'3AA',3),
        (1985,19,'3AA',3),
        (NULL, 20,'3AC',3),
        (2005,21,'3AC',3),
        (2005, 22,'3AC',3),
        (2005,23,'3AC',3);

Вот как я нахожу режим InDate для оборудования в ProcessID.

WITH CTE_CountofEquipment AS
 (
  SELECT
    ProcessID
   ,SiteID
   ,cnt   = COUNT(1)
   ,rid   = ROW_NUMBER() OVER (PARTITION BY ProcessID ORDER BY COUNT(1) DESC)
   ,InDate
    FROM dbo.Table_basic 
  GROUP BY  SiteID, ProcessID, InDate
 )
 SELECT
   ProcessID
  ,cnt = cnt
  ,[SiteID]
  ,InDate
 FROM CTE_CountofEquipment
 WHERE rid = 1
 ORDER BY SiteID;

Я хотел бы использовать эти определенные режимы для заполнения NULL InDate для данного ProcessID.

Пример желаемого результата:

(NULL, 3,'1PAA',1),
(2001, 3,'1PAA',1),
(2001, 3,'1PAA',1),
(1999, 3,'1PAA',1),
(2000, 3,'1PAA',1),
(2001, 3,'1PAA',1),

становится

(2001, 3,'1PAA',1), -- InDate updated to modal value
(2001, 3,'1PAA',1),
(2001, 3,'1PAA',1),
(1999, 3,'1PAA',1),
(2000, 3,'1PAA',1),
(2001, 3,'1PAA',1),

Спасибо

Answer 1

Я бы сделал расчет следующим образом:

with modes as (
      select p.*
      from (select tb.processId, tb.indate, count(*) as cnt,
                   row_number() over (partition by tb.processId order by count(*) desc) as seqnum
            from table_basic tb
            group by tb.processId, tb.indate
           ) p
      where seqnum = 1
     )
update tb
    set indate = m.indate
    from table_basic tb join
         modes m
         on tb.processId = m.processId
    where indate is null;

Это отвечает на ваш вопрос. Я понятия не имею, почему ваш расчет режима использует SiteId, Это не часть вопроса. Я не знаю, что это за ссылка NULL значения для EquipmentId, Это тоже не часть вопроса.

Однако вы должны иметь возможность легко изменить это для других группировок для режимов или других столбцов.

Answer 2

Вы можете использовать запрос, подобный следующему, чтобы сделать UPDATE:

;WITH CTE_CountofEquipment AS (
    SELECT InDate, ProcessID, SiteID, 
           COUNT(*) OVER (PARTITION BY ProcessID, SiteID, InDate) AS cnt
    FROM dbo.Table_basic
), ToUpdate AS (
   SELECT InDate, ProcessID, SiteID,
          FIRST_VALUE(InDate) 
          OVER 
             (PARTITION BY ProcessID, SiteID
             ORDER BY cnt DESC ) AS mode
    FROM CTE_CountofEquipment
)
UPDATE ToUpdate
SET InDate = mode
WHERE InDate IS NULL

Запрос использует оконные функции для вычисления mode значение:

• COUNT OVER() используется для определения населения каждого InDate срез внутри каждого ProcessID, SiteID раздел
• FIRST_VALUE(InDate) is used to select theInDate` с наибольшим населением

Демо здесь