Progressive queries

Count the people in the Sales department; how many of them live in Beijing; and how many of those living in Beijing are female.
To count the people in the Sales department:

    SELECT count(*) FROM employee WHERE dept='Sales'

To count how many of them live in Beijing.

SELECT count(*) FROM employee
WHERE dept='Sales' AND domicile='beijing'

To count how many of those living in Beijing are female.

    SELECT count(*) FROM employee
    WHERE dept='Sales' AND domicile='beijing' AND gender='female'

You can’t perform a SQL computation through several query calls, so the user needs to write the condition repeatedly in a progressive query. It is tedious and inefficient.

A B
1 =demo.query(“select * from employee”).select(dept:”sales”) =A1.len()
2 =A1.select(domicile:”beijing”) =A2.len()
3 =A2.select(gender:”female”) =A3.len()

esProc supports using record sets as variable values, enabling users to make the progressive query step by step.

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