SUMMARIZATION bis

The SUMMARIZE operator has been part of Tutorial D since its inception. With the introduction of image relations, however, that operator became logically redundant—and while there might be reasons (perhaps pedagogic ones) to retain it, the fact is that most summarizations can be more succinctly expressed by means of EXTEND.[105] Recall Example SX1 from the previous section (“For each supplier, get the supplier number and a count of the number of parts supplied”). The SUMMARIZE formulation looked like this:

     SUMMARIZE SP PER ( S { SNO } ) : { PCT := COUNT ( PNO ) }

Here by contrast is an equivalent EXTEND formulation:

     EXTEND S { SNO } : { PCT := COUNT ( !!SP ) }

(Since the combination {SNO,PNO} is a key for relvar SP, there’s no need to project the image relation on {PNO} before computing the count.) As the example suggests, EXTEND is certainly another context in which image relations make sense; in fact, they’re arguably even more useful in this context than they are in WHERE clauses.

The rest of this section consists of more examples. I’ve continued the numbering from the examples in the section IMAGE RELATIONS bis.

Example 6: For each supplier, get supplier details and total shipment quantity, taken over all shipments for the supplier in question.

     EXTEND S : { TOTQ := SUM ( !!SP , QTY ) }

Example 7: For each supplier, get supplier details and total, maximum, and minimum shipment quantity, taken over all shipments for the supplier in question.

     EXTEND S : { TOTQ := SUM ( !!SP , QTY ) ,
                  MAXQ := MAXX ( !!SP , QTY , 0 ) ,
                  MINQ := MINX ( !!SP , QTY , 0 ) }

Note the use of the multiple form of EXTEND in this example.

Example 8: For each supplier, get supplier details, total shipment quantity taken over all shipments for the supplier in question, and total shipment quantity taken over all shipments for all suppliers.

     EXTEND S : { TOTQ  := SUM ( !!SP , QTY ) ,
                  GTOTQ := SUM (  SP , QTY ) }

Result:

SNO

TOTQ

GTOTQ

S1

1300

3100

S2

700

3100

S3

200

3100

S4

900

3100

S5

0

3100

Example 9: For each city c, get c and the maximum and minimum shipment quantities for all shipments for which the supplier city and part city are both c.

     WITH ( TEMP := S JOIN SP JOIN P ) :
     EXTEND TEMP { CITY } : { MAXQ := MAXX ( !!TEMP , QTY , 0 ) ,
                              MINQ := MINX ( !!TEMP , QTY , 0 ) }

The point of this rather contrived example is to illustrate the usefulness of WITH, in connection with “SUMMARIZE-type” EXTENDs in particular, in avoiding the need to write out some possibly lengthy subexpression several times. Note: This book generally has little to say about performance matters, but I think it’s worth pointing out that we would surely expect the system, in examples like this one, to evaluate the pertinent subexpression once instead of several times. In other words, the use of WITH can be one of those nice win-win situations that are good for both the user and the DBMS.



[105] Not to mention the fact that SUMMARIZE involves a syntactic construct that looks a bit like an aggregate operator invocation but isn’t one—which as pointed out earlier is a good reason why it might be desirable to dispense with SUMMARIZE altogether.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.147.42.193