ここでは、実践的な例を使用して maxcompute SQL を説明しているので、SQL の作成方法をすばやく理解することができます。maxcompute SQL と標準 SQL の違いについて理解するには、MaxCompute SQL の基本ドキュメントと併せてお読みください。
データセットの準備
この例では、データセットとして Emp/Dept テーブルを使用します。 誰でも簡単に操作できるよう、maxcompute のテーブル作成文およびデータファイル (emp テーブルデータファイル、dept テーブルデータファイル) が提供されています。ここでは、テーブルを作成し、maxcompute プロジェクトにデータをアップロードします。
emp テーブルを作成する DDL 文は次のとおりです。
CREATE TABLE IF NOT EXISTS emp (
EMPNO string,
ENAME string ,
JOB string ,
MGR bigint ,
HIREDATE datetime ,
SAL double ,
COMM double ,
DEPTNO bigint );
Dept テーブルを作成する DDL 文は次のとおりです。
CREATE TABLE IF NOT EXISTS dept (
DEPTNO bigint ,
DNAME string,
LOC string);
SQL操作
-
Group By を使用する場合、Select 部分はグループ項目または AGGREGATE 関数のいずれかである必要があります。
-
Order by の後に Limit n を続ける必要があります。
-
Select 式では、サブクエリがサポートされていません。 サブクエリを使用するには、コードを Join 句に書き直します。
-
Join 句では、デカルト積と MapJoin を使用できません。
-
Union all はサブクエリの形式で使用します。
-
In/Not in 文はサブクエリの 1 列のみに対応し、返される結果の行数は 1,000 を超えられません。 それ以外の場合は、Join を使用して文を書き換えます。
例 1: 1 人以上の従業員がいる部署をすべてリストします。
大量のデータが生じるという状況を避けるには (注意事項 6 の問題はよく発生します)、join を使用して上書きする必要があります。 例:
SELECT d. *
FROM dept d
JOIN (
SELECT the DISTINCT deptno AS no
FROM emp
) e
on d.deptno = e.no;
例 2: Smith より給与が高いすべての従業員をリストします。
次のコードに示すように、この例は MapJoin の典型的なシナリオです。
SELECT /*+ MapJoin(a) */ e.empno
, e.ename
, e.sal
FROM emp e
JOIN (
SELECT MAX(sal) AS sal
FROM 'emp'
WHERE `ENAME` = 'SMITH'
) a
ON e.sal> a.sal;
例 3: すべての従業員の名前と直属の上司の名前をリストします。
次のコードに示すように、非等価結合を使用します。
SELECT a.ename
, b.ename
FROM emp a
LEFT OUTER JOIN emp b
ON b.empno = a.mgr;
例 4: 給与が 1500 元より高いすべての仕事をリストします。
次のコードに示すように、Having を使用します。
SELECT emp. 'JOB'
, MIN(emp.sal) AS sal
FROM 'emp'
GROUP BY emp. 'JOB'
HAVING MIN(emp.sal) > 1500;
例 5: 各部署の従業員の数、および 平均給与と平均勤続年数をリストします。
次のコードに示すように、時間処理には多くの組み込み関数を使用します。
SELECT COUNT(empno) AS cnt_emp
, ROUND(AVG(sal), 2) AS avg_sal
, ROUND(AVG(datediff(getdate(), hiredate, 'dd')), 2) AS avg_hire
FROM 'emp'
GROUP BY 'DEPTNO';
例 6: 給与が高い従業員の上位 3 人の名前とそのランクをリストします (上位 n がよく使用されます) 。
SQL 文は次のとおりです。
SELECT *
FROM (
SELECT deptno
, ename
,sal
, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS nums
FROM emp
) emp1
WHERE emp1.nums < 4;
例 7: 1 つの SQL で各部署の人数、事務員 (cleak) が部署の総人数に占める割合を書き出します。
SQL 文は次のとおりです。
SELECT deptno
, COUNT(empno) AS cnt
, ROUND(SUM(CASE
WHEN job = 'CLERK' THEN 1
ELSE 0
END) / COUNT(empno), 2) AS rate
FROM `EMP`
GROUP BY deptno;