SQL Server NULL 替换为动态值

Question

我希望将 NULL 值替换为基于表中数据的模式值。

在下面的示例中，我想将设备ID的空InDate替换为该进程ID的InDates的模式值。我已经计算了进程 ID 的模式 InDate，我只是无法弄清楚如何使用该值将设备ID 的 NULL 值替换为进程 ID

下面是一个设置示例：

 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);

这就是我在进程 ID 中找到设备输入模式的方式。

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;

我想使用这些确定的模式来填充给定进程 ID 的空 InDate。

期望的结果示例：

(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()用于确定每个ProcessID, SiteID分区中每个InDate片的人口
• FIRST_VALUE(InDate) is used to select the 人口最多的InDate'

在这里演示