COUNT命令格式、參數和傳回值 - MaxCompute

COUNT函數用於計算記錄數。

適用範圍

視窗函數只能出現在SELECT語句中。
視窗函數中不能嵌套使用視窗函數和彙總函式。
視窗函數不能和同層級的彙總函式一起使用。

命令格式

-- 計算記錄數
BIGINT COUNT([DISTINCT|ALL] <colname>)

-- 計算視窗中的記錄數
BIGINT COUNT(*) OVER ([partition_clause] [orderby_clause] [frame_clause])
BIGINT COUNT([DISTINCT] <expr>[,...]) OVER ([partition_clause] [orderby_clause] [frame_clause])

參數說明

DISTINCT|ALL：可選。表示在計數時是否去除重複記錄，預設為ALL，即計算全部記錄。如果指定DISTINCT，則只計算唯一值數量。
colname：必填。列值可以為任意類型。colname可以為*，即COUNT(*)，此時返回所有行數。colname值為NULL時，該行不參與計算。
expr：必填。待計算計數值的列。可以為任意類型。當值為NULL時，該行不參與計算。當指定DISTINCT關鍵字時，表示取唯一值的計數值。
COUNT([DISTINCT] <expr>[,...])：計算指定視窗記錄中所有值均不為NULL的行數。若指定DISTINCT關鍵字，則對這些行去重後計數。
partition_clause、orderby_clause及frame_clause：詳情請參見windowing_definition。

傳回值說明

返回BIGINT類型。colname值為NULL時，該行不參與計算。

使用樣本

準備測試資料

如果已有資料，可忽略該步驟。

下載測試資料test_data.txt。

建立測試表

CREATE TABLE IF NOT EXISTS emp(
  empno BIGINT,
  ename STRING,
  job STRING,
  mgr BIGINT,
  hiredate DATETIME,
  sal BIGINT,
  comm BIGINT,
  deptno BIGINT
);

載入資料
根據資料檔案的實際path(路徑以及名稱)替換FILE_PATH。
```
TUNNEL UPLOAD FILE_PATH emp;   
```

樣本1：指定某一列為開窗列，返回不排序情況下累計計數值

指定薪水（sal）為開窗列，不排序，返回當前視窗（相同sal）的從開始行到最後一行的累計計數值。

命令樣本

SELECT sal, COUNT(sal) OVER (PARTITION BY sal) AS count FROM emp;

返回結果

+------------+------------+
| sal        | count      | 
+------------+------------+
| 800        | 1          | 
| 950        | 1          | 
| 1100       | 1          | 
| 1250       | 2          |  -- 視窗開始行。第1行和第2行的sal一致，則第1行的count為第2行的累計計數值。
| 1250       | 2          |  -- 當前視窗從第1行到第2行的累計計數值。
| 1300       | 2          | 
| 1300       | 2          | 
| 1500       | 1          | 
| 1600       | 1          | 
| 2450       | 2          | 
| 2450       | 2          | 
| 2850       | 1          | 
| 2975       | 1          | 
| 3000       | 2          | 
| 3000       | 2          | 
| 5000       | 2          | 
| 5000       | 2          | 
+------------+------------+

樣本2：非Hive相容模式下，指定某一列為開窗列，返回排序後累計計數值

非Hive相容模式下，指定薪水（sal）為開窗列，並排序，返回當前視窗（相同sal）從開始行到當前行的累計計數值。

命令樣本

-- 關閉Hive相容模式。
SET odps.sql.hive.compatible=false;

SELECT sal, COUNT(sal) OVER (PARTITION BY sal ORDER BY sal) AS count FROM emp;

返回結果

+------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 1          |   -- 視窗開始行。第1行的累計計數值是1。
| 1250       | 2          |   -- 第2行的累計計數值是2。
| 1300       | 1          |
| 1300       | 2          |
| 1500       | 1          |
| 1600       | 1          |
| 2450       | 1          |
| 2450       | 2          |
| 2850       | 1          |
| 2975       | 1          |
| 3000       | 1          |
| 3000       | 2          |
| 5000       | 1          |
| 5000       | 2          |
+------------+------------+

樣本3：Hive相容模式下，指定某一列為開窗列，返回排序後累計計數值

Hive相容模式下，指定薪水（sal）為開窗列，並排序，返回當前視窗（相同sal）從開始行至最後一行的累計計數值。

命令樣本

-- 開啟Hive相容模式。
SET odps.sql.hive.compatible=true;

SELECT sal, COUNT(sal) OVER (PARTITION BY sal ORDER BY sal) AS count FROM emp;

返回結果

+------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 2          |   -- 視窗開始行。第1行和第2行的sal一致，則第1行的count為第2行的累計計數值。
| 1250       | 2          |   -- 當前視窗從第1行到第2行的累計計數值。
| 1300       | 2          |
| 1300       | 2          |
| 1500       | 1          |
| 1600       | 1          |
| 2450       | 2          |
| 2450       | 2          |
| 2850       | 1          |
| 2975       | 1          |
| 3000       | 2          |
| 3000       | 2          |
| 5000       | 2          |
| 5000       | 2          |
+------------+------------+

樣本4：返回總行數

計算所有部門的總職工人數。

命令樣本
```
SELECT COUNT(*) FROM emp;
```

返回結果

+------------+
| _c0        |
+------------+
| 17         |
+------------+

樣本5：分組計算各組總數

與GROUP BY配合使用，對所有職工按照部門（deptno）分組，計算各部門（deptno）的職工人數。

命令樣本

SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;

返回結果

+------------+------------+
| deptno     | _c1        | 
+------------+------------+
| 20         | 5          | 
| 30         | 6          | 
| 10         | 6          | 
+------------+------------+

樣本6：去重計數

通過DISTINCT去重，計算部門數量。

命令樣本
```
SELECT COUNT(DISTINCT deptno) FROM emp;
```

返回結果

+------------+
| _c0        |
+------------+
| 3          |
+------------+

MaxCompute：COUNT函數

適用範圍

命令格式

參數說明

傳回值說明

使用樣本

準備測試資料

樣本1：指定某一列為開窗列，返回不排序情況下累計計數值

樣本2：非Hive相容模式下，指定某一列為開窗列，返回排序後累計計數值

樣本3：Hive相容模式下，指定某一列為開窗列，返回排序後累計計數值

樣本4：返回總行數

樣本5：分組計算各組總數

樣本6：去重計數

相關函數