letzter Datensatz aus Gruppe + vorletzter

[frankwu]

Hallo,

ich habe eine Tabelle in der ich für mehrere Rechner (VMs) die Speicherplatzinfo speichere.

id,time,hostname,mountpoint,free,usage

nun frage ich die Tabelle ab und gruppiere nach hostname und mountpoint, und wende die Aggregat-funktion Max auf time an, um in jeder Gruppe den aktuellsten Wert abzufragen.

select max(created),hostname,mountpoint,free,`usage` from diskfree group by hostname,mountpoint

nun benötige ich aber zusätzlich den vorletzten Eintrag (Vergleichswert und für ein kommendes email-flag), wie stelle ich das am sinnvollsten an?
Ich dachte ich kann das in einem Subselect suchen (die id) und dann via left join reinlinken. Hat aber leider den nachteil, dass erst die komplette Tabelle in eine Temptable geschrieben wird (nicht gruppiert) und dann erst weiterverarbeitet wird...ein Performance-Supergau

Vielleicht habt ihr eine Idee

Gruß Frank

[h3ll]

Die Abfrage ist fehlerhaft. Im GROUP BY müssen alle Spalten aus dem SELECT angeführt werden.

Zu dem Thema gibts übrigens sogar einen Artikel im MySQL-Handbuch:

https://dev.mysql.com/doc/refman/8.0...group-row.html

[frankwu]

ich will doch nur nach den 2 Spalten (hostname+mountpoint) gruppieren und nicht nach allen...auslesen möchte ich aber alle. ein Gruppieren nach dem freien Speicherplatz (free) und der prozentualen Auslastung (usage) macht meiner Meinung nach nicht viel Sinn

dein Link zeigt (soweit ich die Queries verstehe) nur das Maximum aber nicht das vorletzte "Maximum". ersteres Habe ich mit dem "group by" ja schon erreicht...es zeigt mir pro hist und mountpoint den letzten Datensatz an (max(time)).

[h3ll]

ich will doch nur nach den 2 Spalten (hostname+mountpoint) gruppieren und nicht nach allen...

Das geht nicht. Jede anständige Datenbank würde dir eine Fehlermeldung um die Ohren hauen. MySQL liefert halt stillheimlich ein zufälliges Ergebnis.

dein Link zeigt (soweit ich die Queries verstehe) nur das Maximum aber nicht das vorletzte "Maximum". ersteres Habe ich mit dem "group by" ja schon erreicht...es zeigt mir pro hist und mountpoint den letzten Datensatz an (max(time)).

Tut es nicht. Die Abfrage ist wie gesagt fehlerhaft. Wenn das Ergebnis stimmt, dann nur zufällig. MySQL würfelt einfach, welche Daten geliefert werden. Kann stimmen, oder auch nicht. In den meisten Fällen tut es das nicht.

[frankwu]

habe das Query jetzt entsprechend umgebaut auf den Weg aus der MySQL-Doku:

SELECT created,hostname,mountpoint,free,`usage`
FROM   diskfree d1
WHERE  created=(SELECT MAX(d2.created)
              FROM diskfree d2
              WHERE d1.hostname = d2.hostname AND d1.mountpoint=d2.mountpoint)
ORDER BY created;

dauert auch ewig, obwohl ich für created,hostname und mountpoint indizies definiert habe

explain sagt das:

"id"	"select_type"	"table"	"type"	"possible_keys"	"key"	"key_len"	"ref"	"rows"	"Extra"
"1"	"PRIMARY"	"d1"	"ALL"	\N	\N	\N	\N	"8712"	"Using where; Using filesort"
"2"	"DEPENDENT SUBQUERY"	"d2"	"ref"	"hostname,mountpoint"	"hostname"	"20"	"db.d1.hostname"	"2564"	"Using where"

tabellen-create sieht so aus:

CREATE TABLE `diskfree` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`hostname` VARCHAR(6) NOT NULL COLLATE 'utf8_unicode_ci',
	`mountpoint` VARCHAR(15) NOT NULL COLLATE 'utf8_unicode_ci',
	`free` INT(11) NOT NULL COMMENT 'freier Speicher (Avail) in MB',
	`usage` INT(11) NOT NULL COMMENT 'belegt (Use) in %',
	PRIMARY KEY (`id`),
	INDEX `created` (`created`),
	INDEX `hostname` (`hostname`),
	INDEX `mountpoint` (`mountpoint`)
)
COLLATE='utf8_unicode_ci'
ENGINE=MyISAM
AUTO_INCREMENT=8707
;

bei der Join-variante sieht es genauso aus...

SELECT d1.created,d1.hostname,d1.mountpoint,d1.free,d1.`usage`
FROM diskfree d1
LEFT JOIN diskfree d2 ON d1.hostname = d2.hostname AND d1.mountpoint=d2.mountpoint AND d1.created < d2.created
WHERE d2.created IS NULL
ORDER BY d1.created;

"id"	"select_type"	"table"	"type"	"possible_keys"	"key"	"key_len"	"ref"	"rows"	"Extra"
"1"	"SIMPLE"	"d1"	"ALL"	\N	\N	\N	\N	"8724"	"Using filesort"
"1"	"SIMPLE"	"d2"	"ref"	"created,hostname,mountpoint"	"hostname"	"20"	"infoscreen.d1.hostname"	"2564"	"Using where; Not exists"

habe auch sowas probiert, funktioniert halt auch nicht, da das subquery zuerst aufgerufen wird und dort d1 noch nicht bekannt ist:

select distinct(concat_ws('_',d1.hostname,d1.mountpoint)) as host_mount,d1.hostname,d1.mountpoint from diskfree as d1
cross join 
  (select id from diskfree d3 where d3.hostname=d1.hostname and d3.mountpoint=d1.mountpoint order by d3.created limit 2) as d4

beim googlen bin ich auch auf das gestoßen: https://www.developerfiles.com/get-t...roup-in-mysql/

nur ich bekomme es nicht hin das auf meine Werte (und die 2-Spalten-Gruppe) umzusetzen

[frankwu]

ich habe es jetzt so:

SELECT
  created,
  hostname,
  mountpoint,
  free,
  `usage`
FROM
  diskfree,
  (
    SELECT 
      GROUP_CONCAT(top_ids_per_group) AS top_ids
    FROM
      (
        SELECT 
          SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY created DESC), ',', 2) AS top_ids_per_group
        FROM
          diskfree
        GROUP BY
          hostname,mountpoint
      ) s_top_ids_per_group
  ) s_top_ids
WHERE
  FIND_IN_SET(id, top_ids)
ORDER BY
  hostname,
  mountpoint,
  created desc
;

ist ursprünglich von hier: SQL: selecting top N records per group, another solution | code.openark.org

Ergebnis scheint zu passen, aber auch hier habe ich beim group by andere Felder als beim zugehörigen Select (oder ist das sorum erlaubt?). Ich habe aber gelesen, dass diese Regel nur zutrifft, wenn man KEINE Aggregationsfunktion nutzt (in diesem Fall ist das group_concat(), vorher das max())

Abfrage geht auch sehr schnell

wäre nur besser, wenn letzter und vorletzte eintrag miteinander verjoint wären (eine zeile), vielleicht geht das noch

[Gast]

Die Abfrage ist fehlerhaft. Im GROUP BY müssen alle Spalten aus dem SELECT angeführt werden.

Zu dem Thema gibts übrigens sogar einen Artikel im MySQL-Handbuch:

https://dev.mysql.com/doc/refman/8.0...group-row.html among us

Thank you very much, I have been seeking for this information for a very long time, and the information that you provided in your article has been very helpful to me in my work.

[Gast]

Excellent article! Always anticipate what this publication will highlight!! fnaf unblocked