gpt4 book ai didi

SQL Server 平均值/总和/组/计数查询问题

转载 作者:行者123 更新时间:2023-12-05 01:15:37 25 4
gpt4 key购买 nike

我有下表:

╔══════════╦════════╦═══════╦═══════╗║ PlayerID ║ GameID ║ Stat1 ║ Stat2 ║╠══════════╬════════╬═══════╬═══════╣║        1 ║ A      ║     2 ║     1 ║║        1 ║ B      ║     4 ║     2 ║║        1 ║ C      ║     6 ║     5 ║║        2 ║ A      ║     2 ║     4 ║║        2 ║ B      ║     2 ║     6 ║║        2 ║ C      ║     2 ║     8 ║║        2 ║ D      ║     4 ║     2 ║║        2 ║ E      ║     6 ║     1 ║║        3 ║ A      ║     5 ║     9 ║║        3 ║ G      ║     6 ║     4 ║║        3 ║ H      ║     4 ║     2 ║║        3 ║ N      ║     8 ║     6 ║╚══════════╩════════╩═══════╩═══════╝

What I am trying to achieve is the following:

╔══════════╦═══════════╦═══════════════════╦═══════════════════╗║ PlayerID ║ GameCount ║ Stat 1 Avg / Game ║ Stat 2 Avg / Game ║╠══════════╬═══════════╬═══════════════════╬═══════════════════╣║        1 ║         3 ║ 4                 ║ 2.66              ║║        2 ║         5 ║ 3.2               ║ 4.2               ║║        3 ║         4 ║ 5.75              ║ 5.25              ║╚══════════╩═══════════╩═══════════════════╩═══════════════════╝

The game count should be the total number of games played per player and the stats should be the average per game. Basically the calculation for PlayerID 1 is the following:

"Stat1 Avg / Game" = (2 + 4 + 6) / 3"Stat2 Avg / Game" = (1 + 2 + 5) / 3

I have tried numerous variations of the same query with a combination of SUMS and COUNTS but the GameCount never comes out correct. An example of what I have tried is below

SELECT PlayerID, 
COUNT(GameID) AS GameCount,
SUM(Stat1) / COUNT(GameID) "Stat 1 Avg / Game",
SUM(Stat2) / COUNT(GameID) "Stat 2 Avg / Game"
FROM PublishedStats A
GROUP BY PlayerID

SELECT PlayerID,
COUNT(GameID) OVER (PARTITION BY PlayerID) AS GameCount,
SUM(Stat1) / COUNT(GameID) OVER (PARTITION BY PlayerID) "Stat 1 Avg / Game",
SUM(Stat2) / COUNT(GameID) OVER (PARTITION BY PlayerID) "Stat 2 Avg / Game",
FROM Stats
GROUP BY PlayerID, GameID

最佳答案

所以只需添加 DISTINCT 关键字即可。

SELECT PlayerID, 
COUNT(DISTINCT GameID) AS GameCount,
AVG(Stat1) "Stat 1 Avg / Game",
AVG(Stat2) "Stat 2 Avg / Game"
FROM PublishedStats A
GROUP BY PlayerID

关于SQL Server 平均值/总和/组/计数查询问题,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/16575901/

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