sql - 每月 Oracle Sql 组平均值
问题描述
我有一张桌子
ID REFGROUP YEAR MONTH VALUE
--- ----- ------ ----- -----
1 1000 2018 1 10
2 1000 2018 2 8
3 1000 2018 3 12
4 2000 2018 1 6
5 2000 2018 2 8
6 2000 2018 3 1
我想按组和按年逐月获得平均
ID REFGROUP YEAR MONTH VALUE
--- ----- ------ ----- -----
1 1000 2018 1 10
2 1000 2018 2 9
3 1000 2018 3 10
4 2000 2018 1 6
5 2000 2018 2 7
6 2000 2018 3 5
结果会在上面。第二个月,获得前两个月的平均值。对于第三个月,Iw 将通过 refgroup 和年份获得前三个月组的平均值。
我每个月都尝试过工会,但是。它使性能变慢。我每年都会做12个月。怎么写sql查询比我写的快。我的需要8分钟,这太多了
select 1 AS AMONTHH, REFGROUP,AYEAR, AVG(TOTAL) from BMS.FNC_W_PAYROL WHERE AMONTH<=1 GROUP BY REFGROUP,AYEAR
UNION
select 2 AS AMONTHH, REFGROUP,AYEAR, AVG(TOTAL) from BMS.FNC_W_PAYROL WHERE AMONTH<=2 GROUP BY REFGROUP,AYEAR
UNION
select 3 AS AMONTHH, REFGROUP,AYEAR, AVG(TOTAL) from BMS.FNC_W_PAYROL WHERE AMONTH<=3 GROUP BY REFGROUP,AYEAR
UNION
select 4 AS AMONTHH, REFGROUP,AYEAR, AVG(TOTAL) from BMS.FNC_W_PAYROL WHERE AMONTH<=4 GROUP BY REFGROUP,AYEAR
UNION
select 5 AS AMONTHH, REFGROUP,AYEAR, AVG(TOTAL) from BMS.FNC_W_PAYROL WHERE AMONTH<=5 GROUP BY REFGROUP,AYEAR
UNION
select 6 AS AMONTHH, REFGROUP,AYEAR, AVG(TOTAL) from BMS.FNC_W_PAYROL WHERE AMONTH<=6 GROUP BY REFGROUP,AYEAR
UNION
select 7 AS AMONTHH, REFGROUP,AYEAR, AVG(TOTAL) from BMS.FNC_W_PAYROL WHERE AMONTH<=7 GROUP BY REFGROUP,AYEAR
UNION
select 8 AS AMONTHH, REFGROUP,AYEAR, AVG(TOTAL) from BMS.FNC_W_PAYROL WHERE AMONTH<=8 GROUP BY REFGROUP,AYEAR
UNION
select 9 AS AMONTHH, REFGROUP,AYEAR, AVG(TOTAL) from BMS.FNC_W_PAYROL WHERE AMONTH<=9 GROUP BY REFGROUP,AYEAR
UNION
select 10 AS AMONTHH, REFGROUP,AYEAR, AVG(TOTAL) from BMS.FNC_W_PAYROL WHERE AMONTH<=10 GROUP BY REFGROUP,AYEAR
UNION
select 11 AS AMONTHH, REFGROUP,AYEAR, AVG(TOTAL) from BMS.FNC_W_PAYROL WHERE AMONTH<=11 GROUP BY REFGROUP,AYEAR
UNION
select 12 AS AMONTHH, REFGROUP,AYEAR, AVG(TOTAL) from BMS.FNC_W_PAYROL WHERE AMONTH<=12 GROUP BY REFGROUP,AYEAR
解决方案
我们可以在这里使用分析函数:
SELECT
ID, REFGROUP, YEAR, MONTH,
AVG(VALUE) OVER (PARTITION BY REFGROUP, YEAR ORDER BY MONTH) AVG_VALUE
FROM yourTable
ORDER BY
REFGROUP, YEAR, MONTH;
演示
推荐阅读
- flutter - 使用颤振,每次我重新加载 ListView 时,我的带有 MemoryImage 的 CircleAvatar 都会闪烁
- r - 使用 ggplot2 和 log2_trans() 进行 log_2 缩放的问题
- mysql - 如果不为空,Mysql 会合并列
- django - 表单验证未显示在表单上
- java - 外键必须与引用的主键错误具有相同的列数,但没有具有复合键的实体
- python - TensorFlow v2:替换 tf.contrib.predictor.from_saved_model
- powershell - Shell 脚本 - 在现有 xls 文件上设置自动过滤器
- javascript - 我创建了一个非常简单的井字游戏,我在使用 jquery 进行索引和获胜检查时遇到了问题
- python - 如何根据条件删除python有序字典中的子键、值对?
- applescript - 如何在 AppleScript 中获取/设置文件夹而不是窗口的视图选项?