HiveQL共通クエリー文where、group by、having、join句レコード
4542 ワード
hivesqlの中で文を検索する時、よく使うクエリーの条件は比較的に多いため、専門的に1篇の博文を書いてHiveQLのクエリーの文に対して総括を行って、連絡と区別を整理します.
目次
1.where句(別名は使用できません)
(1)比較演算子
(2)likeとrlike(正則マッチング)
(3)論理演算子and or not
2.group by句(毎...)
3.having句(groupby以降のみ)
4.join句(A join B on A.key=B.key where X.key is null)
(1)A join B on A.フィールド=B.フィールド、2枚のテーブルを接続する
(2)内部接続(交差を求める)
(3)左外部接続(左表独自を求める)
(4)右外部接続(右表独自を求める)
(5)全外接続(並列化)
(6)全外部接続を再接続する(交差部分を並列化して除去する)
(7)マルチテーブル接続
(8)デカルト積
1.where句
where句は通常from tablenameを使用し、where句ではフィールド別名は使用できません.
(1)比較演算子
< > = ! is null is not null;
A(not)between B and C:Aは[B,C]の間にある.
A in(B,C):in演算リストの値、すなわちA=B,A=Cをクエリする場合.
(2)likeとrlike(正則マッチング)
%は任意の文字、ワイルドカードを表します.1文字を表す
A(not)like'x%':Aはxで始まる必要があります
A(not)like'%x':Aはxで終わる必要があります
A(not)like'%x%':Aはxを含む必要があります
A (not) like ‘_5%’:A 2位は5
A rlike'[Java正規表現]'
ps:一重引用符を付ける必要があります
(3)論理演算子and or not
2つのフィールドを同時にクエリーします.論理和、論理または、論理No
2.group by句(毎...)
groupby句は通常、集約関数とともに使用され、パケットフィールドに接続され、パケット化された後、各グループを集約します.
一般的に需要の中で各XXXを要求するために用いられ、このXXXフィールドをグループ化する.
ps:groupbyは、複数行を集約して1行の結果を出力します.max(col)、collect_がなければset(col)、concat_ws('~',col)などの行転列,すなわち複数行が1行になるsql条件は,集約できない誤りを報告する.
3.having句(groupby以降のみ)
havingはgroupby句のみに使用された後、パケットフィールドに条件を追加します.
groupbyフィールドhaving句:各[一定条件(having条件)を満たす]XXX.
4.join句(A join B on A.key=B.key where X.key is null)
(1)A join B on A.フィールド=B.フィールド、2枚のテーブルを接続する
たとえばhiveテーブル2枚、従業員テーブルempと部門テーブルdept、deptnoで直列に接続
要件:従業員テーブルと部門テーブルの部門番号に基づいて、従業員番号、従業員名、部門名を問い合せます.
(2)内部接続(交差を求める)
(3)左外部接続(左表独自を求める)
(4)右外部接続(右表独自を求める)
(5)全外接続(並列化)
(6)全外部接続を再接続する(交差部分を並列化して除去する)
(7)マルチテーブル接続
ps:hiveは左から右の順序で実行され、マルチテーブル接続joinの順序もクエリーフィールドの上から下に従います.
(8)デカルト積
目次
1.where句(別名は使用できません)
(1)比較演算子
(2)likeとrlike(正則マッチング)
(3)論理演算子and or not
2.group by句(毎...)
3.having句(groupby以降のみ)
4.join句(A join B on A.key=B.key where X.key is null)
(1)A join B on A.フィールド=B.フィールド、2枚のテーブルを接続する
(2)内部接続(交差を求める)
(3)左外部接続(左表独自を求める)
(4)右外部接続(右表独自を求める)
(5)全外接続(並列化)
(6)全外部接続を再接続する(交差部分を並列化して除去する)
(7)マルチテーブル接続
(8)デカルト積
1.where句
where句は通常from tablenameを使用し、where句ではフィールド別名は使用できません.
select ~ from tablename where
(1)比較演算子
< > = ! is null is not null;
A(not)between B and C:Aは[B,C]の間にある.
A in(B,C):in演算リストの値、すなわちA=B,A=Cをクエリする場合.
(2)likeとrlike(正則マッチング)
%は任意の文字、ワイルドカードを表します.1文字を表す
A(not)like'x%':Aはxで始まる必要があります
A(not)like'%x':Aはxで終わる必要があります
A(not)like'%x%':Aはxを含む必要があります
A (not) like ‘_5%’:A 2位は5
A rlike'[Java正規表現]'
ps:一重引用符を付ける必要があります
(3)論理演算子and or not
2つのフィールドを同時にクエリーします.論理和、論理または、論理No
2.group by句(毎...)
groupby句は通常、集約関数とともに使用され、パケットフィールドに接続され、パケット化された後、各グループを集約します.
一般的に需要の中で各XXXを要求するために用いられ、このXXXフィールドをグループ化する.
emp
0: jdbc:hive2://hadoop100:10000> select deptno,job,max(sal) max_sal from emp group by deptno,job;
+---------+------------+----------+--+
| deptno | job | max_sal |
+---------+------------+----------+--+
| 10 | CLERK | 1300.0 |
| 10 | MANAGER | 2450.0 |
| 10 | PRESIDENT | 5000.0 |
| 20 | ANALYST | 3000.0 |
| 20 | CLERK | 1100.0 |
| 20 | MANAGER | 2975.0 |
| 30 | CLERK | 950.0 |
| 30 | MANAGER | 2850.0 |
| 30 | SALESMAN | 1600.0 |
+---------+------------+----------+--+
ps:groupbyは、複数行を集約して1行の結果を出力します.max(col)、collect_がなければset(col)、concat_ws('~',col)などの行転列,すなわち複数行が1行になるsql条件は,集約できない誤りを報告する.
3.having句(groupby以降のみ)
havingはgroupby句のみに使用された後、パケットフィールドに条件を追加します.
groupbyフィールドhaving句:各[一定条件(having条件)を満たす]XXX.
2000
0: jdbc:hive2://hadoop100:10000> select deptno,avg(sal) avg_sal from emp group by deptno having avg_sal>2000;
+---------+---------------------+--+
| deptno | avg_sal |
+---------+---------------------+--+
| 10 | 2916.6666666666665 |
| 20 | 2175.0 |
+---------+---------------------+--+
4.join句(A join B on A.key=B.key where X.key is null)
(1)A join B on A.フィールド=B.フィールド、2枚のテーブルを接続する
たとえばhiveテーブル2枚、従業員テーブルempと部門テーブルdept、deptnoで直列に接続
要件:従業員テーブルと部門テーブルの部門番号に基づいて、従業員番号、従業員名、部門名を問い合せます.
0: jdbc:hive2://hadoop100:10000> select e.empno,e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
+----------+----------+-------------+--+
| e.empno | e.ename | d.dname |
+----------+----------+-------------+--+
| 7369 | SMITH | RESEARCH |
| 7499 | ALLEN | SALES |
| 7521 | WARD | SALES |
| 7566 | JONES | RESEARCH |
| 7654 | MARTIN | SALES |
| 7698 | BLAKE | SALES |
| 7782 | CLARK | ACCOUNTING |
| 7788 | SCOTT | RESEARCH |
| 7839 | KING | ACCOUNTING |
| 7844 | TURNER | SALES |
| 7876 | ADAMS | RESEARCH |
| 7900 | JAMES | SALES |
| 7902 | FORD | RESEARCH |
| 7934 | MILLER | ACCOUNTING |
+----------+----------+-------------+--+
(2)内部接続(交差を求める)
select * from A a
join B b
on a.key = b.key;
(3)左外部接続(左表独自を求める)
select * from A a
left join B b
on a.key = b.key
where b.key is null;
(4)右外部接続(右表独自を求める)
select * from A a
left join B b
on a.key = b.key
where a.key is null;
(5)全外接続(並列化)
select * from A a
full join B b
on a.key = b.key;
(6)全外部接続を再接続する(交差部分を並列化して除去する)
select * from A a
full join B b
on a.key = b.key
where a.key is null or b.key is null;
(7)マルチテーブル接続
select a.name, b.name, c.name
from A a
join B b
on a.key1 = b.key1
join C c
on a.key2 = c.key2;
ps:hiveは左から右の順序で実行され、マルチテーブル接続joinの順序もクエリーフィールドの上から下に従います.
(8)デカルト積
0: jdbc:hive2://hadoop100:10000> select empno,dname from emp, dept;