[SQL] Datenbankabfrage für Trafficrecords

[Enigma]

Ich habe folgende Tabelle

CREATE TABLE `trafficdata` (
  `time` int(10) unsigned NOT NULL,
  `addr` int(10) unsigned NOT NULL,
  `direction` tinyint(3) unsigned NOT NULL,
  `protocol` tinyint(3) unsigned NOT NULL,
  `bytes` bigint(20) unsigned NOT NULL,
  `packets` bigint(20) unsigned NOT NULL,
  PRIMARY KEY  (`time`,`addr`,`direction`,`protocol`)
)

Die folgende Abfrage, holt den gesammten Traffic aus der Datenbank:

                  "SELECT \n" .
                  "  INET_NTOA(`addr`) 'ip', \n" .
                  "  SUM(`bytes`)  'bytes_in', \n" .
                  "FROM \n" .
                  "  `".$cfg['db']['t_traffic_netflow']."` traffic \n" .
                  "WHERE \n" .
                  "  `addr` IN (".$stringIpAdresse.") \n" .
                  "  AND `time`>=UNIX_TIMESTAMP('".$von."') \n" .
                  "  AND `time`< UNIX_TIMESTAMP('".$bis."') \n" .
                  "GROUP BY `addr` " .
                  "ORDER BY `addr` ASC \n" .

Soweit so gut - aber, ich brauch den Traffic aufgeschlüsselt nach eingehend und ausgehend. Jedoch will das mit mySQL nicht so richtig. Folgende Datenbankabfrage wäre zwar theoretisch korrekt

( SELECT 
  INET_NTOA(`addr`) 'ip', 
  `bytes` 'bytes_in', 
  0 'bytes_out' 
FROM 
  `trafficdata` 
WHERE 
  `direction`=0 
  AND `addr` IN (1047483664,1047483665,1047483666,1047483667,1047483668,1047483669,1047483670,1047483671) 
  AND `time`>=UNIX_TIMESTAMP('2006-10-01') 
  AND `time`< UNIX_TIMESTAMP('2006-10-15') 
) UNION ( SELECT 
  INET_NTOA(`addr`) 'ip', 
  0 'bytes_in', 
  `bytes` 'bytes_out' 
FROM 
  `trafficdata` 
WHERE 
  `direction`=1 
  AND `addr` IN (1047483664,1047483665,1047483666,1047483667,1047483668,1047483669,1047483670,1047483671) 
  AND `time`>=UNIX_TIMESTAMP('2006-10-01') 
  AND `time`< UNIX_TIMESTAMP('2006-10-15') 
) 
GROUP BY `ip`
ORDER BY `ip` ASC 
;

Jedoch kann mySQL kein Union in verbindung mit GROUP BY. Ärgerlich, aber wahr. Kennt ihr noch eine Möglichkeit die mir nicht eingefallen ist, die ebenfalls funktionieren müsste?

Bitte berücksicht, dass in der Tabelle später ca. 20-30 Mio Rows drin sind und ca. 1000MB gross ist. Also mit temporären Tabellen brauch ich erst gar nicht anzufangen

 

[tobiasge]

Hallo,

wenn du ein GROUP BY addr, direction machst, sollte das funktionieren. Das Ergebnis sind dann natürlich doppelt soviele Zeilen. Jede vorher dagewesene, wir in zwei aufgespalten, jeweils eine für eingehenden und ausgehenden Traffic

                  "SELECT \n" .
                  "  INET_NTOA(`addr`) 'ip', \n" .
                  "  SUM(`bytes`)  'bytes_in', \n" .
                  "FROM \n" .
                  "  `".$cfg['db']['t_traffic_netflow']."` traffic \n" .
                  "WHERE \n" .
                  "  `addr` IN (".$stringIpAdresse.") \n" .
                  "  AND `time`>=UNIX_TIMESTAMP('".$von."') \n" .
                  "  AND `time`< UNIX_TIMESTAMP('".$bis."') \n" .
                  "GROUP BY `addr`, `direction` " .
                  "ORDER BY `addr` ASC \n" .

Gruß Tobias

 

[davidbaumann]

Ich habs jetzt nicht genau probiert und eher Erfahrung mit MSSQL 2000, aber wie wäre es mit select * from ( hier dein query ohne group by ) group by blah ?

David

 

[Enigma]

Ahh die Lösung von davidbaumann funktioniert Sauber die Datenbank verarscht
hier die "lösung" als pseudocode

SELECT *
FROM (
 (
 SELECT ...
 FROM `trafficdata` t1
 WHERE...
 )
 UNION (
 
 SELECT ...
 FROM `trafficdata` t2
 WHERE ...
 )
) t3
GROUP BY ...
ORDER BY ... ASC

 

[davidbaumann]

Freut mich

Naja hinter Select * from kann eine beliebige Datenmenge stehen, ob es jetzt eine Tabelle, eine View oder ein Ergebnis eines Query ist.. egal

David