Wie kann man `SELECT` und fehlende Zeilen aus vorherigen Werten herstellen?

Question

Ich habe das folgende (vereinfachte) Ergebnis aus SELECT * FROM table ORDER BY tick,refid :

tick refid value
----------------
1    1     11
1    2     22
1    3     33
2    1     1111
2    3     3333
3    3     333333

Beachten Sie die "fehlenden" Zeilen für refid 1 (Tick 3) und refid 2 (Ticks 2 und 3)

Wenn möglich, wie kann ich eine Abfrage erstellen, um diese fehlenden Zeilen unter Verwendung der neuesten vorherigen value dafür refid ? "Jüngster" ist der Wert für die Zeile mit der gleichen Referenznummer wie die fehlende Zeile und dem größten Tick, so dass der Tick kleiner ist als der Tick für die fehlende Zeile. z. B.

tick refid value
----------------
1    1     11
1    2     22
1    3     33
2    1     1111
2    2     22
2    3     3333
3    1     1111
3    2     22
3    3     333333

Zusätzliche Bedingungen:

• Alle refids haben Werte bei tick=1.
• Es kann viele "fehlende" Häkchen für ein Refid in der Folge geben (wie oben für Refid 2).
• Es gibt viele Erstattungen, und es ist nicht bekannt, welche davon wo spärliche Daten haben werden.
• Es wird viele Ticks jenseits von 3 geben, aber alle nacheinander. Im korrekten Ergebnis wird jedes refid ein Ergebnis für jeden Tick haben.
• Fehlende Zeilen sind nicht im Voraus bekannt - dies wird mit mehreren Datenbanken durchgeführt, die alle die gleiche Struktur und unterschiedliche "fehlende" Zeilen haben.

Ich verwende MySQL und kann die Datenbank gerade nicht ändern. Es steht Ihnen frei, die Antwort in einem anderen Dialekt zu posten, um die Diskussion zu erleichtern, aber ich werde eine Antwort im MySQL-Dialekt anderen vorziehen.

Ja, ich weiß, dass dies im Code möglich ist, den ich implementiert habe. Ich bin nur neugierig, ob es mit SQL gemacht werden kann.

Answer 1

Was value zurückgegeben werden sollte, wenn eine bestimmte Zecken-Refid-Kombination nicht existiert? In dieser Lösung habe ich einfach den niedrigsten Wert für die gegebene refid zurückgegeben.

Revision

Ich habe die Logik aktualisiert, um zu bestimmen, welcher Wert im Falle einer Null zu verwenden ist. Es ist zu beachten, dass ich davon ausgehe, dass ticks+refid in der Tabelle eindeutig ist.

Select Ticks.tick
    , Refs.refid
    , Case
        When Table.value Is Null 
            Then    (
                    Select T2.value
                    From Table As T2
                    Where T2.refid = Refs.refId
                        And T2.tick =  (
                                        Select Max(T1.tick)
                                        From Table As T1
                                        Where T1.tick < Ticks.tick
                                            And T1.refid = T2.refid
                                        )
                    )
        Else Table.value
        End As value
From    (
        Select Distinct refid
        From Table
        ) As Refs
    Cross Join  (
                Select Distinct tick
                From Table
                ) As Ticks
    Left Join Table
        On Table.tick = Ticks.tick
            And Table.refid = Refs.refid

Answer 2

Wenn Sie im Voraus wissen, wie Ihre "tick"- und "refid"-Werte lauten,

1. Erstellen Sie eine Hilfstabelle, die alle möglichen Tick- und Refid-Werte enthält.
2. Dann linke Verknüpfung von der Hilfstabelle auf tick und refid zu Ihrer Datentabelle.

Wenn Sie nicht genau wissen, wie Ihre "tick"- und "refid"-Werte lauten, können Sie vielleicht trotzdem diese Methode verwenden, aber anstelle einer statischen Hilfstabelle müsste diese dynamisch erzeugt werden.

Answer 3

Um die Liste der einzufügenden Paare (tick, refid) zu erhalten, muss eine ganze Liste erstellt werden:

     SELECT a.tick, b.refid
       FROM ( SELECT DISTINCT tick  FROM t) a
 CROSS JOIN ( SELECT DISTINCT refid FROM t) b

Ziehen Sie nun von dieser Abfrage die vorhandenen ab:

     SELECT a.tick tick, b.refid refid
       FROM ( SELECT DISTINCT tick  FROM t) a
 CROSS JOIN ( SELECT DISTINCT refid FROM t) b
 MINUS
     SELECT DISTINCT tick, refid FROM t

Jetzt können Sie mit t verknüpfen, um die endgültige Abfrage zu erhalten (beachten Sie, dass ich inner join + left join verwende, um das vorherige Ergebnis zu erhalten, aber Sie können es anpassen):

INSERT INTO t(tick, refid, value)
SELECT c.tick, c.refid, t1.value
  FROM (        SELECT a.tick tick, b.refid refid
                  FROM ( SELECT DISTINCT tick  FROM t) a
            CROSS JOIN ( SELECT DISTINCT refid FROM t) b
            MINUS
                SELECT DISTINCT tick, refid FROM t
       ) c
 INNER JOIN t t1 ON t1.refid = c.refid and t1.tick < c.tick
  LEFT JOIN t t2 ON t2.refid = c.refid AND t1.tick < t2.tick AND t2.tick < c.tick
 WHERE t2.tick IS NULL

Answer 4

Die folgende Tabelle hat für meinen Geschmack zu viele Subselects, aber sie erzeugt das gewünschte Ergebnis in MySQL, solange jedes Tick und jede Refid mindestens einmal separat in der Tabelle vorkommt.

Beginnen Sie mit einer Abfrage, die jedes Paar von tick und refid erzeugt. Im Folgenden wird die Tabelle verwendet, um die Paare zu generieren. Wenn also ein Tick nie in der zugrunde liegenden Tabelle auftaucht, wird er auch in den generierten Paaren fehlen. Das Gleiche gilt für refid, obwohl die Einschränkung "Alle refids haben Werte bei tick=1" sicherstellen sollte, dass letzteres nicht passiert.

SELECT tick, refid FROM
  (SELECT refid FROM chadwick WHERE tick=1) AS r
  JOIN 
  (SELECT DISTINCT tick FROM chadwick) AS t

Erzeugen Sie auf diese Weise jedes fehlende Tick- und Refid-Paar zusammen mit dem größten Tick, der in der Tabelle existiert, indem Sie Refid und θ gleichsetzen. _≥ -Anschluss an die Zecke. Gruppierung nach dem erzeugten Tick, refid, da nur eine Zeile für jedes Paar gewünscht ist. Der Schlüssel zum Herausfiltern vorhandener tick, refid-Paare ist die HAVING Klausel. Streng genommen können Sie die Klausel weglassen HAVING ; die daraus resultierende Abfrage gibt vorhandene Zeilen mit ihren vorhandenen Werten zurück.

SELECT tr.tick, tr.refid, MAX(c.tick) AS ctick
  FROM 
      (SELECT tick, refid FROM
        (SELECT refid FROM chadwick WHERE tick=1) AS r
        JOIN 
        (SELECT DISTINCT tick FROM chadwick) AS t
      ) AS tr
  JOIN chadwick AS c ON tr.tick >= c.tick AND tr.refid=c.refid
  GROUP BY tr.tick, tr.refid
  HAVING tr.tick > MAX(c.tick)

Ein letzter Select aus der obigen Tabelle als Sub-Select, verbunden mit der ursprünglichen Tabelle, um den Wert für den angegebenen ctick zu erhalten, gibt die neuen Zeilen für die Tabelle zurück.

INSERT INTO chadwick
SELECT missing.tick, missing.refid, c.value
  FROM (SELECT tr.tick, tr.refid, MAX(c.tick) AS ctick
    FROM 
      (SELECT tick, refid FROM
        (SELECT refid FROM chadwick WHERE tick=1) AS r
        JOIN 
        (SELECT DISTINCT tick FROM chadwick) AS t
      ) AS tr
    JOIN chadwick AS c ON tr.tick >= c.tick AND tr.refid=c.refid
    GROUP BY tr.tick, tr.refid
  ) AS missing
  JOIN chadwick AS c ON missing.ctick = c.tick AND missing.refid=c.refid
;

Leistung auf dem Mustertisch, zusammen mit (tick, refid) y (refid, tick) Indizes:

+----+-------------+------------+-------+-------------------+----------+---------+----------+------+---------------------------------+
| id | select\_type | table      | type  | possible\_keys     | key      | key\_len | ref      | rows | Extra                           |
+----+-------------+------------+-------+-------------------+----------+---------+----------+------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL              | NULL     | NULL    | NULL     |    3 |                                 |
|  1 | PRIMARY     | c          | ALL   | tick\_ref,ref\_tick | NULL     | NULL    | NULL     |    6 | Using where; Using join buffer  |
|  2 | DERIVED     | <derived3> | ALL   | NULL              | NULL     | NULL    | NULL     |    9 | Using temporary; Using filesort |
|  2 | DERIVED     | c          | ref   | tick\_ref,ref\_tick | ref\_tick | 5       | tr.refid |    1 | Using where; Using index        |
|  3 | DERIVED     | <derived4> | ALL   | NULL              | NULL     | NULL    | NULL     |    3 |                                 |
|  3 | DERIVED     | <derived5> | ALL   | NULL              | NULL     | NULL    | NULL     |    3 | Using join buffer               |
|  5 | DERIVED     | chadwick   | index | NULL              | tick\_ref | 10      | NULL     |    6 | Using index                     |
|  4 | DERIVED     | chadwick   | ref   | tick\_ref          | tick\_ref | 5       |          |    2 | Using where; Using index        |
+----+-------------+------------+-------+-------------------+----------+---------+----------+------+---------------------------------+

Wie ich schon sagte, zu viele Unterauswahlen. Eine temporäre Tabelle kann Abhilfe schaffen.

Zur Überprüfung auf fehlende Zecken:

SELECT clo.tick+1 AS missing_tick
  FROM chadwick AS chi
    RIGHT JOIN chadwick AS clo ON chi.tick = clo.tick+1
  WHERE chi.tick IS NULL;

Dies führt zu mindestens einer Zeile, deren Tick gleich 1 + dem größten Tick in der Tabelle ist. Der größte Wert in diesem Ergebnis kann also ignoriert werden.