柚子快報邀請碼778899分享：hadoop Hive sql

http://yzkb.51969.com/

DML-Load加載數(shù)據(jù)：

load data [local] inpath 'filepath' [overwrite] into table tablename

[partition(partcol1=val1,partcol2=val2...)]

有l(wèi)ocal就在本地文件系統(tǒng)

沒有l(wèi)ocal就在hdfs分布式文件系統(tǒng)

如果有overwrite關鍵字，則目標表（或者分區(qū)）中的已經(jīng)存在的數(shù)據(jù)就會被刪除，然后再將filepath指向的文件/目錄中的內(nèi)容添加到表/分區(qū)表中。

將數(shù)據(jù)文件插入到分區(qū)表中，但是不指定分區(qū)的情況：

在這種load不指定分區(qū)的情況下，會將load轉變?yōu)閕nsert as select。

DML-insert使用方式：

insert+select表示：將后面查詢返回的結果作為內(nèi)容插入到指定表當中，注意overwrite將覆蓋已有數(shù)據(jù)。

1.需要保證查詢結果列和數(shù)目和需要插入數(shù)據(jù)表格的列數(shù)據(jù)一致。

2.如果查詢出來的數(shù)據(jù)類型和插入表格對應的列數(shù)據(jù)類型不一致，將會進行轉換，但是不能保證轉換一定成功，轉換失敗的數(shù)據(jù)將會為null。

insert overwrite table 和 insert into table。

動態(tài)分區(qū)插入：（先設置成非嚴格模式）

set hive.exec.dynamic.partition = true;

set hive.exec.dynamic.partition.mode = nonstrict;

???????

select語法：

select [ALL|DISTINCT] select_expr,select_expr,....

from table

[where where_condition]

[group by col_list]

[order by col_list]

[

cluster by col_list | [distribute by col_list][sort by col_list]

]?

[limit [offset,] rows];

1. select_expr的4種可能性：

查詢所有字段或者指定字段

select * from t_usa_covid19_p;

select country,cases,deaths from t_usa_covid19_p;

查詢匹配正則表達式的所有字段

SET hive.support.quoted.identifiers = none;

select '^c.*' from t_usa_covid19_p;;

查詢當前數(shù)據(jù)庫(省去from關鍵字)

select current_database();

查詢使用函數(shù)

select count(country) from t_usa_covid19_p;

2.ALL和DISTINCT：

在select中默認是all，代表我們返回所有匹配的行。

如果是distinct的話，就代表是去重。

3.where：

在where中不能使用聚合函數(shù)，因為使用聚合函數(shù)的前提是已經(jīng)由where進行篩選之后，獲得了確定的結果集之后的，才能再使用聚合函數(shù)。

select * from t_usa_covid19_p where 1>2;

這個where的結果是false，如果是false的話，那么前面的select就不會執(zhí)行，所以這個語句的查詢結果就是空集。

select * from t_usa_covid19_p where 1=1;

這個where的結果是true，所以前面的select就會執(zhí)行。

select * from t_usa_covid19_p where length(state)>10;

這個就會找到所有state的長度大于10的結果。

select * from A where A.a IN (select foo from B);

where子句支持子查詢。

4.分區(qū)查詢：

where的條件可以用來做分區(qū)查詢，提高查詢效率。

5.group by概念：

group by語句用于結合聚合函數(shù)，根據(jù)一個或者多個列對結果集進行分組。

但是出現(xiàn)在group by中select_expr的字段：要么是group by分組的字段，要么是被聚合函數(shù)應用的字段。

6.having：

在sql中增加having子句的原因是，where無法和聚合函數(shù)一起使用，那么having負責的是在group by分組之后的篩選結果集的任務。

也就是先執(zhí)行where，然后再分組，然后再用having進行二次篩選。

where是在分組前過濾，having在分組后過濾。

7.limit：

limit 5，代表從偏移量0開始返回5行數(shù)據(jù)。

limit 2，3 代表從偏移量2（也就是第3行）開始返回3行數(shù)據(jù)。

8.執(zhí)行順序：

在查詢過程中執(zhí)行順序：

from>where>group by>having>order>select;

9.order by:

Hive中的order by語法類似于標準sql語言中的order by語法，會對輸出的結果進行全局排序。因此當?shù)讓邮褂肕apReduce引擎執(zhí)行的時候，只會有一個reducetask執(zhí)行，如果輸出的行數(shù)太大了，就會導致需要很長的時間來完成。

asc升序，desc降序。

10.cluster by：

根據(jù)指定字段將數(shù)據(jù)分組，然后再在組內(nèi)進行排序，而且只能是正序排序。

說白了就是根據(jù)同一個字段，分且排序。

兩個例子：

1.

select * from student cluster by num;

這個就是分為一組去排序。因為默認是1。

2.

先設置reducetask數(shù)量為2。

set mapreduce.job.reduces = 2;

select * from student cluster by num;

但是其實這也是他的局限性，如果我分組和排序的字段不是同一個字段，那么cluster by就無法處理了，而且局限性還有一個，那就是只能是升序排序。

11.distribute by+order by：

distribute管的是分組，order by管的是排序。

那么其實distribute by+order by就是相當于cluster by的。

總結一下，其實就是order by只能是全局排序，也就是reducetask只能有1個，不管你自己設置的mapreduce.job.reduces = 多少，只能是1。那么cluster by就是多了一個分組的功能，但是分組和排序的字段只能是1個，而且只能是升序排序。distribute by可以管分組，但是不管排序；sort by管分組后的排序，但是不管分組。如果將distribute by 和 sort by合起來使用就能夠做到，又分組又排序。

12.union聯(lián)合查詢：

用于將多個select語句的結果合并為一個結果集。

union all 不去重，所有結果集加在一起。

union distinct 去重，所有結果集加在一起。

如果union后面不加all，那就是默認distinct，自動會去重的。

如果想要針對其中的select語句設置limit參數(shù)，那就必須如下操作：

如下就是，先聯(lián)合，然后limit再生效。

13.from子句中子查詢：

這里面的tmp 其實是給“select num,name from student_local”這個句子命名的

14.where子句中子查詢：

where子句支持的子查詢：

1.不相關子查詢：該子查詢不引用父查詢中的列，可以將查詢結果視為IN和NOT IN語句的常量。

select *

from t1

where t1.num IN (select num from t2 limit 2);

子查詢根本沒有涉及到父查詢中的表和字段。

2.相關子查詢：子查詢引用父查詢中的列。

select A

from t1

where exists (select B from t2 where t1.X?= t2.Y);

在這里，子查詢引用了父查詢中的字段，這個就叫相關子查詢。

關于相關子查詢，其實是這樣的，會先執(zhí)行select A from t1這個操作，也就是先執(zhí)行父查詢這個操作，然后會逐個的到子查詢的集合中去檢查是否存在這個父查詢的數(shù)據(jù)，如果子查詢的集合中存在我這個父查詢的條件，那么我就返回這行數(shù)據(jù)。但是在不相關子查詢中，會先執(zhí)行子查詢的結果集，然后再根據(jù)子查詢?nèi)ハ拗聘覆樵兊慕Y果。

in語句，實際上是先計算子查詢的，是把外表和內(nèi)表作hash連接。

exists語句，實際上是對外表做loop循環(huán)，每次loop循環(huán)再去對內(nèi)表進行查詢。也就是外表一行一行的把數(shù)據(jù)拿出來跟內(nèi)表去比對。這樣就不用去循環(huán)內(nèi)表了，因為內(nèi)表比較大。

子查詢大的用exists，子查詢小的用in。

15.CTE（common table expressions）：

公用表表達式（CTE）是一個臨時結果集：該結果集是從WITH子句中指定的簡單查詢派生而來的，緊接在SELECT或者INSERT關鍵字之前。

CTE僅在單個語句的執(zhí)行范圍內(nèi)定義。

CTE可以在SELECT，INSERT，CREATE TABLE AS SELECT 或者CREATE VIEW AS SELECT語句中使用。

例子：

with q1 as (select num,name,age from student where num = 95002)

select *

from q1;

with q1 as (select num,name,age from student where num = 95002)

from q1

select *;

CTEs鏈式

with ?q1 ?as(select * from student where num = 95002),

? ? ? ? ?q2 ?as(select num,name,age from q1)

select * from (select num from q2) a;

with q1 as(select * from student where num = 95002)

insert overwrite table s1

select * from q1;

16.6種join語法：

inner join（內(nèi)連接）：

left join（左連接）：

right join（右連接）：

full outer join（全外連接）：可以是full join

left semi join（左半開連接）：會返回左邊表的記錄，前提是其記錄對于右邊的表滿足on語句中的判定條件。相當于是inner join，但是只返回左表的信息。

cross join（交叉連接，也叫做笛卡爾乘積）：兩個表的行數(shù)乘積。

Hive內(nèi)置運算符：

show functions ?顯示所有的函數(shù)和運算符

describe function count 查看運算符或者函數(shù)的使用說明

describe function extended count 使用extended可以查看更加詳細的使用說明

關系運算符：

關系運算符是二元運算符，執(zhí)行的是兩個操作數(shù)的比較運算。

每個關系運算符都返回boolean類型結果（true或者falze）

舉例子：

null 或者not null

select 1 from dual where 'itcast' is null; //這個語句where的條件是false，所以不會執(zhí)行。

select 1 from dual where 'itcast' is not null; //這個就返回1，寫死在這里的。

--like比較：_表示任意單個字符 %表示任意數(shù)量字符。

--否定比較：NOT A like B。

select 1 from dual where 'itcast' like 'it_'; ?//后面的_代表只有一個字符，所以返回空

select 1 from dual where 'itcast' like 'it%'; //后面的%代表可以自由匹配幾個字符都行，所以返回1。

select 1 from dual where 'itcast' not like 'hadoo_'; //返回1，not like就是不匹配

select 1 from dual where not 'itcast' like 'hadoo_'; //也返回1，因為不匹配

--rlike：確定字符串是否匹配正則表達式，是REGEXP_LIKE()的同義詞。

select 1 from dual where 'itcast' rlike '^i.*t$';

select 1 from dual where '123456' rlike '^\\d+$'; --判斷是否全為數(shù)字。

select 1 from dual where '123456aa' rlike '^\\d+$'; --明顯不都是數(shù)字。

算術運算符：

算術運算符操作數(shù)必須是數(shù)值類型。分為一元運算符和二元運算符：

一元運算符，只有一個操作數(shù)；

二元運算符有兩個操作數(shù)，運算符在兩個操作數(shù)之間。

--取整操作：div 給出將A除以B所得的整數(shù)部分。

select 17 div 3； --結果就是5

--取余操作：也叫做取模操作

select 17 % 3； --結果就是2

--位與操作： & A和B按位進行與操作的結果。

select 4 & 8 from dual；--4轉換二進制：0100 8轉換二進制：1000 結果是0

select 6 & 4 from dual；--4轉換二進制：0100 6轉換二進制：0110 結果是4

--位或操作：^ A和B按位進行異或操作的結果 異或表示兩者的值不同，則結果為1。

select 4 ｜?8 from dual; ?--4轉換二進制： 0100 8轉換二進制： 1000 結果是 12

select 6 ｜?4 from dual; ?--6轉換二進制： 0110 ?4轉換二進制： 0100 結果是 6

--位異或操作：^ A和B按位進行異或操作的結果 異或表示兩者的值不同，則結果為1

select 4 ^?8 from dual; ?--4轉換二進制： 0100 8轉換二進制： 1000 結果是 12

select 6 ^?4 from dual; ?--6轉換二進制： 0110 ?4轉換二進制： 0100 結果是 2

邏輯運算符：

???????--與操作： A and B 如果A和B均為true，則為true，否則為false。如果A或者B為NULL，則為NULL。

select 1 from dual where 3>1 and 2>1; ?//結果為1

--或操作：A or B 如果A或B或兩者均為TRUE，則為TRUE，否則為false。

select 1 from dual where 3>1 or 2!=2; // 結果為1

--非操作：NOT A , !A. 如果A為false，則為true，如果A為NULL，則為NULL，否則為FALSE。

select 1 from dual where not 2>1; //返回結果是空。

select 1 from dual where !2=1; //。!2=1 和 not 2=1的結果其實是等價的。

--在：A IN（val1,val2,...）如果A等于任何值，則為TRUE。

select 1 from dual where 11 in (11,22,33); //結果為1

--不在：A NOT IN（val1,val2,...）如果A不等于任何值，則為TRUE。

select 1 from dual where 11 not in(22,33,44); //結果為true，因為11不在這里面。

--邏輯是否存在：[NOT] EXISTS (subquery)

--將主查詢的數(shù)據(jù)，放到子查詢中做條件驗證，根據(jù)驗證結果（TRUE或者FALSE）來決定主查詢的數(shù)據(jù)結果是否得以保留。相關子查詢。

select A.* from A where exists (select B.id from B where A.id = B.id);

Hive的函數(shù)：

內(nèi)置函數(shù)（built-in Functions）：

數(shù)學類型函數(shù)：

--取整函數(shù)

select round(3.1415926);

--指定精度取整函數(shù)：round(double a,int d) //返回指定精度d的double類型。

--向下取整函數(shù)：

select floor(3.1415926); //3

select floor(-3.1415926); //-4

--向上取整函數(shù)：

select ceil(3.1415926);

select ceil(-3.1415926);

--取隨機數(shù)函數(shù)：rand 每次執(zhí)行都不一樣 返回一個0到1范圍內(nèi)的隨機數(shù)

select rand();

--指定種子取隨機數(shù)函數(shù)：rand(int seed) 得到一個穩(wěn)定的隨機數(shù)序列

select rand(2);

日期類型函數(shù)：

--獲取當前日期：

select current_date();

--獲取當前時間戳：

select current_timestamp();

--獲取當前unix時間戳函數(shù)：unix_timestamp

select unix_timestamp(); ? //此時此刻所對應的時間戳。

--日期轉unix時間戳函數(shù)：unix_timestamp

select unix_timestamp("2011-12-07 13:01:03"); ?//這個時間的時間戳。

--指定格式日期轉unix時間戳函數(shù)：unix_timestamp

select unix_timestamp('2011120713:01:03','yyyyMMdd HH:mm:ss');

--unix時間戳轉日期函數(shù)：from_unixtime

select from_unixtime(xxxx);

select from_unixtime(0,'yyyy-MM-dd HH:mm:ss'); ?//結果為1970-01-01 00:00:00

--日期比較函數(shù)：datediff 日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'

select datediff('2012-12-08','2012-05-09'); ?//顯示相差的結果：213

--日期增加函數(shù)：date_add('2012-02-28',10); ? //結果為2012-02-18

--日期減少函數(shù)：date_sub('2012-01-1',10); ?//結果為2011-12-22

字符串函數(shù)：

describe function extended concat；

--拼接函數(shù)：

select concat("angela","baby");?

--規(guī)定分隔符的拼接函數(shù)：

select concat_ws('.','www',array('itcast','cn'));?

--字符串截取函數(shù)：

select?substr("angelababy",2); //ngelababy是從2開始截取的。

select substr("angelababy",-2)；//pos是從1開始的索引，如果為負數(shù)則倒著數(shù)，結果為by。

select substr("angelababy",2,2); ?//結果為ng。

--正則表達式替換函數(shù)：regexp_replace(str,regexp,rep)

select regexp_replace('100-200','(\\d+)','num'); ?結果為num-num。

--正則表達式解析函數(shù)：regexp_extract(str,regexp[,idx])提取正則匹配到的指定組內(nèi)容。

select regexp_extract('100-200','(\\d+)-(\\d+)',2); ?結果為200。

-URL解析函數(shù)：parse_url 一次解析出多個，可以使用parse_url_tuple這個UDTF函數(shù)。

select parse_url('http://www.itcast.cn/path/p1.php?query=1','HOST');

-分割字符串函數(shù)：split(str,regex);

select split('apache hive','\\s+'); ?//記住\\s+代表任意的空白字符。拆出來的結果是一個數(shù)組。

-返回最后一個有數(shù)值的分區(qū)的函數(shù)：max_pt(table_full_name)

select * from table_name where pt = max_pt(table_name);

這樣就會返回最后一個有數(shù)據(jù)的一級分區(qū)。

-拆分函數(shù)：split_part(拆分字段，拆分的標識符，從哪段開始拆分，要前幾段)

split_part(ip,'.',1,3)；這個例子如果ip是234.213.32.34的話，那么最后的答案就應該是234.213.32。

-date_format(date,pattern);

將時間變成我們想要的格式。

date_format(2018-09-21,MMMM); September（九月份）。

???????

集合函數(shù)：

用的少，如果需要用直接去視頻里面看就行。

條件函數(shù)：

???????if條件判斷：if（boolean testCondition, T valueTrue, T valueFalseOrNull）

select if(1=2,100,200);

select if(sex='男','M','W') from student limit 3; //完成了從中文到字符的一個替換過程。

--空判斷函數(shù)：isnull();

select isnull("allen");

--非空判斷函數(shù)：isnotnull();

select isnotnull("allen");

select isnotnull(null);

--空值轉換函數(shù)：nvl(T value,T default_value)

select nvl("allen","itcast"); ?如果第一個不為空，那就是第一個值。

select nvl(null,"itcast"); 如果第一個是空的，那么就要第二個值。

--非空查找函數(shù)：COALESCE(T v1, T v2, ...);

--返回參數(shù)中的第一個非空值；如果所有值都為NULL，那么返回NULL。

select COALESCE(null,11,22,33); ?//11

select COALESCE(null,null.null,33); ? //33

select COALESCE(null,null,null); ?//null

--nullif(a,b)

--如果a=b，則返回null，否則返回第一個

select nullif(11,11); ?// null

select nullif(11,22); ?//11

--條件轉換函數(shù)：

case a when b then c [when d then e] * [else f] end;

select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end; ?// mary

select case sex when '男' then 'male' else 'female' end from student limit 3; ?

// male female female

類型轉換函數(shù)：//任意數(shù)據(jù)類型之間顯式的轉換：cast

select cast(12.14 as bigint); ?//12

select cast(12.14 as string); ?// '12.14'

select cast("hello" as int); // null 結果出來就是空的。

數(shù)據(jù)脫敏函數(shù)：主要完成對數(shù)據(jù)脫敏轉換功能，屏蔽原始數(shù)據(jù)，主要如下：

mask:查詢回的數(shù)據(jù)，大寫字母轉換為X，小寫字母轉換為x，數(shù)字轉換為n。

select mask("abc123DEF"); ?//xxxnnnXXX

select mask("abc123DEF", '-' , '.' , '^'); ?

//大寫字母替換成-，小寫字母替換成. ，數(shù)字替換為^。

//...^^^---

mask_first_n(string str[, int n]):對于前幾個進行脫敏替換。

select mask_first_n("abc123DEF",4); //xxxn23DEF。

mask_last_n(string str[, int n]);

select mask_last_n("abc123DEF",4); //abc12nXXX。

mask_show_first_n(string str[, int n]); //除了前4個，其余都進行脫敏操作。

select mask_show_first_n("abc123DEF",4); //abc1nnXXX。

mask_show_last_n(string str[, int n]); //除了后4個，其余都進行脫敏操作。

select mask_show_last_N("abc123DEF",4); //xxxnn3DEF。

mask_hash(string|char|varchar str); //返回字符串的hash編碼。

select mask_hash("abc123DEF");

其余雜項函數(shù)：

哈希加密：取哈希值

select hash("allen");

SHA-1加密：sha1(string/binary)

select sha1("allen");

SHA-2家族算法加密：sha2(string/binary, int) (SHA-224,SHA-256,SHA384,SHA-512)

select sha2("allen",224);

select sha2("allen",512);

crc32加密：

select crc32("allen");

MD5加密：md5(string/binary)

select md5("allen");

用戶定義函數(shù)（User-Defined Functions）：

現(xiàn)在的范圍是所有的函數(shù)，不一定是用戶自定義的。

UDF：普通函數(shù)，一進一出。

UDAF：聚合函數(shù)，多進一出。

select sex from student;

select collect_set(sex) from student;

select collect_list(sex) from student;???????

UDTF：表生成函數(shù)，一進多出。

explode函數(shù)：只能接受array和map這兩種類型的函數(shù)。

select explode('array'(11,22,33,44,55));

select explode('map'("id",10086,"name","allen","age",18)); ?//3個元素。

???????Lateral View是一種特殊的語法，主要搭配UDTF類型函數(shù)一起使用，用于解決UDTF函數(shù)的一些查詢限制的問題。

只要是使用UDTF函數(shù)，就會固定搭配Lateral View使用。

舉例子：

舉例子：UDAF函數(shù)，也就是聚合函數(shù)。

--count(*):所有行進行統(tǒng)計，包括NULL行。

--count(1):所有行進行統(tǒng)計，包括NULL行。

--count(column):對column中非NULL進行統(tǒng)計（排除掉null）。

統(tǒng)計男生的數(shù)量第一種方法：select sum(if(sex='男',1,0);

統(tǒng)計男生的數(shù)量第二種方法：select sum(case when sex='男' ?then 1 else 0 end);

聚合函數(shù)不支持嵌套聚合函數(shù)：

select avg(count(*)) from student;

val1 ? ?val2

2 ? ? ? ? ? 3

1 ? ? ? ? ? ?2

null ? ? ? ?2

select sum(val1),sum(val1+val2) from tmp_1;// 3 8

sum在計算的時候val如果一個為null那就自動舍棄。

select

? ? sum(coalesce(val1,0)),

? ? sum(coalesce(val1,0)+val2)

from tmp_1;

下面這個的意思就是分成男女性別兩組，然后max函數(shù)是自動計算struct里面第一個字段的最大值，所以也就是找出男女里面分別年齡最大的和他們的名字。

select

? ? ?sex,

? ? ?max(struct(age,name)).col1 as age,

? ? ?max(struct(age,name)).col2 as name

from student

group by sex;

增強聚合--grouping sets

select?

?? ? month,

?? ? day,

?? ? count(distinct cookieid) as nums,

? ? ?grouping_id

from cookie_info

group by month,day

grouping sets(month,day)

order by grouping__id

窗口函數(shù)：

不光是sum，還可以是avg，min，max等。

select cookieid,createtime,pv,sum(pv) over() as total_pv

from website_pv_info;

select cookieid,createtime,pv,sum(pv) over(partition by cookieid) as total_pv

from website_pv_info;

select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime) as current_total_pv

from website_pv_info;

窗口表達式：windows expression

select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime) as pv1

from website_pv_info;

select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv 2

from website_pv_info;?

這兩個其實是一個效果，都是從第一行到當前行。

select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and current row) as pv4

from website_pv_info;

select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5

???????from website_pv_info;

select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between current row?and unbounded following) as pv6

???????from website_pv_info;

?窗口排序函數(shù)--row_number家族(包括dense_rank,rank)：

select?

? ? ? ?cookieid,

? ? ? ?createtime,

? ? ? ?pv,

? ? ? ?RANK() over(partition by cookieid order by pv desc) as rn1, //rank

? ? ? ?DENSE_RANK() over(partition by cookieid order by pv desc) as rn2, //dense_rank

? ? ? ?ROW_NUMBER() over(partition by cookieid order by pv desc) as rn3 //row_number

from website_pv_info

where cookieid = 'cookie1';?

適合TopN業(yè)務分析

select *

from(

select cookieid1,createtime,pv,row_number() over(partition by cookieid order by pv desc) as rn1?

from website_pv_info???????

) tmp

where tmp.rn1<=3;

窗口排序函數(shù)--ntile:

將每個分組內(nèi)的數(shù)據(jù)分為指定的若干個桶里（分為若干個部分），并且為每一個桶分配一個桶編號。

如果不能平均分配，則優(yōu)先分配較小編號的桶，并且各個桶中能放的行數(shù)最多相差1.

有時會有這樣的需求，如果數(shù)據(jù)排序后分為三個部分，業(yè)務人員只關心其中的一部分，如何將這中間的三分之一數(shù)據(jù)拿出來呢？NTILE函數(shù)的作用就體現(xiàn)在這里。

select *

from(

select?cookieid,createtime,pv,ntile(3) over(partition by cookieid order by createtime) as rn2

from website_pv_info???????

) tmp

where tmp.rn2 = 1;//這就是前三分之一的部分，也就是第一部分。

------------------------------------------------------------------------------

select *

from(

select?cookieid,createtime,pv,ntile(3) over(partition by cookieid order by createtime) as rn2

from website_pv_info???????

) tmp

where tmp.rn2 = 2;//這就是中間那個部分，也就是第二部分。

-------------------------------------------------------------------------------

select *

from(

select?cookieid,createtime,pv,ntile(3) over(partition by cookieid order by createtime) as rn2

from website_pv_info???????

) tmp

where tmp.rn2 = 3;//這就是最后那個部分，也就是第三部分。

總而言之，NTILE(x)可以將數(shù)據(jù)分成x個部分。

窗口分析函數(shù)：

LAG():用于統(tǒng)計窗口內(nèi)往上第n行值

LEAD():用于統(tǒng)計窗口內(nèi)往下第n行值

FIRST_VALUE():取分組內(nèi)排序后，截止到當前行，第一個值

LAST_VALUE():取分組內(nèi)排序后，截止到當前行，最后一個值

抽樣函數(shù)：

三種方式：

1.隨機抽樣?：使用rand()函數(shù)來確保隨機獲取數(shù)據(jù)，limit來限制抽取的數(shù)據(jù)個數(shù)。

速度有點慢，尤其是在表多的時候。

2.基于數(shù)據(jù)塊抽樣：

Block塊采樣允許隨機獲取n行數(shù)據(jù)，百分比數(shù)據(jù)或指定大小的數(shù)據(jù)。

但是缺點就是不隨機。

3.基于分桶表抽樣

針對分桶表進行了優(yōu)化，優(yōu)點是既隨機速度也很快。

TABLESAMPLE（BUCKET * OUT OF ?y ?[ON colname]）--回頭再詳細了解吧，用的不多。

柚子快報邀請碼778899分享：hadoop Hive sql

http://yzkb.51969.com/

相關文章