abfrage mit jons aus dern selben tabelle und einer 2ten

chrisi303

chrisi303

Aktiver Benutzer
Beiträge
32
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);
 
Werbung:
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
 
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?
 
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
 
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.
 
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)
 
Werbung:
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
 

Ähnliche Themen

OlafNagel
stored procedure, select innerhalb bringt kein Ergebnis
2
Antworten
16
Aufrufe
1K
OlafNagel
OlafNagel
H
y2k38-Problem und utc_time
Antworten
7
Aufrufe
306
Haksi
H
S
Beste variante um 1-2k Datensätze zu Updaten
Antworten
5
Aufrufe
1K
akretschmer
akretschmer
S
Abfrage funktioniert nicht
Antworten
7
Aufrufe
2K
Sporty1975
S
F
Alle aus Tabelle A bis auf die, die NULL als Wert haben
Antworten
1
Aufrufe
2K
dabadepdu
dabadepdu
Zurück
Oben