SQL Schlüsselwörter
WHERE
Mit der Option "WHERE" können in einem SQL-Statement weitere Filter angegeben werden, die von der FROM-Angabe (z.B. bei weiteren Filterungen, die nicht im FROM-Teil möglich sind) nicht übernommen werden sollen.
Wenn Sie Datenpunktelemente in einer Select-Anweisung abfragen, müssen Sie die Datenpunkttyp-Klausel verwenden, da WinCC OA IDs verwendet und derselbe Datenpunktelementname unterschiedliche IDs für verschiedene DP-Typen haben kann. Daher dürfen Sie eine Abfrage des folgenden Formats nicht verwenden:
string strQuery = "SELECT '.name:_original.._value, .Datapoint1.Element1:_original.._value' FROM '*'";
Verwenden Sie stattdessen:
string strQuery = "SELECT '.name:_original.._value,.Datapoint.Element1:_original.._value' FROM '*' WHERE _DPT = \"ExampleDp_Float\"";
Der Datenpunkttyp muss auch angegeben werden, wenn die WHERE-Bedingung DP-Elementnamen enthält: z.B.: WHERE '.dpe' = 1!
Beachten Sie, dass als WHERE-Bedingung im SQL-Panel nur Attribute eines Konfigs vorkommen dürfen.
Verwenden Sie dasselbe Attribut nicht zwei Mal in einem SELECT-Ausdruck.
Um die Abfrage schneller ausführen zu lassen, macht es Sinn, die Datenpunktangabe direkt hinter dem FROM-Statement anzugeben.
Erläuterungen zu den TIMERANGE- oder Gruppenfunktionen bzw. zu den Variablen start und stop siehe im entsprechenden Kapitel dieses Abschnitts.
Es werden hier nur die Werte des Datenpunktes in die Ergebnistabelle geschrieben, deren Online-Wert größer als 45 ist.
main()
{
dyn_dyn_anytype tab;
int z;
dpQuery("SELECT '_original.._value' FROM 'ExampleDP_Arg*' WHERE ('_original.._value' > 45) TIMERANGE(\"2007.10.06 09:00:00.000\", \"2007.10.06 10:55:00.000\",1,2)", tab);
/* Note that the query has to be written on one row without line breaks */
shape list1=getShape("SELECTION_LIST1");
for(z=2;z<=dynlen(tab);z++)
// dynlen(tab) Returns the length of tab.
setValue(list1,"appendItem",tab[z][2]);
}
Das FROM-Statement in diesem Beispiel akzeptiert die Online-Werte aller Datenpunkte. Über die WHERE-Funktion werden anschließend nur Datenpunkte vom Typ ExampleDP_Float zugelassen.
dpQuery("SELECT '_online.._value' FROM '*'WHERE _DPT = \"ExampleDP_Float\" TIMERANGE(\""+start+"\",\""+stop+"\", 1, 2)", tab);
Das FROM-Statement in diesem Beispiel akzeptiert die Online-Werte aller Datenpunkte. Über die WHERE-Funktion werden anschließend nur Datenpunkte zugelassen, deren Alias mit einem "A" beginnt.
dpQuery("SELECT '_original.._value' FROM '*' WHERE _ALIAS LIKE "A*"", tab);
Das FROM-Statement in diesem Beispiel akzeptiert die Online-Werte aller Datenpunkte. Über die WHERE-Funktion werden anschließend nur Datenpunkte zugelassen, deren Kommentar "OP" beinhaltet.
dpQuery("SELECT '_original.._value' FROM '*' WHERE _COMMENT LIKE "*OP*"", tab);
In diesem Beispiel werden alle Datenpunktelemente aller Datenpunkte ('*.**) vom Datenpunkttyp "PUMP1" von 09.07.2018 16.02 bis 09.07.2018 16:22 abgefragt.
main()
{
dyn_dyn_anytype tab;
int z;
dpQuery("SELECT '_online.._value' FROM '*.**' WHERE _DPT = \"PUMP1\" TIMERANGE(\"2018.07.09 16:02:00.000\", \"2018.07.09 16:22:00.000\",1,2)", tab);
/* Note that the query must be written on one row without line breaks */
shape list1=getShape("SELECTION_LIST1");
for(z=2;z<=dynlen(tab);z++)
//dynlen(tab) returns the length of tab
setValue(list1,"appendItem",tab[z][2]);
}
WHERE und AND
Mit dem Schlüsselwort "AND" können Sie eine WHERE- Abfrage einschränken. Das folgende Beispiel zeigt eine Abfrage mit dem Sie alle Onlinewerte mit Blattelementen die das Userbit7 gesetzt haben, abfragen können. Beachten Sie, dass die Abfrage im Code auf einer Zeile (ohne einen Zeilenumbruch) stehen muss, da sonst ein Syntax-Fehler ausgegeben wird.
main()
{
dyn_dyn_anytype tab;
int z;
int length;
dpQuery("SELECT '_online.._value' FROM '*' WHERE _LEAF AND '_online.._userbit7' == 1",tab);
//select all online values with leaf
//elements that have the userbit7 set.
length=dynlen(tab);
DebugN("Length:",length);
for(z=1;z<=length;z++)
{
DebugN(tab[z]);
}
}
Ebenso ist es möglich, eine Einschränkung der Daten mittels der Schlüsselwörter für Datenpunkte vorzunehmen.
main()
{
dyn_dyn_anytype tab;
dpQuery("SELECT ALL '_online.._value' FROM '*' WHERE _DPT = \"ANALOG1\" AND _EL = \"C1.AVG_WT0\"", tab);
DebugN(tab);
}
oder
main()
{dyn_dyn_anytype tab;
dpQuery("SELECT ALL '_online.._value' FROM '*' WHERE _DPT = \"ANALOG1\" AND _EL = \"C1.AVG_WT0\" AND DP = \"DP1 \"", tab);
DebugN(tab);
}
\"TRUE\" und \"FALSE\"
Wenn Sie einen booleschen Vergleich in einer WHERE-Klausel verwenden möchten, müssen die Schlüsselwörter TRUE oder FALSE wie folgt (\"TRUE\") geschrieben werden.
string SQLAbf = "SELECT '_online.._value' FROM '*' WHERE _LEAF AND '_online.._userbit7' == \"TRUE\" ";
Diese Abfrage kann natürlich genauso wie im obigen Beispiel unter Schlüsselwort "AND" verwendet werden.
TIMERANGE()-Funktion
Die TIMERANGE()-Funktion geht über Standard-SQL hinaus. Sie gestattet die Ausführung zeitabhängiger Abfragen auf Datenpunkte, muss jedoch einem SELECT-Statement folgen. Diese Funktion stellt das Pendant zu der dpGetPeriod-Funktionalität in WinCC OA dar, ist aber bei Connect-Funktionen nicht verfügbar (da diese auf aktuelle Werte verbinden).
Die Syntax der Funktion lautet:
TIMERANGE (start , stop , modus , bonus)
Die Angaben start und stop geben den zeitlichen Bereich vor, für den die Daten ausgelesen werden. Dabei ist zu beachten, dass start und stop vom Typ string sind (und nicht vom Typ time) und folgendes Format besitzen müssen:
YYYY.MM.DD hh:mm:ss.ttt
1998.03.05 14:06:45.012
, als Beispiel
Neben „now“ können die Schlüsselwörter „min“ und „max“ verwendet werden.
Die Option "now" kann verwendet werden, um Werte ab einem Zeitpunkt bis jetzt oder den aktuellen Wert abzufragen.
Wenn die Handhabung der Sommerzeit oder verschiedener Zeitzonen ein wichtiges Thema im Projekt ist, dann muss das ISO8601-Zeitformat in Strings anstelle des Standard-Stringformats für Zeitvariablen verwendet werden.
Im Beispiel unterhalb werden die Werte in eine Liste geschrieben:
/* Abfrage von 16.8.2017 um 9 Uhr bis jetzt + 1 Wert vor und nach dem Intervall
Beachten Sie, dass damit das Beispiel funktioniert, eine Liste benötigt wird (Im Beispiel "SELECTION_LIST1").
Fügen Sie die Liste zu Ihrem Panel hinzu. Siehe Grafikeditor GEDI->
Auswahlliste.
Fügen Sie zudem ein
Archiveconfig zu dem Datenpunkt ExampleDP_Arg1. der abgefragt wird, hinzu.*/
main(mapping event)
{
dyn_dyn_anytype tab;
int z;
shape list1=getShape("SELECTION_LIST1");
dpQuery("SELECT '_online.._value' FROM 'ExampleDP_Arg1' WHERE _DPT = \"ExampleDP_Float\"TIMERANGE(\"2017.08.16 09:00:00.000\", \"now\", 1, 1)", tab);
for(z=2;z<=dynlen(tab);z++)
// dynlen(tab) liefert die Länge vom Tab
setValue(list1,"appendItem",tab[z][2]);
}
/* Das folgende Beispiel liefert den aktuellen Wert + 1 Wert vor und nach dem Intervall.
Beachten Sie, dass damit das Beispiel funktioniert, eine Liste benötigt wird (Im Beispiel "SELECTION_LIST1").
Fügen Sie die Liste zu Ihrem Panel hinzu. Siehe Grafikeditor GEDI->
Auswahlliste
Fügen Sie zudem ein Archiveconfig zu dem Datenpunkt ExampleDP_Arg1. der abgefragt wird, hinzu.*/
main(mapping event)
{
dyn_dyn_anytype tab;
int z;
shape list1=getShape("SELECTION_LIST1");
dpQuery("SELECT '_online.._value' FROM 'ExampleDP_Arg1' WHERE _DPT = \"ExampleDP_Float\"TIMERANGE(\"now\", \"now\", 1, 1)", tab);
for(z=2;z<=dynlen(tab);z++)
// dynlen(tab) liefert die Länge vom Tab
setValue(list1,"appendItem",tab[z][2]);
}
"_corr.._manager" und "_corr.._user"
Die Attribute "_corr.._manager" und "_corr.._user" können nur in einer SQL-Abfrage innerhalb der "WHERE"-Klausel zusammen mit dem Schlüsselwort "TIMERANGE" verwendet werden. Das Attribut "_corr.._manager" ist nur für NGA verfügbar.
Dies ist eine gültige Abfrage mit dem Attribut "_corr.._manager" :
SELECT '_original.._value' FROM '*.**' WHERE '_corr.._manager' == 16844034 TIMERANGE("2023.09.10 16:29:56","2023.09.11 16:29:59",1,0)
ARCHIVENUM
Mit dem Schlüsselwort "ARCHIVENUM" kann ein bestimmtes Archiv abgefragt werden. Im folgenden Beispiel wird z.B. das Archiv mit der Nummer 2 abgefragt.
dpQuery("SELECT '_online.._value' FROM 'ExampleDP_Arg1' WHERE _DPT = \"ExampleDP_Float\" TIMERANGE(\"2005.10.26 00:00:00.000\", \"2005.10.26 23:59:59.000\", 1, 2) ARCHIVENUM 2", tab);
Modus - Modus = 1
Bonus
Über den Modusparameter hinaus kann im Bonusparameter auch noch die Anzahl der um das Intervall zusätzlich benötigten Werte übergeben werden. So liefert z.B. ein Modus 1 mit Bonus 2 alle Werte dem Standardverhalten entsprechend plus (mindestens) je zwei Ereignisse vor und nach dem Intervall. Die Werte vor und nach der Grenze sind pro Attribut aufzufassen. So wird sichergestellt, dass wirklich für jede Spalte der Abfrage zwei gültige Werte vorliegen.
mit start ="1998.05.26 11:04:25.000" vom Typ string und stop ="1998.05.26 23:59:59.000" vom Typ string ergibt sich:
dpQuery("SELECT '_online.._value' FROM 'mld_*.**' TIMERANGE(\""+start+"\",\""+stop+"\",1,2)", tab);
SORT BY-Funktion - SORT BY x [ASC | DESC]
Diese Funktion sortiert das Ergebnis nach dem x. Eintrag zwischen den Schlüsselwörtern SELECT und FROM. Mit dem Parameter ASC wird aufsteigend sortiert (Defaulteinstellung), mit DESC absteigend. Diese Funktion ist nicht bei Connect-Befehlen verfügbar! SORT/ORDER BY 0 - bezieht sich auf den Zeitstempel (erste Spalte der Ergebnistabelle). SORT/ORDER BY 1 - bezieht sich auf das erste Attribut nach dem 'SELECT' (zweite Spalte der Ergebnistabelle) usw. Wenn keine Sortierung angegeben wird, erfolgt die Sortierung anhand des Zeitstempels.
dpQuery("SELECT '_online.._value', '_online.._stime'FROM 'mld_float.**' TIMERANGE (\"1998.05.2600:00:00.000\", .\"1998.05.26 23:59:59.000\",1,2) SORT BY 1 DESC", tab);
Nach dem Auslesen der Werte (_online.._value und _online.._stime) im angegebenen Zeitbereich werden diese absteigend nach der ersten Angabe zwischen dem SELECT und FROM sortiert (hier: _online.._value).
dpQuery("SELECT ALERT '_alert_hdl.._value', '_alert_hdl.._text' FROM 'mld_float.**' TIMERANGE(\""+start+"\",\""+stop+"\", 1, 2) SORT BY 2,1", tab);
Hier wird zunächst nach dem Meldungstext sortiert, so dass alle Meldungen mit dem gleichen Text untereinander stehen. Sind bei der Meldebehandlung drei Bereiche definiert, ergeben sich also zwei Gruppen (Über- oder Unterschreitungen des Gutbereiches). Diese beiden Gruppen werden anschließend für sich nach der Größe geordnet.
Erläuterungen zu den TIMERANGE- oder Gruppenfunktionen bzw. zu den Variablen start und stop siehe im entsprechenden Kapitel dieses Abschnitts.
ORDER BY-Funktion
Siehe SORT BY-Funktion
(vom Aufruf und der internen Arbeitsweise identisch)
GROUP BY-Funktion - GROUP BY x
Diese Funktion fasst die Daten in Gruppen zusammen. Dazu ist es nötig, die Daten zunächst nach der gleichen Spalte zu sortieren (siehe SORT BY).
Die Verwendung der GROUP BY-Funktion macht oft nur in Verbindung mit einer Gruppenfunktion Sinn, da als Ergebnis des Gruppierens eine Zeile zurückgegeben wird. (Ausnahme z.B. die Summe aller aus einem SQL-Statement gewonnenen Werte). Ohne Gruppenfunktion enthält die Ergebnisspalte den Eintrag der letzten Zeile dieser Gruppe.
Diese Methode ist nicht für die Behandlung von Alarmwerten (ALERT) und in Verbindung mit Connect-Befehlen vorgesehen.
GROUP BY wird vom RDB-Manager nicht unterstützt.
Es stehen drei verschiedene Methoden zur Verfügung, nach denen gruppiert werden kann:
GROUP BY-Funktion nach Datenpunktelementen
mit einer Zahl von 1 bis n, wobei n die Anzahl der vor der FROM-Anweisung stehenden Datenpunktteile angibt.
dpSet("mld_float.:_original.._value", 28);
delay(1);
dpSet("mld_float.:_original.._value", 5);
delay(1);
dpSet("mld_float.:_original.._value", 28);
delay(1);
dpSet("mld_float.:_original.._value", 5);
delay(1);
dpSet("mld_float.:_original.._value", 28);
delay(1);
dpSet("mld_float.:_original.._value", 5);
...
...
dpQuery("SELECT '_online.._value' FROM 'mld_float.**' TIMERANGE(\"1998.05.26 00:00:00.000\",\"1998.05.26 23:59:59.000\",1,2) ORDER BY 1 GROUP BY 1", tab);
Hier werden alle auftretenden Werte der Größe nach geordnet und gruppiert. Pro Gruppe gibt es eine Ausgabezeile. So werden alle Werte, die mehrfach auftraten, zu je einer Gruppe zusammengefasst:
1 | 2 | |
---|---|---|
1 | Type:0 Sys:0 Dp:0 El:0 Conf:29 Det:0 Attr:0 |
:_online.._value Type:0 Sys:0 Dp:0 El:0 Conf:29 Det:0 Attr:131173 |
2 |
System1: mld_float. Type:0 Sys:1 Dp:1651 El:1 Conf:0 Det:0 Attr:0 |
5 |
3 |
System1: mld_float. Type:0 Sys:1 Dp:1651 El:1 Conf:0 Det: 0 Attr:0 |
28 |
GROUP BY-Funktion nach der Zeit
Hier können durch den Befehl SECS[x] die aufgetretenen Daten in Pakete zu x Sekunden zusammengestellt werden.
Mit der Angabe MONTH werden die ausgelesenen Daten nach dem Monat gruppiert. Hierzu ist als Sortierungsmerkmal "0" anzugeben.
dpQuery("SELECT 'MIN(_online.._value)' FROM 'mld_float.**' TIMERANGE(\"1998.05.26 00:00:00.000\", \"1998.05.26 23:59:59.000\",1,2) ORDER BY 0 GROUP BY SECS(60)", tab);
Die aufgetretenen Daten im angegebenen Zeitbereich werden nach der Zeit sortiert (ORDER BY 0) und in Gruppen zu 60 Sekunden zusammengefasst. Von diesen Werten wird jeweils das Minimum ermittelt.
GROUP BY-Funktion nach Datenpunktebenen
Wird als Gruppierungsmerkmal einer der nachfolgenden Schlüssel verwendet, werden für n verschiedene Vorkommen innerhalb des Schlüssels auch n Gruppen gebildet. Die Gruppierung nach einer dieser Merkmale setzt als Sortierungsmerkmal die Angabe "0" voraus.
Die Schlüsselworte für die Bestandteile der Datenpunkte und Typen sind:
_SYS, _DP, _EL and_ELC
Erläuterungen hierzu siehe "Schlüsselworte für die einzelnen Teile eines Datenpunktes".
dpQuery("SELECT 'COUNT(_online.._value)' FROM 'mld_*.**' TIMERANGE(\"1998.05.26 11:04:25.000\",\"1998.05.26 23:59:59.000\",1,2) GROUP BY _DP", tab); SORT BY 0
Als Ergebnis wird hier nach den Datenpunkten gruppiert (bei drei verschiedenen Datenpunkten ergeben sich entsprechend drei Gruppen). Ausgegeben wird die Anzahl der gespeicherten Werte pro Datenpunkt.
LAST-Funktion - LAST x
Mit diesem Befehl werden (höchstens) die letzten x Zeilen der Ergebnistabelle zurückgeliefert, ggf. weniger, wenn die Abfrage durch weitere Conditions eingeschränkt wird.
dpQuery("SELECT ALERT '_alert_hdl.._value', '_alert_hdl.._text' FROM 'mld_*.**' LAST 2", tab);
DebugN("Number n-1: ",tab[2][3]);
DebugN("Text n-1: ",tab[2][4]);
DebugN("Number n : ",tab[3][3]);
DebugN("Text n : ",tab[3][4]);
FIRST-Funktion - FIRST x
wie LAST, nur werden die ersten x Zeilen der Ergebnistabelle zurückgegeben.
dpQuery("SELECT ALERT '_alert_hdl.._value','_alert_hdl.._text' FROM 'mld_float.**' FIRST 2", tab);
DebugN("Number 1: ",tab[2][3]);
DebugN("Text 1: ",tab[2][4]);
DebugN("Number 2: ",tab[3][3]);
DebugN("Text 2: ",tab[3][4]);
NULL
Im Gegensatz zu "0" stellt NULL fest, ob z.B. der Wert eines vorhandenen Datenpunktattributes 0 beträgt.
Um die Existenz eines Attributes festzustellen, muss auf "0" abgefragt werden.
dpQuery("SELECT '_online.._value' FROM '*' WHERE '_alert_hdl.._type' != NULL", tab);
Diese Abfrage macht wenig Sinn: Als Abfrageergebnis werden alle in diesem Projekt vorhandenen Datenpunkte ausgegeben. Dies geschieht, weil das Typ-Attribut immer definiert ist.
dpQuery("SELECT '_online.._value' FROM '*' WHERE '_alert_hdl.._type' !=0", tab);
Bei dieser Konstellation werden alle Datenpunkte ausgegeben, die mit einer Meldebehandlung belegt sind.
Logische Vergleiche
Logische Vergleiche werden zur Auswahl einzelner Attribute verwendet. Als logische Vergleichsoperatoren stehen zur Verfügung:
Tabelle: Logische Vergleiche
Operator | Beschreibung |
---|---|
=,==, IS | ist gleich |
!=,<>, IS NOT | ist ungleich |
>, >= | größer (gleich) |
<,<= | kleiner gleich |
&&, AND | logisch und |
||, OR | logisch oder |
IN (a1,a2,...) | Auswahlliste, siehe Beispiel unten |
LIKE string | Stringvergleich mit Wildcards, siehe Beispiel unten |
NOT LIKE string | Negation eines Stringvergleichs mit Wildcards, siehe Beispiel unten |
IN-Vergleich
dpQuery("SELECT '_online.._value' FROM '*' WHERE _DP IN (\"mld_float\",\"mld_bit\")", tab);
Die Online-Werte aller Datenpunkte werden ausgelesen und später danach gefiltert, ob die Ebene DP mld_float oder mld_bit heißt.
LIKE-Vergleich
Es findet ein Textvergleich statt, der nur mit mld beginnende Datenpunkte zulässt. Als Ergebnis wird je eine Zeile pro Datenpunkt mit dem Onlinewert ausgegeben.
dpQuery("SELECT '_online.._value' FROM '*' WHERE _DP LIKE \"mld*\"", tab);
NOT LIKE-Vergleich
dpQuery("SELECT ALL '_online.._value' FROM 'ExampleDPArg*.' WHERE _DP NOT LIKE "ExampleDPArg3", tab);
Es werden alle Datenpunkte ExampleDPArg* außer ExampleDPArg3 geliefert.
Gruppenfunktionen
Mit den folgenden Gruppenfunktionen, die auch von Standard-SQL bereitgestellt werden, kann eine Gruppe von Ergebniszeilen ausgewertet werden.
Wird eine Gruppenfunktion ohne GROUP BY-Klausel angegeben, so wird die gesamte Ergebnistabelle in eine Zeile zusammengefasst. Das Argument der Gruppenfunktion muss eine gültige Angabe vor dem FROM-Statement sein. Das Wildcard "*" wird - im Gegensatz zu Standard-SQL - hier nicht unterstützt.
Wenn eine GROUP BY-Klausel angegeben wird, so muss die Tabelle zunächst nach den gleichen Spalten (in der gleichen Reihenfolge) sortiert worden sein: Mit SORT BY 1,3,4: GROUP BY 1,4 (aber nicht GROUP BY 4,1).
Dem Gruppenbefehl folgt in Klammern das Statement vor dem FROM. Die Gruppenbefehle sind folgend aufgeführt:
COUNT - COUNT(x)
Diese Funktion liefert die Anzahl der Zeilen einer Gruppe.
dpQuery("SELECT 'COUNT(_online.._value)' FROM 'mld_float.**' TIMERANGE(\"1998.05.26 00:00:00.000\", \"1998.05.26 23:59:59.000\",1,2)", tab);
MIN - MIN(x)
Mit der Funktion MIN wird das Minimum innerhalb einer Gruppe ausgegeben.
dpQuery("SELECT 'MIN(_online.._value)' FROM 'mld_float.**' TIMERANGE(\"1998.05.26 00:00:00.000\", \"1998.05.26 23:59:59.000\",1,2)", tab);
MAX - MAX(x)
Die Option MAX gibt als Ergebnis das Maximum der Gruppe zurück.
dpQuery("SELECT 'MAX(_online.._value)' FROM 'mld_float.**' TIMERANGE(\"1998.05.26 00:00:00.000\", \"1998.05.26 23:59:59.000\",1,2)", tab);
AVG - AVG(x)
Diese Gruppenfunktion liefert den Durchschnittswert (arithmetisches Mittel) der betreffenden Werte.
dpQuery("SELECT 'AVG(_online.._value)' FROM 'mld_float.**' TIMERANGE(\"1998.05.26 00:00:00.000\", \"1998.05.26 23:59:59.000\",1,2), tab);
SUM - SUM(x)
Mit der Funktion SUM wird die Gruppensumme gebildet.
dpQuery("SELECT 'SUM(_online.._value)' FROM 'mld_float.**' TIMERANGE(\"1998.05.26 00:00:00.000\", \"1998.05.26 23:59:59.000\",1,2)", tab);
Das Ergebnis für alle Gruppenfunktionen kann mit folgendem Statement abgefragt werden:
dpQuery("SELECT 'MAX(_original.._value)', 'MIN(_original.._value)', 'SUM(_original.._value)','AVG(_original.._value)' FROM 'mld_float.**' TIMERANGE(\""+start+"\",\""+stop+"\",1,2)", tab);
Erläuterungen zu der TIMERANGE, zu den Variablen start und stop siehe im entsprechenden Kapitel dieses Abschnitts.