At the end of this video the student will know how to filter out aggregate results using the HAVING clause.
- [Instructor] Now let's take a look…at Filtering Aggregates with the HAVING clause.…The HAVING Clause, like the WHERE clause,…allows you to focus your result set, this time however,…that filtering is performed after you've done…and aggregation, which is kind of a unique thing,…and actually very common in lot of analysis work.…First what we're going to do is build a simple aggregate query…then filter those results using the HAVING clause.…Here in my cloud environment I'm going to open up…5-2.sql, copy this, go over to my Hue editor,…paste that in, and we can start here…by creating a simple aggregate query.…
We have select, product category, sub category and key.…And then a sum of sales amount from sales all years…where the year equals 2009, remember that's our partition.…Then we have to add our grouping, which is all the fields…that are not a part of an aggregation,…and we're goin to limit that to a thousand results,…just in case there are millions of results,…we just want the top thousand to make sure our query…
This course shows how to use Hive to process data. Instructor Ben Sullins starts by showing you how to structure and optimize your data. Next, he explains how to get Hue, the Hadoop user interface, to leverage HiveQL when analyzing data. Using the newly configured option, he then demonstrates how to load data, create aggregate tables for fast query access, and run advanced analytics. He also takes you through managing tables and putting functions to use. This course is designed to help you find new ways to work with datasets so you can answer the tough data science questions that come your way.
- Defining data structures in Hive
- Selecting data
- Joining tables
- Manipulating data
- Filtering results
- Aggregating data
- Using built-in aggregate functions
- Mastering built-in table-generating functions
- Using CUBE and ROLLUP
- Using clauses: WHERE and HAVING
- Using LIKE, JOIN, and SEMI JOIN
- Using functions: String, math, date, and conditional