Exploratory Data Analysis

Temporal EDA

Exploratory Data Analysis (EDA) is an iterative process with no specific checklist for EDA questions.

Untitled

SQL Functions for EDA

-- CONVERT Syntax:
CONVERT (data_type[(length)], expression[,style])
-- Returns expression based on data_type

--DATEPART Syntax
DATEPART(datepart, date)
-- Returns int

-- DATENAME syntax
DATENAME(datepart, date)
-- Returns nvarchar

-- DATEDIFF Syntax
DATEDIFF(datepart, startdate, enddate)
-- Returns int; can't use datepart weekday value

-- datepart value s= year, quarter, month, dayofyear, day, week, weekday, hour,
-- minute, second, microsecond, nanosecond

-- CONVERT
SELECT
    TOP 1 PickUpDate,
    CONVERT(DATE, PickupDate) AS DateOly
FROM YellowTripData

-- DATEPART
SELECT
    TOP 3 COUNT(ID) AS NumberofRides,
    DATEPART(HOUR, PickupDate) AS Hour
FROM YellowTripData
GROUP BY DATEPART(HOUR, PickupDate)
ORDER BY COUNT(ID) DESC

-- DATENAME
SELECT
    TOP 3 ROUND(
        SUM(FareAmount),
        0
    ) as TotalFareAmt,
    DATENAME(WEEKDAY, PickupDate) AS DayofWeek
FROM YellowTripData
GROUP BY DATENAME(WEEKDAY, PickupDate)
ORDER BY SUM(FareAmount) DESC;

--DATEDIFF
SELECT
    AVG(
    DATEDIFF(SECOND, PickupDate, DropOffDate)/ 60
    ) AS AvgRideLengthInMin
FROM YellowTripData
WHERE DATENAME(WEEKDAY, PickupDate) = 'Sunday';

Variables for datetime data

-- DECLARE variable and assign initial value
DECLARE @StartTime as time = '08:00 AM'

-- DECLARE variabel and then SET value
DECLARE @StarTime AS time
SET @StartTIme = '08:00  AM'

--  DECLARE variable then SET value
DECLARE @BeginDate as date
SET
    @BeginDate = (
        SELECT TOP 1 PickupDate
        FROM YellowTripData
        ORDER BY PickupDate ASC
    )

CASTing

-- CAST syntax
CAST (expression AS data_type [(length)])
-- Returns expression based on data_type

-- DECLARE datetime variabel
-- SET value to @BeginDate and @StartTime while CASTing
DECLARE @StartDateTime as datetime
SET @StartDateTime = CAST(@BeginDate as datetime) + CAST(@StartTime as datetime)

-- DECLARE table variable with two columns
DECLARE @TaxiRideDates TABLE (
    StartDate date,
    EndDate date
)

-- INSERT static values into table variable
INSERT INTO @TaxiRideDates (StartDate, EndDate)
SELECT '3/1/2018', '3/2/2018'

-- INSERT query result
INSERT INTO @TaxiRideDates(StartDate, EndDate)
SELECT DISTINCT
    CAST(PickuPDate as date),
    CAST(DropOffDate as date)
    FROM YellowTripData;

Date Manipulation

GETDATE

SELECT GETDATE()

DECLARE @CurrentDateTime AS datetime
SET @CurrentDateTime = GETDATE()
SELECT @CurrentDateTIme

-- DATEADD Syntax:
DATEADD(datepart, number, date)
-- Returns expression based on data_type

-- Oneday after 2/27/2019
SELECT DATEADD(day,1,'2/27/2019')

DATEADD and GETDATE

--Yesterday
SELECT DATEADD(d,-1,GETDATE())

-- Yesterday's Taxi Passenger Count
SELECT SUM(PassengerCount)
FROM YellowTripData
WHERE CAST(PickupDate as date) = DATEADD(d,-1,GETDATE())

DATEDIFF

SELECT DATEDIFF(day, '2/27/2019','2/28/2019')

SELECT DATEDIFF(year, '12/31/2017', '1/1/2019')

-- First Day of Current Week
SELECT DATEADD(week, DATEDIFF(week, 0, GETDATE()),0)

-- First step
GETDATE()

-- How many weeks between today and 1/1/1900?
SELECT DATEDIFF(week,0,GETDATE())

-- Add zero to the 6218nd week
SELECT DATEADD(week, DATEDIFF(week,0,GETDATE()),0)

User Defined Functions

What are User Defined Functions (UDFs)?

User Defined Functions are routines that

can accept input parameters
perform an action
return result (single scalar value or table)

Why use UDFs?

Because

It can reduce execution time.
It can reduce network traffic.
It allows for modular programming.

What is modular programming?

Software design technique
Separates functionality into independent, interchangeable modules
Allows code reuse
Improves code readability

Scalar UDF

with no input parameter

-- Scalar function with no input parameters
CREATE FUNCTION GetTomorrow()
    RETURNS date AS BEGIN
RETURN (SELECT DATEADD(day,1,GETDATE()))
END

with one parameter

-- Scalar function with one parameter
CREATE FUNCTION GetRideHrsOneDay(@DateParm date)
    RETURNS numeric AS BEGIN
RETURN (
    SELECT
        SUM(
            DATEDIFF(second, PickupDate, DropoffDate)
        )/360
        FROM
            YellowTripData
        WHERE
            CONVERT (date, PickupDate) = @DatePar
)
END;

with two parameters

-- Scalar function with two input parameters
CREATE FUNCTION GetRideHrsDateRange (
    @StartDateParm datetime, @EndDateParm datetime
) RETURNS numeric AS BEGIN RETURN (
SELECT
    SUM(
        DATEDIFF(second, PickupDate, DropOffDate)
    )/ 3600
FROM YellowTripData
WHERE
    PickupDate > @StartDateParm
    AND DropoffDate < @EndDateParm
) END;

Table Valued UDFs

Inline Table Valued Functions (ITVF)

CREATE FUNCTION SumLocationStats(
    @StartDate AS datetime = '1/1/2017'
) RETURNS TABLE AS RETURN
SELECT
    PULocationID AS PickupLocation,
    COUNT(ID) AS RideCount,
    SUM(TripDistance) AS TotalTripDIstance
FROM YellowTripData
WHERE CAST(PickupDate AS Date) = @StartDate
GROUP BY PULocationID;

Multi-Statement Table-Valued Function (MSTVF)

CREATE FUNCTION CountTripAvgFareDay (
    @Month char(2),
    @Year char(4)
) RETURNS @TripCountAvgFare TABLE(
    DropOffDate date, TripCount int, AvgFare numeric
) AS BEGIN INSERT INTO @TripCountAvgFare
SELECT
    CAST(DropOffDate as date),
    COUNT(ID),
    AVG(FareAmount) as AvgFareAmt
FROM YellowTripData
WHERE
    DATEPART(month, DropOffDate) = @Month
    AND DATEPART(year, DropOffDate) = @Year
GROUP BY CAST(DropOffDate as date)
RETURN END;

Differences between ITVF vs MSTVF

	ITVF	MSTVF
The RETURNS Syntax	You simply state `RETURNS TABLE` and the return table’s definition will be based on the function’s `SELECT` statement. No need to specify the structure of the return table.	Your `RETURNS` syntax explicitly specifies the structure
of the return table. This is done by declaring a TABLE variable that
will be used to store and accumulate the rows that are returned as the
value of the function.
The BEGIN/END Syntax	ITVFs do not use the `BEGIN`/`END` syntax.	MSTVFs do use the `BEGIN`/`END` syntax.
Performance	Generally faster than MTSVFs.	Generally slower than ITVFs.
Data Updates	In some cases it’s possible to update data in the underlying tables using an ITFV.	You cannot update data in the underlying tables using a MSTVF.

UDFs In Action

Execute Scalar with SELECT

-- Select with no parameters
SELECT dbo.GetTomorrow()

Execute Scalar with EXEC and Store Result

-- EXEC & store result in variable
DECLARE @TotalRideHrs AS numeric
EXEC @TotalRideHrs = dbo.GetRideHrsOneDay @DateParm = '1/15/2017'
SELECT
    'Total Ride Hours for 1/15/2017:',
@TotalRideHrs

SELECT parameter value $scalar UDF

-- Declare parameter variable
-- Set to oldest date in YellowTripData
-- Pass to function with select
DECLARE @DateParm as date =
(SELECT TOP 1 CONVERT(date, PickupDate)
    FROM YellowTripData
    ORDER BY PickupDate DESC)
SELECT @DateParm, dbo.GetRideHrsOneDay (@DateParm)

DECLARE @CountTripAvgFareDay TABLE(
    DropOffDate date,
    TripCount int,
AvgFare numeric)
INSERT INTO @CountTripAvgFareDay
SELECT TOP 10 *
FROM dbo.CountTripAvgFareDay (01, 2017)
ORDER BY DropOffDate ASC

SELECT * FROM @CountTripAvgFareDay

Maintaining User Defined Functions

ALTER Function

ALTER FUNCTION SumLocationStats (@EndDate as datetime = '1/01/2017')
RETURNS TABLE AS RETURN
SELECT
    PULocationID as PickupLocation,
    COUNT(ID) as RideCount,
    SUM(TripDistance) as TotalTripDistance
FROM YellowTripData
WHERE CAST(DropOffDate as Date) = @EndDate
GROUP BY PULocationID;

CREATE OR ALTER

CREATE OR ALTER FUNCTION SumLocationStats (
@EndDate AS datetime = '1/01/2017')
RETURNS TABLE AS RETURN
SELECT
    PULocationID as PickupLocation,
    COUNT(ID) AS RideCount,
    SUM(TripDistance) AS TotalTripDistance
FROM YellowTripData
WHERE CAST(DropOffDate AS Date) = @EndDate
GROUP BY PULocationID;

-- Delete function
DROP FUNCTION dbo.CountTripAvgFareDay

-- Create CountTripAvgFareDay as Inline TVF instead of MSTVF
CREATE FUNCTION dbo.CountTripAvgFareDay(
    @Month char(2),
    @Year char(4)
) RETURNS TABLE AS RETURN (
SELECT
    CAST(DropOffDate as date) as DropOffDate,
    COUNT(ID) as TripCount,
    AVG(FareAmount) as AvgFareAmt
FROM YellowTripData
WHERE
    DATEPART(month, DropOffDate) = @Month
    AND DATEPART(year, DropOffDate) = @Year
GROUP BY CAST(DropOffDate as date));

Determinism Improves Performance

A function is deterministic when it returns the same result given
- the same input parameters
- the same database state

Schemabinding

Specifies the schema is bound to the database objects that it references
Prevents changes to the schema if schema bound objects are referencing it

Stored Procedures

What is a Stored Procedure?

Routines that

Accept input parameters
Perform actions (EXECUTE, SELECT, INSERT, UPDATE, DELETE, and other SP statements)
Return status (success or failure)
Return output parameters

Why use Stored Procedures?

Can reduce execution time
Can reduce network traffic
Allow for Modular Programming
Improved Security

What’s the difference?

| --- | --- |

Create Procedure with Output Parameter

-- First four lines of code
-- SP name must be unique
CREATE PROCEDURE dbo.cuspGetRideHrsOneDay
    @DateParm date,
    @RideHrsOut numeric OUTPUT
AS
SET NOCOUNT ON
BEGIN
SELECT
    @RideHrsOut = SUM(
    DATEDIFF(second, PickupDate, DropoffDate)
    )/ 3600
FROM YellowTripData
WHERE CONVERT(date, PickupDate) = @DateParm
RETURN
END;