Overlapped conditional groups

To group the employees by their length of service: less than one year, one to three years, more than three years, and more than five years, and compute the number of male and female employees respectively in each group.

SQL

WITH A AS
     (SELECT 1 sequence_no.,'less_than_a_year' division,
           0 lower_bound,1 upper_bound FROM dual UNION
      SELECT 2,'one_to_three_years',1,3 FROM dual UNION
      SELECT 3,'more_than_three_years',3,100 FROM dual UNION
      SELECT 4,'more_than_five_years',5,100 FROM dual)
SELECT division,
      (SELECT count(*) FROM employee
      WHERE service_year > =A.lower_bound
           AND service_year<A.upper_bound
           AND gender='male') male_total,
      (SELECT count(*) FROM employee
      WHERE service_year > =A.lower_bound
           AND service_year > A.upper_bound
           AND gender='female') female_total
FROM A ORDER BY sequence_no.

To compute the overlapped conditional groups, it is very complicated to use join and much clearer to use subquery. However the latter will have to compute the same subset repeatedly.

SPL

A B
1 ?<1 Less than a year
2 ?>=1 && ?<3 One to three years
3 ?>=3 More than three years
4 ?>=5 More than five years
5 =demo.query("select * from employee").enum@r([A1:A4],service_year) =[B1:B4]
6 =A5.new(B5(#):division, ~.count(gender=="male"):male_total,
7 ~.count(gender=="female"):female_total)

The ad hoc SPL conditional grouping can conveniently perform this type of computations, and the subset of each group can be re-used.

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.