The computing power of SQL for mass structured data is complete, that is to say, it is impossible to find anything that SQL cannot compute. But its support layer is too low, which can lead to over-elaborate operation in practical application.
The over-elaborate operation is specifically reflected in the following four aspects:
Computation without sub-step: SQL requires computation to be written out in one statement, and it is necessary to adopt storage procedure to implement computation step by step. No sub-step not only causes difficulty in thinking, but also makes it difficult to use intermediate result.
Set is unordered: SQL does not directly provide the mechanism of using position to refer to set members, and conversion is needed to implement computation relating to order and positioning.
Set-lization is not complete: SQL set function is simple and is only used to indicate the query result set and cannot be explicitly applied as basic data type.
It lacks object reference: SQL does not support record reference, the association between data tables adopts equivalent foreign key scheme, and in conducting multi-table joint computation, it is necessary to conduct join operation. So it is not only difficult to understand, but also low in efficiency.
Implementing data computation process based on a type of computation system is in fact the process of translating business problem into formalized computation syntax (which is similar to the case in which a primary-school student solves an application problem by translating the problem into formalized four arithmetic operations). Because of the above-mentioned four problems of SQL, in handling complex computation, its model system is inconsistent with people’s natural thinking habit. It causes a great barrier in translating problems, leading to the case that the difficulty to formalize the problem-solving method into computation syntax is much greater than to find the solution of the problem.
We give the following examples to describe respectively the problems in the four aspects.
To make the statement in the examples as simple as possible, here a large number of SQL2003 standard window functions are used. So we adopt the ORACLE database syntax that does a relatively good job in supporting SQL2003 standard as it will be generally more complex to adopt the syntax of other databases to program these SQLs.
Computation without sub-step
Carrying out complex computation step by step can reduce the difficulty of the problem to a great extent, conversely, collecting a multi-step computation into one to be completed in just one step increases the complexity of the problem.
Task1 :The number of persons of the sales department, where, the number of persons whose native place is NY, and where, the number of female employees?
The number of persons of the sales department
|
| select count(*) from employee where department=‘sales’ |
Where, the number of persons whose native place is Beijing
| | select count(*) from employee where department=‘sales ’ and native_place=‘NY’ |
And where, the number of female employees
| | select count (*) from employee |
|
| where department=‘sales’ and native_place=‘NY’ and gender =‘female’ |
Conventional thought: Select the persons of the sales department for counting, and from it, find out the persons whose native place is NY for counting, and then further find out the number of female employees for counting. The query each time is based on the existing result last time, so it is not only simple in writing but also higher in efficiency.
But, the computation of SQL cannot be conducted in steps, and it is impossible to reuse the preceding result in answering the next question, and it is only possible to copy the query condition once more.
Task2 .Each department selects a pair of male and female employees to form a game team.
|
| with A as |
|
| (select name, department, |
|
| row_number() over (partition by department order by 1) seq |
|
| from employee where gender =‘female’), |
|
| B as |
|
| (select name, department, |
|
| row_number() over(partition by department order by 1) seq |
|
| from employee where sex =‘female’) |
|
| select name, department from A |
|
| where department in ( select distinct department from B ) and seq=1 |
|
| union all |
|
| select name, department from B |
|
| where department in (select distinct department from A ) and seq=1 |
Computation without sub-step sometimes not only causes trouble in writing and low efficiency in computation, but even causes serious deformation in the train of thought.
The intuitive thought of this task: For each department cycle, if this department has male and female employees, then select one male employee and one female employee and add them to the result set. But SQL does not support this kind of writing with which the result set is completed step by step (to implement this kind of scheme, it is necessary to use the stored procedure). At this time, it is necessary to change the train of thought into: Select male employee from each department, select female employee from each department, select out, respectively from the two result sets, members whose departments appear in another result set, and finally seek the union of the sets.
Fortunately, there are still with sub-statement and window function over (SQL2003 standard begins to support); otherwise this SQL statement will be simply ugly.
Related:
Raqsoft
