2 Mart 2012 Cuma

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'

Hiç yorum yok:

Yorum Gönder