gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-12-02 03:27:36 25 4
gpt4 key购买 nike

我正在尝试将名为 [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。如有任何帮助,我们将不胜感激!

最佳答案

简单地说:

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

关于sql - 如何在 SQL 中添加包含 ZScore 的新列,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/52766346/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com