Sorting and filtering the grouping subsets

Find out the student whose score ranks top 10 for all subjects.

SQL

SELECT name FROM
    (SELECT name FROM
       (SELECT name,
           rank() OVER(PARTITION BY subject ORDER BY score DESC)
           rank
       FROM score_table)
     WHERE rank<=10)
GROUP BY name
HAVING count(*)=(SELECT count(DISTINCT subject) FROM score_table)

Using the grouping window function can conveniently rank the students in each subject, but it is not easy to compute the intersection of students within top 10. The user will need to change the way of thinking to count the occurrences of each student in the top 10 ranking of each subject, when the count is equal to the number of subjects, it means the student ranks top 10 of all subjects.

SPL

A
1 =demo.query("select * from score_table").group(subject)
2 =A1.(~.rank(score).pselect@a(~<=10)) The position of the top 10 in each group.
3 =A1.(~(A2(#)).(name)).isect()

SPL allows the user to write code by following their natural mental process.

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.