Welcome everybody. You're watching Mr. Fugu Data Science.
And today we're using Python to Postgres to create two views with some bonus
date/time queries. As always the code is in the link and description below. If you
do not have user credentials: please click the card with the video above. If
you do not have psy cop g2 installed, please pause the video and do that now.
If you do not need user credentials feel free to use this generalized setup here
to get started. I need you to import psycopg2, pandas and if you have
your credentials. Here where this is your file name this is the function storing
your parameters, where mine are key value pairs. So create my connection, then I
create a cursor a cursor allows you to run your queries and send and retrieve
data. I use the auto commit equals true because, I want after each operation I
perform to happen immediately. Now, these are what our data will look like after
we create our first view. We have two tables which are staging fake people and
staging fake CPU purchases. Where we have the credit card, email, first and last
name, and the phone number. And the first one the date of purchase was CPU they
purchased, and the credit card number used. Which will account for our inner
join. Then we will create this setup of our table after our join. But, what is a
view consider a pseudo table that's representing our data. And does not store
anything unless you're using a materialized view. The materialized view
is very good for fast look ups and a good technique for data warehousing. You
can use your view to basically subset your data, join multiple tables, or even
grant permissions. The general setup you can do a create a replace and you can
create a temp view. Which ends after each session. Or you can send us some options
and whatever query you would like to perform.
When would you actually use this? Well if you're performing some kind of complex
query or running some kind of task quite often; it would be a good idea to use
your view. But, consider the fact that doing updates, deletes, or inserts aren't
used in your view. You're just basically using it to look at your data or run
further queries or processing for analytics. But there are situations where
you can create a view, through triggers to do your updates, inserts or
deletes so take that into consideration. Our first view, we need to assume that
our supervisor wants us to create a table with the first to last name which
would be joined together with a credit card number the CPU and the purchase
date. Well we have these two tables here which we would like to create this one
table through a join of the credit card. Let's do that now. So you say create view
and then you put your view name that you would like to use. Where I will call mine
customer CPU sales. And then you say as select and then what are you doing?
You're first going to take the first and last name and join those together into
one column instead of two from this table. We need to call our first name
I'll just copy that real quick. Let's start a new line. But, we have something
here. Where we take the pipe symbol and then we take a empty string and then the
pipes again. Then we're going to call in our last name. So now that's put
together as one. What else do we need to do? We need to alias it. Let's
just call a full name; and then we need to go into our second table. And now we
need something from there. Ao we need to paste that in. And let's call our CPU. what
we're taking in. We're also going to take maybe this one this table. And then now
we say from staging fake CPU purchases; that's inner join our data. And then we
need to do the staging fake people we are saying on: where we take the credit
card for each equals and then the staging fake people on the credit card
as well. And then that should be good here, perfect. Now, let's execute that and
see what happens. But, how do we check it? well the Select
everything from and what is our view our view is called customer CPU sales and
then this limit that. So then we could just print off a few. So here we go. So
now we have the first name and last name put together. We have the CPU that they
have in the purchase date. Let us look at what this looks like inside of Postgres.
So we could actually see full name CPU and the purchase date, okay that's fine.
We're getting into what we need. Now if you needed to alter this; like you needed
to change the first name of it you could put the first name of what your original
view was. And then put the new name you want to call it or you can also drop the
view and say drop view with your view name. Now let's look and print the days
that have elapsed since the purchase date. Well how would you actually do that?
You would say: take that and call it sales by date, and we need to actually
select day. and we're gonna use this truncate where we were calling the day
But we're taking it from today's date and we're subtracting it from the
purchase date. And then you do this notation what two colons and call it
date, okay. Then you're calling that from the customer CPU sales, okay. So let's
look at that. Here's what one of those looks like. We can look at all of them.
Here's what they look like. So this is the first one, the second one, etc. Now,
that's interesting; so then we could say well let's put that into a view. Purchase
by date, that would look better. So then we call them the same thing what we
just did. And we could call this column what we're creating day since purchase.
We could set up this view there we are. Then let's just select all from this, and
there you go. So here's the day so that elapsed since this purchase, here's these
days that elapsed since this purchase, okay. Cool so we have to currently but, what
about if we wanted to say let's track the age of a purchase for some warranty
information? And you say okay well what do we do with this? Then we get to do
something nifty and say now what else can we do? Well let's use the extract
function. And let's assumed that we wanted to track the
age of a customer purchase for warranty purposes. Well we can do that. Where we
select everything from customer purchased by a date we just created. And
then we'll use the where clause where we use the extract function. And we want to
take the days from, what? Well what do we call this above? We called it
they sense purchase so we want to pluck that. And then we want to do anything
less than 180 days. And let's just look at what a few of those entries look like.
Here's what we look like. So let's assume that we had a warranty
lit was only for six months, and we wanted to look at all the purchases that
happened and how will those purchases are. That's one way we could do this
right here so that sounds good now sometimes you have something that
doesn't necessarily work for a query. So if you're doing a case when; which is
basically like an if-else statement. You have an issue with this extract function.
Because, we use a where clause above which handled this. But, then what you
notice here is that if you are creating a new column that says yes it's in
warranty no it's out of warranty. It's not taking care of this. So this is
basically getting not used, okay. Sometimes you have issues with how a
clause or the order of operations can affect what you're doing next. I have
some workarounds of how you can do this. But, I just wanted to let you know that
if you try to use this as an nested statement it's not working. So take that
into consideration, okay. Lastly let's could create a date range;
where we want to look at sales data by month and year, okay. And this is
something that would realistically come up for your job. So you need to
do the day truncate again. But, now we're doing it by a month. Where
we're taking a purchase date, and we are saying what? Let's take this as some
variable name, I don't know I'll just make up a variable name. And then we
shall count these data by the CPU purchased. And we'll call it as monthly
sum. And then we're going to say from what?
We're going to say from the people CPU purchase table ,and then lets do a group
buy, and then we're going to group by this variable that I just made it up. And
then you also need to do an order by. And you need to take that and do it in
descending order. So let's look at what this is, okay. Now, we got this correctly
labeled. But, it looks kind of goofy because now used to have some timestamp
information, that we need to take care of. So you have one more native function
that you need to take care of where you call PG underscore catalog and then dot
date and that takes care of this. Also, if you didn't pay attention here and you
use some you would have an issue because you're not dealing with numeric data.
You're actually dealing with string names. So that's why you have to use
count. So just take those into consideration. So now you have a query
that's based on month and year sales data and the quantity that is sold during
that month of that year, which is really good. And you could do a simple plot for
that. Lastly, I'd like to say so like if you thought this was useful content and brought
utility to somebody. Subscribe and turn on that notification bell so you can be
up to date for any new material. As always I'd like to say thank you for
watching. See in the next video, bye