Utilizar funçõs estadísticas

4D v20 R7

As funções estatísticas permitem realizar cálculos em uma serie de valores. SQL contém numerosas funções de agregação MIN, MAX, AVG, SUM, etc. Utilizando as funções de agregação, queremos obter informação sobre o número de boletos vendidos cada ano. O resultado será ordenado por ano.
Para fazer isto, devemos realizar a suma de todos os boletos vendidos para cada filme e depois ordenar por ano.

O código 4D inicial seria:

C_LONGINT($vMin;$vMax;$vSum) C_REAL($vAverage) C_TEXT($AlertTxt) REDUCE SELECTION([MOVIES];0) $vMin:=0 $vMax:=0 $vAverage:=0 $vSum:=0 ALL RECORDS([MOVIES]) $vMin:=Min([MOVIES]Sold_Tickets) $vMax:=Max([MOVIES]Sold_Tickets) $vAverage:=Average([MOVIES]Sold_Tickets) $vSum:=Sum([MOVIES]Sold_Tickets) `$AlertTxt:="" `$AlertTxt:=$AlertTxt+"Minimum tickets sold: "+Chaine($vMin)+Caractere(13) `$AlertTxt:=$AlertTxt+"Maximum tickets sold: "+Chaine($vMax)+Caractere(13) `$AlertTxt:=$AlertTxt+"Average tickets sold: "+Chaine($vAverage)+Caractere(13) `$AlertTxt:=$AlertTxt+"Total tickets sold: "+Chaine($vSum)+Caractere(13)

Utilizando código SQL:

C_LONGINT($vMin;$vMax;$vSum) C_REAL($vAverage) C_TEXT($AlertTxt) $vMin:=0 $vMax:=0 $vAverage:=0 $vSum:=0 Begin SQL SELECT MIN(Sold_Tickets), MAX(Sold_Tickets), AVG(Sold_Tickets), SUM(Sold_Tickets) FROM MOVIES INTO :$vMin, :$vMax, :$vAverage, :$vSum; End SQL `$AlertTxt:="" `$AlertTxt:=$AlertTxt+"Minimum tickets sold: "+Chaine($vMin)+Caractere(13) `$AlertTxt:=$AlertTxt+"Maximum tickets sold: "+Chaine($vMax)+Caractere(13) `$AlertTxt:=$AlertTxt+"Average tickets sold: "+Chaine($vAverage)+Caractere(13) `$AlertTxt:=$AlertTxt+"Total tickets sold: "+Chaine($vSum)+Caractere(13) `ALERT($AlertTxt)

Utilizando comandos SQL genéricos:

C_LONGINT($vMin;$vMax;$vSum) C_REAL($vAverage) C_TEXT($tQueryTxt) C_TEXT($AlertTxt) $vMin:=0 $vMax:=0 $vAverage:=0 $vSum:=0 SQL LOGIN(SQL_INTERNAL;"";"") $tQueryTxt:="" $tQueryTxt:=$tQueryTxt+"SELECT MIN(Sold_Tickets), MAX(Sold_Tickets), AVG(Sold_Tickets), SUM(Sold_Tickets)" $tQueryTxt:=$tQueryTxt+" FROM MOVIES" SQL EXECUTE($tQueryTxt;$vMin;$vMax;$vAverage;$vSum) SQL LOAD RECORD(SQL all records) SQL LOGOUT $AlertTxt:="" `$AlertTxt:=$AlertTxt+"Minimum tickets sold: "+Chaine($vMin)+Caractere(13) `$AlertTxt:=$AlertTxt+"Maximum tickets sold: "+Chaine($vMax)+Caractere(13) `$AlertTxt:=$AlertTxt+"Average tickets sold: "+Chaine($vAverage)+Caractere(13) `$AlertTxt:=$AlertTxt+"Total tickets sold: "+Chaine($vSum)+Caractere(13) `ALERT($AlertTxt)

Utilizando o comando EXECUTE IMMEDIATE:

C_LONGINT($vMin;$vMax;$vSum) C_REAL($vAverage) C_TEXT($tQueryTxt) C_TEXT($AlertTxt) $vMin:=0 $vMax:=0 $vAverage:=0 $vSum:=0 $tQueryTxt:="" $tQueryTxt:=$tQueryTxt+"SELECT MIN(Sold_Tickets), MAX(Sold_Tickets), AVG(Sold_Tickets), SUM(Sold_Tickets)" $tQueryTxt:=$tQueryTxt+" FROM MOVIES" $tQueryTxt:=$tQueryTxt+" INTO :$vMin, :$vMax, :$vAverage, :$vSum;" Begin SQL EXECUTE IMMEDIATE :$tQueryTxt; End SQL `$AlertTxt:="" `$AlertTxt:=$AlertTxt+"Minimum tickets sold: "+Chaine($vMin)+Caractere(13) `$AlertTxt:=$AlertTxt+"Maximum tickets sold: "+Chaine($vMax)+Caractere(13) `$AlertTxt:=$AlertTxt+"Average tickets sold: "+Chaine($vAverage)+Caractere(13) `$AlertTxt:=$AlertTxt+"Total tickets sold: "+Chaine($vSum)+Caractere(13) `ALERT($AlertTxt)

Para provar todos os exemplos anteriores, lance a base "4D SQL Code Samples" e vá até a janela principal. A continuação, pode escolher o modo de pesquisa e pressionar o botão Using Aggregate functions.