Postgres Nächste/Vorherige Zeile SQL-Abfrage

Ich haben die folgenden Tabellen in einer PostgreSQL-9.1 Datenbank-aber die ideale Lösung sein sollte, DB-agnostischen, wenn möglich:

Tabelle: Benutzer 
|id|username| 
|1 |ein | 
|2 |zwei | 
|3 |drei | 

Tabelle: Elemente 
|id|userid|itemname|erstellt | 
|1 |1 |a |timestamp| 
|2 |1 |b |timestamp| 
|3 |1 |c |timestamp| 
|4 |2 |d |timestamp| 
|5 |2 |e |timestamp| 
|6 |2 |f |timestamp| 
|7 |3 |g |timestamp| 
|8 |3 |h |timestamp| 
|9 |3 |ich |timestamp|

Ich habe eine Abfrage (für eine Ansicht), welche das nächste und das Vorherige Element.id.

z.B.

Anzeigen: UserItems 
|id|userid|itemname|nextitemid|previtemid|erstellt | 
|1 |1 |a |2 |null |timestamp| 
|2 |1 |b|3 |1 |timestamp| 
|3 |1 |c|4 |2 |timestamp| 
|4 |2 |d|5 |3 |timestamp| 
|5 |2 |e|6 |4 |timestamp| 
|6 |2 |f|7 |5 |timestamp| 
|7 |3 |g|8 |6 |timestamp| 
|8 |3 |h|9 |7 |timestamp| 
|9 |3 |ich |null |8 |timestamp|

Ich kann dies mit der folgenden Abfrage:

SELECT
  DISTINCT i.id AS id,
  i.userid AS userid,
  i.itemname AS itemname,
  LEAD(i.id) OVER (ORDER BY i.created DESC) AS nextitemid,
  LAG(i.id) OVER (ORDER BY i.created DESC) AS previtemid,
  i.created AS created
FROM items i
  LEFT JOIN users u
  ON i.userid = u.id
ORDER BY i.created DESC;

Können Sie zur Lösung der folgenden Probleme:

1) gibt es eine Möglichkeit, die ids wrap d.h.

  • die NULL itemid in der letzten Zeile der nextitemid Spalte 1
  • die NULL itemid in der ersten Zeile der previtemid Spalte 9

2) gibt es eine leistungsfähige Weise zu der Gruppe der nächsten und vorherigen itemids von userid z.B.

Anmerkung: in diesem Beispiel wird die itemids für einen Benutzer der Reihe nach, dies ist nicht der Fall für die realen Daten, die itemids für jeden Benutzer verschachtelt werden.

Anzeigen: UserItems 
|id|userid|itemname|nextitemid|previtemid|nextuseritemid|prevuseritemid|erstellt | 
|1 |1 |ein|2 |9 |2 |3 |timestamp| 
|2 |1 |b|3 |1 |3 |1 |timestamp| 
|3 |1 |c|4 |2 |1 |2 |timestamp| 
|4 |2 |d|5 |3 |5 |6 |timestamp| 
|5 |2 |e|6 |4 |6 |4 |timestamp| 
|6 |2 |f|7 |5 |4 |5 |timestamp| 
|7 |3 |g|8 |6 |8 |9 |timestamp| 
|8 |3 |h|9 |7 |9 |7 |timestamp| 
|9 |3 |ich|1 |8 |7 |8 |timestamp|
InformationsquelleAutor davec | 2013-08-17
  1. 12

    Q1: FIRST_VALUE/LAST_VALUE

    Q2: PARTITION (als römische Pekar bereits vorgeschlagen)

    SIEHE FIDDLE HIER

    SELECT
  DISTINCT i.id AS id,
  i.userid AS userid,
  i.itemname AS itemname,
  COALESCE(LEAD(i.id)        OVER (ORDER BY i.created DESC)
          ,FIRST_VALUE(i.id) OVER (ORDER BY i.created DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) AS nextitemid,
  COALESCE(LAG(i.id)         OVER (ORDER BY i.created DESC)
          ,LAST_VALUE(i.id)  OVER (ORDER BY i.created DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) AS previtemid,
  COALESCE(LEAD(i.id)        OVER (PARTITION BY i.userid ORDER BY i.created DESC)
          ,FIRST_VALUE(i.id) OVER (PARTITION BY i.userid ORDER BY i.created DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) AS nextuseritemid,
  COALESCE(LAG(i.id)         OVER (PARTITION BY i.userid ORDER BY i.created DESC)
          ,LAST_VALUE(i.id)  OVER (PARTITION BY i.userid ORDER BY i.created DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) AS prevuseritemid,
  i.created AS created
FROM items i
  LEFT JOIN users u
  ON i.userid = u.id
ORDER BY i.created DESC;
    • +1 für first_value und last_value 🙂
    • Dass der trick funktioniert gut, vielen Dank Jungs. Sie können entfernen Sie die ungetestet Kommentar zu sqlfiddle.com/#!1/58d92/28
    InformationsquelleAutor dnoeth
  2. 8

    update ich habe vergessen first_value und last_value Funktionen in PostgreSQL, Dank dnoeth er erinnerte mich daran. Jedoch, seine Abfrage nicht funktioniert, weil last_value arbeitet mit Standard-Fenster RANGE BETWEEN UNBOUNDED VORHERGEHENDE UND AKTUELLE ZEILE und nicht die richtigen Ergebnisse zurück, so müssen Sie entweder ändern Sie Bereich innerhalb der over-Klausel, oder verwenden Sie first_value mit order by asc:

    select
    i.id as id,
    i.userid as userid,
    i.itemname as itemname,
    coalesce(
        lead(i.id) over(order by i.created desc),
        first_value(i.id) over(order by i.created desc)
    ) as nextitemid,
    coalesce(
        lag(i.id) over(order by i.created desc),
        first_value(i.id) over(order by i.created asc)
    ) as previtemid,
    coalesce(
        lead(i.id) over(partition by i.userid order by i.created desc),
        first_value(i.id) over(partition by i.userid order by i.created desc)
    ) as nextuseritemid,
    coalesce(
        lag(i.id) over(partition by i.userid order by i.created desc),
        first_value(i.id) over(partition by i.userid order by i.created asc)
    ) as prevuseritemid,
    i.created as created
from items as i
   left outer join users as u on u.id = i.userid
order by i.created desc

    sql fiddle-demo

    vorherigen version

    Ich glaube, Sie könnten dies tun:

    SELECT
  i.id AS id,
  i.userid AS userid,
  i.itemname AS itemname,
  coalesce(
      LEAD(i.id) OVER (ORDER BY i.created DESC),
      (select t.id from items as t order by t.created desc limit 1)
  ) AS nextitemid,
  coalesce(
      LAG(i.id) OVER (ORDER BY i.created DESC),
      (select t.id from items as t order by t.created asc limit 1)
  ) AS previtemid,
  coalesce(
      LEAD(i.id) OVER (partition by i.userid ORDER BY i.created DESC),
      (select t.id from items as t where t.userid = i.userid order by t.created desc limit 1)
  ) AS nextuseritemid,
  coalesce(
      LAG(i.id) OVER (partition by i.userid ORDER BY i.created DESC),
      (select t.id from items as t where t.userid = i.userid order by t.created asc limit 1)
  ) AS prevuseritemid,
  i.created AS created
FROM items i
  LEFT JOIN users u
  ON i.userid = u.id
ORDER BY i.created DESC;

    sql fiddle-demo

    • Yep, ich habe die ZEILEN. Aber anstatt das ändern der Sortierreihenfolge, die ich ändern würde die ZEILEN um ZEILEN ZWISCHEN UNBOUNDED vor-UND GRENZENLOS FOLGENDEN. Damit sollte der optimizer zu tun, alle OLAP-Funktionen mit dem gleichen PARTITION BY und ORDER BY in einem einzigen Schritt
    • yep, tun Sie es, und ich werde upvote du 🙂 ich wollte mal upvote, aber es funktioniert nicht, also müssen Sie es zuerst Bearbeiten 🙂
    • Okay, ich veränderte meine Antwort 🙂
    • gut downvoter warum -1?
    InformationsquelleAutor Roman Pekar
Schreibe einen Kommentar