查詢語句語法：

[WITH CommonTableExpression (, CommonTableExpression)*]    (Note: Only available starting with Hive 0.13.0)
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [ORDER BY col_list]
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT number]

1 基本查詢（Select…From）

1.1 全表和特定列查詢

hive (default)> select * from emp;
hive (default)> select empno, ename from emp;

1.2 列別名

hive (default)> select ename AS name, deptno dn from emp;

1.3 算術(shù)運算符

運算符	描述
A+B	A和B 相加
A-B	A減去B
A*B	A和B 相乘
A/B	A除以B
A%B	A對B取余
A&B	A和B按位取與
A\|B	A和B按位取或
A^B	A和B按位取異或
~A	A按位取反

hive (default)> select sal +1 from emp;

1.4 常用函數(shù)

hive (default)> select count(*) cnt from emp;
hive (default)> select max(sal) max_sal from emp;
hive (default)> select min(sal) min_sal from emp;
hive (default)> select sum(sal) sum_sal from emp; 
hive (default)> select avg(sal) avg_sal from emp;

1.5 Limit語句

hive (default)> select * from emp limit 5;

2 Where語句

hive (default)> select * from emp where sal >1000;

2.1 比較運算符（Between/In/ Is Null）

下面表中描述了謂詞操作符物蝙，這些操作符同樣可以用于JOIN…ON和HAVING語句中瞧柔。

操作符	支持的數(shù)據(jù)類型	描述
A=B	基本數(shù)據(jù)類型	如果A等于B則返回TRUE葬项，反之返回FALSE
A<=>B	基本數(shù)據(jù)類型	如果A和B都為NULL，則返回TRUE脱货，其他的和等號（=）操作符的結(jié)果一致颂跨，如果任一為NULL則結(jié)果為NULL
A<>B, A!=B	基本數(shù)據(jù)類型	A或者B為NULL則返回NULL祈惶；如果A不等于B柒瓣，則返回TRUE，反之返回FALSE
A<B	基本數(shù)據(jù)類型	A或者B為NULL撵枢，則返回NULL民晒；如果A小于B，則返回TRUE锄禽，反之返回FALSE
A<=B	基本數(shù)據(jù)類型	A或者B為NULL潜必，則返回NULL；如果A小于等于B沃但，則返回TRUE磁滚，反之返回FALSE
A>B	基本數(shù)據(jù)類型	A或者B為NULL，則返回NULL；如果A大于B垂攘，則返回TRUE维雇，反之返回FALSE
A>=B	基本數(shù)據(jù)類型	A或者B為NULL，則返回NULL晒他；如果A大于等于B吱型，則返回TRUE，反之返回FALSE
A [NOT] BETWEEN B AND C	基本數(shù)據(jù)類型	如果A仪芒，B或者C任一為NULL唁影，則結(jié)果為NULL。如果A的值大于等于B而且小于或等于C掂名，則結(jié)果為TRUE，反之為FALSE哟沫。如果使用NOT關(guān)鍵字則可達(dá)到相反的效果饺蔑。
A IS NULL	所有數(shù)據(jù)類型	如果A等于NULL，則返回TRUE嗜诀，反之返回FALSE
A IS NOT NULL	所有數(shù)據(jù)類型	如果A不等于NULL猾警，則返回TRUE，反之返回FALSE
IN(數(shù)值1, 數(shù)值2)	所有數(shù)據(jù)類型	使用 IN運算顯示列表中的值
A [NOT] LIKE B	STRING 類型	B是一個SQL下的簡單正則表達(dá)式隆敢，如果A與其匹配的話发皿，則返回TRUE；反之返回FALSE拂蝎。B的表達(dá)式說明如下：‘x%’表示A必須以字母‘x’開頭穴墅，‘%x’表示A必須以字母’x’結(jié)尾，而‘%x%’表示A包含有字母’x’,可以位于開頭温自，結(jié)尾或者字符串中間玄货。如果使用NOT關(guān)鍵字則可達(dá)到相反的效果。
A RLIKE B, A REGEXP B	STRING 類型	B是一個正則表達(dá)式悼泌，如果A與其匹配松捉，則返回TRUE；反之返回FALSE馆里。匹配使用的是JDK中的正則表達(dá)式接口實現(xiàn)的隘世，因為正則也依據(jù)其中的規(guī)則。例如鸠踪，正則表達(dá)式必須和整個字符串A相匹配丙者，而不是只需與其字符串匹配。

案例實操

hive (default)> select * from emp where sal =5000;
hive (default)> select * from emp where sal between 500 and 1000;
hive (default)> select * from emp where comm is null;
hive (default)> select * from emp where sal IN (1500, 5000);

2.2 Like和RLike

使用LIKE運算選擇類似的值
選擇條件可以包含字符或數(shù)字:

% 代表零個或多個字符(任意個字符)慢哈。

_ 代表一個字符蔓钟。
RLIKE子句是Hive中這個功能的一個擴(kuò)展，其可以通過Java的正則表達(dá)式這個更強大的語言來指定匹配條件卵贱。

案例實操

查找以2開頭薪水的員工信息
hive (default)> select * from emp where sal LIKE '2%';

查找第二個數(shù)值為2的薪水的員工信息
hive (default)> select * from emp where sal LIKE '_2%';

查找薪水中含有2的員工信息
hive (default)> select * from emp where sal RLIKE '[2]';

2.3 邏輯運算符（And/Or/Not）

操作符	含義
AND	邏輯并
OR	邏輯或
NOT	邏輯否

hive (default)> select * from emp where sal>1000 and deptno=30;
hive (default)> select * from emp where sal>1000 or deptno=30;
hive (default)> select * from emp where deptno not IN(30, 20);

3 分組

3.1 Group By語句

GROUP BY語句通常會和聚合函數(shù)一起使用滥沫，按照一個或者多個列隊結(jié)果進(jìn)行分組侣集，然后對每個組執(zhí)行聚合操作。

hive (default)> select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;
hive (default)> select t.deptno, t.job, max(t.sal) max_sal from emp t group by t.deptno, t.job;

3.2 Having語句

having與where不同點

（1）where針對表中的列發(fā)揮作用兰绣，查詢數(shù)據(jù)世分；having針對查詢結(jié)果中的列發(fā)揮作用，篩選數(shù)據(jù)缀辩。

（2）where后面不能寫分組函數(shù)臭埋，而having后面可以使用分組函數(shù)。

（3）having只用于group by分組統(tǒng)計語句臀玄。

案例實操

hive (default)> select deptno, avg(sal) from emp group by deptno;
hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

4 Join語句

4.1 等值 Join

Hive支持通常的SQL JOIN語句瓢阴，但是只支持等值連接，不支持非等值連接健无。

hive (default)> select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;

4.2 內(nèi)連接

內(nèi)連接：只有進(jìn)行連接的兩個表中都存在與連接條件相匹配的數(shù)據(jù)才會被保留下來荣恐。

hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;

4.3 左外連接

左外連接：JOIN操作符左邊表中符合WHERE子句的所有記錄將會被返回。

hive (default)> select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;

4.4 右外連接

右外連接：JOIN操作符右邊表中符合WHERE子句的所有記錄將會被返回累贤。

hive (default)> select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;

4.5 滿外連接

滿外連接：將會返回所有表中符合WHERE語句條件的所有記錄叠穆。如果任一表的指定字段沒有符合條件的值的話，那么就使用NULL值替代臼膏。

hive (default)> select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;

4.6 多表連接

注意：連接 n個表硼被，至少需要n-1個連接條件。例如：連接三個表渗磅，至少需要兩個連接條件嚷硫。

vim location.txt

1700    Beijing
1800    London
1900    Tokyo

創(chuàng)建位置表

create table if not exists default.location(
loc int,
loc_name string
)
row format delimited fields terminated by '\t';

導(dǎo)入數(shù)據(jù)

hive (default)> load data local inpath '/opt/module/datas/location.txt' into table default.location;

多表連接查詢

hive (default)>SELECT e.ename, d.deptno, l. loc_name
FROM   emp e 
JOIN   dept d
ON     d.deptno = e.deptno 
JOIN   location l
ON     d.loc = l.loc;

大多數(shù)情況下，Hive會對每對JOIN連接對象啟動一個MapReduce任務(wù)夺溢。本例中會首先啟動一個MapReduce job對表e和表d進(jìn)行連接操作论巍，然后會再啟動一個MapReduce job將第一個MapReduce job的輸出和表l進(jìn)行連接操作。

注意：為什么不是表d和表l先進(jìn)行連接操作呢风响？這是因為Hive總是按照從左到右的順序執(zhí)行的嘉汰。

4.7 笛卡爾積

笛卡爾集會在下面條件下產(chǎn)生

（1）省略連接條件

（2）連接條件無效

（3）所有表中的所有行互相連接

4.8 連接謂詞中不支持or

錯誤示例

hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno or e.ename=d.ename;

5 排序

5.1 全局排序（Order By）

Order By：全局排序，一個Reducer

1．使用 ORDER BY 子句排序

ASC（ascend）: 升序（默認(rèn)）

DESC（descend）: 降序

2．ORDER BY 子句在SELECT語句的結(jié)尾

3．案例實操

hive (default)> select * from emp order by sal;
hive (default)> select * from emp order by sal desc;

5.2 按照別名排序

hive (default)> select ename, sal*2 twosal from emp order by twosal;

5.3 多個列排序

hive (default)> select ename, deptno, sal from emp order by deptno, sal ;

5.4 每個MapReduce內(nèi)部排序（Sort By）

Sort By：每個Reducer內(nèi)部進(jìn)行排序状勤，對全局結(jié)果集來說不是排序鞋怀。

1．設(shè)置reduce個數(shù)

hive (default)> set mapreduce.job.reduces=3;

2．查看設(shè)置reduce個數(shù)

hive (default)> set mapreduce.job.reduces;

3．根據(jù)部門編號降序查看員工信息

hive (default)> select * from emp sort by empno desc;

4．將查詢結(jié)果導(dǎo)入到文件中（按照部門編號降序排序）

hive (default)> insert overwrite local directory '/opt/module/datas/sortby-result'
 select * from emp sort by deptno desc;

5.5 分區(qū)排序（Distribute By）

Distribute By：類似MR中partition，進(jìn)行分區(qū)持搜，結(jié)合sort by使用密似。

注意，Hive要求DISTRIBUTE BY語句要寫在SORT BY語句之前葫盼。

對于distribute by進(jìn)行測試残腌，一定要分配多reduce進(jìn)行處理，否則無法看到distribute by的效果。

案例實操：

先按照部門編號分區(qū)抛猫，再按照員工編號降序排序蟆盹。

hive (default)> set mapreduce.job.reduces=3;

hive (default)> insert overwrite local directory '/opt/module/datas/distribute-result' select * from emp distribute by deptno sort by empno desc;

5.6 Cluster By

當(dāng)distribute by和sorts by字段相同時，可以使用cluster by方式闺金。

cluster by除了具有distribute by的功能外還兼具sort by的功能逾滥。但是排序只能是升序排序，不能指定排序規(guī)則為ASC或者DESC败匹。

hive (default)> select * from emp cluster by deptno;

hive (default)> select * from emp distribute by deptno sort by deptno;

注意：按照部門編號分區(qū)寨昙，不一定就是固定死的數(shù)值，可以是20號和30號部門分到一個分區(qū)里面去掀亩。

6 分桶及抽樣查詢

6.1 分桶表數(shù)據(jù)存儲

分區(qū)針對的是數(shù)據(jù)的存儲路徑舔哪；分桶針對的是數(shù)據(jù)文件。

分區(qū)提供一個隔離數(shù)據(jù)和優(yōu)化查詢的便利方式槽棍。不過尸红，并非所有的數(shù)據(jù)集都可形成合理的分區(qū)，特別是要確定合適的劃分大小刹泄。

分桶是將數(shù)據(jù)集分解成更容易管理的若干部分的另一個技術(shù)。

一. 先創(chuàng)建分桶表怎爵，通過直接導(dǎo)入數(shù)據(jù)文件的方式

導(dǎo)入數(shù)據(jù)

vim student.txt

1001    ss1
1002    ss2
1003    ss3
1004    ss4
1005    ss5
1006    ss6
1007    ss7
1008    ss8
1009    ss9
1010    ss10
1011    ss11
1012    ss12
1013    ss13
1014    ss14
1015    ss15
1016    ss16

創(chuàng)建分桶表

create table stu_buck(id int, name string)
clustered by(id) 
into 4 buckets
row format delimited fields terminated by '\t';

查看表結(jié)構(gòu)

hive (default)> desc formatted stu_buck;
Num Buckets:            4

導(dǎo)入數(shù)據(jù)到分桶表中

hive (default)> load data local inpath '/opt/module/datas/student.txt' into table
 stu_buck;

發(fā)現(xiàn)還是只有一個分桶特石，并沒有分成4個桶

二. 創(chuàng)建分桶表時，數(shù)據(jù)通過子查詢的方式導(dǎo)入

? 1. 先建一個普通的stu表

create table stu(id int, name string)
row format delimited fields terminated by '\t';

向普通的stu表中導(dǎo)入數(shù)據(jù)
清空stu_buck表中數(shù)據(jù)
導(dǎo)入數(shù)據(jù)到分桶表鳖链，通過子查詢的方式
發(fā)現(xiàn)還是只有一個分桶
需要設(shè)置一個屬性

hive (default)> set hive.enforce.bucketing=true;
hive (default)> set mapreduce.job.reduces=-1;
hive (default)> insert into table stu_buck
select id, name from stu;

查詢分桶的數(shù)據(jù)

hive (default)> select * from stu_buck;
OK
stu_buck.id     stu_buck.name
1004    ss4
1008    ss8
1012    ss12
1016    ss16
1001    ss1
1005    ss5
1009    ss9
1013    ss13
1002    ss2
1006    ss6
1010    ss10
1014    ss14
1003    ss3
1007    ss7
1011    ss11
1015    ss15

6.2 分桶抽樣查詢

對于非常大的數(shù)據(jù)集姆蘸，有時用戶需要使用的是一個具有代表性的查詢結(jié)果而不是全部結(jié)果。Hive可以通過對表進(jìn)行抽樣來滿足這個需求芙委。

查詢表stu_buck中的數(shù)據(jù)逞敷。

hive (default)> select * from stu_buck tablesample(bucket 1 out of 4 on id);

注：tablesample是抽樣語句，語法：TABLESAMPLE(BUCKET x OUT OF y) 灌侣。

y必須是table總bucket數(shù)的倍數(shù)或者因子推捐。hive根據(jù)y的大小，決定抽樣的比例侧啼。例如牛柒，table總共分了4份，當(dāng)y=2時痊乾，抽取(4/2=)2個bucket的數(shù)據(jù)皮壁，當(dāng)y=8時，抽取(4/8=)1/2個bucket的數(shù)據(jù)哪审。

x表示從第幾個bucket開始抽取蛾魄，如果需要取多個分區(qū)，以后的分區(qū)號為當(dāng)前分區(qū)號加上y。例如滴须，table總bucket數(shù)為4舌狗，tablesample(bucket 1 out of 2)，表示總共抽让柘凇（4/2=）2個bucket的數(shù)據(jù)把夸，抽取第1(x)個和第3(x+y)個bucket的數(shù)據(jù)。

注意：x的值必須小于等于y的值铭污，否則

FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buck

7其他常用查詢函數(shù)

7.1空字段賦值

函數(shù)說明

NVL：給值為NULL的數(shù)據(jù)賦值恋日，它的格式是NVL( string1, replace_with)。它的功能是如果string1為NULL嘹狞，則NVL函數(shù)返回replace_with的值岂膳，否則返回string1的值，如果兩個參數(shù)都為NULL 磅网，則返回NULL谈截。
數(shù)據(jù)準(zhǔn)備：采用員工表

查詢：如果員工的comm為NULL，則用-1代替

hive (default)> select nvl(comm,-1) from emp;
OK
_c0
20.0
300.0
500.0
-1.0
1400.0
-1.0
-1.0
-1.0
-1.0
0.0
-1.0
-1.0
-1.0
-1.0

7.2 CASE WHEN

數(shù)據(jù)準(zhǔn)備

name	dept_id	sex
悟空	A	男
大海	A	男
宋宋	B	男
鳳姐	A	女
婷姐	B	女
婷婷	B

需求

求出不同部門男女各多少人涧偷。結(jié)果如下：
```
A     2       1
B     1       2
```

創(chuàng)建本地emp_sex.txt簸喂，導(dǎo)入數(shù)據(jù)

vi emp_sex.txt

悟空   A   男
大海   A   男
宋宋   B   男
鳳姐   A   女
婷姐   B   女
婷婷   B   女

創(chuàng)建hive表并導(dǎo)入數(shù)據(jù)

create table emp_sex(
name string, 
dept_id string, 
sex string) 
row format delimited fields terminated by "\t";
load data local inpath '/opt/module/datas/emp_sex.txt' into table emp_sex;

按需求查詢數(shù)據(jù)

select 
  dept_id,
  sum(case sex when '男' then 1 else 0 end) male_count,
  sum(case sex when '女' then 1 else 0 end) female_count
from 
  emp_sex
group by
  dept_id;

7.3 行轉(zhuǎn)列

相關(guān)函數(shù)說明

CONCAT(string A/col, string B/col…)：返回輸入字符串連接后的結(jié)果，支持任意個輸入字符串;

CONCAT_WS(separator, str1, str2,...)：它是一個特殊形式的 CONCAT()燎潮。第一個參數(shù)剩余參數(shù)間的分隔符喻鳄。分隔符可以是與剩余參數(shù)一樣的字符串。如果分隔符是 NULL确封，返回值也將為 NULL除呵。這個函數(shù)會跳過分隔符參數(shù)后的任何 NULL 和空字符串。分隔符將被加到被連接的字符串之間;

COLLECT_SET(col)：函數(shù)只接受基本數(shù)據(jù)類型爪喘，它的主要作用是將某字段的值進(jìn)行去重匯總颜曾，產(chǎn)生array類型字段。
數(shù)據(jù)準(zhǔn)備

name constellation blood_type

孫悟空白羊座 A

大海射手座 A

宋宋白羊座 B

豬八戒白羊座 A

鳳姐射手座 A

name	constellation	blood_type
孫悟空	白羊座	A
大海	射手座	A
宋宋	白羊座	B
豬八戒	白羊座	A
鳳姐	射手座	A

需求

把星座和血型一樣的人歸類到一起秉剑。結(jié)果如下：

射手座,A            大海|鳳姐
白羊座,A            孫悟空|豬八戒
白羊座,B            宋宋

創(chuàng)建本地constellation.txt泛豪，導(dǎo)入數(shù)據(jù)

vim constellation.txt
孫悟空  白羊座 A
大海        射手座    A
宋宋        白羊座    B
豬八戒    白羊座   A
鳳姐        射手座    A

創(chuàng)建hive表并導(dǎo)入數(shù)據(jù)

create table person_info(
name string, 
constellation string, 
blood_type string) 
row format delimited fields terminated by "\t";
load data local inpath “/opt/module/datas/person_info.txt” into table person_info;

按需求查詢數(shù)據(jù)

select
    t1.base,
    concat_ws('|', collect_set(t1.name)) name
from
    (select
        name,
        concat(constellation, ",", blood_type) base
    from
        person_info) t1
group by
    t1.base;

7.3列轉(zhuǎn)行

函數(shù)說明

EXPLODE(col)：將hive一列中復(fù)雜的array或者map結(jié)構(gòu)拆分成多行。

LATERAL VIEW

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解釋：用于和split, explode等UDTF一起使用秃症，它能夠?qū)⒁涣袛?shù)據(jù)拆成多行數(shù)據(jù)候址，在此基礎(chǔ)上可以對拆分后的數(shù)據(jù)進(jìn)行聚合。
數(shù)據(jù)準(zhǔn)備

movie category

《疑犯追蹤》懸疑,動作,科幻,劇情

《Lie to me》懸疑,警匪,動作,心理,劇情

《戰(zhàn)狼2》戰(zhàn)爭,動作,災(zāi)難

movie	category
《疑犯追蹤》	懸疑,動作,科幻,劇情
《Lie to me》	懸疑,警匪,動作,心理,劇情
《戰(zhàn)狼2》	戰(zhàn)爭,動作,災(zāi)難

需求

將電影分類中的數(shù)組數(shù)據(jù)展開种柑。結(jié)果如下：

《疑犯追蹤》      懸疑
《疑犯追蹤》      動作
《疑犯追蹤》      科幻
《疑犯追蹤》      劇情
《Lie to me》   懸疑
《Lie to me》   警匪
《Lie to me》   動作
《Lie to me》   心理
《Lie to me》   劇情
《戰(zhàn)狼2》        戰(zhàn)爭
《戰(zhàn)狼2》        動作
《戰(zhàn)狼2》        災(zāi)難

創(chuàng)建本地movie.txt岗仑，導(dǎo)入數(shù)據(jù)

vi movie.txt
《疑犯追蹤》   懸疑,動作,科幻,劇情
《Lie to me》  懸疑,警匪,動作,心理,劇情
《戰(zhàn)狼2》    戰(zhàn)爭,動作,災(zāi)難

創(chuàng)建hive表并導(dǎo)入數(shù)據(jù)

create table movie_info(
    movie string, 
    category array<string>) 
row format delimited fields terminated by "\t"
collection items terminated by ",";

load data local inpath "/opt/module/datas/movie.txt" into table movie_info;

按需求查詢數(shù)據(jù)

select
    movie,
    category_name
from 
    movie_info lateral view explode(category) table_tmp as category_name;

7.4窗口函數(shù)

相關(guān)函數(shù)說明

OVER()：指定分析函數(shù)工作的數(shù)據(jù)窗口大小，這個數(shù)據(jù)窗口大小可能會隨著行的變而變化

CURRENT ROW：當(dāng)前行

n PRECEDING：往前n行數(shù)據(jù)

n FOLLOWING：往后n行數(shù)據(jù)

UNBOUNDED：起點聚请，UNBOUNDED PRECEDING 表示從前面的起點荠雕， UNBOUNDED FOLLOWING表示到后面的終點

LAG(col,n)：往前第n行數(shù)據(jù)

LEAD(col,n)：往后第n行數(shù)據(jù)

NTILE(n)：把有序分區(qū)中的行分發(fā)到指定數(shù)據(jù)的組中稳其，各個組有編號，編號從1開始炸卑，對于每一行既鞠，NTILE返回此行所屬的組的編號。注意：n必須為int類型盖文。

數(shù)據(jù)準(zhǔn)備：name嘱蛋，orderdate，cost

jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94

需求

（1）查詢在2017年4月份購買過的顧客及總?cè)藬?shù)

（2）查詢顧客的購買明細(xì)及月購買總額

（3）上述的場景,要將cost按照日期進(jìn)行累加

（4）查詢顧客上次的購買時間

（5）查詢前20%時間的訂單信息
創(chuàng)建本地business.txt五续，導(dǎo)入數(shù)據(jù)
```
vi business.txt
```

創(chuàng)建hive表并導(dǎo)入數(shù)據(jù)

create table business(
name string, 
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

load data local inpath "/opt/module/datas/business.txt" into table business;

按需求查詢數(shù)據(jù)

（1）查詢在2017年4月份購買過的顧客及總?cè)藬?shù)

select name,count(*) over () from business where substring(orderdate,1,7) = '2017-04' group by name;

（2）查詢顧客的購買明細(xì)及月購買總額

select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from business;

（3）上述的場景,要將cost按照日期進(jìn)行累加

select name,orderdate,cost, 
sum(cost) over() as sample1,--所有行相加 
sum(cost) over(partition by name) as sample2,--按name分組洒敏，組內(nèi)數(shù)據(jù)相加 
sum(cost) over(partition by name order by orderdate) as sample3,--按name分組，組內(nèi)數(shù)據(jù)累加 
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,--和sample3一樣,由起點到當(dāng)前行的聚合 
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, --當(dāng)前行和前面一行做聚合 
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,--當(dāng)前行和前邊一行及后面一行 
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --當(dāng)前行及后面所有行 
from business;

（4）查看顧客上次的購買時間

select name,orderdate,cost, 
lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1, lag(orderdate,2) over (partition by name order by orderdate) as time2 
from business;

（5）查詢前20%時間的訂單信息

select * from (
    select name,orderdate,cost, ntile(5) over(order by orderdate) sorted
    from business
) t
where sorted = 1;

7.5 Rank

函數(shù)說明

RANK() 排序相同時會重復(fù)疙驾，總數(shù)不會變

DENSE_RANK() 排序相同時會重復(fù)凶伙，總數(shù)會減少

ROW_NUMBER() 會根據(jù)順序計算

數(shù)據(jù)準(zhǔn)備

name	subject	score
孫悟空	語文	87
孫悟空	數(shù)學(xué)	95
孫悟空	英語	68
大海	語文	94
大海	數(shù)學(xué)	56
大海	英語	84
宋宋	語文	64
宋宋	數(shù)學(xué)	86
宋宋	英語	84
婷婷	語文	65
婷婷	數(shù)學(xué)	85
婷婷	英語	78

需求

計算每門學(xué)科成績排名。

創(chuàng)建本地movie.txt它碎，導(dǎo)入數(shù)據(jù)
```
 vim score.txt
```

創(chuàng)建hive表并導(dǎo)入數(shù)據(jù)

create table score(
name string,
subject string, 
score int) 
row format delimited fields terminated by "\t";
load data local inpath '/opt/module/datas/score.txt' into table score;

按需求查詢數(shù)據(jù)

select name,
subject,
score,
rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) drp,
row_number() over(partition by subject order by score desc) rmp
from score;

name    subject score   rp      drp     rmp
孫悟空  數(shù)學(xué)    95      1       1       1
宋宋    數(shù)學(xué)    86      2       2       2
婷婷    數(shù)學(xué)    85      3       3       3
大海    數(shù)學(xué)    56      4       4       4
宋宋    英語    84      1       1       1
大海    英語    84      1       1       2
婷婷    英語    78      3       2       3
孫悟空  英語    68      4       3       4
大海    語文    94      1       1       1
孫悟空  語文    87      2       2       2
婷婷    語文    65      3       3       3
宋宋    語文    64      4       4       4

Hive 系列 - DQL 查詢數(shù)據(jù)

1 基本查詢（Select…From）

1.1 全表和特定列查詢

1.2 列別名

1.3 算術(shù)運算符

1.4 常用函數(shù)

1.5 Limit語句

2 Where語句

2.1 比較運算符（Between/In/ Is Null）

2.2 Like和RLike

2.3 邏輯運算符（And/Or/Not）

3 分組

3.1 Group By語句

3.2 Having語句

4 Join語句

4.1 等值 Join

4.2 內(nèi)連接

4.3 左外連接

4.4 右外連接

4.5 滿外連接

4.6 多表連接

4.7 笛卡爾積

4.8 連接謂詞中不支持or

5 排序

5.1 全局排序（Order By）

5.2 按照別名排序

5.3 多個列排序

5.4 每個MapReduce內(nèi)部排序（Sort By）

5.5 分區(qū)排序（Distribute By）

5.6 Cluster By

6 分桶及抽樣查詢

6.1 分桶表數(shù)據(jù)存儲

6.2 分桶抽樣查詢

7其他常用查詢函數(shù)

7.1空字段賦值

7.2 CASE WHEN

7.3 行轉(zhuǎn)列

7.3列轉(zhuǎn)行

7.4窗口函數(shù)

7.5 Rank