sql - 如何在 SQL 中添加包含 ZScore 的新列

Question

我正在尝试将名为 [Z Score] 的列更新到 SQL 中的表中。我编写了在 SELECT 查询中创建新列的代码，如下所示:

SELECT cd.*
FROM (
       SELECT [Cardholder Name], [Transaction Date], [Merchant], 
       [Debit Amount], 
       (
        [Debit Amount] - 
        AVG([Debit Amount]) OVER ()) / (STDEV([Debit Amount]) OVER ()
       ) as [ZScore]
       FROM ['PCard Output']
     ) cd;

话虽如此，当谈到使用 UPDATE 查询将数据实际添加到列 [Z Score] 时，我迷失了方向。我正在使用 SQL Server 17。如有任何帮助，我们将不胜感激!

Answer 1

简单地说:

UPDATE T
SET YourColumn = cd.[ZScore]
FROM 
(
    SELECT [Cardholder Name], 
           [Transaction Date], 
           [Merchant], 
           [Debit Amount], 
           ([Debit Amount] - AVG([Debit Amount]) OVER ()) / (STDEV([Debit Amount]) OVER ()) as [ZScore]
    FROM ['PCard Output']
) cd INNER JOIN YourTable T ON cd.ID = T.ID;

请注意，我认为您不需要选择的其他列，因此您的查询可能如下所示:

UPDATE T
SET YourColumn = cd.[ZScore]
FROM 
(
    SELECT ([Debit Amount] - AVG([Debit Amount]) OVER ()) / (STDEV([Debit Amount]) OVER ()) as [ZScore]
    FROM ['PCard Output']
) cd INNER JOIN YourTable T ON cd.ID = T.ID;

或

UPDATE YourTable
SET YourColumn = 
(
    SELECT ([Debit Amount] - AVG([Debit Amount]) OVER ()) / (STDEV([Debit Amount]) OVER ()) as [ZScore]
    FROM ['PCard Output']
);