SQL User Defined Functions (UDE'S)

 Create a function named SumRideHrsSingleDay() which returns the total ride time in hours for the @DateParm parameter passed.


- Create SumRideHrsSingleDay

CREATE FUNCTION SumRideHrsSingleDay (@DateParm date)

-- Specify return data type

RETURNS numeric

AS

-- Begin

BEGIN

RETURN

-- Add the difference between StartDate and EndDate

(SELECT SUM(DATEDIFF(second, StartDate, EndDate))/3600

FROM CapitalBikeShare

 -- Only include transactions where StartDate = @DateParm

WHERE CAST(StartDate AS date) = @DateParm)

-- End

END



2. Often times you will need to pass more than one parameter to a function. Create a function that accepts @StartDateParm and @EndDateParm and returns the total ride hours for all transactions that have a StartDate within the parameter values.


CREATE FUNCTION SumRideHrsDateRange (@StartDateParm datetime,@EndDateParm datetime)

-- Specify return data type

RETURNS numeric

AS

BEGIN

RETURN

-- Sum the difference between StartDate and EndDate

(SELECT SUM(DATEDIFF(second, StartDate, EndDate))/3600

FROM CapitalBikeShare

-- Include only the relevant transactions

WHERE StartDate > @StartDateParm and StartDate < @EndDateParm)

END



ITVF

CREATE FUNCTION SumStationStats(@StartDate AS datetime)

-- Specify return data type

RETURNS TABLE

AS

RETURN

SELECT

StartStation,

    -- Use COUNT() to select RideCount

COUNT(ID) AS RideCount,

    -- Use SUM() to calculate TotalDuration

    SUM(Duration) AS TotalDuration

FROM CapitalBikeShare

WHERE CAST(StartDate as Date) = @StartDate

-- Group by StartStation

GROUP BY StartStation;














Multi Statement TVF

CREATE FUNCTION CountTripAvgDuration (@Month CHAR(2), @Year CHAR(4))
-- Specify return variable
RETURNS @DailyTripStats TABLE(
TripDate date,
TripCount int,
AvgDuration numeric)
AS
BEGIN
-- Insert query results into @DailyTripStats
INSERT @DailyTripStats
SELECT
    -- Cast StartDate as a date
CAST(StartDate AS date),
    COUNT(ID),
    AVG(Duration)
FROM CapitalBikeShare
WHERE
DATEPART(month, StartDate) = @Month AND
    DATEPART(year, StartDate) = @Year
-- Group by StartDate as a date
GROUP BY CAST(StartDate AS date)
-- Return
RETURN
END

Comments

Post a Comment

Popular posts from this blog

Binomial Test in Python

  Binomial Test Let’s imagine that we are analyzing the percentage of customers who make a purchase after visiting a website. 1000 customers visited the site this month, and 58 of them made a purchase. The marketing department reports that historical data suggests about 72 of every 1000 visitors make a purchase. Thus, they estimate that the probability of any particular customer making a purchase is 7.2%. We would like to know if this month’s number, 58 purchases, is significantly different from normal or a reasonable fluctuation due to random chance. In previous exercises, we collected samples of numerical information (eg. order price) and then used the mean and standard deviation of those samples to make comparisons. In contrast, we now have a sample where each unit (a visitor) falls into one of two discrete categories: “made a purchase” “did not make a purchase” Instead of comparing sample means, we want to compare the percent in the “made a purchase” category to some expectatio...
Read more

Slicing and Indexing in Python Pandas

 # Make a list of cities to subset on cities = ["Moscow", "Saint Petersburg"] # Subset temperatures using square brackets print(temperatures[temperatures["city"].isin(cities)]) # Subset temperatures_ind using .loc[] print(temperatures_ind.loc[cities]) temperatures_ind = temperatures.set_index(['country','city']) # List of tuples: Brazil, Rio De Janeiro & Pakistan, Lahore rows_to_keep = [('Brazil','Rio De Janeiro'),('Pakistan','Lahore')] # Subset for rows to keep print(temperatures_ind.loc[rows_to_keep]) # Sort temperatures_ind by index values print(temperatures_ind.sort_index()) # Sort temperatures_ind by index values at the city level print(temperatures_ind.sort_index(level="city")) # Sort temperatures_ind by country then descending city print(temperatures_ind.sort_index(level=["country", "city"], ascending = [True, False])) Slice rows with code like  df.loc[("a", ...
Read more

Python Syntax and Functions Part2 (Summary Statistics)

The  .agg()  method allows you to apply your own custom functions to a DataFrame, as well as apply functions to more than one column of a DataFrame at once, making your aggregations super efficient. In the custom function for this exercise, "IQR" is short for inter-quartile range, which is the 75th percentile minus the 25th percentile. It's an alternative to standard deviation that is helpful if your data contains   import numpy as np def iqr(column):     return column.quantile(0.75) - column.quantile(0.25) # Update to print IQR and median of temperature_c, fuel_price_usd_per_l, & unemployment print(sales[["temperature_c", "fuel_price_usd_per_l", "unemployment"]].agg([iqr, np.median])) Calulating Cumulative Statistics Syntax = df.drop_duplicates sales_1_1 = sales_1_1.sort_values("date") # Get the cumulative sum of weekly_sales, add as cum_weekly_sales col sales_1_1["cum_weekly_sales"] = sales_1_1["weekly_sales...
Read more