Estudio de la eficacia de los procedimientos limpieza y blanqueamiento de ceras

AllInformixdatabaseservers supportthefollowingaggregatefunctions:

v AVG v COUNT v MAX v MIN v RANGE v STDEV v SUM v VARIANCE

Anaggregatefunctionreturnsonevaluefora setofqueried rows.The aggregatefunctionstakeonvaluesthatdependonthesetofrowsthatthe

WHEREclauseoftheSELECTstatementreturns.IntheabsenceofaWHERE clause,theaggregatefunctionstakeonvaluesthatdependonall therows thattheFROM clauseforms.

Youcannotuseaggregatefunctionsforexpressionsthatcontainthefollowing datatypes: v TEXT v BYTE DynamicServer v CLOB v BLOB v

Opaquedatatypes(unlessuser createsaggregatefunctions)

v Collectiondatatypes

v Rowtypes

EndofDynamic Server

Aggregatesareoftenusedtosummarizeinformationaboutgroupsofrowsin atable.ThisuseisdiscussedinChapter5.Whenyouapplyanaggregate functiontoanentiretable,theresultcontains asinglerow thatsummarizesall theselectedrows.

Usingthe COUNTFunction

Figure4-1countsanddisplays thetotalnumberofrowsinthestocktable.

Figure4-3includesaWHEREclausetocountspecific rowsinthestocktable, inthiscase,onlythoserowsthathavea manu_codeofSHM.

SELECT COUNT(*) FROM stock

Figure4-1.Query

(count(*))

73

Byincludingthekeyword DISTINCT(oritssynonymUNIQUE)andacolumn nameinFigure4-5,youcantallythenumberofdifferentmanufacturercodes inthestocktable.

UsingtheAVGFunction

Figure4-7computes theaverageunit_priceofallrowsinthestocktable.

Figure4-9computes theaverageunit_priceofjustthoserowsinthestock tablethathaveamanu_codeofSHM.

SELECT COUNT (*) FROM stock WHERE manu_code = ’SHM’

Figure4-3.Query

(count(*))

17

Figure4-4.QueryResult

SELECT COUNT (DISTINCT manu_code) FROM stock

Figure4-5.Query

(count)

9

Figure4-6.QueryResult

SELECT AVG (unit_price) FROM stock

Figure4-7.Query

(avg)

$197.14

Usingthe MAXandMIN Functions

YoucancombineaggregatefunctionsinthesameSELECTstatement. For example,youcanincludeboththeMAXandtheMINfunctionsintheselect list,asFigure4-11shows.

Figure4-11findsand displaysboththehighestand lowestship_chargeinthe orderstable,asFigure4-12 shows.

Usingthe SUMFunction

Figure4-13 calculatesthetotalship_weightoforders thatwereshippedon July13,1998.

SELECT AVG (unit_price) FROM stock WHERE manu_code = ’SHM’

Figure4-9.Query

(avg)

$204.93

Figure4-10.QueryResult

SELECT MAX (ship_charge), MIN (ship_charge) FROM orders

Figure4-11.Query

(max) (min)

$25.20 $5.00

Figure4-12.QueryResult

SELECT SUM (ship_weight) FROM orders WHERE ship_date = ’07/13/1998’

Usingthe RANGEFunction

TheRANGEfunction computesthedifferencebetweenthemaximumand the minimumvaluesfortheselectedrows.

YoucanapplytheRANGEfunctiononlytonumericcolumns.Figure4-15 findstherangeof pricesforitemsinthestocktable.

Aswithotheraggregates,theRANGEfunctionappliestotherowsofagroup whenthequeryincludesaGROUPBYclause,whichFigure4-17 shows.

(sum)

130.5

Figure4-14.QueryResult

SELECT RANGE(unit_price) FROM stock

Figure4-15.Query

(range)

955.50

Figure4-16.QueryResult

SELECT RANGE(unit_price) FROM stock GROUP BY manu_code

Usingthe STDEVFunction

TheSTDEVfunctioncomputes thestandarddeviationfortheselectedrows. ItisthesquarerootoftheVARIANCEfunction.

YoucanapplytheSTDEVfunction onlyto numericcolumns.Thefollowing queryfindsthestandarddeviationona population:

SELECT STDEV(age) FROM u_pop WHERE age > 21

Aswiththeotheraggregates,theSTDEV functionappliestotherowsofa groupwhenthequeryincludesa GROUPBYclause,asthefollowingexample shows:

SELECT STDEV(age) FROM u_pop GROUP BY state

WHERE STDEV(age) > 21

Nullsareignoredunlesseveryvalueinthespecifiedcolumnisnull.Ifevery columnvalueisnull,theSTDEVfunction returnsa nullforthatcolumn.For moreinformationabouttheSTDEV function,seetheExpressionsegmentin theIBM Informix:GuidetoSQLSyntax.

Usingthe VARIANCEFunction

TheVARIANCEfunctionreturnsthevarianceforasampleofvaluesasan unbiasedestimate ofthevariance forallrowsselected.Itcomputesthe followingvalue:

(SUM(Xi**2) - (SUM(Xi)**2)/N)/(N-1)

Inthis example,Xiiseachvalue inthecolumnand Nisthetotalnumber of valuesinthecolumn.Youcanapply theVARIANCEfunction onlytonumeric columns.Thefollowingqueryfindsthevarianceonapopulation:

SELECT VARIANCE(age) FROM u_pop WHERE age > 21 (range) 820.20 595.50 720.00 225.00 632.50 0.00 460.00 645.90 425.00

Aswiththeotheraggregates,theVARIANCEfunction appliestotherowsof agroupwhenthequeryincludesaGROUPBYclause,whichthefollowing exampleshows:

SELECT VARIANCE(age) FROM u_pop GROUP BY birth

WHERE VARIANCE(age) > 21

Nullsareignoredunlesseveryvalueinthespecifiedcolumnisnull.Ifevery columnvalueisnull,theVARIANCEfunctionreturnsanull forthatcolumn. FormoreinformationabouttheVARIANCEfunction,seetheExpression segmentintheIBM Informix:GuidetoSQLSyntax.

ApplyingFunctionstoExpressions

Figure4-19 showshowyoucanapply functionstoexpressionsand supply displaylabelsfortheirresults.

Figure4-19 findsanddisplays themaximum,minimum,and averageamounts oftime(indays,hours,and minutes)betweenthereceptionandresolutionof acustomercalland labelsthederived valuesappropriately.Figure4-20shows thesequalitiesoftime.