Ergebnis 1 bis 2 von 2

Thema: Problem mit Windowingfunktion mit Zeitstempel

  1. #1
    stede ist offline Neuer Benutzer
    Registriert seit
    02.08.2011
    Beiträge
    1

    Frage Problem mit Windowingfunktion mit Zeitstempel

    Hallo zusammen,

    ich habe ein Problem beim Benutzen einer Windowfunction. Ich möchte in einer Tabelle (Beispiel folgt) gerne wissen, ob es noch weitere Zeilen gibt, die die selbe any_fk_id haben und die vor einer ref_time liegen (zum Vergleich habe ich drei Zeiten angegeben). Es soll so sein, dass bei ref_time1 für die Zeilen mit any_fk_id=4 genau count_before_ref1=2 zurück geben, da ja beide Existierenden Zeilen noch vor der ref_time1 liegen. Bei ref_time2 sind wir genau zwischen den beiden Zeiten der beiden Zeilen mit any_fk_id=4 folglich liegt nur noch eine Zeile vor der ref_time2 und entsprechend sollte/ist count_before_ref2=1. So weit so gut. Nun ist ref_time3 größer als die beiden Zeitstempel der Zeilen mit any_k_id=4 und somit sollte count_before_ref3=0 sein. Wie ihr sehen könnt ist es das aber nicht.

    Die Frage ist nun warum ist das so und was für eine Lösung gibt es für dieses Anliegen?

    Hier noch der Beispielcode:
    Code:
    CREATE TABLE test_tab (
      id SERIAL  NOT NULL ,
      any_fk_id INTEGER NOT NULL ,
      release_time TIMESTAMP,
    PRIMARY KEY(id)  
    );
    
    INSERT INTO test_tab (any_fk_id,release_time) VALUES (4,TIMESTAMP '2011-08-02 10:10:00');
    INSERT INTO test_tab (any_fk_id,release_time) VALUES (4,TIMESTAMP '2011-08-02 10:20:00');
    INSERT INTO test_tab (any_fk_id,release_time) VALUES (5,TIMESTAMP '2011-08-02 11:10:00');
    INSERT INTO test_tab (any_fk_id,release_time) VALUES (5,TIMESTAMP '2011-08-02 11:20:00');
    
    with mts as ( select 
        TIMESTAMP '2011-08-02 10:05:00' as mt1
    ,    TIMESTAMP '2011-08-02 10:15:00' as mt2
    ,    TIMESTAMP '2011-08-02 10:25:00' as mt3  
    )
    select 
        id
    ,    any_fk_id
    ,    mts.mt1 as ref_time1 -- time1 to compare to
    ,    mts.mt2 as ref_time2 -- time2 to compare to
    ,    mts.mt3 as ref_time3 -- time3 to compare to
    ,    count(*) over (partition by any_fk_id, release_time>=mts.mt1) as count_before_ref1 -- expect 2 rows
    ,    count(*) over (partition by any_fk_id, release_time>=mts.mt2) as count_before_ref2 -- expect 1 row
    ,    count(*) over (partition by any_fk_id, release_time>=mts.mt3) as count_before_ref3 -- expect 0 rows
    from mts, test_tab
    ;
    
    DROP TABLE test_tab;
    Ich freue mich über jede Anregung/Hilfe!
    Danke im Voraus.

  2. #2
    akretschmer ist offline Super-Moderator
    Registriert seit
    29.10.2006
    Ort
    Dresden
    Beiträge
    5.472

    Standard

    Zitat Zitat von stede Beitrag anzeigen
    Hallo zusammen,

    ich habe ein Problem beim Benutzen einer Windowfunction. Ich möchte in einer Tabelle (Beispiel folgt) gerne wissen, ob es noch weitere Zeilen gibt, die die selbe any_fk_id haben und die vor einer ref_time liegen (zum Vergleich habe ich drei Zeiten angegeben). Es soll so sein, dass bei ref_time1 für die Zeilen mit any_fk_id=4 genau count_before_ref1=2 zurück geben, da ja beide Existierenden Zeilen noch vor der ref_time1 liegen. Bei ref_time2 sind wir genau zwischen den beiden Zeiten der beiden Zeilen mit any_fk_id=4 folglich liegt nur noch eine Zeile vor der ref_time2 und entsprechend sollte/ist count_before_ref2=1. So weit so gut. Nun ist ref_time3 größer als die beiden Zeitstempel der Zeilen mit any_k_id=4 und somit sollte count_before_ref3=0 sein. Wie ihr sehen könnt ist es das aber nicht.

    Die Frage ist nun warum ist das so und was für eine Lösung gibt es für dieses Anliegen?

    Hier noch der Beispielcode:
    Code:
    CREATE TABLE test_tab (
      id SERIAL  NOT NULL ,
      any_fk_id INTEGER NOT NULL ,
      release_time TIMESTAMP,
    PRIMARY KEY(id)  
    );
    
    INSERT INTO test_tab (any_fk_id,release_time) VALUES (4,TIMESTAMP '2011-08-02 10:10:00');
    INSERT INTO test_tab (any_fk_id,release_time) VALUES (4,TIMESTAMP '2011-08-02 10:20:00');
    INSERT INTO test_tab (any_fk_id,release_time) VALUES (5,TIMESTAMP '2011-08-02 11:10:00');
    INSERT INTO test_tab (any_fk_id,release_time) VALUES (5,TIMESTAMP '2011-08-02 11:20:00');
    
    with mts as ( select 
        TIMESTAMP '2011-08-02 10:05:00' as mt1
    ,    TIMESTAMP '2011-08-02 10:15:00' as mt2
    ,    TIMESTAMP '2011-08-02 10:25:00' as mt3  
    )
    select 
        id
    ,    any_fk_id
    ,    mts.mt1 as ref_time1 -- time1 to compare to
    ,    mts.mt2 as ref_time2 -- time2 to compare to
    ,    mts.mt3 as ref_time3 -- time3 to compare to
    ,    count(*) over (partition by any_fk_id, release_time>=mts.mt1) as count_before_ref1 -- expect 2 rows
    ,    count(*) over (partition by any_fk_id, release_time>=mts.mt2) as count_before_ref2 -- expect 1 row
    ,    count(*) over (partition by any_fk_id, release_time>=mts.mt3) as count_before_ref3 -- expect 0 rows
    from mts, test_tab
    ;
    
    DROP TABLE test_tab;
    Ich freue mich über jede Anregung/Hilfe!
    Danke im Voraus.
    Ich denke mal, diesen Vergleich da als Regel für die Partition zu nehmen ist ein logischer Fehler. Laß das da weg und zähle die Datensätze entsprechend Deiner Bedingung.
    Ich hab mir einen VIEW gebastelt und darauf dann die Abfrage, kannst das natürlich auch wieder als direktes SELECT aufdröseln:

    Code:
    test=*# \d+ view_test
                                 View "stede.view_test"
        Column    |            Type             | Modifiers | Storage | Description
    --------------+-----------------------------+-----------+---------+-------------
     id           | integer                     |           | plain   |
     any_fk_id    | integer                     |           | plain   |
     mt1          | timestamp without time zone |           | plain   |
     mt2          | timestamp without time zone |           | plain   |
     mt3          | timestamp without time zone |           | plain   |
     release_time | timestamp without time zone |           | plain   |
    View definition:
     WITH mts AS (
             SELECT '2011-08-02 10:05:00'::timestamp without time zone AS mt1, '2011-08-02 10:15:00'::timestamp without time zone AS mt2, '2011-08-02 10:25:00'::timestamp without time zone AS mt3
            )
     SELECT test_tab.id, test_tab.any_fk_id, mts.mt1, mts.mt2, mts.mt3, test_tab.release_time
       FROM mts, test_tab;
    
    test=*# select *, sum (case when release_time >= mt1 then 1 else 0 end) over (partition by any_fk_id) from view_test ;
     id | any_fk_id |         mt1         |         mt2         |         mt3         |    release_time     | sum
    ----+-----------+---------------------+---------------------+---------------------+---------------------+-----
      1 |         4 | 2011-08-02 10:05:00 | 2011-08-02 10:15:00 | 2011-08-02 10:25:00 | 2011-08-02 10:10:00 |   2
      2 |         4 | 2011-08-02 10:05:00 | 2011-08-02 10:15:00 | 2011-08-02 10:25:00 | 2011-08-02 10:20:00 |   2
      3 |         5 | 2011-08-02 10:05:00 | 2011-08-02 10:15:00 | 2011-08-02 10:25:00 | 2011-08-02 11:10:00 |   2
      4 |         5 | 2011-08-02 10:05:00 | 2011-08-02 10:15:00 | 2011-08-02 10:25:00 | 2011-08-02 11:20:00 |   2
    (4 rows)
    
    Time: 0,458 ms
    test=*# select *, sum (case when release_time >= mt2 then 1 else 0 end) over (partition by any_fk_id) from view_test ;
     id | any_fk_id |         mt1         |         mt2         |         mt3         |    release_time     | sum
    ----+-----------+---------------------+---------------------+---------------------+---------------------+-----
      1 |         4 | 2011-08-02 10:05:00 | 2011-08-02 10:15:00 | 2011-08-02 10:25:00 | 2011-08-02 10:10:00 |   1
      2 |         4 | 2011-08-02 10:05:00 | 2011-08-02 10:15:00 | 2011-08-02 10:25:00 | 2011-08-02 10:20:00 |   1
      3 |         5 | 2011-08-02 10:05:00 | 2011-08-02 10:15:00 | 2011-08-02 10:25:00 | 2011-08-02 11:10:00 |   2
      4 |         5 | 2011-08-02 10:05:00 | 2011-08-02 10:15:00 | 2011-08-02 10:25:00 | 2011-08-02 11:20:00 |   2
    (4 rows)
    
    Time: 0,505 ms
    test=*# select *, sum (case when release_time >= mt3 then 1 else 0 end) over (partition by any_fk_id) from view_test ;
     id | any_fk_id |         mt1         |         mt2         |         mt3         |    release_time     | sum
    ----+-----------+---------------------+---------------------+---------------------+---------------------+-----
      1 |         4 | 2011-08-02 10:05:00 | 2011-08-02 10:15:00 | 2011-08-02 10:25:00 | 2011-08-02 10:10:00 |   0
      2 |         4 | 2011-08-02 10:05:00 | 2011-08-02 10:15:00 | 2011-08-02 10:25:00 | 2011-08-02 10:20:00 |   0
      3 |         5 | 2011-08-02 10:05:00 | 2011-08-02 10:15:00 | 2011-08-02 10:25:00 | 2011-08-02 11:10:00 |   2
      4 |         5 | 2011-08-02 10:05:00 | 2011-08-02 10:15:00 | 2011-08-02 10:25:00 | 2011-08-02 11:20:00 |   2
    (4 rows)
    Andreas
    --
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    https://www.xing.com/profile/Andreas_Kretschmer7

Ähnliche Themen

  1. Zeitstempel, Periode
    Von g06lepe im Forum PostGIS
    Antworten: 3
    Letzter Beitrag: 17.01.2009, 19:15

Stichworte

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •  

Links: Tobias Bauer - IT- und Funkdienstleistungen   -   Bilderrahmen-kaufen.de - Der Onlineshop für Bilderrahmen & Fotorahmen   -   Funkforum