第六篇 著名函数之分析函数
1、AVG([DISTINCT|ALL] expr) OVER(analytic_clause) 计算平均值。
例如:
--聚合函数
SELECT col, AVG(value) FROM tmp1 GROUP BY col ORDER BY col;
--分析函数
SELECT col, AVG(value) OVER(PARTITION BY col ORDER BY col)
FROM tmp1
ORDER BY col;
2、SUM ( [ DISTINCT | ALL ] expr ) OVER ( analytic_clause )
例如:
--聚合函数
SELECT col, sum(value) FROM tmp1 GROUP BY col ORDER BY col;
--分析函数
SELECT col, sum(value) OVER(PARTITION BY col ORDER BY col)
FROM tmp1
ORDER BY col;