Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

abfrage mit jons aus dern selben tabelle und einer 2ten

Dieses Thema im Forum "PostgreSQL" wurde erstellt von chrisi303, 24 Februar 2016.

  1. chrisi303

    chrisi303 Aktiver Benutzer

    Hallo ich habe Probleme mit einer abfrage.
    das Ergebnis soll so aussehen
    Keyword | Position | Vorwoche | Suchvol. | cpc /* die Vorwoche als Postion oder als Veränderung +- Platz */

    der code mit Beispiel insert
    Code:
    CREATE TABLE "serps_from" (
        "id" INTEGER NOT NULL DEFAULT E'',
        "kw" VARCHAR(10) NULL DEFAULT E'0',
        "keyword" VARCHAR(200) NULL DEFAULT E'0',
        "deeplink" TEXT NULL DEFAULT E'0',
        "host" TEXT NULL DEFAULT E'0',
        "position" INTEGER NULL DEFAULT E'0',
        PRIMARY KEY ("id")
    )
    ;
    COMMENT ON COLUMN "serps_from"."id" IS E'';
    COMMENT ON COLUMN "serps_from"."kw" IS E'';
    COMMENT ON COLUMN "serps_from"."keyword" IS E'';
    COMMENT ON COLUMN "serps_from"."deeplink" IS E'';
    COMMENT ON COLUMN "serps_from"."host" IS E'';
    COMMENT ON COLUMN "serps_from"."position" IS E'';
    
    
    CREATE TABLE "keyword" (
        "id" INTEGER NULL DEFAULT NULL,
        "keyword" VARCHAR(60) NULL DEFAULT NULL,
        "cpc" VARCHAR(20) NULL DEFAULT NULL,
        "Suchvol" INTEGER NULL DEFAULT NULL,
        "vol" INTEGER NULL DEFAULT NULL
    )
    ;
    COMMENT ON COLUMN "keyword"."id" IS E'';
    COMMENT ON COLUMN "keyword"."keyword" IS E'';
    COMMENT ON COLUMN "keyword"."cpc" IS E'';
    COMMENT ON COLUMN "keyword"."Suchvol" IS E'';
    COMMENT ON COLUMN "keyword"."vol" IS E'';
    
    INSERT INTO "keyword" ("id", "keyword", "cpc", "Suchvol", "vol") VALUES (36, E'fliesenpark.de', E'', NULL, 30);
    
    INSERT INTO "serps_from" ("id", "kw", "keyword", "deeplink", "host", "position") VALUES (3260995, E'16_08', E'fliesenpark.de', E'http://www.fliesenpark.de/', E'www.fliesenpark.de', 1);
    INSERT INTO "serps_from" ("id", "kw", "keyword", "deeplink", "host", "position") VALUES (3260996, E'16_08', E'fliesenpark.de', E'http://www.fliesenpark.de/bad-fliesen.html', E'www.fliesenpark.de', 2);
    INSERT INTO "serps_from" ("id", "kw", "keyword", "deeplink", "host", "position") VALUES (3260997, E'16_08', E'fliesenpark.de', E'http://www.fliesenpark.de/holzoptik-fliese.html', E'www.fliesenpark.de', 3);
    INSERT INTO "serps_from" ("id", "kw", "keyword", "deeplink", "host", "position") VALUES (3260998, E'16_08', E'fliesenpark.de', E'http://www.fliesenpark.de/villeroy-boch.html', E'www.fliesenpark.de', 4);
    INSERT INTO "serps_from" ("id", "kw", "keyword", "deeplink", "host", "position") VALUES (3260999, E'16_08', E'fliesenpark.de', E'http://www.fliesenpark.de/wandfliesen-fliesen.html', E'www.fliesenpark.de', 5);
    INSERT INTO "serps_from" ("id", "kw", "keyword", "deeplink", "host", "position") VALUES (3261000, E'16_08', E'fliesenpark.de', E'http://www.fliesenpark.de/bodenfliesen-fliesen.html', E'www.fliesenpark.de', 6);
    INSERT INTO "serps_from" ("id", "kw", "keyword", "deeplink", "host", "position") VALUES (3261001, E'16_08', E'fliesenpark.de', E'http://www.fliesenpark.de/agrob-buchtal.html', E'www.fliesenpark.de', 7);
    INSERT INTO "serps_from" ("id", "kw", "keyword", "deeplink", "host", "position") VALUES (3261002, E'16_08', E'fliesenpark.de', E'http://www.fliesenpark.de/marazzi.html', E'www.fliesenpark.de', 8);
    INSERT INTO "serps_from" ("id", "kw", "keyword", "deeplink", "host", "position") VALUES (3261003, E'16_08', E'fliesenpark.de', E'http://www.fliesenpark.de/ariostea.html', E'www.fliesenpark.de', 9);
    INSERT INTO "serps_from" ("id", "kw", "keyword", "deeplink", "host", "position") VALUES (3261004, E'16_08', E'fliesenpark.de', E'http://www.fliesenpark.de/jasba.html', E'www.fliesenpark.de', 10);
    INSERT INTO "serps_from" ("id", "kw", "keyword", "deeplink", "host", "position") VALUES (3261005, E'16_08', E'fliesenpark.de', E'http://www.fliesenpark.de/mosaikfliesen.html', E'www.fliesenpark.de', 11);
    
    INSERT INTO "serps_from" ("id", "kw", "keyword", "deeplink", "host", "position") VALUES (2834146, E'16_07', E'fliesenpark.de', E'http://www.fliesenpark.de/', E'www.fliesenpark.de', 1);
    INSERT INTO "serps_from" ("id", "kw", "keyword", "deeplink", "host", "position") VALUES (2834147, E'16_07', E'fliesenpark.de', E'http://www.fliesenpark.de/bad-fliesen.html', E'www.fliesenpark.de', 2);
    INSERT INTO "serps_from" ("id", "kw", "keyword", "deeplink", "host", "position") VALUES (2834148, E'16_07', E'fliesenpark.de', E'http://www.fliesenpark.de/holzoptik-fliese.html', E'www.fliesenpark.de', 3);
    INSERT INTO "serps_from" ("id", "kw", "keyword", "deeplink", "host", "position") VALUES (2834149, E'16_07', E'fliesenpark.de', E'http://www.fliesenpark.de/villeroy-boch.html', E'www.fliesenpark.de', 4);
    INSERT INTO "serps_from" ("id", "kw", "keyword", "deeplink", "host", "position") VALUES (2834150, E'16_07', E'fliesenpark.de', E'http://www.fliesenpark.de/wandfliesen-fliesen.html', E'www.fliesenpark.de', 5);
    INSERT INTO "serps_from" ("id", "kw", "keyword", "deeplink", "host", "position") VALUES (2834151, E'16_07', E'fliesenpark.de', E'http://www.fliesenpark.de/bodenfliesen-fliesen.html', E'www.fliesenpark.de', 6);
    INSERT INTO "serps_from" ("id", "kw", "keyword", "deeplink", "host", "position") VALUES (2834152, E'16_07', E'fliesenpark.de', E'http://www.fliesenpark.de/agrob-buchtal.html', E'www.fliesenpark.de', 7);
    INSERT INTO "serps_from" ("id", "kw", "keyword", "deeplink", "host", "position") VALUES (2834153, E'16_07', E'fliesenpark.de', E'http://www.fliesenpark.de/jasba.html', E'www.fliesenpark.de', 8);
    INSERT INTO "serps_from" ("id", "kw", "keyword", "deeplink", "host", "position") VALUES (2834154, E'16_07', E'fliesenpark.de', E'http://www.fliesenpark.de/ariostea.html', E'www.fliesenpark.de', 9);
    INSERT INTO "serps_from" ("id", "kw", "keyword", "deeplink", "host", "position") VALUES (2834155, E'16_07', E'fliesenpark.de', E'http://www.fliesenpark.de/marazzi.html', E'www.fliesenpark.de', 10);
    INSERT INTO "serps_from" ("id", "kw", "keyword", "deeplink", "host", "position") VALUES (2834156, E'16_07', E'fliesenpark.de', E'http://www.fliesenpark.de/mosaikfliesen.html', E'www.fliesenpark.de', 11);
    
     
  2. chrisi303

    chrisi303 Aktiver Benutzer

    hier die abfrage leider stimmen die Positionen nicht
    Code:
    select serps_from.position as "diese Woche", serps_from.keyword,  keyword.cpc, keyword.vol , lastweek.position as Vorwoche
    from serps_from
    INNER JOIN
                 (select serps_from.position, serps_from.keyword
                    from serps_from
                    Where host = 'www.fliesenpark.de'
                    and kw = '16_07'
                ) as lastweek
    on lastweek.keyword = serps_from.keyword
    INNER JOIN keyword
    on serps_from.keyword = keyword.keyword
    Where host = 'www.fliesenpark.de'
    and kw = '16_08'
    order by serps_from.position asc
    diese Woche | keyword | cpc | vol | vorwoche
    1| Engers Metro| 0| 1
    1 | casa legno naturale| | 20| 2
    1| italienische fliesen online| | 0,58 | 70 1
    1| fliesenpark.de| | 30| 11
    1 | fliesenpark.de| | 30| 10
    1| fliesenpark.de| | 30| 9
    1| fliesenpark.de| | 30| 8
    1| fliesenpark.de| | 30| 7
    1| fliesenpark.de| | 30| 6
    1| fliesenpark.de| | 30| 5
    1| fliesenpark.de| | 30| 4
    1| fliesenpark.de| | 30| 3
    1| fliesenpark.de| | 30| 2
    1| fliesenpark.de| | 30| 1
     
  3. akretschmer

    akretschmer Datenbank-Guru

    ich schaue es mir nach Feierabend mal an, derzeit geht grad nix ...
     
  4. akretschmer

    akretschmer Datenbank-Guru

    Deine Daten passen nicht zum gezeigten Resultat, daher kann ich nur raten, Du suchst sowas wie:

    Code:
    test=*# select * from serps_from ;
      id  |  kw  |  keyword  |  deeplink  |  host  | position
    ---------+-------+----------------+-----------------------------------------------------+--------------------+----------
     3260995 | 16_08 | fliesenpark.de | http://www.fliesenpark.de/  | www.fliesenpark.de |  1
     3260996 | 16_08 | fliesenpark.de | http://www.fliesenpark.de/bad-fliesen.html  | www.fliesenpark.de |  2
     3260997 | 16_08 | fliesenpark.de | http://www.fliesenpark.de/holzoptik-fliese.html  | www.fliesenpark.de |  3
     3260998 | 16_08 | fliesenpark.de | http://www.fliesenpark.de/villeroy-boch.html  | www.fliesenpark.de |  4
     3260999 | 16_08 | fliesenpark.de | http://www.fliesenpark.de/wandfliesen-fliesen.html  | www.fliesenpark.de |  5
     3261000 | 16_08 | fliesenpark.de | http://www.fliesenpark.de/bodenfliesen-fliesen.html | www.fliesenpark.de |  6
     3261001 | 16_08 | fliesenpark.de | http://www.fliesenpark.de/agrob-buchtal.html  | www.fliesenpark.de |  7
     3261002 | 16_08 | fliesenpark.de | http://www.fliesenpark.de/marazzi.html  | www.fliesenpark.de |  8
     3261003 | 16_08 | fliesenpark.de | http://www.fliesenpark.de/ariostea.html  | www.fliesenpark.de |  9
     3261004 | 16_08 | fliesenpark.de | http://www.fliesenpark.de/jasba.html  | www.fliesenpark.de |  10
     3261005 | 16_08 | fliesenpark.de | http://www.fliesenpark.de/mosaikfliesen.html  | www.fliesenpark.de |  11
     2834146 | 16_07 | fliesenpark.de | http://www.fliesenpark.de/  | www.fliesenpark.de |  1
     2834147 | 16_07 | fliesenpark.de | http://www.fliesenpark.de/bad-fliesen.html  | www.fliesenpark.de |  2
     2834148 | 16_07 | fliesenpark.de | http://www.fliesenpark.de/holzoptik-fliese.html  | www.fliesenpark.de |  3
     2834149 | 16_07 | fliesenpark.de | http://www.fliesenpark.de/villeroy-boch.html  | www.fliesenpark.de |  4
     2834150 | 16_07 | fliesenpark.de | http://www.fliesenpark.de/wandfliesen-fliesen.html  | www.fliesenpark.de |  5
     2834151 | 16_07 | fliesenpark.de | http://www.fliesenpark.de/bodenfliesen-fliesen.html | www.fliesenpark.de |  6
     2834152 | 16_07 | fliesenpark.de | http://www.fliesenpark.de/agrob-buchtal.html  | www.fliesenpark.de |  7
     2834153 | 16_07 | fliesenpark.de | http://www.fliesenpark.de/jasba.html  | www.fliesenpark.de |  8
     2834154 | 16_07 | fliesenpark.de | http://www.fliesenpark.de/ariostea.html  | www.fliesenpark.de |  9
     2834155 | 16_07 | fliesenpark.de | http://www.fliesenpark.de/marazzi.html  | www.fliesenpark.de |  10
     2834156 | 16_07 | fliesenpark.de | http://www.fliesenpark.de/mosaikfliesen.html  | www.fliesenpark.de |  11
    (22 rows)
    
    test=*# select * from (select deeplink, position as diese_woche, lag(position) over (partition by deeplink order by kw) as last_week from serps_from ) bla where last_week is not null;
      deeplink  | diese_woche | last_week
    -----------------------------------------------------+-------------+-----------
     http://www.fliesenpark.de/  |  1 |  1
     http://www.fliesenpark.de/agrob-buchtal.html  |  7 |  7
     http://www.fliesenpark.de/ariostea.html  |  9 |  9
     http://www.fliesenpark.de/bad-fliesen.html  |  2 |  2
     http://www.fliesenpark.de/bodenfliesen-fliesen.html |  6 |  6
     http://www.fliesenpark.de/holzoptik-fliese.html  |  3 |  3
     http://www.fliesenpark.de/jasba.html  |  10 |  8
     http://www.fliesenpark.de/marazzi.html  |  8 |  10
     http://www.fliesenpark.de/mosaikfliesen.html  |  11 |  11
     http://www.fliesenpark.de/villeroy-boch.html  |  4 |  4
     http://www.fliesenpark.de/wandfliesen-fliesen.html  |  5 |  5
    (11 rows)
    
    
    Paßt das?
     
  5. chrisi303

    chrisi303 Aktiver Benutzer

    soory hatte mir den neuen thread auf nicht beobachten gestellt und die letzten paar tage war ich in einem anderen Projekt ziemlich eingespannt.
    habe mich jetzt den Vormittag mit den Windowsfunktionen beschäftige aber schlau werde ich nicht wirklich daraus :-(
    die Abfrage habe ich etwas veränderte, so wäre das Ergebnis okay
    Code:
    select * from
    (select serps_from.keyword, position as diese_woche, deeplink as url, host as webseite, kw,
    lag(position) over (partition by deeplink order by kw) as last_week from serps_from )  bla
    where webseite = 'www.fliesenpark.de' and kw = '16_10'
    and  keyword = 'fliesen' ;
    
    keyword;diese_woche;url;webseite;kw;last_week
    fliesen;3;http://www.fliesenpark.de/;www.fliesenpark.de;16_10;8


    Die Vorwochen Ergebnisse stimmen nicht (8).
    Wenn ich das Richtig verstanden habe mit partition werden die Ergebnisse Gruppiert nach "Host" und dann sortiert . Wir genau bekomme man aber da die Vorwoche raus?

    Kontrolle abfrage
    select * from serps_from where host = 'www.fliesenpark.de' and keyword = 'fliesen'

    id;kw;keyword;deeplink;host;position
    3723384;16_08;fliesen;http://www.fliesenpark.de/;www.fliesenpark.de;3
    4261641;16_09;fliesen;http://www.fliesenpark.de/;www.fliesenpark.de;5
    4798561;16_10;fliesen;http://www.fliesenpark.de/;www.fliesenpark.de;3
     
  6. akretschmer

    akretschmer Datenbank-Guru

    lag(vvv) liefert dir den Vorgänger (du kannst auch den x-ten Vorgänger holen), auf was sich dieser Vorgänger bezieht regelt dann tatsächlich das (partition by ... order by ...), daß muß nur korrekt alles gesetzt sein.

    Ich hab jetzt grad leider nicht die Zeit, mich in Dein Problem zu vertiefen, bin @work ... aber es gibt massig Doku dazu.
     
  7. chrisi303

    chrisi303 Aktiver Benutzer

    kein Problem, bin aber mit der Fenstermethode nicht weiter gekommen. Ich glaube das funktioniert nicht weil mehre host existieren und nicht explizit nur nach 1 host gesucht wird.
    Des weiteren dauert das sehr lang (+ 55,437 sec. network)
     
  8. akretschmer

    akretschmer Datenbank-Guru

    kannst Du ein vereinfachtes Beispiel zeigen? Vielleicht gleich mit passenden Create Table / Insert - Befehlen.
     
  9. chrisi303

    chrisi303 Aktiver Benutzer

    okay ich glaub ich habe es statt on habe ich using verwendet weil ich da 2 Verknüpfungen erstellen kann.

    Code:
    WITH L AS (
    select serps_from.position , serps_from.keyword, deeplink
    from serps_from
    Where host = 'www.fliesenpark.de'
    and kw = '16_09'
    )
    
    select serps_from.keyword, serps_from.position as "diese Woche",  l.position as Vorwoche, kw, host as webseite, deeplink
    from serps_from JOIN L
    using (keyword ,deeplink)
    
    where host = 'www.fliesenpark.de' and kw = '16_10'
    ORDER BY serps_from.position
    
    Dauer von 1 query: 0,000 sec. (+ 3,547 sec. network)
    Ergebnis passt
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden