Inceptor查询语句
-- MySQL中的语句都能⽤，不再⼀⼀描述，只记录⼀些不同详情见Inceptor 6.0⽂档 3.4.4查询语句这节
-- 查询语句 SELECT开头，可以通过添加多种从句从Inceptor中的表中获得信息。
-- 最常使⽤的数据查询语句的语法如下：
SELECT[ALL | DISTINCT] select_expression, select_expression, ...
FROM table_reference
[WHERE where_condition]
[GROUPBY col_list]
[CLUSTERBY col_list
| [DISTRIBUTE BY col_list][SORT BY col_list]
]
[LIMIT (M,)N
| [OFFSET M ROWSFETCHNEXT | FIRST] N ROWSONLY];
-- 如果不指定数据库，Inceptor默认从default数据库中寻⽤户指定的查询来源。要指定数据库，可以⽤"."来表⽰表和数据库的从属关系，⽐如
SELECT*FROM database_name.table_name;
-- 或者还可以在查询前使⽤USE语句来指定当前数据库：
USE database_name;
SELECT query_specifications;
USE default;
1、-- 过滤
-- WHERE在SELECT之前过滤，⽽HAVING在SELECT之后过滤。
2、-- ORDER BY, SORT BY, DISTRIBUTE BY和CLUSTER BY 略
3、-- GROUP BY
3.1--单列GROUP BY
-- 单列GROUP BY就是GROUP BY ⼦句中只有⼀列。
--举例我们可以⽤对transactions表⽤GROUP BY查看各个账户进⾏交易的次数：
SELECT acc_num, COUNT(trans_id) AS cnt FROM transactions GROUP BY acc_num;
--GROUP BY前⾯的列只能包含聚合函数和被分组列
--注意：⼀次查询可以使⽤多个聚合函数，但是聚合函数的参数中的DISTINCT列必须相同。
3.2-- GROUP BY (number)
SELECT select_expression1, select_expression2, ...
GROUP BY groupby_expression [,groupby_expression, ...]
-- Inceptor中⽀持group by 1 (第⼀列），group by 2（第⼆列）这类⽤法，其中数字1是指select_expression的位置。
3.3--多列GROUP BY
-- 多列GROUP BY就是GROUP BY⼦句中有不⽌⼀列。
SELECT acc_num, trans_type, COUNT(trans_id) AS cnt FROM transactions
GROUP BY acc_num, trans_type;
3.4--⽤表达式GROUP BY
-- tdh_todate是Inceptor⾃带的函数，可以⽤来提取trans_time 中的⽉份：
SELECT tdh_todate(trans_time, 'yyyyMMddHHmmss', 'MM') AS date,COUNT(trans_id) AS cnt FROM transactions
GROUP BY tdh_todate(trans_time, 'yyyyMMddHHmmss', 'MM');
3.5--在GROUP BY⼦句中过滤：HAVING ⼦句
-- 如果过滤条件受带GROUP BY的查询结果影响，那么就不能⽤WHERE⼦句来过滤，⽽要⽤HAVING⼦句
SELECT acc_num, MAX(price*amount) AS max_value FROM transactions WHERE trans_time<'20140630235959'
GROUP BY acc_num
HAVING MAX(price*amount)>5000;
--有两个过滤条件：WHERE⼦句中的过滤条件和查询结果⽆关，⽽HAVING⼦句中的过滤要在查询结束后才执⾏。
--注意：在WHERE⼦句中不能有聚合函数，因为Inceptor在执⾏GROUP BY⼦句之前就会执⾏WHERE⼦句。HAVING⼦句中可以含有聚合函数：
3.6-- SELECT DISTINCT + GROUP BY
--当执⾏形如SELECT DISTINCT <key> +　GROUP BY的语句之前必须先打开开关inceptor.abled。该开关默认处于关闭状态，如果不启动则出现语法报错。SET inceptor.abled = TRUE;
SELECT DISTINCT acc_num, COUNT(1)FROM transactions
GROUP BY acc_num;
3.7-- GROUP BY 扩展：ROLLUP/CUBE/GROUPING SETS
-- ROLLUP
-- ⽣成聚合⾏、超聚合⾏和总计⾏。举例来说，下⾯的代码
SELECT a, b, c, SUM(expression) FROM table GROUP BY ROLLUP(a, b, c);
-- 会为 (a, b, c)、(a, b) 和 (a) 值的每个唯⼀组合⽣成⼀个带有⼩计的⾏。还将计算⼀个总计⾏。
-- 详细地说，以上代码会计算以下四条查询并将结果⼀并输出：
SELECT a, b, c, sum(expression) FROM table GROUP BY a, b, c;  -- (a, b, c)组合⼩计
SELECT a, b, NULL, sum(expression) FROM table GROUP BY a, b;  -- (a, b)组合⼩计
SELECT a, NULL, NULL, sum(expression) FROM table GROUP BY a;  -- (a)组合⼩计
SELECT NULL, NULL, NULL, sum(expression) FROM table;          -- 总计
-
- CUBE
-- ⽣成聚合⾏、超聚合⾏、交叉表格⾏和总计⾏
SELECT a, b, c, SUM (expression) FROM table GROUP BY CUBE (a,b,c);
-- 会为 (a, b, c)、(a, b)、(a, c)、(b, c)、(a)、(b) 和 (c) 值的每个唯⼀组合⽣成⼀个带有⼩计的⾏，还会⽣成⼀个总计⾏。
-- 详细来说，以上代码会进⾏以下⼋条计算，并将结果⼀并输出：
SELECT a, b, c, sum(expression) FROM table GROUP BY a, b, c;  -- (a, b, c)组合⼩计
SELECT a, b, NULL, sum(expression) FROM table GROUP BY a, b;  -- (a, b)组合⼩计
SELECT a, NULL, c, sum(expression) FROM table GROUP BY a, c;  -- (a, c)组合⼩计
SELECT NULL, b, c, sum(expression) FROM table GROUP BY b, c;  -- (b, c)组合⼩计
SELECT a, NULL, NULL, sum(expression) FROM table GROUP BY a;  -- (a)组合⼩计
SELECT NULL, b, NULL, sum(expression) FROM table GROUP BY b;  -- (b)组合⼩计
SELECT NULL, NULL, c, sum(expression) FROM table GROUP BY c;  -- (c)组合⼩计
SELECT NULL, NULL, NULL, sum(expression) FROM table;          -- 总计
-- GROUPING SETS
-- GROUPING SETS⽣成交叉表格⾏。
-- 举例来说，
SELECT a, b, c, sum(expression) FROM table GROUP BY GROUPING SETS(a,b,c);
-- 会为 (a)、(b) 和 (c) 值的每个唯⼀组合⽣成⼀个带有⼩计的⾏。
-- 详细地说，以上代码会进⾏以下三条计算，并将结果⼀并输出：
SELECT a, NULL, NULL, sum(expression) FROM table GROUP BY a;  -- (a)组合⼩计
SELECT NULL, b, NULL, sum(expression) FROM table GROUP BY b;  -- (b)组合⼩计
SELECT NULL, NULL, c, sum(expression) FROM table GROUP BY c;  -- (c)组合⼩计
-
-FUNC(DISTINCT KEY)+GROUP BY 扩展
--⽀持形如UDAF(DISTINCT)+GROUP BY [ROLLUP|CUBE|GROUPING SET]的语句。
SELECT COUNT(store), COUNT(type), COUNT(DISTINCT grade)FROM inventory
GROUP BY ROLLUP(store, type, grade);
4、-- 多表查询：JOIN
4.1-- 笛卡尔连接：Cartesian Join
SELECT*FROM join_demo1 JOIN join_demo2 ON1=1;
-- 这样的表连接会产⽣⼤量的数据，⽽且并不经常具有意义，所以很少出现在实际应⽤中。
-- ⼤多数这样的表连接产⽣于没有加JOIN条件的错误。
-- 所以为了代码的清晰，如果真的需要进⾏笛卡尔积连接，最好使⽤专门的关键词CROSS JOIN:
l1, l2, ...FROM table1 CROSS JOIN table2
4.2-- 内连接：INNER JOIN
-- 内连接只显⽰参与连接的表中有匹配的记录。JOIN和INNER JOIN在这⾥⽤法⼀样。在⼀次查询中可以连接两个以上的表：
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key= b.key1) JOIN c ON (c.key= b.key2)
4.3-- 外连接：OUTER JOIN
-- 内连接会将被连接的两张表中互相没有匹配值的纪录忽略。
-- 如果想要在连接结果中看到没有匹配值的记录，则应该使⽤外连接。
-- 外连接⼜分为左外连接(left outer join)、右外连接(right outer join)和全外连接(full outer join)。
SELECT student_info.stu_name, urse_name FROM student_info LEFT OUTER JOIN course_info ON (urse_id = urse_id); SELECT student_info.stu_name, urse_name FROM student_info RIGHT OUTER JOIN course_info ON (urse_id = urse_id); SELECT student_info.stu_name, urse_name FROM student_info FULL OUTER JOIN course_info ON (urse_id = urse_id); 4.4--  隐式连接：Implicit JOIN
-- 隐式JOIN的命令中不含有JOIN…ON…关键词，⽽是通过WHERE⼦句作为连接条件将两张表连接。
SELECT student_info.stu_name, urse_name FROM student_info, course_info
WHERE urse_id = urse_id;
4.5-- ⾃然连接：NATURAL JOIN
-- 使⽤NATURAL JOIN，⽤户可以不需要明确写出JOIN条件。
-- Inceptor会⾃动在被连接的两张表中寻名字相同的列。如果两张表table1和table2中存在同名列col1，Inceptor会⾃动⽣成JOIN条件l1。
-- 如果Inceptor在被连接的两张表中不到同名列，Inceptor会将指令作为⽆条件的连接，也就是⼀个笛卡尔积。
SELECT name, trans_idFROM user_info NATURAL JOIN transactions;
4.6-- 多表连接
-
- 可以在⼀次查询中⽤多个JOIN⼦句连接多张表。
SELECT select_expression, select_expression, ...
FROM table_reference [(RIGHT|LEFT|FULL) OUTER]JOIN table_reference ON (join_condition) [(RIGHT|LEFT|FULL) OUTER]JOIN table_reference ON (join_condition) ... -- 每⼀个JOIN⼦句都可以是不同的连接（内连接，左/右/全外连接，左半连接等等）。
4.7-- 重复连接
-- 有时候同⼀张表在⼀次查询中需要和多张其他表连接。假设我们有三张表table1, table2和table3。其中table1要和table2和table3各连接⼀次。
-- 这种情况下，⽤户需要给table1在两次连接中起两个不同的化名来让Inceptor能够分辨在各⼦句中table1的⾓⾊：
l1, l2, t2.col1, t3.col,...
FROM table1 t1 JOIN table2 t2 l1 = t2.col1
INNER JOIN table3 t3  l2 = t3.col1
INNER JOIN table1 tb1 l = l2;
4.8-- 表的⾃连接
-- ⼀张表也可以和⾃⼰连接，此时需要给表取两个不同的化名来让Inceptor能够分辨在各⼦句中表的⾓⾊。
-- 我们⽤⼀个包含了员⼯信息的表来作为例⼦。表中含有员⼯⼯号，员⼯姓名，员⼯的上级⼯号和⼊职时间：
-- 我们⽤这张表和⾃⼰连接来查询员⼯和他们上级的姓名：
ployee_name, ployee_name AS manager_name
FROM employee_info e
LEFT OUTER JOIN employee_info sup
ON (e.sup_id = ployee_id);
4.9-- 左半连接和左半反连接
-- 左半连接⽤来查看左表中符合和JOIN条件的记录。左半反连接⽤来查看左表不符合JOIN条件的记录。
-- 左半连接和左半反连接都只显⽰左表中的记录。
-- 左半连接可以通过LEFT SEMI JOIN, WHERE… IN 和WHERE EXISTS 中嵌套⼦查询来实现。⽽左半反连接可以通过在LEFT …NOT IN/EXISTS中嵌套⼦查询来实现。
4.9.1-- 左半连接
SELECT t1.num, t1.letter FROM test11 t1 LEFT SEMI JOIN test2 t2 ON t1.num = t2.num;
SELECT t1.num, t1.letter FROM test11 t1 WHERE t1.num IN (SELECT t2.num FROM test2 t2 );
SELECT t1.num, t1.letter FROM test11 t1 WHERE EXISTS ( SELECT1FROM test2 t2  WHERE t2.num = t1.num);
4.9.2--左半反连接
SELECT t1.num, t1.letter FROM test11 t1 WHERE t1.num NOT IN (SELECT t2.num FROM test2 t2 );
SELECT t1.num, t1.letter FROM test11 t1 WHERE NOT EXISTS (SELECT1FROM test2 t2 WHERE t1.num = t2.num);
4.10--不等价连接
-- 要执⾏不等价连接，ON⼦句中的连接条件必须是等价条件，不等价条件体现在WHERE⼦句中的过滤条件中。
-- 不等价连接和笛卡尔积相像，很容易返回⼤量结果（在两表⾏数乘积的级别），ON⼦句中要求有等价条件可以限制结果的数量。
-- 如果确定不需要限制结果数量，可以在ON⼦句中的等价条件⾥放⼀个永远成⽴的等式，⽐如1=1。
-- 执⾏这样的操作必须格外⼩⼼。
-- 假设公司想要在员⼯中间组织⼀次下棋⽐赛，每对员⼯之间都要进⾏⼀场⽐赛，我们想要⽣成包含所有员⼯对的数据：
ployee_name, e2.employee_name
FROM employee_info e1
JOIN employee_info e2 ON1=1
ployee_name <> e2.employee_name;
-- 这⾥join条件是e1.employee_name<> e2.employee_name，因为⼀个⼈不能和⾃⼰⽐赛。
-- 这条指令会导致每个员⼯对都会重复⼀次，所以我们可以修改连接条件为e1.employee_name > e2.employee_name：
ployee_name, e2.employee_name
FROM employee_info e1
JOIN employee_info e2 ON1=1
ployee_name > e2.employee_name;
4.11-- MAP JOIN
-- 如果两张被连接的表中有⼀张⽐较⼩（100MB以下），那么可以通过MAP JOIN来提⾼执⾏速度。
-
-MAP JOIN会将⼩表放⼊内存中，在map阶段直接拿另⼀张表的数据和内存中表数据做匹配，由于省去了shuffle，速度会⽐较快。
SELECT/*+ MAPJOIN(b) */ select_expression, select_expression, ...
FROM table_reference JOIN table_reference ON join_condition
-- Inceptor已经有了⾃动MAP JOIN的功能，就是在有⼀张表在100MB⼀下时，Inceptor会⾃动执⾏MAP JOIN。
--所以⽤户可以⽆需特别指明使⽤MAP JOIN。如果在参与JOIN的表都较⼤时却指明使⽤MAP JOIN，可能会导致内存溢出。
5-- ⼦查询
-- ⼦查询是嵌套在查询语句中的查询语句。⼦查询根据是否和包含它的⽗查询的结果相关分为⾮关联⼦查询和关联⼦查询。
-- Inceptor⾼度⽀持⼦查询的各种嵌套：⾮关联⼦查询可以在FROM,WHERE,SELECT和HAVING⼦句中嵌套。
-- 关联⼦查询可以在WHERE和SELECT中嵌套，⽽不能在HAVING和FROM⼦句中嵌套。
5.1-- ⾮关联⼦查询
-- ⾮关联⼦查询内容和包含它的⽗查询结果不相关。当⼦查询和⽗查询不相关，Inceptor会在执⾏⽗查询之前先执⾏完成⼦查询。
5.1.1-- 在WHERE⼦句中嵌套
-- 单⾏单列的⼦查询结果
SELECT name, acc_num, citizen_id, acc_level, reg_date
FROM user_info
WHERE reg_date = (
SELECT MIN(reg_date) FROM user_info
);
-- 单⾏多列的⼦查询结果
-- IN运算符当⼦查询结果有不⽌⼀条记录，要⽤IN来表⽰查询结果须是⼦查询结果集合中的元素：
SELECT employee_name
FROM employee_info
WHERE employee_id IN (
SELECT sup_id FROM employee_info
);
SELECT employee_name
FROM employee_info
WHERE employee_id NOT IN (
distinct查询SELECT sup_id FROM employee_info
);
5.1.2--在FROM⼦句中嵌套
-- 下例查询所有进⾏过交易的账户持有⼈名字：
SELECT DISTINCT name
FROM (
SELECT name FROM user_info
JOIN transactions
ON user_info.acc_num = transactions.acc_num
);
-- 下例查询所有个⼈平均交易额⼤于所有平均交易额的⽤户名字
SELECT name
FROM user_info JOIN (
SELECT transactions.acc_num, AVG(price*amount) avg_trans
FROM transactions
GROUP BY transactions.acc_num
) temp
ON user_info.acc_num =temp.acc_num
WHERE avg_trans > (SELECT AVG(price*amount) FROM transactions);
5.1.3--在SELECT⼦句中嵌套
-- 下例查看各⽤户的个⼈平均交易额和所有交易的平均交易额的差：
SELECT acc_num, AVG(price*amount) - (SELECT AVG(price*amount) FROM transactions) AS avg_gap
FROM transactions
GROUP BY acc_num;
5.1.4--在HAVING⼦句中嵌套
-- 下例查询最⼤⼀笔交易的执⾏账户和交易额
SELECT acc_num, MAX(price*amount) AS max_value
FROM transactions
GROUP BY acc_num
HAVING MAX(price*amount) = (
SELECT max(price*amount) FROM transactions
);
5.2-- 关联⼦查询
-- 关联⼦查询的内容和⽗查询相关。Inceptor会对每条在⽗查询中出现的记录执⾏⼀次⼦查询
-- 注意：关联⼦查询中的关联条件不⽀持OR，也不⽀持仅包含⾮等值⽐较。
5.2.1-- 在WHERE⼦句中嵌套
-- Inceptor⽀持的WHERE⼦句嵌套需要满⾜以下要求：
-- 1. WHERE⼦句中必须包含⾄少⼀条等值关系，如果执⾏业务没有客观要求等值关联，请⽤户⼿动添加条件“1=1”。这是为了避免资源被贪婪占⽤导致枯竭，以保证系统的稳定性。-- 2. 主查询和⼦查询之间必须⽤标量⽐较运算符连接（包括‘>’、‘<’、‘=’、‘<>’、‘>=’、‘<=’）。
-- 3. 要求⼦查询的结果必须是⼀⾏⼀列的返回，即标量。
-- 4. ⼦查询中允许有等值与⾮等值条件
-- 下例查询了总共进⾏过3笔交易的账户持有⼈姓名和账户号码。注意，当关联⼦查询中有COUNT函数时，必须打开开关set hive.support.unt=true。
set hive.support.unt=true;
SELECT user_info.name, user_info.acc_num
FROM user_info
WHERE3=(
SELECT COUNT(*) FROM transactions
WHERE user_info.acc_num = transactions.acc_num
);
SELECT COUNT(*) AS cnt FROM TABLEA A, TABLEB B
WHERE ((1=1)) AND ((A.salary > (SELECT (SUM(csA.age) -114)
FROM TABLEA csA, TABLEB csB
WHERE (A.age = csA.age) AND
((csB.salary /-9) = (A.salary *-79)))));
5.2.2-- EXISTS和NOT EXISTS
-- 在WHERE中嵌套⼦查询时经常会⽤到EXISTS和NOT EXISTS。当我们只关⼼⼦查询有记录返回，
⽽不关⼼⼦查询返回的记录内容和记录条数时，我们就可以⽤WHERE EXISTS。-- WHERE EXISTS⽤来查看⼦查询中的关系是否成⽴并且返回使得⼦查询中关系成⽴的记录（也就是过滤掉使得⼦查询中的关系不成⽴的记录）。
-- ⽐如，假设查询买过单股价格在100元以内的股票的⽤户，语句应为
SELECT user_info.name, user_info.acc_num
FROM user_info WHERE EXISTS (
SELECT1FROM transactions
WHERE user_info.acc_num = transactions.acc_num
AND price <100
);
-- 事实上，我们建议如果WHERE⼦句需要满⾜某种关系（⼤于、等于、⼩于、不等于，等等），尽量使⽤WHERE EXISTS并在⼦查询中表达关系，⽽不是通过⽐较⼦查询的结果和别的量来表达关系。--
WHERE NOT EXISTS则⽤来查看⼦查询中的关系是否成⽴并且返回使得⼦查询中关系不成⽴的记录（也就是过滤掉使得⼦查询中的关系成⽴的记录）。
-- 下例查询了所有没有进⾏交易的账户持有⼈姓名和账户号码
SELECT user_info.name, user_info.acc_num
FROM user_info WHERE NOT EXISTS (
SELECT1FROM transactions
WHERE user_info.acc_num = transactions.acc_num
);
-- 这⾥，WHERE NOT EXISTS⼦句中嵌套的⼦查询返回的是⼀个常数，这充分体现了EXISTS和NOT EXISTS仅关⼼⼦查询是否返回结果，⽽不关⼼返回的结果是什么。
5.2.3-- 在SELECT⼦句中嵌套
-- 下例返回所有账户的持有⼈姓名，账户号码和账户平均交易额：
SELECT user_info.name, user_info.acc_num, (
SELECT AVG(price*amount) FROM transactions
WHERE user_info.acc_num = transactions.acc_num
)
FROM user_info;
5.3-- ⼦查询的多层嵌套
SELECT name FROM (
SELECT name, acc_num FROM (
SELECT name, acc_num, password FROM (
SELECT name, acc_num, password, bank_acc FROM user_info)
)
)
;
6--  集合运算：UNION/INTERSECT/EXCEPT
-- Inceptor提供三种⽅法来对SELECT语句结果进⾏集合运算：并集(UNION)、交集(INTERSECT)和减去(EXCEPT)。⽽每个集合运算都有两种选择，带有ALL和不带有ALL
-- UNION和INTERSECT带上ALL不去重
-- EXCEPT和EXCEPT ALL做集合减法。
-- A EXCEPT B 将A中所有和B重合的记录除去，然后返回去重后的A中剩下的记录。
-- A EXCEPT ALL B 将A中所有和B重合的记录除去，然后不去重的A中剩下的记录。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。