分析函数order by 的效果

最新推荐文章于 2024-12-20 23:53:00 发布

浅笑雨忆

最新推荐文章于 2024-12-20 23:53:00 发布

阅读量881

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle专题

本文链接：https://blog.youkuaiyun.com/maoweiting19910402/article/details/7899479

oracle专题专栏收录该内容

8 篇文章

订阅专栏

本文介绍了一个SQL查询案例，通过使用WITH AS子句创建临时视图，并利用OVER窗口函数进行数据汇总。示例展示了如何根据不同条件（如分组、排序等）计算特定列的总和。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

WITH v AS
(SELECT 'a' AS col, 1.1 AS val
FROM dual
UNION ALL
SELECT 'a' AS col, 1.2 AS val
FROM dual
UNION ALL
SELECT 'a' AS col, 1.3 AS val
FROM dual
UNION ALL
SELECT 'a' AS col, 1.2 AS val
FROM dual
UNION ALL
SELECT 'a' AS col, 1.4 AS val FROM dual
UNION ALL
SELECT 'b' AS col, 1.3 AS val FROM dual
UNION ALL
SELECT 'b' AS col, 1.5 AS val FROM dual
UNION ALL
SELECT 'b' AS col, 1.5 AS val FROM dual
UNION ALL
SELECT 'b' AS col, 1.6 AS val FROM dual
UNION ALL
SELECT 'b' AS col, 1.4 AS val FROM dual
)
SELECT v.col,
v.val,
SUM(v.val) over() s1,--sum max avg都一样
SUM(v.val) over(PARTITION BY v.col) s2,
SUM(v.val) over(ORDER BY v.val) s3,
SUM(v.val) over(PARTITION BY v.col ORDER BY v.val DESC) s4
FROM v
ORDER BY v.col