mar
ох... много раз у меня баз аданных умирала...
но всетаки я написал...
вот что уменя получилось:
Код:
$LogList = mysql_query('SELECT l.Date,
count(*) as Count,
sum(l.ByteSent) as AllByteSent,
sum(l.ByteSent) / '.$MaxByte['Max'].' as prb,
count(*) / '.$MaxCount['Max'].' as prc,
l2.C as C
FROM logs as l
LEFT JOIN ( SELECT l3.Date,
count(*) as C
FROM ( SELECT Date
FROM logs
GROUP BY Date, FromIp) as l3
GROUP BY l3.Date) as l2 ON l.Date = l2.Date
WHERE l.Date >= "'.$StartDate.'"
AND l.Date <= "'.$EndDate.'"
GROUP BY l.Date, l2.Date
'.$sort.';') or print mysql_error();
но работает все это очень медленно
буду оптимизировать.
Добавлено:
в итоге было:
Код:
$MaxByte = mysql_fetch_array(mysql_query('SELECT sum(ByteSent) as Max
FROM logs
WHERE Date >= "'.$StartDate.'"
AND Date <= "'.$EndDate.'"
GROUP BY Date
ORDER BY Max DESC;'));
$MaxCount = mysql_fetch_array(mysql_query('SELECT count(*) as Max
FROM logs
WHERE Date >= "'.$StartDate.'"
AND Date <= "'.$EndDate.'"
GROUP BY Date
ORDER BY Max DESC;'));
$LogList = mysql_query('SELECT l.Date,
count(*) as Count,
sum(l.ByteSent) as AllByteSent,
sum(l.ByteSent) / '.$MaxByte['Max'].' as prb,
count(*) / '.$MaxCount['Max'].' as prc,
l2.C as C
FROM logs as l
LEFT JOIN ( SELECT l3.Date,
count(*) as C
FROM ( SELECT Date
FROM logs
GROUP BY Date, FromIp) as l3
GROUP BY l3.Date) as l2 ON l.Date = l2.Date
WHERE l.Date >= "'.$StartDate.'"
AND l.Date <= "'.$EndDate.'"
GROUP BY l.Date, l2.Date
'.$sort.';') or print mysql_error();
стало:
Код:
$LogList = mysql_query('SELECT l1.*,
l1.Count / m.Max_Count as prc,
l1.AllByteSent / m.Max_Byte as prb,
l2.C
FROM ( SELECT Date,
count(*) as Count,
sum(ByteSent) as AllByteSent
FROM logs
WHERE Date >= "'.$StartDate.'"
AND Date <= "'.$EndDate.'"
GROUP BY Date
'.$sort.') as l1,
( SELECT l3.Date,
count(*) as C
FROM ( SELECT Date
FROM logs
GROUP BY Date, FromIp) as l3
GROUP BY l3.Date) as l2,
( SELECT MAX(m1.Max_Byte) as Max_Byte,
MAX(m2.Max_Count) as Max_Count
FROM ( SELECT Date,
sum(ByteSent) as Max_Byte
FROM logs
GROUP BY Date
ORDER BY Max_Byte DESC) as m1
LEFT JOIN ( SELECT Date,
count(*) as Max_Count
FROM logs
WHERE Date >= "'.$StartDate.'"
AND Date <= "'.$EndDate.'"
GROUP BY Date
ORDER BY Max_Count DESC) as m2
USING( Date )) as m
WHERE l1.Date=l2.Date;') or print mysql_error();
