SQL - Zählen von Datensätzen

[idem1]

Hallo zusammen,

ich stehe vor folgendem Problem und komme einfach nicht weiter.

Meine Tabelle sieht folgendermaßen aus:

ID Wert1 Wert2
1 x t
1 x NULL
1 y b
2 NULL c
2 NULL NULL


Ich möchte gerne zählen, wie oft sich die aufeinander folgenden Werte innerhalb eines Attributs (Wert1, Wert2) pro ID ändert. Die Ausgabe soll so aussehen:

ID Änderung_Wert1 Änderung_Wert2
1 1 2
2 0 1

Hoffe, ihr könnt mir weiterhelfen.

 

[akretschmer]

könnte mittels lead() oder lag() gehen, allerdings suche ich ein Sortiermerkmal. Der zweite und dritte Datensatz könnte auch andersrum stehen, dann wäre des Resultat ein anderes.

 

[idem1]

Danke dir für den Tipp mit lead() und lag(). Die kenne ich gar nicht.

Es ist in der Tat so, dass ein anderes Ergebnis geliefert werden soll, wenn die beiden DS vertauscht werden.

Ich möchte lediglich schauen, wie oft sich die Werte eines Atttributs verändern.

 

[akretschmer]

Aus Datenbanksicht sich folgende Datenmenge identisch:

test=*# select * from foo;
 id | wert
----+------
  1 |  1
  1 |  1
  1 |  2
(3 rows)

und

test=*# select * from foo;
 id | wert
----+------
  1 |  1
  1 |  2
  1 |  1
(3 rows)

Hast Du nun 1 oder 2 mal einen Wechsel in der wert-Spalte?

PS.: um zu zeigen, was ich gemacht habe. PostgreSQL hat eine versteckte Spalte, ctid.

test=*# select *, ctid from foo;
 id | wert | ctid  
----+------+-------
  1 |  1 | (0,1)
  1 |  1 | (0,2)
  1 |  2 | (0,3)
(3 rows)

Ich habe den Datensatz mit ctid (0,2), wo wert ja auf 1 steht, geupdatet. Und zwar habe ich wert wieder auf 1 gesetzt. Völlig identische Daten also, zumindest in den vom Anwender definierten Spalten:

test=*# update foo set wert = 1 where ctid = '(0,2)';
UPDATE 1
test=*# select *, ctid from foo;
 id | wert | ctid  
----+------+-------
  1 |  1 | (0,1)
  1 |  2 | (0,3)
  1 |  1 | (0,4)
(3 rows)

Es hat sich allerdings die physische Anordnung der Datensätze geändert. Ohne ein definiertes ORDER BY ist es der Datenbank völlig freigestellt, wie sie die Daten als Ergebnis liefert. Daher fragte ich in meiner ersten Antwort nach einem Sortiermerkmal, denn sonst würdest Du u.U. bei exakt gleichen Daten je nach Lust und Laune der Datenbank unterschiedliche Ergebnisse bekommen. Das kann z.B. passieren, wenn dieselbe Tabelle manchmal in einer anderen Session ebenfalls gelesen wird, dann könnte die DB des nutzen, um die Performance zu verbessern. In PostgreSQL ist das ein Feature seit 8.3, hier aus den Release Notes:

Concurrent large sequential scans can now share disk reads (Jeff Davis)
This is accomplished by starting the new sequential scan in the middle of the table (where another sequential scan is already in-progress) and wrapping around to the beginning to finish. This can affect the order of returned rows in a query that does not specify ORDER BY.

 

[idem1]

Danke dir! Mit deinem Beispiel ist mir jetzt klar, was du mit dem Sortiermerkmal meintest. Ich habe tatsächlich noch ein Attribut mit dem Datum.

 

[idem1]

Ok, ich hab mir mal lag und lead angeschaut.
Ich müsste meine Datensätze nach ID gruppieren und nach Datum sortieren. Dann müsste ich eine Schleife einbauen, die so oft durchläuft, wie die Anzahl meiner gruppierten IDs -1 (Beispiel ID = 1 müsste zweimal durchlaufen) und dabei müsste Wert1 mit Lead(Wert1) verglichen werden. Bei Ungleichheit müsste dann ein separater Zähler +1 machen und Wert1 den Wert von Lead(Wert1) übergeben bekommen.

Stimmt mein Gedankengang und ist sowas mit sql möglich?

 

[akretschmer]

Das wird jetzt etwas, ähm, ...

test=# select * from idem1;
 id | wert1 | wert2 | c
----+-------+-------+---
  1 | x  | t  | 1
  1 | x  |  | 2
  1 | y  | b  | 3
  2 |  | c  | 4
  2 |  |  | 5
(5 rows)

test=*# select
  id,
  sum(c1) as aenderung_1,
  sum(c2) as aenderung_2
from (
  select
  *,
  case when coalesce(wert1,'NULL') != coalesce(w1,'NULL') then 1 else 0 end as c1,
  case when coalesce(wert2,'NULL') != coalesce(w2,'NULL') then 1 else 0 end as c2
  from (
  select
  id,
  c,
  wert1,
  lead(wert1) over (partition by id order by c) w1,
  wert2,
  lead(wert2) over (partition by id order by c) w2,
  row_number() over (partition by id order by c desc)
  from idem1)
  foo
  where
  row_number != 1
  order by c
) bla
group by id
order by id;
 id | aenderung_1 | aenderung_2
----+-------------+-------------
  1 |  1 |  2
  2 |  0 |  1
(2 rows)

test=*#

Ich habe statt einem Datumsfeld einfach eine fortlaufende Nummerierung gemacht (Spalte c). Der Rest ist selbsterklärend, oder?


;-)

 

[idem1]

Ähhh, joa.. bin grad etwas sprachlos...
Ich danke dir erst mal herzlich! Um das Ganze verstehen zu können muss ich das, was du geschrieben hast erst einmal auseinanderpflücken. Ich melde mich wieder (... je nachdem wie lange ich jetzt brauchen werde).

 

[idem1]

Ok, ich muss zugeben, das hätte ich niemals (!) so hinbekommen (du zauberst das kurz mal hier so hin), aber ich kann wenigstens nachvollziehen, was du machst. Immerhin...

Weisst du, ob es irgendeine Alternative für lead() gibt? Ich arbeite mit einem älteren SQL Server, sodass die Funkion nicht erkannt wird.

 

[ukulele]

Schon verblüffend das akretschmer MSSQL-kompatiblen Code schreibt ohne irgendwelche PG-spezifische Syntax und jetzt ist dein MSSQL zu alt :-/

Welche Version hast du denn? lag() und lead() lassen quasi nachbauen, ist aber ekelhaft. Einen Select mit ROW_NUMBER() mit sich selbst joinen, nur eben versetzt. Ideal wäre ein aktuelleres MSSQL.

 

[akretschmer]

Schon verblüffend das akretschmer MSSQL-kompatiblen Code schreibt ohne irgendwelche PG-spezifische Syntax und jetzt ist dein MSSQL zu alt :-/

ROFL.

 

[idem1]

Um die Funktionsweise von lead() zu verstehen, wollte ich mir in einer neuen Spalte die Datensätze einer vorhandenen Spalte versetzt anzeigen lassen. Ich bekomme jedoch die Meldung "LEAD' wird nicht als Name einer integrierten Funktion erkannt."

Ich arbeite mit der Version 2008.

 

[idem1]

Hmm.. habe ich etwas verpasst?!

 

[ukulele]

Ein Update auf ein neueres SQL steht nicht zufällig an?

Hier mal ein Beispiel, wie man lag() nachbauen kann:

WITH t(spalte1,spalte2) AS (
   SELECT 'A','A' UNION ALL
   SELECT 'A','B' UNION ALL
   SELECT 'A','C'
   )
SELECT   t.spalte1,
     t.spalte2,
     lag(spalte2) OVER (ORDER BY spalte2) AS lag
FROM   t

WITH t(spalte1,spalte2) AS (
   SELECT 'A','A' UNION ALL
   SELECT 'A','B' UNION ALL
   SELECT 'A','C'
   ), tabelle(zeile,spalte1,spalte2) AS (
   SELECT   ROW_NUMBER() OVER (PARTITION BY spalte1 ORDER BY spalte2),
       *
   FROM   t
   )
SELECT   t1.spalte1,
     t1.spalte2,
     t2.spalte2 AS lag
FROM   tabelle t1
LEFT JOIN tabelle t2
ON     t1.spalte1 = t2.spalte1
AND     t1.zeile = t2.zeile + 1

t ist nur meine Testtabelle. Aus t machst du tabelle in der die Zeilen durchnummeriert werden und dann joinst du das mit sich selbst.