ePrivacy and GPDR Cookie Consent by Cookie Consent

Advanced SQL Queries You May Know

Udgivet 2017-09-08 - Skrevet af

Advanced SQL Queries You May Know but Have to Look Up



The idea for this article came to me a day at work where I was looking up the same solution to a problem I had just had earlier that week. So I thought why not start collecting these queries that you occasionally use, but are still hard enough to require a look up.
I'll keep it updated with queries as I come across relevant ones.

Grouping by ranges of values

Let's say you have a table (Team) with 3 values, name, birth year, and salary. From this table you want to see how much people make based upon their age in ranges of 10. We start by creating a Common Table Expression where we generate the ranges as shown here:
with Series as (	select generate_series(1950, 2010, 10) as r_from), Range as (	select	r_from, 			(r_from + 9) as r_to 	from 	series)
This generates 10 year ranges from 1950 to 2009, e.g. 1950-1959, 1960-1969 and so on.
What we want to do from here is associate each range with a number of team members and their average salaries as shown below.
select	r_from, 		r_to,		(select	count(*) 		from 	Team 		where 	birth_year between r_from and r_to) as team_members,		round(				(select avg(salary) 				from 	Team 				where 	birth_year between r_from and r_to), 				2) as salary_avg,		(select min(salary) 		from 	Team 		where 	birth_year between r_from and r_to) as salary_min,		(select	max(salary) 		from 	Team 		where 	birth_year between r_from and r_to) as salary_maxfrom Range
The above generates a table with the following columns, where I have included an example result for a range.

r_fromr_toteam_memberssalary_avgsalary_minsalary_max
198019894$91,250$80,000$110,000

Retrieving the latest entry

This is for example retrieving the latest purchase a customer has made.
The query finds the latest dates (max) for each customer (group by) and then joins it 1-1 with the table itself.
select	t.username, 		t.date, 		t.valuefrom	MyTable t		inner join 			(select	username, 					max(date) as MaxDate			from	MyTable			group by username) tm 		on t.username = tm.username and t.date = tm.MaxDate


Do you think I missed a good query? Feel free to contact me.


Kommentarer

Der er ingen kommentarer.

Tilføj kommentar