Explore
For Enterprise
Join for Free
Log In

閲覧
検索
企業用
ログイン
参加は無料

Grouping Data with SQL

Loading...

SQL for Data Science

カリフォルニア大学デービス校（University of California, Davis）

4.6 (2,740 件の評価) | 120K在籍する学生

As data collection has increased exponentially, so has the need for people skilled at using and interacting with data; to be able to think critically, and provide insights to make better decisions and optimize their businesses. This is a data scientist, “part mathematician, part computer scientist, and part trend spotter” (SAS Institute, Inc.). According to Glassdoor, being a data scientist is the best job in America; with a median base salary of $110,000 and thousands of job openings at a time. The skills necessary to be a good data scientist include being able to retrieve and work with data, and to do that you need to be well versed in SQL, the standard language for communicating with database systems. This course is designed to give you a primer in the fundamentals of SQL and working with data so that you can begin analyzing it for data science purposes. You will begin to ask the right questions and come up with good answers to deliver valuable insights for your organization. This course starts with the basics and assumes you do not have any knowledge or skills in SQL. It will build on that foundation and gradually have you write both simple and complex queries to help you select data from tables. You'll start to work with different types of data like strings and numbers and discuss methods to filter and pare down your results. You will create new tables and be able to move data into them. You will learn common operators and how to combine the data. You will use case statements and concepts like data governance and profiling. You will discuss topics on data, and practice using real-world programming assignments. You will interpret the structure, meaning, and relationships in source data and use SQL as a professional to shape your data for targeted analysis purposes. Although we do not have any specific prerequisites or software requirements to take this course, a simple text editor is recommended for the final project. So what are you waiting for? This is your first step in landing a job in the best occupation in the US and soon the world!

シラバスを表示

学習するスキル

Data Science, Data Analysis, Sqlite, SQL

レビュー

4.6 (2,740 件の評価)

5 stars
1,915 ratings
4 stars
614 ratings
3 stars
128 ratings
2 stars
52 ratings
1 star
31 ratings

MS

Mar 11, 2019

Great course i had a very good learning experience from this course and what i was expecting from this course i got that knowledge and i am very happy to take this course. thank you so much.

NP

Jan 07, 2018

A nice course to get introduced to writing SQL queries in Data science. Provides hands on exercises that boost confidence. Genuinely appreciate the ease with which SQL topics are covered

レッスンから

Filtering, Sorting, and Calculating Data with SQL

In this module, you will be able to use several more new clauses and operators including WHERE, BETWEEN, IN, OR, NOT, LIKE, ORDER BY, and GROUP BY. You will be able to use the wildcard function to search for more specific or parts of records, including their advantages and disadvantages, and how best to use them. You will be able to discuss how to use basic math operators, as well as aggregate functions like AVERAGE, COUNT, MAX, MIN, and others to begin analyzing our data.

Module Introduction1:34

Basics of Filtering with SQL8:42

Advanced Filtering: IN, OR, and NOT7:26

Using Wildcards in SQL6:48

Sorting with ORDER BY4:37

Math Operations4:18

Aggregate Functions6:47

Grouping Data with SQL5:32

Putting it All Together3:24

講師

Sadie St. Lawrence
Data Scientist at VSP Global

字幕

Congratulations, you are almost at the end of this module. We've gone over a lot of great information that not only will help you filter down your data and be really specific about what you're trying to achieve, but also trying to perform some calculations and some aggregate functions on your data. In our previous video, we went over using aggregate functions, such as average, min, max and sum. However, one of the things we didn't over is what you are actually aggregating those functions on. In all the examples we previously went through, what we had seen was how to pull a single field and aggregate on the single field. Well, this is helpful. A lot of times, we'll be looking at the total price for something and we may want to look at it for customers or for products and we need to choose how are we going to aggregate that function. That's what this video was about. And after this lesson, you'll be able to perform additional aggregations using GROUP BY and HAVING clause. Discuss how NULLs are or aren't affected by the GROUP BY and HAVING clauses, and how to use the GROUP BY and ORDER BY clauses together to better sort your data. Let's start off with a grouping example. Let's say, for this example, we want to know the number of customers we have, but we want to know this by each region. In the last lesson, we went over how to count customer IDs and to see our total number of customers. But now, what we're going to do is add in that region portion. If we were to just have our select statement with select region and then we have our aggregate function count customer IDs as total customers from the customers table, we're going to get an error return. Because we haven't satisfied, okay, how do you want me to count the customer IDs? That's where we add this group by a clause. We have now after this from, we put the GROUP BY clause and what we want it to group it by. Some important things to note about the GROUP BYs is we can group it by multiple columns. We may have more columns than just region. For example, maybe we want to know region, city, country. We can list all of those in our GROUP BY clause. We will just add a comma after every column that we want listed. One of the things to note that's really important is all of the fields that your pulling in with the aggregate function need to be on your GROUP BY clause, except for the aggregated calculation. Even though you may only want to group by that region, it's going to need to be summarized by all the other columns as well. So, make sure you're listing those out in order. Another think to note is you have NULLs in one of your categories such as your region. Let's say, you have the regions of California, Idaho, Texas, Nebraska. But then you also have some of the other regions that are blank. They have no values. NULL will actually be grouped as its own category then. This is helpful, because it helps you make sure that you're not missing any data. But it's just something to watch out for and look out for. Now that we've started to use aggregate functions and started to group them, it's also important to know how filtering with the WHERE clause works for some of these aggregates. WHERE does not work for groups, because it filters on rows. Therefore, we have to use the HAVING clause when filtering with an aggregate function in grouping them. In this example, we want the count of orders for customers. But we only want to see the total orders for the customers who've had more than two orders. So to do this, we're going to select our customer IDs, then we're going to count all of the records as orders. We're going to get this from our orders table. And then since we want to look at this by each individual customer, we're going to group it by our customer ID. Then to be able to make sure we're only selecting those customers who had orders greater than or equal to 2, we'll place our HAVING clause of HAVING COUNT greater than or equal to 2. Again, just remember that WHERE filters before the data is grouped and then HAVING filters after the data is grouped. Rows will be eliminated by the WHERE clause and those will be included in the GROUP BY clause. This is just important to know when you should use WHERE versus HAVING. Both are very powerful. Both have different purposes. Just be sure to note when each one is appropriate, so you are not missing any data that should be counted. Another thing to note when you're working with GROUP BYs is that it's always good practice to use the order by clause. The GROUP BY does not sort the data in any fashion. It only groups it together. In our previous examples, we have a list of states, a list of regions. It's not going to sort those regions in alphabetical order. It's just going to group them by different regions. I always recommend using an order by in this sort of situation. It just makes it a little easier to read through your data. It's just good practice to get into.

コース一覧で検討

サインアップは無料です。今すぐサインアップして、パーソナライズされたお勧め、更新、サービスを利用しましょう。

コーセラは、世界トップクラスの教育を世界中に配信しています。最高峰の大学や組織とのパートナーシップに基づくオンラインコースが提供されています。

© 2019 Coursera (株)すべての権利予約します。

Coursera
このコースについて
リーダーシップ
キャリア
カタログ
修了証
MasterTrack™の修了証
学位
企業用
政府向け
キャンパス向け

コミュニティ
Learners
Partners
Developers
Beta Testers
Translators

つながる
ブログ
Facebook
LinkedIn
Twitter
Instagram
Youtube
技術ブログ

さらに表示
規約
プライバシー
ヘルプ
アクセシビリティ
報道関係者
お問い合わせ
ディレクトリ
アフィリエイト