Median

Find out the employee whose age is the median of the ages of all employees.

SQL

SELECT name,birthday
FROM (SELECT name,birthday,
             row_number() OVER (ORDER BY birthday) age_rank
      FROM employee)
WHERE age_rank=(SELECT floor((count(*)+1)/2) FROM employee)

The SQL record set is unordered, so the user will need to create the sequence numbers for rows before they can use the conditional statement to query the employee record that in the middle position in terms of age.

SPL

A
1 =demo.query("select * from employee").sort(birthday)
2 =A1((A1.len()+1)/2)

The SPL record set is in order originally, so it is an easy task for the user to get the median.

In order to simplify the SQL statement as much as possible in the examples, the window functions of SQL 2003 standard are widely used, and accordingly the Oracle database syntax which has best support for SQL 2003 is adopted in this essay.