group by having用法(GROUP BY語句及HAVING語句怎么用)

GROUPBY語句


創建一個股價交易表
CREATEEXTERNALTABLEIFNOTEXISTSstocks(
exchangeSTRING,
symbolSTRING,
ymdSTRING,
price_openFLOAT,
price_highFLOAT,
price_lowFLOAT,
price_closeFLOAT,
volumeINT,
price_adj_colseFLOAT)
ROWFORMATDELIMITEDFIELDSTERMINATEDBY','
LOATION'/data/stocks';

eg:按照年份對股票記錄進行分組,然后計算每年的平均收盤價
hive>SELECTyear(ymd),avg(price_close)FROMstocks
>WHEREexchange='NASDAQ'ANDsymbol='AAPL'
>GROUPBYyear(ymd);
198425.57
198520.54
198632.46
198753.89
198841.66
198937.56
199052.50
...

HAVING語句
HAVING子句允許我們通過一個簡單語法完成原本需要通過子查詢才能對GROUPBY語句產生分組進行條件過濾的任務。

eg:如下是對前面的查詢語句增加一個HAVING語句來限制輸出結果中年平均收盤價要大于50.0
hive>SELECTyear(ymd),avg(price_close)FROMstocks
>WHEREexchange='NASDAQ'ANDsymbol='AAPL'
>GROUPBYyear(ymd)
>HAVINGavg(price_close)>50.0;
198753.89
199052.50
...

如果沒使用HAVING子句,那么這個查詢將需要使用一個嵌套子查詢:
hive>SELECTs2.year,s2.avgFROM
>(SELECTyear(ymd)ASyear,avg(price_close)ASavgFROMstocks
>WHEREexchange='NASDAQ'ANDsymbol='AAPL'
>GROUPBYyear(ymd))s2
>WHEREs2.avg>50.0;
198753.89
199052.50
...