编程论坛 → SQL Server论坛

给位大佬帮帮忙呗，请帮我解答如何算出平均值

Melody6188 发布于 2020-03-25 10:27， 4011 次点击

下面是一组数据
SELECT '2019-01' AS Month, 'YT' AS BIZ, 'AP' AS Region, 100.90 AS Revenue
INTO T
UNION ALL
SELECT '2019-01' AS Month, 'ZY' AS BIZ, 'AP' AS Region, 200.90 AS Revenue
UNION ALL
SELECT '2019-01' AS Month, 'AIO' AS BIZ, 'AP' AS Region, 300 AS Revenue
UNION ALL
SELECT '2019-01' AS Month, 'XY' AS BIZ, 'AP' AS Region, 400 AS Revenue
UNION ALL
SELECT '2019-01' AS Month, 'AA' AS BIZ, 'AP' AS Region, 250 AS Revenue
UNION ALL
SELECT '2019-01' AS Month, 'BB' AS BIZ, 'AP' AS Region, 900 AS Revenue
UNION ALL
SELECT '2019-01' AS Month, 'XY' AS BIZ, 'EMEA' AS Region, 400 AS Revenue
UNION ALL
SELECT '2019-01' AS Month, 'AA' AS BIZ, 'EMEA' AS Region, 250 AS Revenue
UNION ALL
SELECT '2019-01' AS Month, 'BB' AS BIZ, 'EAEA' AS Region, 900 AS Revenue

UNION ALL
SELECT '2019-02' AS Month, 'AIO' AS BIZ, 'AP' AS Region, 300 AS Revenue
UNION ALL
SELECT '2019-02' AS Month, 'XY' AS BIZ, 'AP' AS Region, 400 AS Revenue
UNION ALL
SELECT '2019-02' AS Month, 'AA' AS BIZ, 'AP' AS Region, 250 AS Revenue
UNION ALL
SELECT '2019-02' AS Month, 'BB' AS BIZ, 'AP' AS Region, 900 AS Revenue

UNION ALL
SELECT '2019-02' AS Month, 'AIO' AS BIZ, 'EMEA' AS Region, 200 AS Revenue
UNION ALL
SELECT '2019-02' AS Month, 'XY' AS BIZ, 'EMEA' AS Region, 100 AS Revenue
UNION ALL
SELECT '2019-02' AS Month, 'AA' AS BIZ, 'EMEA' AS Region, 700 AS Revenue
UNION ALL
SELECT '2019-02' AS Month, 'BB' AS BIZ, 'EMEA' AS Region, 300 AS Revenue

UNION ALL
SELECT '2019-03' AS Month, 'AIO' AS BIZ, 'AP' AS Region, 300 AS Revenue
UNION ALL
SELECT '2019-03' AS Month, 'XY' AS BIZ, 'AP' AS Region, 400 AS Revenue
UNION ALL
SELECT '2019-03' AS Month, 'AA' AS BIZ, 'AP' AS Region, 250 AS Revenue
UNION ALL
SELECT '2019-03' AS Month, 'BB' AS BIZ, 'AP' AS Region, 900 AS Revenue

建表以后，
求月，地区，总值，平均值（相同区域跨月的平均）
下面的表格是期待的结果,数据略有出入，但大体的意思如图所示
谢谢

只有本站会员才能查看附件，请登录

13 回复

#2

xianfajushi2020-03-25 11:11

SQL语句里面有一条求均值的直接使用就是.

#3

Melody61882020-03-26 06:27

回复 2楼 xianfajushi

不是那么简单的，我已经试过了，那样是求不出来我要的结果的。
我要的结果是在group by 以后每个地区的平均值。

#4

mywisdom882020-03-26 16:21

以下是引用Melody6188在2020-3-26 06:27:52的发言：

不是那么简单的，我已经试过了，那样是求不出来我要的结果的。
我要的结果是在group by 以后每个地区的平均值。

1.按“月，地区”分组，求平均

select Month,地区,sum(Revenue) as Revenue,avg(Revenue) as Average from 你表名称 group by 地区,Month

#5

Melody61882020-03-28 07:15

回复 4楼 mywisdom88

谢谢您的回复，最后得到的平均值也不是我想要的结果。
我想要图中几个地区加起来以后的平均值，并不是原始表中的平均值。
比如现在的表里地区AP有三个，分别是2150，1850，1550，我想得到的平均值是他们三个加起来除以3以后的那个数字是1850
另外的两个地区也是这样的算法
是否考虑用到partition by这个function呢？

#6

mywisdom882020-03-30 08:40

以下是引用Melody6188在2020-3-28 07:15:02的发言：

谢谢您的回复，最后得到的平均值也不是我想要的结果。
我想要图中几个地区加起来以后的平均值，并不是原始表中的平均值。
比如现在的表里地区AP有三个，分别是2150，1850，1550，我想得到的平均值是他们三个加起来除以3以后的那个数字是1850
另外的两个地区也是这样的算法
是否考虑用到partition by这个function呢？

那就只按地区分组
select 地区,sum(Revenue) as Revenue,avg(Revenue) as Average from 你表名称 group by 地区

#7

mywisdom882020-03-30 08:50

--SQL语法
--要显示你的那个效果
select t1.*,t2.求和,t2.平均 from 你的表名称 as t1
left join (select 地区,sum(Revenue) as 求和,avg(Revenue) as 平均 from 你的表名称 group by 地区) as t2
on t1.地区=t2.地区

** VFP SQL语法 (区别：SQL的不需要分行，VFP 分行时要用分号 “;”）
**要显示你的那个效果
select t1.*,t2.求和,t2.平均 from 你的表名称 as t1 ;
left join (select 地区,sum(Revenue) as 求和,avg(Revenue) as 平均 from 你的表名称 group by 地区) as t2;
on t1.地区=t2.地区

#8

Melody61882020-03-31 10:27

回复 7楼 mywisdom88

谢谢您的建议，但是你的QUERY结果还是跟我想要的有区别

#9

mywisdom882020-03-31 17:21

怎么会有区别呢？
你原表字段和数据？
你要的结果字段和数据是？

#10

Melody61882020-04-01 07:05

回复 9楼 mywisdom88

我要的结果就是跟图里几乎是一摸一样的。尤其AP这个地区的值应该是跟图里一摸一样的，另外的两个地区略有区别
特别感谢您能一直回复我，因为我的问题还是没有解决
麻烦您费心

#11

mywisdom882020-04-01 09:12

只有本站会员才能查看附件，请登录

-- 先对表进行Month,Region分组求和，然后再 Region分组求平均
select Month,Region,sum(Revenue) as Revenue,t2.Average from 你的表 as t1 group by Month,Region
left join (select Region,avg(Revenue) as Average from 你的表 group by Region) as t2
on t1.Region=t2.Region

#12

mywisdom882020-04-01 09:14

-- 先对表进行Month,Region分组求和，然后再 Region分组求平均，如果报错误，就下面
select t1.Month,t1.Region,sum(Revenue) as Revenue,t2.Average from 你的表 as t1 group by Month,Region
left join (select Region,avg(Revenue) as Average from 你的表 group by Region) as t2
on t1.Region=t2.Region

#13

sssooosss2020-06-20 12:40

学习

#14

sssooosss2020-06-22 08:30

学习

1