Explore
For Enterprise
Join for Free
Log In

Artes e Humanas
Negócios
Ciência da Computação
Ciência de Dados
Tecnologia da informação
da saúdesaúde
Matemática e Lógica
Desenvolvimento Pessoal
Ciência e Engenharia Física
Ciências Sociais
Línguas
Diplomas e certificados

Explore todo o Coursera

Navegar
Pesquisar
For Enterprise
Entrar
Inscreva-se gratuitamente

Lab Walkthrough - Logical Errors, Group Bys, Wildcard Filters

Loading...

Exploring and Preparing your Data with BigQuery

4.6 (829 classificações) | 13K alunos inscritos

Curso 1 de 4 no From Data to Insights with Google Cloud Platform Programa de cursos integrados

Visualizar o programa do curso

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 <<<

Habilidades que você aprenderá

Bigquery, Google Cloud Platform, Cloud Computing, SQL

Avaliações

4.6 (829 classificações)

5 stars
593 ratings
4 stars
192 ratings
3 stars
30 ratings
2 stars
6 ratings
1 star
8 ratings

NM

Aug 29, 2018

this was very thorough and progressed ideally in bite-sized modules. I really appreciate being able to download the videos and the transcripts, too. The labs are wonderful.

JC

Nov 21, 2017

Excelente curso, muy buenas explicaciones. Tuve problemas para entrar al laboratorio, pero las explicaciones posteriores sirvieron para entender la ejecución.

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

Lab Walkthrough - Enable Standard SQL3:52

Lab Walkthrough - Query Validator, Aliases, and Commas5:13

Lab Walkthrough - Logical Errors, Group Bys, Wildcard Filters7:53

Lab Walkthrough - Ordering, Calculated fields, Filtering after Aggregating9:05

Lab Walkthrough - Finding top selling products, Filtering NULLs6:41

Ministrado por

Google Cloud Training

All right. So, here is another question.

If I ran this and let's remove the limit again,

we might not necessarily want to do this because you could have a ton of visitor IDs.

We have 56,000 in this case,

you don't want to just go selecting everything.

So generally, you want to filter your data with

a where clause or an aggregation function.

So, here we've got 56,535 visitors that reached the checkout confirmation.

Does that seem a little strange to you? Well, it should.

If you remember from the previous lessons,

there was maybe 56,000 transactions, sure,

in the revenue transactions table,

but that doesn't necessarily mean as you remember there was on average

of 2-3 products that each visitor purchased.

This is not how many unique visitors reached that checkout confirmation page.

So, what you've got here is what I call logical error,

and logical errors that query will run fine but

the results that show are actually not what we actually want.

So, what we'll do here is we'll do a count. That's visitor count.

Immediately if we try to execute this,

we've done an aggregation function on one of the fuels meaning

roll up all of those visitor ID columns and

aggregation function reduces it from

however many N number of rows that it is into a single row for aggregation function.

But page title, that field feels left out.

Hey, if you're aggregating on one,

you've got to aggregate on the other, right?

So, in order to aggregate a string function,

if you're using an aggregation function in your select clause in one field,

you have to use it on the other.

You could do a count of page title but that wouldn't make too much sense.

So if you're aggregating in the select,

all the other fields have to be aggregated.

So, how we do that of course is with a group by.

So, you can group by the column index,

so you could do number two if you wanted.

But I like to be a little bit more verbose and I'll put in the actual field that's there.

So, this is going to be the count of visitors by each particular page.

Let's see if that gets us closer to the answer.

So this is really, really interesting.

So, now we've got two different things.

We've got the visitor count and then we've got the page title,

and this is how many visitors have reached that particular page.

So, this will tell us a couple of different things.

One, we still haven't solved the problem of de-duplicating our visitors.

So, I'm just going to go ahead and do that now,

and basically say I only want the distinct visitors that have visited one of these pages.

But as you see with the pages here,

you can have many different variants of the word checkout confirmation.

This page is called 11 checkout confirmation.

This one is checker conformation and we have order completed,

and we got checkout confirmation here.

So, if we're just going by the page title or we could go by the page URL,

however you want to work it out,

and we wanted to filter out just those that are the checkout confirmation page,

first let's rerun this query and make sure it works for the distinct count of visitors.

So, you can see 19,000 almost 20,000 unique visitors actually purchased.

But we have a little data quality problem here.

So, the vast majority of them hit the checkout confirmation page,

this is where it really gets exciting, right?

Because you're assuming that all of the pages in

the revenue transactions table are exactly for batum checkout confirmation,

but the cool part is now we get to

see that our data isn't always what we think it's going to be.

So we have nine different records,

one visitor reached all of these different smaller pages,

and you can see different variants for that page title.

So, say we wanted to filter this data.

We filter that now using a where clause.

So by-and-large we captured almost 20,000 visitors,

you have a couple different options, you can ignore the rest of these rows.

So, we could do is where the hits.pageTitle is equal to checkout confirmation.

If you ran this,

you get an error says,

"Unexpected identifier confirmation", what on earth.

So, it's trying to basically say,

"Hey, there's no column called checkout."

The reason why that is as you might have guessed

is if you're matching on things that are non-numeric,

either encapsulate them or escape them in single quotes or double quotes.

So, let's go ahead and run that.

This is where you'll get verbatim checkout confirmation.

Now, what I'll challenge you if I ran this,

I just made a C lowercase and a random letter the N here uppercase,

still a valid query.

Do you think I'll get the same results?

Do you think it's case sensitive?

The answer is, it is.

So, if you're ever working with string values and you're

trying to match on something, say all lowercase,

this is probably a little bit more common, and you're wondering,

"Hey I'm executing this query why on earth am I not getting any results returned?

I've definitely done this in my own day."

It's because you're not absolutely certain that

the value that's going to be returned in that string is uppercase or lowercase.

Well, what's one of the tips and tricks that you can do if you didn't care?

You can actually invoke a quick function.

So, you can say for every single page title that's coming into

this query as we scan row by row as our sequel is executing,

you can actually make every single one of those field values into

a lowercase before we compare it with our string here.

So, whatever the page title is make

every single character in there lowercase and then compare it against here.

So, when you actually ran that,

now you don't have to worry about the case sensitivity

and you'll get it matched on it and returned anyways.

Now what happens if he said, "Hey,

I wanted to include those transactions that reached the checkout confirmation page,

but it wasn't really called the checkout confirmation page as exactly as we have it here.

Is there any way that we can do the wild-card matching?"

Absolutely. We can do that instead of using a equivalency operator,

like the equal sign is your equivalent directly,

we can use a wild-card operator,

and to do that we're going to use the like.

So we just did like, again,

that's pretty much equal to

the equal sign because we didn't have any wild-card operators.

So, say you wanted to begin or end,

or let's just do begin first.

Any number of characters could come before the word checkout and then conformations.

So, let's see if we get any hits off of that.

So, we get a few.

So, you can see this is six colon and space than checker confirmation,

but remember we had nine there before.

So, what you can do of course is add more wildcards to your heart's content.

What this will inform you again is the underlying quality of the data that you have,

if you remember one of

the data points here as we'll find that out is could be even more interesting than this.

Is if you're assuming all of

these different transactions are associated with some kind of a checkout,

let's see if we can do any of the ones that don't match this pattern.

So, this is the really interesting part.

You expected the checkout confirmation to be somewhere in the page title,

yet in our transactions table we have this mugs,

cups, drinkware, Google Merchandise Store.

So, then you can drill down into that particular record and find out

whether or not there was really a transaction or how that ended

up to being recorded as a transaction even though

that page title wasn't recorded as the transaction there.

So again, playing with these different options and getting familiar with the errors that

you're going to get out of here is going to be one of

the most versatile tools in the toolkit that you have.

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.

Baixar na App Store

Baixar no Google Play

Coursera
Sobre
Liderança
Carreiras
Lista de cursos
Certificados
Certificados MasterTrack™
Notas
For Enterprise
For Government

Comunidade
Learners
Partners
Developers
Beta Testers
Translators

Conectar
Blog
Facebook
LinkedIn
Twitter
Instagram
YouTube
Tech Blog

Mais
Termos
Privacidade
Ajuda
Acessibilidade
Imprensa
Contato
Diretório
Afiliados