Shulou(Shulou.com)06/01 Report--

This article mainly explains "GROUP BY sentence and HAVING sentence how to use", the content of the article is simple and clear, easy to learn and understand, now please follow the editor's thinking slowly in depth, together to study and learn "GROUP BY sentence and HAVING sentence how to use" it!

GROUP BY statement

Create a stock price trading table

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_colse FLOAT)

ROW FORMAT DELIMITED FIELDS TERMINATED BY','

LOATION'/ data/stocks'

Eg: group stock records by year, and then calculate the average closing price for each year

Hive > SELECT year (ymd), avg (price_close) FROM stocks

> WHERE exchange='NASDAQ' AND symbol='AAPL'

> GROUP BY year (ymd)

1984 25.57

1985 20.54

1986 32.46

1987 53.89

1988 41.66

1989 37.56

1990 52.50

...

HAVING statement

The HAVING clause allows us to use a simple syntax to accomplish tasks that would otherwise require subqueries to generate groups of GROUP BY statements for conditional filtering.

Eg: the following is to add a HAVING statement to the previous query statement to limit the middle-aged average closing price of the output to more than 50.0

Hive > SELECT year (ymd), avg (price_close) FROM stocks

> WHERE exchange='NASDAQ' AND symbol='AAPL'

> GROUP BY year (ymd)

> HAVING avg (price_close) > 50.0

1987 53.89

1990 52.50

...

If the HAVING clause is not used, the query will need to use a nested subquery:

Hive > SELECT s2.yearjournal s2.avg FROM

> (SELECT year (ymd) AS year,avg (price_close) AS avg FROM stocks

> WHERE exchange='NASDAQ' AND symbol='AAPL'

> GROUP BY year (ymd)) S2

> WHERE s2.avg > 50.0

1987 53.89

1990 52.50

... Thank you for your reading, the above is the content of "how to use GROUP BY sentence and HAVING sentence". After the study of this article, I believe you have a deeper understanding of how to use GROUP BY sentence and HAVING sentence. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.