多次元分析のための GROUP BY 拡張機能の使用 - PolarDB

多次元分析は、データウェアハウスアプリケーションで使用される一般的なプロセスです。このプロセスは、さまざまなディメンションの組み合わせを使用してデータを調べるのに役立ちます。ディメンションは、時間、地理、部門、プロダクトラインなどのデータを分類するために使用されるカテゴリです。特定のディメンションセットに関連付けられた結果は、ファクトと呼ばれます。ファクトは通常、プロダクトの売上、利益、数量、カウントなどのディメンションに関連付けられた数値です。

SQL 集計を使用して、リレーショナルデータベースシステムの一連のディメンションに基づいてこれらのファクトを取得できます。 SQL 集計中、データは特定の基準またはディメンションによってグループ化されます。結果セットは、各グループのデータのカウント、合計、平均などのファクトの集計で構成されています。

SQL SELECT 行の GROUP BY 句は、集計結果の生成プロセスを簡略化する次の拡張機能をサポートしています。

ROLLUP 拡張
CUBE 拡張
GROUPING SETS 拡張

さらに、GROUPING 関数と GROUPING_ID 関数を SELECT リストまたは HAVING 句で使用して、これらの拡張機能を使用された場合に結果を解釈できます。

本ページでは、dept テーブルと emp テーブルを例に、これらの拡張機能を使用する方法について説明します。より有益な結果を提供するために、これらのテーブルに次の変更が行われます。

UPDATE dept SET loc = 'BOSTON' WHERE deptno = 20;
INSERT INTO emp (empno,ename,job,deptno) VALUES (9001,'SMITH','CLERK',40);
INSERT INTO emp (empno,ename,job,deptno) VALUES (9002,'JONES','ANALYST',40);
INSERT INTO emp (empno,ename,job,deptno) VALUES (9003,'ROGERS','MANAGER',40);

emp テーブルと dept テーブルの結合からの次の行が使用されます。

SELECT loc, dname, job, empno FROM emp e, dept d
WHERE e.deptno = d.deptno
ORDER BY 1, 2, 3, 4;

   loc    |   dname    |    job    | empno
----------+------------+-----------+-------
 BOSTON   | OPERATIONS | ANALYST   |  9002
 BOSTON   | OPERATIONS | CLERK     |  9001
 BOSTON   | OPERATIONS | MANAGER   |  9003
 BOSTON   | RESEARCH   | ANALYST   |  7788
 BOSTON   | RESEARCH   | ANALYST   |  7902
 BOSTON   | RESEARCH   | CLERK     |  7369
 BOSTON   | RESEARCH   | CLERK     |  7876
 BOSTON   | RESEARCH   | MANAGER   |  7566
 CHICAGO  | SALES      | CLERK     |  7900
 CHICAGO  | SALES      | MANAGER   |  7698
 CHICAGO  | SALES      | SALESMAN  |  7499
 CHICAGO  | SALES      | SALESMAN  |  7521
 CHICAGO  | SALES      | SALESMAN  |  7654
 CHICAGO  | SALES      | SALESMAN  |  7844
 NEW YORK | ACCOUNTING | CLERK     |  7934
 NEW YORK | ACCOUNTING | MANAGER   |  7782
 NEW YORK | ACCOUNTING | PRESIDENT |  7839
(17 rows)

loc、dname、および job 列は、例で使用されている SQL 集計のディメンションに使用されます。 COUNT(*) 関数は、集計の結果として従業員数を取得するために使用されます。

次の例は、loc、dname、および job 列がグループ化されている基本的なクエリを示しています。

SELECT loc, dname, job, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY loc, dname, job
ORDER BY 1, 2, 3;

拡張機能を使用しない基本的な GROUP BY 句を使用するこの結果セットの行は、基本集計行と呼ばれます。

   loc    |   dname    |    job    | employees
----------+------------+-----------+-----------
 BOSTON   | OPERATIONS | ANALYST   |         1
 BOSTON   | OPERATIONS | CLERK     |         1
 BOSTON   | OPERATIONS | MANAGER   |         1
 BOSTON   | RESEARCH   | ANALYST   |         2
 BOSTON   | RESEARCH   | CLERK     |         2
 BOSTON   | RESEARCH   | MANAGER   |         1
 CHICAGO  | SALES      | CLERK     |         1
 CHICAGO  | SALES      | MANAGER   |         1
 CHICAGO  | SALES      | SALESMAN  |         4
 NEW YORK | ACCOUNTING | CLERK     |         1
 NEW YORK | ACCOUNTING | MANAGER   |         1
 NEW YORK | ACCOUNTING | PRESIDENT |         1
(12 rows)

ROLLUP 拡張および CUBE 拡張が基本集計行に追加され、結果セットに追加レベルの小計を提供します。

GROUPING SETS 拡張を使用すると、さまざまなタイプのグループを 1 つの結果セットに結合できます。

GROUPING 関数および GROUPING_ID 関数は、結果セットを解釈するために使用されます。

これらの拡張機能によって提供される追加機能の詳細については、後続のページをご参照ください。