1. Case study UDFs
It's time to identify what user defined functions we need to create to solve this business problem.
2. Taxi ride business problem
Let's revisit the case study. An EU private equity firm wants to understand the average fare per distance, ride count, and total ride time for each day of the week in each NYC borough. Do you think the overseas firm wants to see the distance in miles as it's stored in the dataset? You're right, they don't. How about the taxi fare data? They want to see these amounts in their home currency. Since you only want to display this data in a different format, you should write user defined functions. You need to create two functions: one to convert miles to kilometers, and another to convert currency based on the exchange rate parameter passed.
3. Taxi ride business problem
Let's review the 3rd item in the business problem.
Once we discover the borough with the most favorable metrics, the firm wants to understand those metrics at the Pickup location level within the borough. They also want to apply a driver shift grouping to address the temporal aspect of this data. You might be wondering how a shift should be defined? Throughout various industries shift definitions tend to change frequently and so we will apply a modular programming technique to isolate this logic in a user defined function. This will ensure the shift definitions can evolve with the ever-changing business needs, without affecting the final stored procedures.
4. Conversion UDFs
So what should our conversion functions look like? Here we have a user defined function that accepts an input parameter named miles, multiplies it by 1,609.34 and returns this distance in meters. Don't forget user defined functions must have the RETURNS data type definition and the RETURN keyword. The body of the function is also contained within a BEGIN END block.
Next, we have a user defined function that accepts two parameters; currency and the exchange rate to be applied to the currency amount.
5. Time to test
How do we know the functions are working as intended? We need to test to verify. This is an essential step in developing user defined functions, since executing them in various scenarios ensures they are robust.
Here we are selecting TripDistance and passing it to the ConvertMileToMeter() function. We are also selecting FareAmount and passing it to the ConvertCurrency() function.
Look closely at the results. Do you see a problem? How can all the MeterDistance values be whole numbers? We have the same problem with our Fare values. Oh no! Our functions are rounding to the nearest whole number. Oops! Let's fix that.
6. Iterate
Because we didn't specify the decimal places to be associated with the numeric data type, SQL used a default of zero. Here we use ALTER FUNCTION to set the precision to 18 and decimal places to 2 for our numeric data type parameters in each function. This also needs to be updated in our return value definitions.
7. What about Shifts?
Here is a function named GetShift() that accepts an hour input parameter and returns the shift integer associated with that hour. Shift logic can often get complex and this function could be modified to contain additional business rules.
8. Test Shifts
Let's test the shift function. This query selects the hour value of the PickupDate and the result of the GetShift() function when that hour value is passed.
Review the results to ensure the function is correct.
9. Your turn!
It's your turn to apply what you've learned to start solving the business case.