Die LisRT-Ergebnisdatenbank

Tags:

  6 Minuten Lesezeit  

Die LisRT-Ergebnisdatenbank unterstützt folgende Datenbanksysteme:

  • SQLite (nicht für den Produktivbetrieb geeignet)

  • Microsoft SQL Server

  • PostgreSQL

  • MariaDB

  • MySQL

typedata
Abbildung 1. Aufbau der Typdaten (gelb) und der Ergebnis-Struktur (grün) in der Datenbank

Die Spalte Classifiction in den Ergebnistabellen ist wie folgt zu interpretieren:

  • 0 = NotMeasured

  • 1 = Passed

  • 2 = Failed

  • 3 = NotEvaluated

  • 4 = Error

SQL-Beispiele für die LisRT-Ergebnisdatenbank

Hier finden Sie typische SQL-Abfragen für die LisRT-Ergebnisdatenbank.

Alle Ergebnisse eines Werkstücks abfragen

Ersetzen Sie workpiece1234 durch die tatsächliche ID des Werkstücks, um die Ergebnisse des letzten Messzyklus dieses Werkstücks anzuzeigen.

  • MS SQL Server

  • PostgreSQL

  • MariaDB

  • SQLite

SELECT
    a.[Key] AS Attribute,
    a.ToleranceMin AS Min,
    r.Value,
    a.ToleranceMax AS Max
FROM
    Result r
    JOIN Attribute a ON r.AttributeId = a.ID
    JOIN MeasureProcess mp ON r.MeasureProcessID = mp.ID
    JOIN MeasureCycle mc ON mp.MeasureCycleId = mc.ID
    JOIN WorkPiece wp ON mc.WorkPieceId = wp.ID
WHERE
    mc.ID = (
        SELECT
            MAX(mc2.ID)
        FROM
            MeasureCycle mc2
            JOIN WorkPiece wp2 ON mc2.WorkPieceId = wp2.ID
        WHERE
            wp2.ItemIdentifier = 'workpiece1234'
    );
SELECT
    a.Key AS Attribute,
    a.ToleranceMin AS Min,
    r.Value,
    a.ToleranceMax AS Max
FROM
    Result r
    JOIN Attribute a ON r.AttributeId = a.ID
    JOIN MeasureProcess mp ON r.MeasureProcessID = mp.ID
    JOIN MeasureCycle mc ON mp.MeasureCycleId = mc.ID
    JOIN WorkPiece wp ON mc.WorkPieceId = wp.ID
WHERE
    mc.ID = (
        SELECT
            MAX(mc2.ID)
        FROM
            MeasureCycle mc2
            JOIN WorkPiece wp2 ON mc2.WorkPieceId = wp2.ID
        WHERE
            wp2.ItemIdentifier = 'workpiece1234'
    );
SELECT
    a.`Key` AS Attribute,
    a.ToleranceMin AS Min,
    r.Value,
    a.ToleranceMax AS Max
FROM
    Result r
    JOIN Attribute a ON r.AttributeId = a.ID
    JOIN MeasureProcess mp ON r.MeasureProcessID = mp.ID
    JOIN MeasureCycle mc ON mp.MeasureCycleId = mc.ID
    JOIN WorkPiece wp ON mc.WorkPieceId = wp.ID
WHERE
    mc.ID = (
        SELECT
            MAX(mc2.ID)
        FROM
            MeasureCycle mc2
            JOIN WorkPiece wp2 ON mc2.WorkPieceId = wp2.ID
        WHERE
            wp2.ItemIdentifier = 'workpiece1234'
    );
SELECT
    a.Key AS Attribute,
    a.ToleranceMin AS Min,
    r.Value,
    a.ToleranceMax AS Max
FROM
    Result r
    JOIN Attribute a ON r.AttributeId = a.ID
    JOIN MeasureProcess mp ON r.MeasureProcessID = mp.ID
    JOIN MeasureCycle mc ON mp.MeasureCycleId = mc.ID
    JOIN WorkPiece wp ON mc.WorkPieceId = wp.ID
WHERE
    mc.ID = (
        SELECT
            MAX(mc2.ID)
        FROM
            MeasureCycle mc2
            JOIN WorkPiece wp2 ON mc2.WorkPieceId = wp2.ID
        WHERE
            wp2.ItemIdentifier = 'workpiece1234'
    );

Werte eines Merkmals für einen Auftrag abfragen

Ersetzen Sie order1234 durch die Auftrags-ID und Voltage durch den gewünschten Merkmalnamen.

  • MS SQL Server

  • PostgreSQL

  • MariaDB

  • SQLite

SELECT
    a.ToleranceMin AS Min,
    r.Value,
    a.ToleranceMax AS Max
FROM
    Result r
    JOIN Attribute a ON r.AttributeId = a.ID
    JOIN MeasureProcess mp ON r.MeasureProcessID = mp.ID
    JOIN MeasureCycle mc ON mp.MeasureCycleId = mc.ID
    JOIN WorkPiece wp ON mc.WorkPieceId = wp.ID
    JOIN TypeData t ON mc.TypeDataId = t.ID
    JOIN [Order] o ON wp.OrderID = O.ID
WHERE
    a.[Key] = 'Voltage'
    AND o.Identifier = 'order1234
SELECT
    a.ToleranceMin AS Min,
    r.Value,
    a.ToleranceMax AS Max
FROM
    Result r
    JOIN Attribute a ON r.AttributeId = a.ID
    JOIN MeasureProcess mp ON r.MeasureProcessID = mp.ID
    JOIN MeasureCycle mc ON mp.MeasureCycleId = mc.ID
    JOIN WorkPiece wp ON mc.WorkPieceId = wp.ID
    JOIN TypeData t ON mc.TypeDataId = t.ID
    JOIN "Order" o ON wp.OrderID = o.ID
WHERE
    a."Key" = 'Voltage'
    AND o.Identifier = 'order1234'
SELECT
    a.ToleranceMin AS Min,
    r.Value,
    a.ToleranceMax AS Max
FROM
    Result r
    JOIN Attribute a ON r.AttributeId = a.ID
    JOIN MeasureProcess mp ON r.MeasureProcessID = mp.ID
    JOIN MeasureCycle mc ON mp.MeasureCycleId = mc.ID
    JOIN WorkPiece wp ON mc.WorkPieceId = wp.ID
    JOIN TypeData t ON mc.TypeDataId = t.ID
    JOIN `Order` o ON wp.OrderID = o.ID
WHERE
    a.`Key` = 'Voltage'
    AND o.Identifier = 'order1234'
SELECT
    a.ToleranceMin AS Min,
    r.Value,
    a.ToleranceMax AS Max
FROM
    Result r
    JOIN Attribute a ON r.AttributeId = a.ID
    JOIN MeasureProcess mp ON r.MeasureProcessID = mp.ID
    JOIN MeasureCycle mc ON mp.MeasureCycleId = mc.ID
    JOIN WorkPiece wp ON mc.WorkPieceId = wp.ID
    JOIN TypeData t ON mc.TypeDataId = t.ID
    JOIN "Order" o ON wp.OrderID = o.ID
WHERE
    a.Key = 'Voltage'
    AND o.Identifier = 'order1234'

Alle Gesamtergebnisse eines Auftrags abfragen

Geben Sie die gewünschte Auftrags-ID (z.B. order1234) an, um alle Gesamtergebnisse dieses Auftrags zu erhalten.

  • MS SQL Server

  • PostgreSQL

  • MariaDB

  • SQLite

SELECT
    wp.ItemIdentifier,
    mc.EndTimeStamp,
    mc.Classification,
    o.ID AS OrderID,
    wp.ID AS WorkPieceID
FROM
    MeasureCycle AS mc
    JOIN WorkPiece AS wp ON mc.WorkPieceId = wp.ID
    JOIN [Order] AS o ON o.ID = wp.OrderID
    JOIN (
        -- Subquery: Get the last MeasureCycle ID for each WorkPiece in the order
        SELECT
            wp2.ID AS WorkPieceID,
            MAX(mc2.ID) AS LastMeasureCycleID
        FROM
            MeasureCycle AS mc2
            JOIN WorkPiece AS wp2 ON mc2.WorkPieceId = wp2.ID
            JOIN [Order] AS o2 ON o2.ID = wp2.OrderID
        WHERE
            o2.Identifier = 'Order1234'
        GROUP BY
            wp2.ID
    ) AS max_mc ON mc.WorkPieceId = max_mc.WorkPieceID
    AND mc.ID = max_mc.LastMeasureCycleID
SELECT
    wp.itemidentifier,
    mc.endtimestamp,
    mc.classification,
    o.id AS orderid,
    wp.id AS workpieceid
FROM
    measurecycle mc
    INNER JOIN workpiece wp ON mc.workpieceid = wp.id
    INNER JOIN "Order" o ON o.id = wp.orderid
    INNER JOIN (
        -- Subquery: Get the last MeasureCycle ID for each WorkPiece in the order
        SELECT
            wp2.id AS workpieceid,
            MAX(mc2.id) AS lastmeasurecycleid
        FROM
            measurecycle mc2
            INNER JOIN workpiece wp2 ON mc2.workpieceid = wp2.id
            INNER JOIN "Order" o2 ON o2.id = wp2.orderid
        WHERE
            o2.identifier = 'Order1234'
        GROUP BY
            wp2.id
    ) max_mc ON mc.workpieceid = max_mc.workpieceid
    AND mc.id = max_mc.lastmeasurecycleid;
SELECT
    wp.ItemIdentifier,
    mc.EndTimeStamp,
    mc.Classification,
    o.ID AS OrderID,
    wp.ID AS WorkPieceID
FROM
    MeasureCycle mc
    INNER JOIN WorkPiece wp ON mc.WorkPieceId = wp.ID
    INNER JOIN `Order` o ON o.ID = wp.OrderID
    INNER JOIN (
        -- Subquery: Get the last MeasureCycle ID for each WorkPiece in the order
        SELECT
            wp2.ID AS WorkPieceID,
            MAX(mc2.ID) AS LastMeasureCycleID
        FROM
            MeasureCycle mc2
            INNER JOIN WorkPiece wp2 ON mc2.WorkPieceId = wp2.ID
            INNER JOIN `Order` o2 ON o2.ID = wp2.OrderID
        WHERE
            o2.Identifier = 'Order1234'
        GROUP BY
            wp2.ID
    ) max_mc ON mc.WorkPieceId = max_mc.WorkPieceID
    AND mc.ID = max_mc.LastMeasureCycleID;
SELECT
    wp.ItemIdentifier,
    mc.EndTimeStamp,
    mc.Classification,
    o.ID AS OrderID,
    wp.ID AS WorkPieceID
FROM
    MeasureCycle mc
    INNER JOIN WorkPiece wp ON mc.WorkPieceId = wp.ID
    INNER JOIN "Order" o ON o.ID = wp.OrderID
    INNER JOIN (
        -- Subquery: Get the last MeasureCycle ID for each WorkPiece in the order
        SELECT
            wp2.ID AS WorkPieceID,
            MAX(mc2.ID) AS LastMeasureCycleID
        FROM
            MeasureCycle mc2
            INNER JOIN WorkPiece wp2 ON mc2.WorkPieceId = wp2.ID
            INNER JOIN "Order" o2 ON o2.ID = wp2.OrderID
        WHERE
            o2.Identifier = 'Order1234'
        GROUP BY
            wp2.ID
    ) max_mc ON mc.WorkPieceId = max_mc.WorkPieceID
    AND mc.ID = max_mc.LastMeasureCycleID;

Prozesszeiten der letzten 100 Teile eines Typs

Es werden Minimum-, Maximum- und Durchschnittszeiten für die letzten 100 Werkstücke eines Typs abgefragt. Die Prozesszeiten eignen sich z.B. für die Anzeige in einem Dashboard. Ersetzen Sie TypeData1234 durch die gewünschte Typ-ID.

  • MS SQL Server

  • PostgreSQL

  • MariaDB

  • SQLite

SELECT
    AVG(ProcessTime) AS AvgProcessTime,
    MIN(ProcessTime) AS MinProcessTime,
    MAX(ProcessTime) AS MaxProcessTime
FROM (
    SELECT TOP 100
        DATEDIFF(millisecond, mp.StartTimeStamp, mp.EndTimeStamp) as ProcessTime
    FROM
        MeasureProcess mp
        JOIN MeasureCycle mc ON mc.ID = mp.ID
        JOIN TypeData t ON t.ID = mc.TypeDataId
    WHERE
        t.TypeIdentifier = 'Type_1234'
    ORDER BY mp.EndTimeStamp DESC
) AS Last100;
SELECT
    AVG(processtime) AS avg_processtime,
    MIN(processtime) AS min_processtime,
    MAX(processtime) AS max_processtime
FROM (
    SELECT
        EXTRACT(EPOCH FROM (mp.endtimestamp - mp.starttimestamp)) * 1000 AS processtime
    FROM
        measureprocess mp
        JOIN measurecycle mc ON mc.id = mp.id
        JOIN typedata t ON t.id = mc.typedataid
    WHERE
        t.typeidentifier = 'Type_1234'
    ORDER BY mp.endtimestamp DESC
    LIMIT 100
) AS last100;
SELECT
    AVG(ProcessTime) AS AvgProcessTime,
    MIN(ProcessTime) AS MinProcessTime,
    MAX(ProcessTime) AS MaxProcessTime
FROM (
    SELECT
        TIMESTAMPDIFF(MICROSECOND, mp.StartTimeStamp, mp.EndTimeStamp) / 1000 AS ProcessTime
    FROM
        MeasureProcess mp
        JOIN MeasureCycle mc ON mc.ID = mp.ID
        JOIN TypeData t ON t.ID = mc.TypeDataId
    WHERE
        t.TypeIdentifier = 'Type_1234'
    ORDER BY mp.EndTimeStamp DESC
    LIMIT 100
) AS Last100;
SELECT
    AVG(ProcessTime) AS AvgProcessTime,
    MIN(ProcessTime) AS MinProcessTime,
    MAX(ProcessTime) AS MaxProcessTime
FROM (
    SELECT
        CAST((julianday(mp.EndTimeStamp) - julianday(mp.StartTimeStamp)) * 86400000 AS INTEGER) AS ProcessTime
    FROM
        MeasureProcess mp
        JOIN MeasureCycle mc ON mc.ID = mp.ID
        JOIN TypeData t ON t.ID = mc.TypeDataId
    WHERE
        t.TypeIdentifier = 'Type_1234'
    ORDER BY mp.EndTimeStamp DESC
    LIMIT 100
) AS Last100;

Zuletzt geändert 16.12.2024