Differenz berechnen

[deathdragon]

Hallo ich suche eine Möglichkeit aus mehreren Datensätzen (durch ID eindeutig identifizierbar) stets Differenz zwischen den kleineren Wert und den nächst größeren Wert zu bilden.

Beispiel:

ID Wert
1 10
2 11
3 12

ich komme einfach auf keine Lösung die den kleineren von den nächst größeren Wert herauszufinden und gleichzeitig noch die Differenz zu bilden.

kann mir jemand helfen?

[defo]

Mit "Analytic" - oder "Window Functions" kannst Du auf den vorigen oder nächsten Datensatz (unter definierter Sortierung) zugreifen.
Nennt sich unter Oracle Lag und Lead, hat MS dann glaub ich auch so genannt.

[deathdragon]

diese Funktionen stehen mir noch nicht zur verfügung ich nutze noch 2008er server

[deathdragon]

hier habe ich noch etwas gefunden aber ich verstehe es leider nicht und kann es auch nicht in Zusammenhang bringen mit der Differenzbildung

http://stackoverflow.com/questions/2...ql-server-2008

[defo]

Schau dir mal das hier an:
http://blog.sqlauthority.com/2011/11...ytic-function/
Abschnitt -- Co-Related subquery (weiter unten)

Wahrscheinlich hilft es Dir, gedanklich erstmal auf die Subtraktion zu verzichten und die Query so zu bauen, dass Du alle Operanden (in einem Rekord) zusammen hast. Die Subtraktion bekommst Du dann sicher auch noch hin.

Dazu fällt mir noch ein:

Divide and conquer

..hat mal irgendwer gesagt..

könnte man hier auch so schreiben

Divide and conquer
..
and than subtract

[Ralf Jansen]

Nur mit klassischen SQL Hausmitteln.

[HIGHLIGHT=SQL]SELECT id, wert,
wert - ISNULL((SELECT TOP 1 wert
FROM Table as t2
WHERE (t2.wert < t1.wert) OR
((t2.wert = t1.wert) AND (t1.id > t2.id))
ORDER BY wert DESC, id), 0) AS Difference
FROM table as t1
ORDER BY wert, id[/HIGHLIGHT]

[deathdragon]

klappt schon mal super aber wie bekomme ich die erste zeile der ausgabe eine 0 oder noch besser NULL. In der ersten Zeile der Ausgabe kann ja keine Differenz gebildet werden deswegen dürfte gerne im Ergebnis ein Null stehen.

Ich hatte folgendes probiert aber irgendwie klappt das nicht! kann jemand helfen?

 SELECT 
		[CounterID], 
		[Zaehler_1K2_15],
		[Zaehler_1K2_15] - ISNULL(( if [CounterID] = 1  Select CAST(NULL as float(6)) as [Zaehler_1K2_15] 
		
													FROM [SKEMS-HST-POWER].[dbo].[EnergieMonat] AS t2
            WHERE (t2.[Zaehler_1K2_15] < t1.[Zaehler_1K2_15]) OR
                   ((t2.[Zaehler_1K2_15] = t1.[Zaehler_1K2_15]) AND (t1.[CounterID] > t2.[CounterID]))
                       ORDER BY [Zaehler_1K2_15] DESC, [CounterID]), 0) AS Difference
												
										else	SELECT TOP 1 [Zaehler_1K2_15]
                        
			FROM [SKEMS-HST-POWER].[dbo].[EnergieMonat] AS t2
            WHERE (t2.[Zaehler_1K2_15] < t1.[Zaehler_1K2_15]) OR
                   ((t2.[Zaehler_1K2_15] = t1.[Zaehler_1K2_15]) AND (t1.[CounterID] > t2.[CounterID]))
                       ORDER BY [Zaehler_1K2_15] DESC, [CounterID]), 0) AS Difference
 
 FROM [SKEMS-HST-POWER].[dbo].[EnergieMonat] AS t1
ORDER BY [Zaehler_1K2_15], [CounterID]

[Ralf Jansen]

klappt schon mal super aber wie bekomme ich die erste zeile der ausgabe eine 0 oder noch besser NULL.

Lass einfach den ISNULL Block um den Subselect weg. Der ist nur dafür da das genau das nicht passiert was du gerade möchtest

[deathdragon]

danke aber darauf hätte ich wirklich auch selber kommen können schande über mich!

aber ich habe gerade erfahren das nun auch bald mehr als ein wert in der Tabelle steht. Das heißt ich müsste aus mehreren werten die Differenz erstellen. wie kann ich das bewerkstelligen? die Ergebnisse zusammen Union sollte ja wegen der order by nicht gehen?! was kann ich hier tuen?

Obwohl es sollte doch reichen wenn ich die Order by erweitere oder? sollte doch funktionieren! ich mach mich gleich mal drüber

[deathdragon]

Soweit klappt das alles sehr gut! Die Differenz wird bestimmt jedoch dauert das alles sehr lange! ich muss die Abfrage optimieren aber geht das überhaupt und wenn ja wie? Oder habe ich einen Fehler?

if year(getdate()) !=	(select max (cast(substring(CONVERT(varchar(10),[Datum],101),7,4) as numeric))
						from [SKEMS-HST-POWER].[dbo].[EnergieMonat] as t1_max
						where CounterID = t1_max.CounterID) 
						and 
	MONTH(GETDATE()) =  (select max (cast(substring(CONVERT(varchar(10),[Datum],101),1,2) as numeric))
						from [SKEMS-HST-POWER].[dbo].[EnergieMonat] as t1_max
						where CounterID = t1_max.CounterID) 
	RAISERROR ('Daten liegen in Quelle nicht vor.', -- Message text.
    16, -- Severity.
    1 -- State.
    );
               
     ELSE			

SELECT [CounterID]
      ,[Datum]
      ,cast(substring(CONVERT(varchar(10),[Datum],101),7,4) as numeric) as Jahr
      ,cast(substring(CONVERT(varchar(10),[Datum],101),4,2) as numeric) as Tag
      ,cast(substring(CONVERT(varchar(10),[Datum],101),1,2) as numeric) as Monat
      ,[Zaehler_1K1_1]
      ,[Zaehler_1K1_1]-(SELECT TOP 1 [Zaehler_1K1_1] FROM [SKEMS-HST-POWER].[dbo].[EnergieMonat] AS t2 WHERE (t2.[Zaehler_1K1_1] < t1.[Zaehler_1K1_1]) OR((t2.[Zaehler_1K1_1] = t1.[Zaehler_1K1_1]) AND (t1.[CounterID] > t2.[CounterID])) ORDER BY [Zaehler_1K1_1] DESC, [CounterID])  AS [Zaehler_1K1_1-Differenz]
      ,[Zaehler_1K1_2]
      ,[Zaehler_1K1_2]-(SELECT TOP 1 [Zaehler_1K1_2] FROM [SKEMS-HST-POWER].[dbo].[EnergieMonat] AS t2 WHERE (t2.[Zaehler_1K1_2] < t1.[Zaehler_1K1_2]) OR((t2.[Zaehler_1K1_2] = t1.[Zaehler_1K1_2]) AND (t1.[CounterID] > t2.[CounterID])) ORDER BY [Zaehler_1K1_2] DESC, [CounterID])  AS [Zaehler_1K1_2-Differenz]
      ,[Zaehler_1K1_3]
      ,[Zaehler_1K1_3]-(SELECT TOP 1 [Zaehler_1K1_3] FROM [SKEMS-HST-POWER].[dbo].[EnergieMonat] AS t2 WHERE (t2.[Zaehler_1K1_3] < t1.[Zaehler_1K1_3]) OR((t2.[Zaehler_1K1_3] = t1.[Zaehler_1K1_3]) AND (t1.[CounterID] > t2.[CounterID])) ORDER BY [Zaehler_1K1_3] DESC, [CounterID])  AS [Zaehler_1K1_3-Differenz]
      ,[Zaehler_1K1_4]
      ,[Zaehler_1K1_4]-(SELECT TOP 1 [Zaehler_1K1_4] FROM [SKEMS-HST-POWER].[dbo].[EnergieMonat] AS t2 WHERE (t2.[Zaehler_1K1_4] < t1.[Zaehler_1K1_4]) OR((t2.[Zaehler_1K1_4] = t1.[Zaehler_1K1_4]) AND (t1.[CounterID] > t2.[CounterID])) ORDER BY [Zaehler_1K1_4] DESC, [CounterID])  AS [Zaehler_1K1_4-Differenz]
      ,[Zaehler_1K1_5]
      ,[Zaehler_1K1_5]-(SELECT TOP 1 [Zaehler_1K1_5] FROM [SKEMS-HST-POWER].[dbo].[EnergieMonat] AS t2 WHERE (t2.[Zaehler_1K1_5] < t1.[Zaehler_1K1_5]) OR((t2.[Zaehler_1K1_5] = t1.[Zaehler_1K1_5]) AND (t1.[CounterID] > t2.[CounterID])) ORDER BY [Zaehler_1K1_5] DESC, [CounterID])  AS [Zaehler_1K1_5-Differenz]
      ,[Zaehler_1K1_6]
      ,[Zaehler_1K1_6]-(SELECT TOP 1 [Zaehler_1K1_6] FROM [SKEMS-HST-POWER].[dbo].[EnergieMonat] AS t2 WHERE (t2.[Zaehler_1K1_6] < t1.[Zaehler_1K1_6]) OR((t2.[Zaehler_1K1_6] = t1.[Zaehler_1K1_6]) AND (t1.[CounterID] > t2.[CounterID])) ORDER BY [Zaehler_1K1_6] DESC, [CounterID])  AS [Zaehler_1K1_6-Differenz]
      ,[Zaehler_1K1_7]
      ,[Zaehler_1K1_7]-(SELECT TOP 1 [Zaehler_1K1_7] FROM [SKEMS-HST-POWER].[dbo].[EnergieMonat] AS t2 WHERE (t2.[Zaehler_1K1_7] < t1.[Zaehler_1K1_7]) OR((t2.[Zaehler_1K1_7] = t1.[Zaehler_1K1_7]) AND (t1.[CounterID] > t2.[CounterID])) ORDER BY [Zaehler_1K1_7] DESC, [CounterID])  AS [Zaehler_1K1_7-Differenz]
      ,[Zaehler_1K1_8]
      ,[Zaehler_1K1_8]-(SELECT TOP 1 [Zaehler_1K1_8] FROM [SKEMS-HST-POWER].[dbo].[EnergieMonat] AS t2 WHERE (t2.[Zaehler_1K1_8] < t1.[Zaehler_1K1_8]) OR((t2.[Zaehler_1K1_8] = t1.[Zaehler_1K1_8]) AND (t1.[CounterID] > t2.[CounterID])) ORDER BY [Zaehler_1K1_8] DESC, [CounterID])  AS [Zaehler_1K1_8-Differenz]

  FROM [SKEMS-HST-POWER].[dbo].[EnergieMonat] as t1 
    
    where year(getdate()) =(select max (cast(substring(CONVERT(varchar(10),[Datum],101),7,4) as numeric))
					from [SKEMS-HST-POWER].[dbo].[EnergieMonat] as t1_max
						where t1.CounterID = t1_max.CounterID)  
						and 
		MONTH(GETDATE()) = (select max (cast(substring(CONVERT(varchar(10),[Datum],101),1,2) as numeric))
					from [SKEMS-HST-POWER].[dbo].[EnergieMonat] as t1_max
					where t1.CounterID = t1_max.CounterID)
	GO

[deathdragon]

keine sourcecode fehler?

[defo]

keine sourcecode fehler?

Wahrscheinlich nicht, es führen viele Wege nach Rom. Aus der Ferne ist das schwer zu beurteilen, aber diese Abfrage wird vermutlich einen fürchterlichen Ausführungsplan produzieren (Solltest Du Dir mal ansehen). Es gibt die Window functions nicht ohne Grund und nicht nur, weil das Selectstatement viel "bequemer" ist.
Wenn Du die Muße hast, probier mal ne aktuelle Express Edition mit Window Functions auf Deinem lokalen Rechner, das wird viel schneller sein.
Wenn 2012er keine Option ist, muss man versuchen, das Statement zu tunen:
Punkt 1a: Die Where Bedingung der Hauptabfrage macht eine Einschränkung auf aktuelles Jahr, aktueller Monat?
nimm das casting raus und mach ein between Anfang des Monats und Ende des Monats und stelle sicher, dass ein Index auf das Feld vorhanden ist und genutzt wird. (Mit Cast kann der Index nicht genutzt werden)
Punkt 1b: Nimm diese Unterabfrage t1max als eigene Datenquelle in die FromClause neben t1
Punkt 2: verwende auf den Zählerfeldern ebenfalls Indizes, probehalber, das kann an anderer Stelle auch negative Auswirkungen haben (bei Insert) oder
Punkt 3: Ziehe die Unterabfragen aus den Feldern des Hauptstatements und packe sie in die Haupt-Fromclause, wenn möglich.

[deathdragon]

hallo defo, danke das du dir die zeit nimmst und mal über die Thematik schaust. Aber ich hätte noch ein paar Fragen. Wie kann ich mir den Ausführungsplan anschauen? geht das im SQL Server Management Studio?

ich habe auch gerade einen Index auf Datum gesetzt. Wird dieser Automatisch herangezogen wenn ich nun eine Select auf Datum mache? Oder muss ich direkt den Index ansprechen?

ansonsten hängel ich mich jetzt step by step durch deine Vorschläge

[defo]

Wie kann ich mir den Ausführungsplan anschauen? geht das im SQL Server Management Studio?

Davon geh ich mal aus. Hab das selten installiert.
Wenn Du nach Ausführungsplan Management Studio im Netz suchst, solltest Du fündig werden.

ich habe auch gerade einen Index auf Datum gesetzt. Wird dieser Automatisch herangezogen wenn ich nun eine Select auf Datum mache? Oder muss ich direkt den Index ansprechen?

Der Index wird wenn möglich automatisch verwendet, wenn ein Select mit einer where Bedingung oder Join über die indizierte Spalte gefahren wird.
Wird die indizierte Spalte nicht direkt angesprochen, sondern mittels diverser Funktionen und Casts zerlegt, dann kann der Index nicht verwendet werden.
Es gibt auch die Möglichkeit, Indexverwendung zu "erzwingen", das hab ich bei MSSQL noch nicht gemacht. Ist auch eher so die letzte Option.

Idealerweise spürt man die Verwendung des Index auch (oder schaut auf die 1000stel Sekunden der Abfragedauer)