Log Parser Aggregate Function

AVG

AVG fonksiyonu ile verilen değerlerin ortalaması hesaplanmaktadır.

AVG ( [ DISTINCT | ALL ] <field_expr> )

SELECT AVG(Size)

FROM C:\windows\system32\*.*

WHERE TO_LOWERCASE(EXTRACT_EXTENSION(Name)) = 'exe'

SELECT  TO_LOWERCASE(EXTRACT_EXTENSION(cs-uri-stem)) AS PageType,

        AVG(time-taken)

FROM ex031118.log

GROUP BY PageType

COUNT

COUNT ile elde edilen sonuçların sayılarını bize göstermektedir.

COUNT ( [ DISTINCT | ALL ] * )
COUNT ( [ DISTINCT | ALL ] <field_expr_list> )

SELECT COUNT(*)

FROM ex040528.log

WHERE cs-uri-stem = '/home.asp'

SELECT cs-uri-stem

FROM ex040528.log

GROUP BY cs-uri-stem

HAVING COUNT(*) > 50

GROUPING

GROUPING ile

GROUPING ( <field_expr> )

SELECT date, cs-uri-stem, COUNT(*), GROUPING(date) AS GDate, GROUPING(cs-uri-stem) AS GPage

FROM ex040528.log

GROUP BY date, cs-uri-stem WITH ROLLUP

Örnek Sonuç;

date       cs-uri-stem         COUNT(ALL *) GDate GPage

---------- ------------------- ------------ ----- -----

2003-11-18 /Default.htm        1            0     0

2003-11-18 /style.css          1            0     0

2003-11-18 /images/address.gif 1            0     0

2003-11-18 /cgi-bin/counts.exe 1            0     0

2003-11-18 /data/rulesinfo.nsf 2            0     0

2003-11-19 /data/rulesinfo.nsf 6            0     0

2003-11-20 /data/rulesinfo.nsf 5            0     0

2003-11-20 /maindefault.htm    1            0     0

2003-11-20 /top2.htm           1            0     0

2003-11-20 /homelog.swf        1            0     0

-          -                   20           1     1

2003-11-18 -                   6            0     1

2003-11-19 -                   6            0     1

2003-11-20 -                   8            0     1

MAX

MAX ile verilen değerlerin maksimum değere sahip olanın bulunması sağlanabilmektedir.

MAX ( [ DISTINCT | ALL ] <field_expr> )

SELECT  TO_LOWERCASE(EXTRACT_EXTENSION(cs-uri-stem)) AS PageType,

        MAX(time-taken)

FROM ex031118.log

GROUP BY PageType

MIN

MIN ile verilen değerlerin minumum değere sahip olanın bulunması sağlanabilmektedir.

MIN ( [ DISTINCT | ALL ] <field_expr> )

SELECT MIN(Size)

FROM C:\windows\system32\*.*

WHERE TO_LOWERCASE(EXTRACT_EXTENSION(Name)) = 'exe'

PROPCOUNT

PROPCOUNT ( * ) [ ON ( <on_field_expr_list> ) ]
PROPCOUNT ( <field_expr_list> ) [ ON ( <on_field_expr_list> ) ]

SELECT SourceName, MUL(PROPCOUNT(*), 100.0) AS Percent

FROM System

GROUP BY SourceName

SourceName              Percent

----------------------- ---------

EventLog                10.322979

Service Control Manager 63.004172

Ati HotKey Poller       3.430691

Application Popup       0.108175

W32Time                 14.680884

DCOM                    0.046361

NtServicePack           0.185443

Win32k                  0.324525

RemoteAccess            2.194406

PROPSUM

PROPSUM ( <field_expr> ) [ ON ( <on_field_expr_list> ) ]

SELECT EXTRACT_EXTENSION(cs-uri-stem) AS PageType, MUL(PROPSUM(sc-bytes), 100.0) AS PercentBytes

FROM ex040528.log

GROUP BY PageType

PageType PercentBytes

-------- ------------

htm      7.236737

css      1.035243

gif      23.772064

exe      1.398888

nsf      24.459391

swf      32.528669

jpg      8.003440

html     0.104051

dll      0.002322

asp      0.000000

js       1.260613

class    0.198582

SUM

Bütün değerlerin toplamını bize vermektedir.

SUM ( [ DISTINCT | ALL ] <field_expr> )

SELECT SUM(Size)

FROM C:\windows\system32\*.*

WHERE TO_LOWERCASE(EXTRACT_EXTENSION(Name)) = 'exe'