COUNT [analytic]
对
窗口中组内的出现次数进行计数。如果指定 * 或某个非 NULL 常数，COUNT() 会对所有行进行计数。
行为类型
不可变语法
COUNT ( expression ) OVER (
[ window-partition-clause ]
[ window-order-clause ]
[ window-frame-clause ] )
参数
- 表达式
- 返回其 expression 不为 null 的每个组中的行数。可以是生成 BIGINT 的任何表达式。
OVER()- 请参阅分析函数。
示例
使用 窗口框架 中定义的架构时，以下 COUNT 函数将忽略窗口顺序子句和窗口框架子句；否则 Vertica 会将其视为窗口聚合。将报告聚合的窗口视为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。
=> SELECT deptno, sal, empno, COUNT(sal)
OVER (PARTITION BY deptno) AS count FROM emp;
deptno | sal | empno | count
--------+-----+-------+-------
10 | 101 | 1 | 2
10 | 104 | 4 | 2
20 | 110 | 10 | 6
20 | 110 | 9 | 6
20 | 109 | 7 | 6
20 | 109 | 6 | 6
20 | 109 | 8 | 6
20 | 109 | 11 | 6
30 | 105 | 5 | 3
30 | 103 | 3 | 3
30 | 102 | 2 | 3
使用 ORDER BY sal 会创建包含默认窗口的移动窗口查询： RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
=> SELECT deptno, sal, empno, COUNT(sal)
OVER (PARTITION BY deptno ORDER BY sal) AS count
FROM emp;
deptno | sal | empno | count
--------+-----+-------+-------
10 | 101 | 1 | 1
10 | 104 | 4 | 2
20 | 100 | 11 | 1
20 | 109 | 7 | 4
20 | 109 | 6 | 4
20 | 109 | 8 | 4
20 | 110 | 10 | 6
20 | 110 | 9 | 6
30 | 102 | 2 | 1
30 | 103 | 3 | 2
30 | 105 | 5 | 3
使用 VMart 架构时，以下查询会查找其每小时工资小于或等于当前员工的员工数。该查询使用默认窗口 RANGE UNBOUNDED PRECEDING AND CURRENT ROW 返回运行/累计平均数（有时称为移动平均数）：
=> SELECT employee_last_name AS "last_name", hourly_rate, COUNT(*)
OVER (ORDER BY hourly_rate) AS moving_count from employee_dimension;
last_name | hourly_rate | moving_count
------------+-------------+--------------
Gauthier | 6 | 4
Taylor | 6 | 4
Jefferson | 6 | 4
Nielson | 6 | 4
McNulty | 6.01 | 11
Robinson | 6.01 | 11
Dobisz | 6.01 | 11
Williams | 6.01 | 11
Kramer | 6.01 | 11
Miller | 6.01 | 11
Wilson | 6.01 | 11
Vogel | 6.02 | 14
Moore | 6.02 | 14
Vogel | 6.02 | 14
Carcetti | 6.03 | 19
...
要返回不是运行（累计）平均数的移动平均数，窗口应指定 ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING：
=> SELECT employee_last_name AS "last_name", hourly_rate, COUNT(*)
OVER (ORDER BY hourly_rate ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
AS moving_count from employee_dimension;