GROUP BY clause review video lecture

Loading...

来自 University of Colorado System 的课程

Relational Database Support for Data Warehouses

406 个评分

University of Colorado System

Relational Database Support for Data Warehouses

406 个评分

课程 3（共 5 门，Specialization Data Warehousing for Business Intelligence）

Relational Database Support for Data Warehouses is the third course in the Data Warehousing for Business Intelligence specialization. In this course, you'll use analytical elements of SQL for answering business intelligence questions. You'll learn features of relational database management systems for managing summary data commonly used in business intelligence reporting. Because of the importance and difficulty of managing implementations of data warehouses, we'll also delve into storage architectures, scalable parallel processing, data governance, and big data impacts.

从本节课中

SQL Subtotal Operators

Now that you have the informational context for relational database support of data warehouses, you’ll start using relational databases to write business intelligence queries! In module 2, you will learn an important extension of the SQL SELECT statement for subtotal operators. You’ll apply what you’ve learned in practice and graded problems using Oracle SQL for problems involving the CUBE, ROLLUP, and GROUPING SETS operators. Because the subtotal operators are part of the SQL standard, your learning will readily apply to other enterprise DBMSs. At the end of this module, you will have solid background to write queries using the SQL subtotal operators as a data warehouse analyst.

GROUP BY clause review video lecture7:47

与讲师见面

Michael Mannino
Associate Professor
Business School, University of Colorado Denver

Welcome to lesson one of module two on SQL sub total operators.

An appropriate alternative title is fun with sub total operators.

I'm gonna start with an important motivational question

that I want you to think about throughout this lesson.

What is an advantage of the group by clause compared to pivot tables?

Module two provides the first detailed content for

this course with ungraded problems and a graded assignment.

Lesson one reviews the group by clause to ensure that you're ready for

the new material about subtotal operators in the other lessons of module two.

Course one in this specialization covered group by clause in modules four and five.

You have three learning objectives in this lesson.

You should review the group by clause so

that you can write select statements involving joins and grouping.

You should be able to explain the differences between group by results and

pivot table results.

As a reflective goal you should think about the importance of subtotal operator

extensions for the group by clause.

Let's begin with a brief review about the purpose and

components of the group by clause.

The group by clause produces row summaries

in which each row summary replaces collections or groups of rows.

Each row summary contains grouping columns and

summary values calculated with aggregate functions.

An aggregate function calculates one value per set of rows.

The standard aggregate functions in SQL are min, max, count,

sum and avg for average.

Many other aggregate functions are typically provided

depending on the dv mess.

You should remember two rules with the group by clause for consistency,

columns in the SELECT clause should match columns in the group by clause.

The order of the columns does not matter, just a set of columns.

For example, if you have three columns in a SELECT clause along with one aggregate

function expression, you should have the same three columns in a group by clause.

You must separate row and group conditions.

Row conditions not involving an aggregate function must be placed in a where cause.

Group conditions involving an aggregate function

must be placed in a having clause.

The examples in module two use the store sales tables.

Module one covered the schema design.

If you completed the guided tutorial in Module five of course two

in the specialization, you should recall the store sales design.

The Oracle diagram is repeated in this lesson for convenience.

This group by example will be extended in other lessons in module two.

The proper requirements are to summarize store sales in USA and

Canada in 2012 by stores zip code and month.

Only groups with more than one row appear in the result.

The select statement for

this problem depicts joint operations with a fact cable and dimension tables,

group by and having clauses, row conditions and sorting of results.

The select clause contains two grouping columns, store zip and time month,

along with three computed columns using aggregate functions, sum, min and count.

The where clause is two joined conditions and

a single table conditions on store nation and time month.

The group by clause creates row summaries on combinations of StoreZip and TimeMonth.

The having count clause restricts row summaries to more than one row.

The order by clause provides a convenient ordering

as the group by clause may not sort results as expected.

I will now execute this statement using the Oracle SQL developer.

The result contains 12 rows, with five columns in each row.

The evaluation order is conceptual rather than actual because most SQL

compilers can produce the same output using many shortcuts.

Evaluation order provides a foundation for understanding the meaning of

select statements independent of system and performance issues.

Row operations occur before group operations.

So it's important not to have any row conditions in have HAVING clause.

Some SQL compilers allow row conditions in the HAVING clause.

Although performance will be improved if all all row conditions are placed in

the where clause.

Grouping occurs only one time,

although this limitation is only an issue in summarizing independent columns.

Even in this situation, other workarounds such as nested queries and

a from clause can be used to overcome this limitation.

The group by clause was found inadequate for business intelligence applications.

The major limitation motivating this module

is a lack of subtotals in the results with group by clause.

Group by results only contain the lowest level totals for

each combination of grouping columns.

Pivot tables can have subtotals on any query dimension or access.

Subtotals are not limited to addition.

Any statistical function can be used to summarize an access

such as average and minimum.

These sample results visually depict the differences between the group by clause

and pivot tables.

A select statement with a group by clause contains row summaries

with one combination of grouping columns in each row.

Only rows with a none null value for all grouping columns appears in the result.

No sub totals appear in the result.

For example the sum of sales for

California CA is abbreviation are not shown in the result.

In contrast, the corresponding pivot table shows subtotals and missing values as

empty cells, such as the combination of CA for California in January.

The subtotals in this example show the sum of sales for rows, that is states, and

months, for the columns, along with the grand total a pivot table for

two dimensions with N values in a row's dimension and

N values in a column's dimension contains N times N cells.

In contrast, the corresponding group by result contains a maximum of

N times N cells or rows, because the results contain no missing values.

This lesson reviewed the group by clause is a prelude for

new material and SQL subtotal operators.

In original design of the SQL select statement,

the group by clause was designed for tactical and strategic decision making.

Over time, deficiencies in the group by clause became apparent

other lessons in this module present details of SQL subtotal operators,

a substantial extension of the group by clause, that addresses some deficiencies.

You can understand the need for subtotal operators by comparing group by results

to a pivot table with summary calculations for query dimensions.

In answer to the opening question, group by results do not show missing values in

comparison to pivot tables with explicit missing values.

As the number of dimensions increases, the number of unique values in each dimension

increases, the sparsity, or number of empty cells increases.

Efficient compression of missing values there's a substantial performance

issue with data cubes and associated pivot table results but

a minor issue is relational by results.

探索我们的目录

免费加入并获得个性化推荐、更新和优惠。

Coursera 致力于普及全世界最好的教育，它与全球一流大学和机构合作提供在线课程。

© 2018 Coursera Inc. 保留所有权利。

通过 App Store 下载

通过 Google Play 获取