From the course: MySQL Data Analysis

CASE example - MySQL Tutorial

From the course: MySQL Data Analysis

Start my 1-month free trial Buy for my team

CASE example

“

- [Narrator] Alright, let's get into more detail on case statements. Like I said, the case statements are very powerful, but they're a little complicated at first. I think once you get the hang of it, you'll realize they're not a big deal. But I'm going to walk you through some examples just to illustrate how it works and hopefully make this a lot more clear. So case statements allow us to specify conditional logic on how our results should be calculated for various different cases. One of the most common things that I use cases for is bucketing data. And my pro tip for you is anytime your data feels a little too noisy or granular, think about rolling it up to a higher level by using a case statement. Here's an example of where we're doing just that. We've got this length data. Minute level length data might be a little too granular. So we take this case statement and we roll up those length values into higher level buckets. In this case, I've tried to create mutually exclusive and collectively exhaustive buckets. That means no length is going to fall into more than one bucket and every length will be represented. So I have a when/then pair that's looking for lengths under 60 minutes and I'm calling them under one hour. I have one that's looking for between 60 and 90 minutes, and I'm calling that 1 to 1.5 hours, and I have one that's looking at over 90 minutes and we're calling that over 1.5 hours. Then we have an else on there that says "Uh oh...check logic." I always put that on there as a catchall. Like I said, I tried to design my case buckets where they would be mutually exclusive and collectively exhaustive, and I would tell you that's a good best practice, but it's also a good best practice to have this else on there in case you mess up on the first part. I do this all the time. I don't always get errors, but I do sometimes catch myself with errors and it's really good to have that on there. Here's what those query results look like. You see we've got our lengths in the left and then we've got the length bucket that was assigned to them using the case in the right. Hopefully this one's pretty easy to follow, but we're also going to walk through this in a little more detail just exactly how the case statement is executing. So here we've got our case. What's important to remember here is when you have multiple when/then pairs, the case statement is going to execute top to bottom, so always think left to right, top to bottom. It will execute that first when/then pair. So in this case when length less than 60, then under one hour. If that first when logic returns true, then the case statement will end. If not, it'll move on to the next when/then pair, and that process will repeat until one statement returns true or we reach the end of the case. If you write a case statement where the buckets are not mutually exclusive, then the uppermost condition will determine the value that comes out because the uppermost condition evaluates first. We'll go through an example of how that would work later. If none of the conditions of the when/then pairs are satisfied and there's no else, then your case will reach an end and it will return a null value. Let's go through these three examples of how the case statement would execute. So for a value of 52, the case statement would look at that first when/then pair, and it would say when length, less than 60. Okay, that's true. Then we'll go ahead and return under one hour, end the case. The case is over because one of those when/then pairs was satisfied. In the case of 65, it will evaluate that first when length, less than 60 as false, and then it'll try the next condition. Then it says when length between 60 and 90. That's true. So it will return one to 1.5 hours, and then in the case of 125, it's going to evaluate the first two conditions, which are both false because 125 is greater than 60 and it's not between 60 and 90, and then it's going to evaluate that third condition and it will come back as true. Now we've modified our case and we've changed these two when/then pairs. This time, our buckets are no longer mutually exclusive or collectively exhaustive. For example, a value of 48 that would fit into both the first and the second bucket and a value of 117 would not satisfy any of the first three criteria. So let's take a look at how some different values would get assigned. Before we do that, I'm going to reiterate something I said previously as my pro tip. Make sure you always include some error messaging in your else. This way you can catch yourself when you make a mistake like this and you can make sure that all of your values get bucketed the way that you want. So let's take a look at the different values. Here we see that 48 and 50, while they satisfy both the first and the second criteria, they've been assigned to under one hour, and that's because the first when/then pair is going to execute first. So when the case is evaluating 48 and 50, it never actually makes it to evaluate when length is less than 90 because the case has already concluded by a successful evaluation of when length is less than 60. Also, take a look at the length of 117. This one doesn't fit in any of our buckets, so we return the error. Uh oh...check logic. Highly recommend that you adopt two practices, always having an error statement and really trying to write your case statements so that they are mutually exclusive and collectively exhaustive.

Contents