Welcome to the Coursera specialization, From Data to Insights with Google Cloud Platform brought to you by the Google Cloud team. I’m Evan Jones (a data enthusiast) and I’m going to be your guide. This first course in this specialization is Exploring and Preparing your Data with BigQuery. Here we will see what the common challenges faced by data analysts are and how to solve them with the big data tools on Google Cloud Platform. You’ll pick up some SQL along the way and become very familiar with using BigQuery and Cloud Dataprep to analyze and transform your datasets. This course should take about one week to complete, 5-7 total hours of work. By the end of this course, you’ll be able to query and draw insight from millions of records in our BigQuery public datasets. You’ll learn how to assess the quality of your datasets and develop an automated data cleansing pipeline that will output to BigQuery. Lastly, you’ll get to practice writing and troubleshooting SQL on a real Google Analytics e-commerce dataset to drive marketing insights. >>> By enrolling in this specialization you agree to the Qwiklabs Terms of Service as set out in the FAQ and located at: https://qwiklabs.com/terms_of_service <<<
Lab Walkthrough - Ordering, Calculated fields, Filtering after Aggregating
Loading...
Do curso por Google Cloud
Exploring and Preparing your Data with BigQuery
829 classificações
Google Cloud
829 classificações
Curso 1 de 4 em Specialization From Data to Insights with Google Cloud Platform
Na lição
Module 3: Exploring your Data with SQL
Learn how to query your data with the basics of SQL (Structured Query Language) and practice writing queries in BigQuery
Conheça os instrutores
Google Cloud Training
All right, for the next question.
Let's take a look at the cities that have the most transaction totals.
For here again, quickly,
I'm going to back to my schema.
I'm going to look for- Again,
you can scroll down or you can search for on your browsers.
City. I'm going to add in my select,
give me the geoNetwork_city,
and give me the totals.
Let's do totals transactions,
and let's also return a distinct count of the visitors from that city.
So, you've got each of the cities,
we've got transactions associated with those,
and the only thing we're missing now,
is the table that we're actually pulling from.
Of course, making sure that we have our legacy SQL disabled.
One of the first things you're going to get is a very common error.
If your referencing things that have aggregations,
and then if you're counting the visitors,
and of course we need to do Group By.
Let's see if that gets us closer,
if we did an aggregation on a full count of visitors and we wanted to say,
"All right, well, let's do the say,
the sum of the total transactions."
Then you execute that,
and we will get a result.
So, a bunch of different interesting cities that could have had only one transaction,
or one visitor that visited from there, now,
if you wanted to order the results because it would use Order By.
So, you want to Order By the total number of distinct visitors,
so who visited the most?
What city has the most visits from? Let's see.
In this particular dataset,
it's not available in this demo dataset, so ignore that one.
Then, we have Mountain View, New York,
and then San Francisco.
One thing that I will caution is,
for those who have been around and use SQL quite a lot,
this sum total transactions as you'll see when we just preview the dataset here.
Let's take a look at this particular Visitor ID,
you can see you rows one and two are the same.
You can see that the total transactions is one.
We have to be very careful that the total transactions is we're not double counting.
So, the total transactions for that particular visitor,
we need to see and take a look at the transactions.
Probably, means one particular order,
which could have multiple different products.
So, it looks like what we've done in
our particular query is not the total number of unique transactions,
but the number of products that were ordered.
So, we're actually going to rename this, total products ordered,
and we'll add in
a filter or we're going to
find the city that ordered the most products and see if it's
also San Francisco, or Mountain View.
As you see, when we execute this query,
then New York wins and towards the number of products ordered,
but it looks like it has fewer visitors.
Mountain View had most visitors besides the one that was not included in this dataset,
but it had fewer products ordered.
So, if you wanted to get that ratio,
we could just do a simple calculated field.
So, we can say, take all of the different products ordered,
divide that by the total number of visitors,
and we get a nice ratio here.
As average number of products ordered by city,
and this will be really interesting because you could have
some cities that have very few visitors but are ordering a ton,
and you may want to target those a little bit more for promotions.
So, let's see. So, we now have that returned. This is going to be cool.
So, we have the average number of products ordered instead of sorting by that,
now let's sort by the average number of products ordered, and we'll find,
I'm hoping some cool cities that have had,
yeah, all right awesome.
We have Jakarta, who had seven visitors but they ordered 254 products.
You get some really interesting statistics here.
So maybe, this will influence your promotions, or however,
you want to further incentivize or reach out to these folks and basically say,
''You ordered a ton or products,
we want to see what you're doing with them.''
Or, if there's any way that we can be of additional support.
Now, one thing will close on here is if you want to do
things like filter app you've already aggregated.
If you're filtering after an aggregation,
you're not going to be doing let's just do it the wrong way if you're doing,
where the average number of products ordered is saying greater than 20.
If you were to execute this,
and see what that would return.
It's a common error. So, it's two errors in here,
unrecognized name average products ordered.
Then you can stare at this for as long as you want.
Basically say, "This is the exact same field name as I've specified here.
But there's two actual errors,
one's in a SQL syntax error,
and the other one's a logical error."
So, SQL's syntax error is instead of actually doing this inside of the Where clause,
if you're filtering on aggregations.
That's actually not done in the Where clause.
It's done after the Group By, and it's saying,
Group By the city but filter after you've grouped,
is done by using a Having clause,
Having averaged products ordered greater than 20.
So, let's execute Selected again on this.
You can see the result that is returned.
The other area that's a little bit more insidious is that, ''Hey,
why on Earth does it recognize this field now average products
ordered Inside of the Having clause but not inside of the Where clause?''
That's one of the fundamental insights of filtering,
that field when BigQuery goes out and executes this,
and this is true for SQL in general is,
it'll go to the dataset first all the different rows,
and it'll immediately look to invoke that filter first.
So right now, it's goes to dataset,
it looks for a field name average products ordered,
because it's a calculated field that's done row-by-row.
It's not available initially for that first dataset filtering.
So, that's why you can't use aliased fields,
or calculated fields inside of a Where clause.
Now, what you could do inside of your Where clause,
is you can copy and paste.
It wouldn't be this because this is an aggregated field,
but say you had something like geoNetwork_city and
you can catenated something onto the front or on the end of it.
You could copy and paste that calculation that you're doing inside of
the Where clause and then pretty much have a duplicative code,
and filtered out that one.
So, Where clause can actually take a calculation,
but that gets a little bit of code duplication.
But, we more likely want to do as we'll cover in future courses,
is use something like a subquery,
or use a common table expression or With clause to
break apart one BigQuery into two separate queries.
We'll show you how to do that in our advanced course.
But for now, we've got the great insight that we've
got a particular city that has a lot of products ordered,
and it's a high number of products ordered.
Explore nosso catálogo
Registre-se gratuitamente e obtenha recomendações, atualizações e ofertas personalizadas.
O Coursera proporciona acesso universal à melhor educação do mundo fazendo parcerias com as melhores universidades e organizações para oferecer cursos on-line.
© 2019 Coursera Inc. Todos os direitos reservados.
