MySQL/Selecting_values.sql at main · DataScienceRoadMapDSRM/MySQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
USE world;

SELECT name, continent, population,
FIRST_VALUE(population) OVER(PARTITION BY continent
                             ORDER BY population DESC
                             RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS max_population_in_continent,
LAST_VALUE(population) OVER(PARTITION BY continent
                             ORDER BY population DESC
                             RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS min_population_in_continent,
NTH_VALUE(population, 10) OVER(PARTITION BY continent
                             ORDER BY population DESC
                             RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 10th_population_in_continent
FROM country;

SELECT name, continent, population,
LEAD(population ,1 ) OVER(PARTITION BY continent ORDER BY population DESC) AS next_lower_population
FROM country;

SELECT name, continent, population,
LAG(population ,1) OVER(PARTITION BY continent ORDER BY population DESC) AS next_higher_population
FROM country
WHERE continent= 'Asia';