Window Function - Part two
In the second part of the article, we are going to discuss analytical functions and how we can use their special capabilities to help us solve different problems and scenarios. To read the first part - click here
analytical functions can be divided into two categories:
Analytical functions can be divided into two categories:
As shown in the table above, each group contains five analytical functions. in this article, however, we are going to discuss only three analytical functions which are among the most commonly used:
row_number?
let's take another look at our sales table:
(down below is the script for creating the sales table. the queries are running on sql server but can be executed on any platform. you can skip this part if don’t wish to run the code on your local environment).
create table sales
(
category varchar(25),
[date] date,
sales int
)
insert into sales values
('electronics','2021-08-18',1617),
('furniture','2021-11-09',1532),
('clothing','2021-11-27',1060),
('clothing','2021-08-18',1617),
('clothing','2021-08-08',1618),
('clothing','2021-11-25',1661),
('office supply','2018-06-29',815),
('office supply','2021-12-20',3696),
('furniture','2018-10-30',844),
('furniture','2021-12-04',1747),
('electronics','2020-11-23',4377),
('electronics','2020-10-16',1115),
('electronics','2021-08-17',3786),
('electronics','2021-10-17',1423),
('electronics','2020-09-23',4331),
('electronics','2018-12-15',3144),
('electronics','2019-07-11',1180),
('electronics','2021-10-14',3499),
('electronics','2019-09-20',406)
----
领英推荐
select * from sales
Suppose that you are the manager of the store and you want to see all the top sales of each month and each category. now, in this example, the table is rather narrow and you could use traditional group by clause to solve it:
select category
datename(month, date) as month_name, max(sales) as top_sale
from sales
group by category, datename(month, date)
order by category,? month_name
But - what if you actually wanted to see the full date of the sale and not just the month name??
Or what if there were other columns that describe the name of the seller who sold that specific sale, his sex, age, working duration in the store - etc.
A group by clause won't help you this time.
you can try writing a CTE:
with group_tab a
(
select category, datename(month,date)as month_name, max(sales) as top_sale
from sales
group by category, datename(month,date)
),
date_tab as
(
select sales,date as sale_date
from sales
)
select gt.category,gt.month_name,gt.top_sale,dt.sale_date
from group_tab gt join date_tab dt
on gt.top_sale = dt.sales
order by category
But again the code is a bit long and clumsy. you can use the exact same result using the row number function:
with tab_1 as
(
select *,
row_number() over(partition by category, month(date) order by sales desc) as rn
from sales
)
select category, datename(month,date) as month_name ,sales as top_sale, date as sale_date
from tab_1
where rn = 1
Much shorter, simpler, and more flexible code!