How to use GROUP BY statement and HAVING statement
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!
