Window Functions: Window helps us to write a complex query. We can use different Analytical functions to perform Window functions in sql. They calculate an aggregate value based on group of rows. Returns multiple rows from each group.
Numbering : ROW_NUMBER, RANK, DENSE-RANK, NTILE,PERCENT_RANK
Navigational : LEAD, LAG, FIRSTVALUE, LASTVALUE
Aggregate : SUM, COUNT, AVG, MIN, MAX
Syntax for window function :
SELECT column_1, column_2 ,
ROW_NUMBER() OVER (…..) as a
FROM table_name
WINDOW w as (…….);
Example:
ROWNUMBER :
select e.*, row_number() over(partition by department_name order by employee_id) as rn from employee;
In the below query we are trying to retrieve the data from employee table, top 3 employees with highest salary in each department:
RANK :
select * from(select e.*, dense_rank() over(partition by department_name order by salary desc) as rank from employees e;) x where x.rank≤3;
DENSE RANK :
select * from(select e.*, rank() over(partition by department_name order by salary desc) as rank from employees e;) x where x.rank≤3;
For the same requirement we can use dense_rank(),but the difference between rank() and dense_rank() is if the salary column have values like 1000,2000,2000,3000 then the rank function gives the rank to 3000 as 1 , 2000 as 2, other 2000 as 2 and last 1000 as 4. Here as there are 2 rows with 2000 data the rank is given as 2 but for next data the rank 3 is skipped and 4 is assigned. But the dense rank function gives 3000 as 1, 2000 as 2, other 2000 as 2 and the next 1000 as 3 in its rank.
FIRST VALUE :
In the below query we are trying to retrieve the data of customers who first logged into the account.
select distinct customer_id, First_Value(account_id) over(partition by customer_id order by event_date) as account_id from Activity;
CTE’s : They are briefly defined as Common Expression Tables. In simple terms they can be called as temporary result sets. Let us imagine a complicated query which contains different sub queries. using CTE’s we can name these sub queries and the sub queries are added at the top of the query.
For example let us go through the below example where we used a cte named CTE_Patient and have given the sub query for counting no.of patients with given conditions. Later we have given the query Select * from CTE_Patient to display all the columns that are extracted from the CTE. While running the query both CTE and the query should be run together.
In the below example we are writing a query to list total number of patients visited based on their visit type and primary diagnosis from January-March 2019 :
WITH CTE_Patient as (
Select distinct Patient_ID A,
VisitType A,
DateScheduled A,
PrimaryDiagnosis R,
Count(Patient_ID) Over(
PARTITION BY VisitType
ORDER BY VisitType ASC)
As PatientsVisited
From AmbulatoryVisits A
Join
ReadmissionRegistry R
On A.Patient_ID = R.Patient_ID
Where AND EXTRACT(Month FROM A."DateScheduled") between 01 and 03 and
EXTRACT(YEAR FROM A."DateScheduled")=2019;)
Select VisitType A,
PrimaryDiagnosis R,
Count(Patient_ID) from
CTE_Patient GROUP BY
GROUPING SETS(
VisitType A,
PrimaryDiagnosis R, ())
Temporary Tables : They are similar to tables , but we can use them multiple times unlike subqueries and CTE’s. Before creating a temporary table we start with “#” sign.
GROUP BY extensions : Group By extensions are of three types:
- GROUPING SETS : Let us imagine we are calculating sum of the population based on city, county, continent using GROUPING SETS. We get the output of the sum of population based on city, country and continent individually.
- ROLL UP : Let us imagine we are calculating sum of the population based on city, county, continent using ROLL UP. We get the output of the sum of population when nothing is grouped by, when three of them(city, country, continent) are grouped by, when two of them(city, country) are grouped by, when only city is grouped by.
- CUBE : Let us imagine we are calculating sum of the population based on city, county, continent using CUBE. We get the output of the sum of population when nothing is grouped by, when three of them(city, country, continent) are grouped by, when two of them(city, country) are grouped by, when only city is grouped by, when two of them(country, continent) are grouped by, when only country is grouped by, when two of them(city, continent) are grouped by, when only continent is grouped by.
Stored Procedures :
It is a group of sql statements, that are created and stored in that database. if we create one stored procedure, it can be used multiple times. If we make one change in stored procedure all the other users who are using it will also face changes. Syntax for stored procedures in mysql:
DELIMITER $$
CREATE PROCEDURE procedure_name(param1,param2,..param_n)
BEGIN
SELECT * from table_name;
END$$
DELIMITER;
To invoke the procedure we can use different codes for example:
CALL database_name.procedure_name();
CALL procedure_name();
Views:
Views are the content of virtual table or tables called base tables. View does not have any physical data, it just shows the data which is stored in base table. As view are temporary virtual data tables we cannot insert or update data into views. Syntax for view in mysql:
CREATE OR REPLACE view view_name as
SELECT column_1, column_2, column_3,………,column_n
FROM table_name;
