时间戳

首页 > 解决方案 > 最大值的平均值

sql - 最大值的平均值

问题描述

我是 sql 新手，并试图找到类似的主题，但没有成功。如果有人可以提供帮助，我将非常感激:-)

这是大代码的一小部分，它基于 Group by 1 列“CustomerID”（我不需要更多！）。我需要每年计算 [Field] 的最大值，然后得出这些 MAX 值的平均值。

SELECT
t0.[CustomerID]
,MAX(t0.[CustomerName]) as 'CustomerName'
,SUM(CASE WHEN t0.[Year] = 2021 then t0.[SalesValue_USD] END) as 'Sales_USD_2021'
,SUM(t0.[SalesValue_USD]) as 'Sales_USD_total'
,AVG(MAX(t0.[Field]) OVER (PARTITION BY [Year])) as 'Field_avr'

From market t0
Where [Year] in (2019,2020,2021)
Group by t0.[CustomerID]

标签： sql

解决方案

您可以分两步完成

SELECT [CustomerID],
    MAX('CustomerName') as 'CustomerName',
    SUM('Sales_USD_2021') as 'Sales_USD_2021',
    SUM('Sales_USD_total') as 'Sales_USD_total',
    AVG(Field_max) as 'Field_avr'
FROM (
    SELECT
      t0.[CustomerID], t0.[Year]
      ,MAX(t0.[CustomerName]) as 'CustomerName'
      ,SUM(CASE WHEN t0.[Year] = 2021 then t0.[SalesValue_USD] END) as 'Sales_USD_2021'
      ,SUM(t0.[SalesValue_USD]) as 'Sales_USD_total'
      ,MAX(t0.[Field])  as 'Field_max'
    From market t0
    Where [Year] in (2019,2020,2021)
    Group by t0.[CustomerID], t0.[Year] ) t
Group by [CustomerID]

推荐阅读