本文为您介绍MaxCompute SQL常见使用场景,让您快速掌握SQL的写法。
准备数据集
本文以emp表和dept表为示例数据集。您可以自行在MaxCompute项目上创建表并上传数据。数据导入请参见概述。
创建emp表。
CREATE TABLE IF NOT EXISTS emp (
EMPNO STRING,
ENAME STRING,
JOB STRING,
MGR BIGINT,
HIREDATE DATETIME,
SAL DOUBLE,
COMM DOUBLE,
DEPTNO BIGINT);
创建dept表。
CREATE TABLE IF NOT EXISTS dept (
DEPTNO BIGINT,
DNAME STRING,
LOC STRING);
SQL示例
- 示例1:查询员工人数大于零的所有部门。
为了避免数据量太大,此场景下建议您使用JOIN子句。
SELECT d.* FROM dept d JOIN ( SELECT 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名的人员的姓名以及其排序。
此场景为典型的Top N场景。
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:查询每个部门的人数以及该部门中办事员(CLERK)人数的占比。
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;
注意事项
- 使用GROUP BY时,SELECT部分必须是分组项或聚合函数。
- ORDER BY后面必须加LIMIT N。
- SELECT表达式中不能用子查询,可以改写为JOIN。
- JOIN不支持笛卡尔积,可以使用MAPJOIN替代。
- UNION All需要改成子查询的格式。
- IN/NOT IN语句对应的子查询只能有一列,而且返回的行数不能超过1000,否则也需要改成JOIN操作执行。