在採用Cassandra前你必須要知道的事-CQL怎麼GROUPBY

SQL開發者絕對少不了會使用聚合函式對資料作加總、平均、計數和取大小值，而Cassandra也支援了SUM、AVG、COUNT、和MIN/MAX的聚合函式。以下用員工表範例來作說明。

-- create
CREATE TABLE CASSANDRA.EMPLOYEE (
  deptName text,
  jobgrade text,
  empId text,
  name text,
  gender text,
  primary key(deptName, jobgrade, gender, empId)
);

-- insert
INSERT INTO CASSANDRA.EMPLOYEE(deptName,jobgrade,empId,name,gender) VALUES ('HR', '1', '001', 'Tom', 'Male');
INSERT INTO CASSANDRA.EMPLOYEE(deptName,jobgrade,empId,name,gender) VALUES ('HR', '3', '002', 'Amy', 'Female');
INSERT INTO CASSANDRA.EMPLOYEE(deptName,jobgrade,empId,name,gender) VALUES ('Design', '2', '003', 'John', 'Male');
INSERT INTO CASSANDRA.EMPLOYEE(deptName,jobgrade,empId,name,gender) VALUES ('Design', '1', '004', 'Alice', 'Female');
INSERT INTO CASSANDRA.EMPLOYEE(deptName,jobgrade,empId,name,gender) VALUES ('HR', '1', '005', 'Mary', 'Female');
INSERT INTO CASSANDRA.EMPLOYEE(deptName,jobgrade,empId,name,gender) VALUES ('Design', '3', '006', 'Henry', 'Male');

-- select
SELECT * FROM CASSANDRA.EMPLOYEE;

deptname | jobgrade | gender | empid | name
----------+----------+--------+-------+-------
       HR |        1 | Female |   005 |  Mary
       HR |        1 |   Male |   001 |   Tom
       HR |        3 | Female |   002 |   Amy
   Design |        1 | Female |   004 | Alice
   Design |        2 |   Male |   003 |  John
   Design |        3 |   Male |   006 | Henry

假設在RDBMS裡用以上建立的資料表，舉幾種查詢情境如下:

-- 依部門計數
SELECT deptname, count(*) 
  FROM EMPLOYEE
 GROUP BY deptname

  deptname | count
----------+-------
       HR |     3
   Design |     3

-- 依職等計數
SELECT jobgrade, count(*) 
  FROM EMPLOYEE
 GROUP BY jobgrade

 jobgrade | count
----------+-------
        1 |     3
        2 |     1
        2 |     2

-- 依部門, 職等計數
SELECT deptname, jobgrade, count(*) 
  FROM EMPLOYEE
 GROUP BY deptname, jobgrade

 deptname | jobgrade | count
----------+----------+-------
       HR |        1 |     2
       HR |        3 |     1
   Design |        1 |     1
   Design |        2 |     1
   Design |        3 |     1

只能用在primary key

如同在Cassandra查詢資料一樣，Cassandra對於使用聚合函式也是有所限制。在使用聚合函式時，只能針對被設定為primary key的欄位使用GROUP BY，也就是聚合函式只能用在被定義為partition key或是clustering key的欄位，而且嚴格限制GROUP BY的順序。以下我們看在Cassandra執行第一支CQL的結果：

-- 依部門計數
SELECT deptname, count(*) 
  FROM CASSANDRA.EMPLOYEE
 GROUP BY deptname

  deptname | count
----------+-------
       HR |     3
   Design |     3

Warnings :
Aggregation query used without partition key

因為deptname在這張表是partition key因此這支CQL可以被執行，但同時Cassandra會附帶警告訊息，表示這個聚合查詢沒有使用partition key。原因在於Cassandra會使用partition key在cluster中存放資料，如果沒有在CQL中使用partition key當作查詢條件，CQL在執行時便會在Cassandra cluster中遍尋(Full Scan)所有的資料，這會造成效能降低與資源浪費。

-- 依部門計數
SELECT deptname, count(*) 
  FROM CASSANDRA.EMPLOYEE
 WHERE deptname = 'HR'
 GROUP BY deptname

 deptname | count
----------+-------
       HR |     3

如果加上deptname當作查詢條件就不會出現警告訊息，這時Cassandra會直接從存放資料的node取出deptname=HR的資料作計數，而不需要遍尋整座Cassandra cluster。

必須依照primary key順序

接著我們看第二支CQL，第二支CQL在執行時會出現以下的錯誤：

-- 依職等計數
SELECT jobgrade, count(*) 
  FROM CASSANDRA.EMPLOYEE
 GROUP BY jobgrade

 InvalidRequest: Error from server: code=2200 [Invalid query] message="Group by currently only support groups of columns following their declared order in the PRIMARY KEY"

當在Cassandra中執行GROUP BY時，必須要依照primary key的順序，也就是deptname、jobgrade和gender這個順序，跳過deptname直接使用jobgrade或是gender作GROUP BY是不允許的。

其中原因在於Cassandra使用partition key決定資料存放的位置，並在每份partion中使用clustering key決定資料的排序順序。因此如果要對沒有任何索引的非primary key欄位作分組計算，就會需要遍尋Cassandra cluster。

第三支CQL因為符合partition和clustering的順序限制所以可以成功執行。

-- 依部門, 職等計數
SELECT deptname, jobgrade, count(*) 
  FROM CASSANDRA.EMPLOYEE
 GROUP BY deptname, jobgrade

 deptname | jobgrade | count
----------+----------+-------
       HR |        1 |     2
       HR |        3 |     1
   Design |        1 |     1
   Design |        2 |     1
   Design |        3 |     1

從以上查詢範例可以發現Cassandra在使用聚合函式時會受限於資料表primary key設計，並不是所有的欄位都能使用。因此如果預期會對資料欄位作聚合函式的計算，就必須預先在建立資料表就放進primary key中。