Hive 數(shù)據(jù)類型

基本數(shù)據(jù)類型

注：Hive會隱式地將類型轉(zhuǎn)換為兩個整型類型中值較大的那個類型；

集合數(shù)據(jù)類型

示例（用類似JSON的結(jié)構(gòu)描述）：

{
    "names": ["Smith" , "Jones"] ,   //列表Array, names[1]="Jones"
    "deductions": {                  //鍵值Map, deductions[’Federal’]=0.2
        "Federal": 0.2 ,
        "State": 0.05,
        "Insurance": 0.1
    }
    "address": {                     //結(jié)構(gòu)Struct, address.city=”Chicago”
        "street": "1 Michigan Ave." ,
        "city": "Chicago" ,
        "state": "IL" ,
        "zip": 60600
    }
}

在Hive中實(shí)際存儲形式：

Smith_Jones,Federal:0.2_State:0.05_Insurance:0.1,Michigan Ave._Chicago_1L_60600
Jan_Ketty,Federal:0.2_State Taxes:0.05_Insurance:0.1,Guang dong._China_0.5L_60661

注：MAP,STRUCT和ARRAY里的元素間關(guān)系都可以用同一個字符表示，這里用“_”

取數(shù)：

select names[1], deductions['Federal'],address.city
from learn.employees;
------
OK
Jones     0.2    Chicago
Ketty    0.2    China
Time taken: 0.123 seconds, Fetched: 2 row(s)

文本文件數(shù)據(jù)編碼

Hive中默認(rèn)的記錄和字段分隔符

在創(chuàng)建表格的時候，可規(guī)定分隔符：

create table employee(
name            string,
salary          float,
subordinates    array<string>,
deductions      map<string, float>,
address         struct<street:string, city:string, state:string, zip:int>
)
row format delimited
fields terminated by '\001'    //列分隔符
collection items terminated by '\002'               //集合元素間的分隔符
map keys terminated by '\003'                      //map 鍵值對的分隔符
lines terminated by '\n'                        //行與行之間的分隔符，只支持'\n'
stored as textfile;        

讀時模式

Hive處理的數(shù)據(jù)是大數(shù)據(jù)，在保存表數(shù)據(jù)時不對數(shù)據(jù)進(jìn)行校驗(yàn)，而是在讀數(shù)據(jù)時校驗(yàn)，不符合格式的數(shù)據(jù)設(shè)置為NULL；

讀時模式的優(yōu)點(diǎn)是，加載數(shù)據(jù)庫快。

HiveQL：數(shù)據(jù)定義

Hive不支持行級插入操作、更新操作和刪除操作，Hive也不支持事務(wù)。
HiveQL數(shù)據(jù)定義語言部分，用于創(chuàng)建、修改和刪除數(shù)據(jù)庫、表、試圖、函數(shù)和索引

Hive中的數(shù)據(jù)庫

創(chuàng)建數(shù)據(jù)庫：

create database financials;

防止因“已存在”而拋出錯誤：

create database if not exits financials;

查看包含的數(shù)據(jù)庫：

show databases;
---
default
financials

使用正則表達(dá)式匹配：

show databases like 'h.*';
---
human_resources

存儲位置：
Hive會為每個數(shù)據(jù)庫創(chuàng)建一個目錄。數(shù)據(jù)庫中的表將會以這個數(shù)據(jù)庫目錄子目錄的形式存儲。數(shù)據(jù)庫所在目錄位于屬性hive.metastore.warehouse.dir所指定的頂層目錄之后?？赏ㄟ^如下命令修改：

hive> create database financials
    > location '/my/preferred/directory'

描述信息：

hive> create database financials
    > comment 'Holds all financial tables'

hive> describe database financials;
---
financials Holds all financial tables
  hdfs://master-server/user/hive/warehouse/financials.db

設(shè)置為當(dāng)前的工作數(shù)據(jù)庫

hive> use financials;

顯示正在使用的數(shù)據(jù)庫

hive> set hive.cli.print.current.db=true;

hive (financials)> use default;

hive (default)> set hive.cli.print.current.db=false;

hive> ...

刪除數(shù)據(jù)庫

hive> drop database if exists financials;  //if exists可選，避免拋出警告

注：默認(rèn)情況下，Hive是不允許用戶刪除一個包含有表的數(shù)據(jù)庫的；要么先刪表，再刪庫；要么加上關(guān)鍵字 cascade

hive> drop database if exists financials cascade;

修改數(shù)據(jù)庫
使用 alter database命令為某個數(shù)據(jù)庫的dbproperties設(shè)置鍵-值對屬性，來描述這個數(shù)據(jù)庫的屬性信息。其他信息不可更改。

hive> alter database financials set dbproperties ('edited-by' = 'Joe Dba')

創(chuàng)建表

在mydb庫中增加employees表：

create table if not exists mydb.employees(
    name              string                comment    'employee name'//comment 后面是屬性
    salary            float                 comment    'employee salary'
    subordinates      array<string>         comment  'names of subordinates'
    deductions        map<string, float>    comment   'names,values'
    address           struct<street:string, city:string, state:string, zip:int> comment 'home address'
)
comment  'description of the table'
tblproperties  ('creator'='me','created_at'='2012-01-02')//可通過鍵值對增加配置信息
location '/user/hive/warehouse/mydb.db/employees';

拷貝一張已經(jīng)存在的表的表模式（而無需拷貝數(shù)據(jù)）：

create table if not exists mydb.employees2
like mydb.employees;

呈現(xiàn)所有表

use mydb;
show tables;
----
employees
employees2

呈現(xiàn)所有表，不在當(dāng)前數(shù)據(jù)庫情況下：

show tables in mydb;
----
employees
employees2

過濾所需表

show tables '*.ees2'
----
employees2

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

describe extended mydb.employees;
----
name string employee name
...

查看更詳細(xì)信息

describe formatted mydb.employees;
----
name string employee name
...

只查看某一列的信息

described mydb.employees.salary;
---
salary float employees salary

管理表vs外部表

上面創(chuàng)建的是所謂的管理表，也被稱為內(nèi)部表；刪除表的時候，會刪除數(shù)據(jù)。
現(xiàn)在可以創(chuàng)建外部表，其可以讀取所有位于/data/stocks 目錄下的以逗號分隔的數(shù)據(jù)：

create external table if not exists stocks (
    exchange      string,
    symbol        string,
    ymd           string,
    price_open    float,
    price_high    float,
    price_low     float,
    price_close   float,
    volume        int,
    price_adj_close    float
)
row format delimited fields terminated by ','
location  '/data/stocks'

external告訴hive這個表是外部的，location告訴hive數(shù)據(jù)位于哪個路徑。這個表對相關(guān)文件無控制權(quán)，因此刪除表并不會刪除數(shù)據(jù)。
通過describe extended tablename查看表信息的時候，可查看是管理表還是外部表：

...tableType:managed_table;
...tableType:external_table;

用戶還可以對一張存在的表進(jìn)行表結(jié)構(gòu)的復(fù)制（不復(fù)制數(shù)據(jù)），這里external關(guān)鍵詞決定了無論源表是管理表還是外部表，創(chuàng)建的表都是外部表。

create external table if not exists mydb.employees3
like mydb.employees
location '/path/to/data';

分區(qū)表、管理表

分區(qū)管理表具有明顯的性能優(yōu)勢
先按照country再按照state來對數(shù)據(jù)進(jìn)行分析：

create table if not exists mydb.employees(
    name              string                comment    'employee name'
    salary            float                 comment    'employee salary'
    subordinates      array<string>         comment  'names of subordinates'
    deductions        map<string, float>    comment   'names,values'
    address           struct<street:string, city:string, state:string, zip:int> comment 'home address'
)
partitioned by (country string,state string);

之前的表存儲在如下目錄：

hdfs://master_server/user/hive/warehouse/mydb.db/employees

數(shù)據(jù)是按照如下子目錄存儲的：

...
.../employees/country=CA/state=AB
.../employees/country=CA/state=BC
...
.../employees/country=US/state=AL
.../employees/country=US/state=AK
...

如何在分區(qū)表查找信息：

select *
from employees
where country = 'US' and state = 'AL'

設(shè)置“strice”模式，對分區(qū)表查詢的時候如果不對分區(qū)進(jìn)行過濾，則任務(wù)不會提交：

set hive.mapred.mode = strict;
set hive.mapred.mode = nostrict;

查看所有分區(qū)

show partitions employees;
-----
country=CA/state=AB
country=CA/state=BC
...

show partitions employees partition (country='US');
-----
country=US/state=AL
country=US/state=AK
...

如何引用HOME環(huán)境變量：

load data local inpath '${env:HOME}/california-employees'
into table employees
patition (country = 'US', state = 'CA');

Hive會把${env:HOME}/california-employees這個目錄下面的文件將會拷貝到上述分區(qū)目錄下。

外部分區(qū)表

外部表也可以使用分區(qū)，可首先創(chuàng)建分區(qū)，然后載入數(shù)據(jù)

create external table if not exists log_messages(
    hms           int,
    severity      string,
    server        string,
    process_id    int,
    message       string
)
partitoned by (year int, month int, day int)
row format delimited fields terminated by '\t';
//這里不要求location子句

可通過alter table語句可以單獨(dú)進(jìn)行增加分區(qū)（同樣適用于管理表）

alter table log_messages add partition(year=2012,month=1,day=2)
location 'hdfs://master_server/data/log_messages/2012/01/02';

刪除表

drop table if exists employees

管理表：刪除表的元信息和數(shù)據(jù)
外部表：刪除表的元信息

修改表

修改表只會影響表的元信息，用戶需自己確保數(shù)據(jù)符合元信息要求。

表重命名

alter table log_messages rename to logmsgs;

增加、修改、刪除表分區(qū)

增加

alter table log_messages add if not exists
partition (year=2012, month=1, day = 1) location '/logs/2011/01/01'
partition (year=2012, month=1, day = 2) location '/logs/2011/01/02'
...

修改

alter table log_messages partition (year=2012, month=1, day = 2) 
set location 's3n:/ourbucket/logs/2011/01/02'

刪除

alter table log_messages drop if exists partition (year=2012, month=1, day = 2) 

修改列信息

alter table log_messages
change columns hms hours_minutes_seconds int
comment 'the hours, minutes, and seconds part of the timestamp'
after severity;

重命名、改類型，改列順序到severity后面（如果想移動到第一個位置則要用first替代after severity）
上述語句只修改元數(shù)據(jù)，用戶需保證數(shù)據(jù)符合元數(shù)據(jù)的要求。

增加列

alter table log_messages add columns(
    app_name     string   comment 'application name',
    session_id   long     comment 'the current session id'
);

刪除或替換列（等于整表替換為如下的列）

alter table log_messages replace columns(
     hours_mins_secs int comment 'hour,minute,seconds from timestamp',
     severity string comment 'The message severity',
     message string comment 'The rest of the message'
)

修改表屬性

alter table log_messages set tblproperties (
    'notes' = 'the process id is no longer captured;this column is always NULL'
)

HiveQL：數(shù)據(jù)操作

向管理表中裝載數(shù)據(jù)

load data local inpath '${env:HOME}/california-employees' 
overwrite into table employees
patition (country='US', state='CA')

local 關(guān)鍵字說明指定的目錄是本地的，如果沒有l(wèi)ocal 關(guān)鍵字則應(yīng)該是分布式文件系統(tǒng)中的路徑。
overwrite 關(guān)鍵字說明先刪除原先存在的數(shù)據(jù)，沒有這個關(guān)鍵字則不刪除。

通過查詢語句向表中插入數(shù)據(jù)

insert overwrite table employees
partition (country = 'US', state = 'OR')
select * from staged_employees se
where se.cnty = 'US' and se.st = 'OR'

如果需要多種對應(yīng)關(guān)系，無需寫很多遍上面的語句，按如下格式插入數(shù)據(jù)效率會高（只需要掃描一遍）：

from staged_employees se
insert overwrite table employees
    partition (country = 'US', state = 'OR')
    select * where se.cnty = 'US' and se.st = 'OR'
insert overwrite table employees
    partition (country = 'US', state = 'CA')
    select * where se.cnty = 'US' and se.st = 'CA'
insert overwrite table employees
    partition (country = 'US', state = 'IL')
    select * where se.cnty = 'US' and se.st = 'IL'

動態(tài)分區(qū)插入

insert overwrite table employees
partition (country, state)
select ...,se.cnty,se.st
from staged_employees se;

hive根據(jù)select語句中最后2列來確定分區(qū)字段country和state的值

混合使用動態(tài)和靜態(tài)分區(qū)

insert overwrite table employees
partition (country = 'US',state)
select  ...,se.cnty,se.st
from staged_employees se
where se.cnty = 'US'

country字段為靜態(tài)，state是動態(tài)值，靜態(tài)分區(qū)鍵必須在動態(tài)分區(qū)鍵之前。
動態(tài)分區(qū)屬性

hive.exec.dynamic.partition 設(shè)置為true，表示開啟動態(tài)分區(qū)功能

單個查詢語句中創(chuàng)建表并加載數(shù)據(jù)

create table ca_employees
as select name, salary, address
from employees se
where se.state = 'CA'

創(chuàng)建表并載入數(shù)據(jù)，不能用于外部表。

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

將所有的字段序列化為字符串寫入到文件中

insert overwrite local directory '/tmp/ca_employees'
select name, salary, address
from employees
where state = 'CA';

輸入到多個文件

from staged_employees se
insert overwrite directory '/tmp/or_employees'
    select * where se.cty = 'US' and se.st = 'OR'
insert overwrite directory '/tmp/ca_employees'
    select * where se.cty = 'US' and se.st = 'CA'
insert overwrite directory '/tmp/il_employees'
    select * where se.cty = 'US' and se.st = 'IL'

視圖

Hive先執(zhí)行這個視圖，然后使用這個結(jié)果進(jìn)行余下后續(xù)的查詢

create view if not exists shipments(time, part)
comment 'time and parts for shipments。'
tblproperties ('creator' = 'me')
as
select ...;

if not exists和comment是可選子句。

• 復(fù)制視圖create view shipments2 like shipments;
• 刪除視圖drop view if exists shipments
• 顯示視圖清單show tables
• 視圖不能作為insert語句和load命令的目標(biāo)表
• 視圖是只讀的

HiveQL 查詢

select...from 語句

select e.name,e.salary
from employees e;

查詢集合數(shù)據(jù)

查詢數(shù)組

注：集合的字符串元素是加上引號的，而基本數(shù)據(jù)類型string的列值是不加引號的。

select name,subordinates
from employees;
---
John Doe        ["Mary Smith","Todd Jones"]
Mary Smith      ["Bill King"]
Todd Jones      [""]
Bill King       [""]

查詢Map

select name,deductions
from employees;
---
John Doe        {"Federal":0.2,"State":0.05,"Insurance":0.1}
Mary Smith      {"Federal":0.2,"State":0.05,"Insurance":0.1}
Todd Jones      {"Federal":0.15,"State":0.03,"Insurance":0.1}
Bill King       {"Federal":0.15,"State":0.03,"Insurance":0.1}        

查詢Struct

select name,address
from employees;
---
John Doe        {"street":"1 Michigan Ave.","City":"Chicago","state":"IL","zip":60600}
Mary Smith      {"street":"30 Michigan Ave.","City":"Chicago","state":"IL","zip":60600}
Todd Jones      {"street":"20 Michigan Ave.","City":"Chicago","state":"IL","zip":60600}
Bill King       {"street":"8 Michigan Ave.","City":"Chicago","state":"IL","zip":60600}   

引用集合數(shù)據(jù)類型中的元素

選取數(shù)組元素

注：string數(shù)據(jù)類型不再加引號

select name,subordinates[0]
from employees;
---
John Doe        Mary Smith
Mary Smith      Bill King
Todd Jones      NULL
Bill King       NULL

引用map元素

select name,deductions["State"]
from employees;
---
John Doe        0.05
Mary Smith      0.05
Todd Jones      0.03
Bill King       0.03  

引用struct的元素，使用“點(diǎn)”符號

select name,address.city
from employees;
---
John Doe        Chicago
Mary Smith      Chicago
Todd Jones      Oak Park
Bill King       Obscuria   

使用正則表達(dá)式制定列

select symbol,`price.*`
from stocks;
---
AAPL    195.69    197.88    194.0
AAPL    195.69    197.88    194.0
AAPL    195.69    197.88    194.0

使用列值進(jìn)行計算

select upper(name),salary,deductions["Federal"]
    ,round(salary * (1-deductions["Federal"]))
from employees;
---
JOHN DOE    10000.0    0.2    8000
MARY SMITH  8000.0     0.2    6400
JOHN DOE    7000.0     0.15   5950

算數(shù)運(yùn)算符

注：

1. 兩種類型數(shù)據(jù)計算：值范圍較小的數(shù)據(jù)類型將轉(zhuǎn)換為范圍更廣的數(shù)據(jù)類型。
2. 需要注意數(shù)據(jù)溢出問題，數(shù)據(jù)溢出的計算結(jié)果不會自動轉(zhuǎn)換為更廣泛的數(shù)據(jù)類型

使用函數(shù)

數(shù)學(xué)函數(shù)

聚合函數(shù)

對多行進(jìn)行計算，返回一個結(jié)果值

注：目前不允許在一個查詢語句中使用多于一個的函數(shù)（distinct ...）表達(dá)式

表生成函數(shù)

將單列拓展成多列或者多行

注：explode不能和其他字段一起使用，比如select name,explode(map) from ee

Hive函數(shù)之explode和inline

其他內(nèi)置函數(shù)

時間函數(shù)

LIMIT語句

select  upper(name),salary,deductions["Federal"]
    ,round(salary * (1 - deductions["Federal"]))
from employees
limit 2;

列別名

select  upper(name),salary,deductions["Federal"] as fed_taxes
    ,round(salary * (1 - deductions["Federal"])) as salary_minus_fed_taxes
from employees
limit 2;

嵌套select語句

from (
select  upper(name),salary,deductions["Federal"] as fed_taxes
    ,round(salary * (1 - deductions["Federal"])) as salary_minus_fed_taxes
from employees
) e
select e.name,e.salary_minus_fed_taxes
where e.salary_minus_fed_taxes > 70000;

case...when...then...end 語句

select name,salary
    case
        when salary < 5000 then 'low'
        when salary < 7000 then 'middle'
        else 'high'
    end as bracket
from employees

where語句

select  upper(name),salary,deductions["Federal"] as fed_taxes
    ,round(salary * (1 - deductions["Federal"])) as salary_minus_fed_taxes
from employees
where round(salary * (1 - deductions["Federal"])) > 7000 //不可使用列別名

謂語操作符

注：一般來說A和B中任一為null，則返回null

關(guān)于浮點(diǎn)數(shù)比較

select *
from employees
where deductions['Federal'] > 0.2;
---
john 0.2
boss 0.3

為什么會返回0.2呢，這是因?yàn)橛脩魧懙母↑c(diǎn)數(shù)0.2，hive會默認(rèn)保存為double類型“0.200000000001”，而deductions是float類型的，將隱式轉(zhuǎn)換為double類型“0.200000100000”
如何避免呢：可以顯示的指出0.2是float類型

select *
from employees
where deductions['Federal'] > cast(0.2 as float);
---
boss 0.3

Group by 語句

group by語句通常和聚合函數(shù)一起使用，對分組進(jìn)行過濾

select year(ymd),avg(price_close)
from stocks
group by year(ymd)
having avg(price_close) > 50;

join 語句

select a.ymd, a.price_close, b.price_close
from stocks a
    join stocks b on a.ymd = b.ymd
where a.symbol = 'AAPL' and b.symbol = 'AAPL'

注：sql不支持“非等值連接”，也不支持在on子句中使用or。

join優(yōu)化

當(dāng)連接3個或更多表時，Hive會嘉定最后一個表時最大的表，然后嘗試將其它表緩存起來，然后掃面最后那個表進(jìn)行計算。
可以顯示的告訴Hive哪張表是達(dá)標(biāo)：

select /*+STREAMTABLE(s)+*/a.ymd, a.price_close, b.price_close
from stocks a
    join stocks b on a.ymd = b.ymd
where a.symbol = 'AAPL' and b.symbol = 'AAPL'

其他join

• left outer join
• outer join
• right outer join
• full outer join

注：where語句在連接操作執(zhí)行后才會執(zhí)行

left semi-join

比inner join更高效的查詢方式，但是只能引用左邊表的字段

select s.ymd, s.symbol, s.price_close
from stocks s 
    left semi join dividends d on s.ymd = d.ymd and s.symbol = d.symbol; 

排序 order by 和 sort by

order by 全局排序

select s.ymd, s.symbol, s.price_close
from stocks s
order by s.ymd asc ,s.symbol desc

sort by 只在reducer內(nèi)部排序，如果有多個reducer，則不保證輸出的是整體有序的。

select s.ymd, s.symbol, s.price_close
from stocks s
sort by s.ymd asc ,s.symbol desc

使用distribute by來保證相同的數(shù)據(jù)會分發(fā)到同一個reducer中進(jìn)行處理：

select s.ymd, s.symbol, s.price_close
from stocks s
distribute by s.symbol    --同一個symbol放到同一個reducer中處理
sort by s.symbol, s.ymd desc

如果上面的語句沒有要求按ymd 倒序排列，則可以用cluster by簡化：

select s.ymd, s.symbol, s.price_close
from stocks s
cluster by s.symbol 

類型轉(zhuǎn)換

cast(value as TYPE)

抽樣查詢

分桶抽樣
分子是要分為幾桶，分子是取回的第幾個桶，rand()表示隨機(jī)排序（如果省略rand()，則返回的結(jié)果會始終一致）

select *
from numbers tablessample(bucket 1 out of 2 on rand()) s;
---
2
4
6
8
10

數(shù)據(jù)塊抽樣

select *
from numbersflat tablesample(0.1 percent) s;

union all 語句

將兩個或多個表進(jìn)行合并，但是要求對應(yīng)的字段類型必須一致。

分析函數(shù)

分析函數(shù)用于計算基于組的某種聚合值，它和聚合函數(shù)的不同之處是：對于每個組返回多行，而聚合函數(shù)對于每個組只返回一行。
基本結(jié)構(gòu)：

分析函數(shù)() + over(partition by ** order by ** rows between ** and **)

基礎(chǔ)數(shù)據(jù).png

SELECT cookieid,createtime,pv,
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默認(rèn)為從起點(diǎn)到當(dāng)前行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --從起點(diǎn)到當(dāng)前行，結(jié)果同pv1 
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv3,   --當(dāng)前行+往前3行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv4,    --當(dāng)前行+往前3行+往后1行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv5   ---當(dāng)前行+往后所有行  
FROM test1;
-----
cookieid    createtime  pv  pv1  pv2    pv3  pv4  pv5
a           2017-12-01  3    3    3      3    3    3
b           2017-12-00  3    3    3      3    3    3
cookie1     2017-12-10  1    1    1      1    6    26
cookie1     2017-12-11  5    6    6      6    13   25
cookie1     2017-12-12  7    13  13      13   16   20
cookie1     2017-12-13  3    16  16      16   18   13
cookie1     2017-12-14  2    18  18      17   21   10
cookie1     2017-12-15  4    22  22      16   20   8
cookie1     2017-12-16  4    26  26      13   13   4
cookie2     2017-12-12  7    7    7      7    13   14
cookie2     2017-12-16  6    13  13      13   14   7
cookie2     2017-12-24  1    14  14      14   14   1
cookie3     2017-12-22  5    5    5      5     5   5

行數(shù)控制（window子句）：

• preceding：往前
• FOLLOWING：往后
  CURRENT ROW：當(dāng)前行
  UNBOUNDED：無邊界，UNBOUNDED PRECEDING 表示從最前面的起點(diǎn)開始， UNBOUNDED FOLLOWING：表示到最后面的終點(diǎn)

開窗函數(shù)

sum

sum()計算的是分區(qū)內(nèi)排序后一個個疊加的值，和order by有關(guān)；如果沒有order by，不僅分區(qū)內(nèi)沒有排序，sum()計算的pv也是整個分區(qū)的pv

select cookieid,createtime,pv,
    sum(pv) over(PARTITION BY cookieid ORDER BY createtime) as pv1 
FROM test1

max

返回最大值

min

返回最小值

ntile

NTILE(n)，用于將分組數(shù)據(jù)按照順序切分成n片，返回當(dāng)前切片值
注1：如果切片不均勻，默認(rèn)增加第一個切片的分布
注2：NTILE不支持ROWS BETWEEN

SELECT cookieid,createtime,pv,
NTILE(2) OVER(ORDER BY createtime) AS ntile1, --分組內(nèi)將數(shù)據(jù)分成2片
NTILE(3) OVER(ORDER BY createtime) AS ntile2,  --分組內(nèi)將數(shù)據(jù)分成3片
NTILE(4) OVER(ORDER BY createtime) AS ntile3   --將所有數(shù)據(jù)分成4片
FROM test1 

row_number

row_number() 從1開始，按照順序，生成分組內(nèi)記錄的序列
類似：

• rank() 生成數(shù)據(jù)項在分組中的排名，排名相等會在名次中留下空位
• dense_rank() 生成數(shù)據(jù)項在分組中的排名，排名相等會在名次中不會留下空位

SELECT cookieid,createtime,pv,
    rank() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rank1,
    dense_rank() OVER(PARTITION BY cookieid ORDER BY pv desc) AS d_rank2,
    row_number() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3 
FROM test1 

cume_dist

返回：

比如，我們可以統(tǒng)計小于等于當(dāng)前薪水的人數(shù)，所占總?cè)藬?shù)的比例

SELECT cookieid,createtime,pv,
    round(cume_dist() OVER(ORDER BY pv),2) AS cd1,
    round(cume_dist() OVER(PARTITION BY cookieid ORDER BY pv),2) AS cd2  
FROM test1;

類似的：percent_rank，返回：

LAG 和 LEAD

LAG(col,n,DEFAULT) 用于統(tǒng)計窗口內(nèi)往上第n行值，第一個參數(shù)為列名，第二個參數(shù)為往上第n行（可選，默認(rèn)為1），第三個參數(shù)為默認(rèn)值（當(dāng)往上第n行為NULL時候，取默認(rèn)值，如不指定，則為NULL）

LEAD 函數(shù)則與 LAG 相反： LEAD(col,n,DEFAULT) 用于統(tǒng)計窗口內(nèi)往下第n行值

SELECT cookieid,createtime,pv,
    ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
    LAG(createtime,1,'1970-01-01') OVER(PARTITION BY cookieid ORDER BY createtime) AS lag1,
    LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS lag2 
FROM test1;

FIRST_VALUE 和 LAST_VALUE

FIRST_VALUE 取分組內(nèi)排序后，截止到當(dāng)前行，第一個值;
LAST_VALUE 函數(shù)則相反，取分組內(nèi)排序后，截止到當(dāng)前行，最后一個值。

SELECT cookieid,createtime,pv,
    ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
    FIRST_VALUE(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS first  
FROM test1;

窗口函數(shù)參考文章