SQL LAG,LEAD,Power.SQRT Functions

 With the LEAD() function, you can access data from a subsequent row in the same query, without using the GROUP BY statement. This way, you can easily compare values from an ordered list.

This is the syntax: LEAD(numeric_expression) OVER ([PARTITION BY column] ORDER BY column)



SELECT 

first_name,

last_name,

total_votes AS votes,

    -- Select the number of votes of the next voter

LEAD(total_votes) OVER (ORDER BY total_votes) AS votes_next_voter,

    -- Calculate the difference between the number of votes

LEAD(total_votes) OVER (ORDER BY total_votes) - total_votes AS votes_diff

FROM voters

WHERE country = 'France'

ORDER BY total_votes;



SELECT 

broad_bean_origin AS bean_origin,

rating,

cocoa_percent,

    -- Retrieve the cocoa % of the bar with the previous rating

LAG(cocoa_percent) 

    OVER(PARTITION BY broad_bean_origin ORDER BY rating) AS percent_lower_rating

FROM ratings

WHERE company = 'Fruition'

ORDER BY broad_bean_origin, rating ASC;



First Value and Last Value Function


Exercise

Getting the first and last value

The analytical functions that return the first or last value from an ordered list prove to be very helpful in queries. In this exercise, you will get familiar with them. The syntax is:

  • FIRST_VALUE(numeric_expression) OVER ([PARTITION BY column] ORDER BY column ROW_or_RANGE frame)

  • LAST_VALUE(numeric_expression) OVER ([PARTITION BY column] ORDER BY column ROW_or_RANGE frame)


SELECT 
first_name + ' ' + last_name AS name,
country,
birthdate,
-- Retrieve the birthdate of the oldest voter per country
FIRST_VALUE(birthdate) 
OVER (PARTITION BY country ORDER BY birthdate) AS oldest_voter,
-- Retrieve the birthdate of the youngest voter per country
LAST_VALUE(birthdate) 
OVER (PARTITION BY country ORDER BY birthdate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS youngest_voter
FROM voters
WHERE country IN ('Spain', 'USA');




Maths and Analytical Function in SQL

1] Power(No,2)
2] SQUARE()
3] SQRT()
4]ABS
5] Ceiling
6] Floor
7] Round()


Sometimes in your database development, you may need to round the results of a calculation. There are three functions you can use for this:

  • CEILING(expression): rounds-up to the nearest integer value
  • FLOOR(expression): rounds-down to the nearest integer value
  • ROUND(expression, length): rounds the expression to the specified length.

The exponential functions are useful when you need to perform calculations in the database. For databases storing real estate information, for example, you may need to calculate areas. In this case, these functions may come in handy:

  • POWER(number, power): raises the number to the specified power
  • SQUARE(number): raises the number to the power of 2

Or, if you need to calculate the distance between two cities, whose coordinates are known, you could use this function:

  • SQRT(number): calculates the square root of a positive number.

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