About this project
Exercise
Let's run some cross-website statistics now. Take the day May 14, 2016 and for each row, show: website_id, revenue on that day, the highest revenue from any website on that day (AS highest_revenue and the lowest revenue from any website on that day (as lowest_revenue).
Select
website_id,
revenue,
first_value(revenue)over(order by revenue) as lowest_revenue,
last_value(revenue)over(order by revenue ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )as highest_revenue
from statistics
where day = '2016-05-14'
website_idrevenuelowest_revenuehighest_revenue30.600.6085.50238.710.6085.50185.500.6085.50
Practice 2
Perfect! Let's move on.
Exercise
Take the statistics for website_id = 1. For each row, show the day, the number of clicks on that day and the median of clicks in May 2016 (calculated as the 16th value of all 31 values in the column clicks when sorted by the number of clicks).
select
day,
clicks,
Nth_value(clicks,16)over(order by clicks rows between UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
from statistics
where website_id = 1
dayclicksnth_value2016-05-151355262016-05-041605262016-05-202295262016-05-01237526
Practice 3
Great! The last exercise ahead of you.
Exercise
For each statistics row of website_id = 3, show the day, the number of clicks on that day and a ratio expressed as percentage: the number of clicks on that day to the greatest number of clicks on any day. Round the percentage to integer values
select
day,
clicks,
Round(clicks::numeric/last_value(clicks)over(order by clicks rows between UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)*100)
from statistics
where website_id = 3
dayclicksround2016-05-01112016-05-02112016-05-0311
Check answers-lokks wrong
Summary
Excellent!! That's all we wanted to teach you today. Let's review what we've learned:
- LEAD(x) and LAG(x) give you the next/previous value in the column x, respectively.
- LEAD(x,y) and LAG(x,y) give you the value in the column x of the row which is y rows after/before the current row, respectively.
- FIRST_VALUE(x) and LAST_VALUE(x) give you the first and last value in the column x, respectively.
- NTH_VALUE(x,n) gives you the value in the column x of the n-th row.
- LAST_VALUE and NTH_VALUE usually require the window frame to be set to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
Exercise 1
priceday33.032016-06-0143.842016-06-0237.252016-06-0350.162016-06-04
Advertisement
Exercise 1
For each row, show the day, the price on that day and the price on the next day.
select
day,
price,
lead(price) over()
from advertisement
daypricelead2016-06-0133.0343.842016-06-0243.8437.252016-06-0337.2550.162016-06-0450.1626.63
Exercise 2
For each row, show the day, the price on that day, the price 7 days earlier and the difference between these two values.
select
day,
price,
lag(price,7) over(order by day),
price-lag(price,7) over(order by day)
from advertisement
daypricelag?column?2016-06-0133.03nullnull2016-06-0243.84nullnull2016-06-0337.25nullnull
Exercise
For each row, show the day, the price on that day, the highest price ever (column name highest_price) and the lowest price ever (column name lowest_price).
select
day,
price,
first_value(price) over(order by price) as lowest_price,
first_value(price) over(order by price desc) as highest_price
from advertisement
daypricelowest_pricehighest_price2016-06-0450.1621.5450.162016-06-3049.7021.5450.162016-06-2249.4421.5450.162016-06-2548.2121.5450.16
SELECTday, price, FIRST_VALUE(price) OVER(ORDERBYprice) ASlowest_price, LAST_VALUE(price) OVER( ORDERBYprice ROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING) AShighest_price FROMadvertisement;
Both are same
Additional project images
