Frage zu Aggregationsabfrage

[ThomasSchoene]

Hallo Datenbankforum,

ich habe eine sql Frage und konnte bisher keine passende oder für mich übertragbare Antwort auf mein Problem finden.
Ich habe derzeit dieses (vermutlich einfache) SQL Problem (SQL Server 2014/2016):

Es geht um eine Aggregationsabfrage:

Stark vereinfacht dargestellt habe ich 2 Tabellen (Namenskonventionen, wie man perfekt Indexe setzt und so weiter - habe ich im Beispiel jetzt aussen vor gelassen - daran also bitte nicht stören falls da was auffallen sollte)

Tabelle tblMaschinen
Feld MaschinenID int (Primärschlüssel)
Feld Maschinenname varchar
Feld Maschinennummer varchar

Tabelle tblMaschinenLogin
Feld MaschinenLoginID int (Primärschlüssel)
Feld MaschinenID int (Fremdschlüssel)
Feld MaschinenloginVerbindungsname varchar
Feld MaschinenloginDatumUndZeit date

Tabelle tblMaschinen sind also Stammdaten, Tabelle tblMaschinenLogin sind Bewegungsdaten wo je login sich z.B. MaschinenloginVerbindungsname unterscheiden kann

Was ich darstellen will ist ein Abfrageergebnis
a) mit den Spalten:
Maschinenname
Maschinennummer
MaschinenloginDatumUndZeit
MaschinenloginVerbindungsname
Count(MaschinenLoginID) as AnzahlLogins
b) und dem Zeilenergebnis:
...für jede Maschine (auch die ohne logins) die Daten nur vom max(MaschinenloginDatumUndZeit) je MaschinenID und Anzahl er Logins der jeweiligen Maschine
(Anmerkung: MaschinenloginVerbindungsname ist zum Teil eine technische, neu generierte Zeichenfolge die ich deshalb hier im Beispiel nicht normalisiert)

Oder anders formuliert möchte ich alle Maschinen mit deren Detaildaten und als weitere Spalten alle Detaildaten der Maschine zum letzten Login plus die Anzahl der Logins der Maschine.

Test:

CREATE TABLE [dbo].[tblMaschinen](
[MaschinenID] [int] NOT NULL,
[Maschinenname] [varchar](50) NOT NULL,
[Maschinennummer] [varchar](20) NULL,
CONSTRAINT [PK_tblMaschinen] PRIMARY KEY CLUSTERED ( [MaschinenID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblMaschinenLogin](
[MaschinenLoginID] [int] NOT NULL,
[MaschinenID] [int] NOT NULL,
[MaschinenloginVerbindungsname] [varchar](20) NOT NULL, [MaschinenloginDatumUndZeit] [datetime] NOT NULL, CONSTRAINT [PK_tblMaschinenLogin] PRIMARY KEY CLUSTERED ( [MaschinenLoginID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO [dbo].[tblMaschinen]([MaschinenID],[Maschinenname],[Maschinennummer]) VALUES(1, 'Maschine1', '123') INSERT INTO [dbo].[tblMaschinen]([MaschinenID],[Maschinenname],[Maschinennummer]) VALUES(2, 'Maschine2', '456') INSERT INTO [dbo].[tblMaschinen]([MaschinenID],[Maschinenname],[Maschinennummer]) VALUES(3, 'Maschine3', '789')


INSERT INTO [dbo].[tblMaschinenLogin]([MaschinenLoginID],[MaschinenID],[MaschinenloginVerbindungsname],[MaschinenloginDatumUndZeit]) VALUES(1, 1, 'V1-abc', '01.01.2020 13:50') INSERT INTO [dbo].[tblMaschinenLogin]([MaschinenLoginID],[MaschinenID],[MaschinenloginVerbindungsname],[MaschinenloginDatumUndZeit]) VALUES(2, 1, 'V1-8546', '08.01.2020 11:50') INSERT INTO [dbo].[tblMaschinenLogin]([MaschinenLoginID],[MaschinenID],[MaschinenloginVerbindungsname],[MaschinenloginDatumUndZeit]) VALUES(3, 3, 'V6-pera', '05.01.2020 11:20') INSERT INTO [dbo].[tblMaschinenLogin]([MaschinenLoginID],[MaschinenID],[MaschinenloginVerbindungsname],[MaschinenloginDatumUndZeit]) VALUES(4, 3, 'V6-parx', '07.01.2020 13:20') INSERT INTO [dbo].[tblMaschinenLogin]([MaschinenLoginID],[MaschinenID],[MaschinenloginVerbindungsname],[MaschinenloginDatumUndZeit]) VALUES(5, 3, 'V2-463s', '08.01.2020 00:20') INSERT INTO [dbo].[tblMaschinenLogin]([MaschinenLoginID],[MaschinenID],[MaschinenloginVerbindungsname],[MaschinenloginDatumUndZeit]) VALUES(6, 3, 'V6-parx', '08.01.2020 11:50')

Die relevanten Datensätze sind hier also: MaschinenLoginID 2, 6 - da sie das je jüngste Login Datum je Maschine darstellen und deren IDs sind.

Mit meinem bisherigen Können (oder Nicht-Können )würde ich es über einen subselect versuchen der auf die MaschinenLoginID 2, 6 "filtert", aber der subselect muss ja auf die MaschinenloginDatumUndZeit und MaschinenID gruppieren und dann aber die MaschinenLoginID zurückgeben...
...oder vermutlich komme ich nicht auf den richtigen Ansatz wo ich hoffe das ihr mir auf die Sprünge helfen könnt.

Grüße Thomas Schöne

 

[akretschmer]

Was ich darstellen will ist ein Abfrageergebnis
a) mit den Spalten:
Maschinenname
Maschinennummer
MaschinenloginDatumUndZeit
MaschinenloginVerbindungsname
Count(MaschinenLoginID) as AnzahlLogins

Das ist unlogisch. Für welchen Login (das können ja viele sein) möchtest Du jetzt MaschinenloginDatumUndZeit ? Frühester, letzter, zufällig, Mittelwert?

 

[ThomasSchoene]

Hallo akretschmer - vermutlich habe ich, was rauskommen soll, nicht verständlich genug beschrieben - deswegen hier gezielt auf die Frage eine Antwort.
">Das ist unlogisch. Für welchen Login (das können ja viele sein) möchtest Du jetzt MaschinenloginDatumUndZeit ? Frühester, letzter, zufällig, Mittelwert?"

in Bezug auf die Frage schrieb ich "--- ...für jede Maschine (auch die ohne logins) die Daten nur vom max(MaschinenloginDatumUndZeit) je MaschinenID und Anzahl der Logins der jeweiligen Maschine..."
und ist "Max(…" nicht immer das jüngste Login?

 

[akretschmer]

mit vereinfachten Tabellen:

test=# create table maschinen(id serial primary key, name text);
CREATE TABLE
test=*# create table maschinen_login(id serial primary key, masch_id int references maschinen, anmelder text, login_ts timestamp);
CREATE TABLE
test=*# insert into maschinen (name) select 'maschine ' || s::text from generate_series(1,5) s;
INSERT 0 5
test=*# insert into maschinen_login (masch_id, anmelder, login_ts) select (random() * 4)+1, 'anmelder ' || (random()*100)::int::text, '2019-01-01'::date + random() * 500 * '1day'::interval from generate_series(1,50) s;
INSERT 0 50
test=*# select m.name, max(l.login_ts),count(l) from maschinen m left join maschinen_login l on m.id=l.masch_id group by m.name;
    name    |            max             | count
------------+----------------------------+-------
 maschine 1 | 2020-03-29 12:28:25.060746 |    10
 maschine 4 | 2020-03-02 08:02:38.15094  |    11
 maschine 5 | 2020-04-25 11:37:31.846237 |     5
 maschine 3 | 2020-04-10 11:50:14.990008 |    13
 maschine 2 | 2020-05-02 06:03:20.302906 |    11
(5 rows)

PostgreSQL, aber Aggregationen gehen überall relativ ähnlich.

 

[ThomasSchoene]

Vielen lieben Dank für die Antwort . Über den Ansatz hatte ich auch schon nachgedacht - der funktioniert im vereinfachten Beispiel wunderbar- mein Problem ist ja aber : ich möchte im Ergebnis zusätzlich z.B. den Wert von MaschinenloginVerbindungsname ausgegeben bekommen und zwar immer genau den Wert vom letzten Login der Maschine. Muss ich da in Richtung "Window" SQL Funktion gehen?

 

[akretschmer]

Mit meinen Tabellen, kannst Du aus der maschinen_login den Namen des Anmelder so ermitteln:

test=*# select masch_id, anmelder from maschinen_login where (masch_id, login_ts) in (select masch_id, max(login_ts) from maschinen_login group by masch_id);
 masch_id |  anmelder   
----------+-------------
        5 | anmelder 46
        4 | anmelder 51
        2 | anmelder 19
        1 | anmelder 90
        3 | anmelder 0
(5 rows)

und alles zusammen:

test=*# select m.name, max(l.login_ts),a.anmelder, count(l) from maschinen m left join maschinen_login l on m.id=l.masch_id left join (select masch_id, anmelder from maschinen_login where (masch_id, login_ts) in (select masch_id, max(login_ts) from maschinen_login group by masch_id)) a on m.id=a.masch_id group by m.name, a.anmelder;
    name    |            max             |  anmelder   | count
------------+----------------------------+-------------+-------
 maschine 1 | 2020-03-29 12:28:25.060746 | anmelder 90 |    10
 maschine 4 | 2020-03-02 08:02:38.15094  | anmelder 51 |    11
 maschine 3 | 2020-04-10 11:50:14.990008 | anmelder 0  |    13
 maschine 2 | 2020-05-02 06:03:20.302906 | anmelder 19 |    11
 maschine 5 | 2020-04-25 11:37:31.846237 | anmelder 46 |     5
(5 rows)

 

[ThomasSchoene]

Hallo AKretschmer,
vielen vielen Dank für die Mühe, ich habe es jetzt auf mein Beispiel so übersetzt und es liefert das Ergebnis:
select m.Maschinenname, max(ml.MaschinenloginDatumUndZeit), sq.MaschinenloginVerbindungsname, count(1) as AnzahlLogins
from tblMaschinen as m
left join tblMaschinenLogin as ml on m.MaschinenID = ml.MaschinenID
left join ( select MaschinenID, MaschinenloginVerbindungsname from tblMaschinenLogin where MaschinenID + MaschinenloginDatumUndZeit in
(select MaschinenID + max(MaschinenloginDatumUndZeit) from tblMaschinenLogin group by MaschinenID)) sq on m.MaschinenID = sq.MaschinenID
group by m.Maschinenname, sq.MaschinenloginVerbindungsname order by m.Maschinenname
Das mit den 2 Feldern subselect konnte ich jetzt bei mir nur mit "+" lösen.
Ich muss es morgen mit wachem Kopf noch 2 x mal lesen ums ganz zu verstehen und einen join auf einen subselect hatte ich noch nie gemacht , also wieder was zum lernen. Dir noch einen schönen Restsonntag! Viele Grüße Thomas Schöne.