Hirdetés

  2. Fórumok
  3. Szoftverfejlesztés
  4. SQL kérdések
  5. (kiemelt téma)
Keresés

Új hozzászólás Aktív témák

  • #5008 kojakhu újonc kw3v865 #4995
    Új Válasz #5008
    Új hozzászólás
    Összes hozzászólása itt Válaszok az összes hozzászólására itt Válaszok erre a hozzászólásra
    Privát üzenet küldése

    kojakhu

    újonc

    válasz kw3v865 #4995 üzenetére

    Hali, Lehet erre gondolsz. Addig egy entry egy csoport, amíg a timestamp gap nagyobb nem lesz mint 1 perc. Csoportokban meg lehet N db entry is amíg a gap nem túl nagy.

    Ezt így lehet, pl.
    Itt az SQLFiddle : [link]

    Kód:

    Setup:

    create table t (dt timestamp);
    -- group 1
    insert into t values (current_timestamp - interval '10' second);
    insert into t values (current_timestamp);
    insert into t values (current_timestamp + interval '10' second);
    -- group 2
    insert into t values (current_timestamp + interval '120' second);
    insert into t values (current_timestamp + interval '130' second);
    -- group 3
    insert into t values (current_timestamp + interval '220' second);
    insert into t values (current_timestamp + interval '230' second);
    Lekérdezés:

    WITH
    diffs AS (
    SELECT dt
    , LAG(dt) OVER (ORDER BY dt) AS prevdt
    , LEAD(dt) OVER (ORDER BY dt) AS nextdt
    , ROW_NUMBER() OVER (ORDER BY dt) AS rn
    FROM t
    ) -- diffs
    , group_gaps_and_flags AS (
    SELECT v.*
    , dt-prevdt AS prev_gap
    , CASE WHEN prevdt IS NULL OR dt-prevdt > interval '1' minute
    THEN 'Y'
    END AS group_start_flag
    , nextdt - dt AS next_gap
    , CASE WHEN nextdt IS NULL OR nextdt-dt > interval '1' minute
    THEN 'Y'
    END AS group_end_flag
    , CASE WHEN prevdt IS NULL OR dt-prevdt > interval '1' minute
    THEN rn
    END AS rn_if_start
    FROM diffs AS v
    ) -- gaps_and_groups
    , groups AS (
    SELECT v.*
    , MAX(rn_if_start) OVER (ORDER BY dt) -- ROWS BETWEEN UNBOUNDED_PRECEEDING AND CURRENT_ROW
    AS my_group
    FROM group_gaps_and_flags AS v
    ) -- groups
    -- .
    -- SELECT * FROM groups; -- separator for testing
    -- .
    SELECT my_group, MIN(dt), MAX(dt), COUNT(dt)
    FROM groups
    GROUP BY my_group
    ORDER BY my_group


    sorry, nem tudom még ezen a fórumon hogy kell kódot beszúrni jól...

  • #5001 bambano titán kw3v865 #4995
    Új Válasz #5001
    Új hozzászólás
    Összes hozzászólása itt Válaszok az összes hozzászólására itt Válaszok erre a hozzászólásra
    Privát üzenet küldése

    bambano

    titán

    válasz kw3v865 #4995 üzenetére

    ezt nem lehet megoldani.
    példa: legyen három timestampod, az első és a harmadik egymástól 1 perc 40 másodperc távolságra. A középső meg félúton. Akkor a középső melyikhez tartozik?

    egyébként timestampokat ki lehet vonni egymásból, timestamp lesz az eredmény, amit lehet konvertálni egész számmá.

  • #4996 nyunyu félisten kw3v865 #4995
    Új Válasz #4996
    Új hozzászólás
    Összes hozzászólása itt Válaszok az összes hozzászólására itt Válaszok erre a hozzászólásra
    Privát üzenet küldése

    nyunyu

    félisten

    válasz kw3v865 #4995 üzenetére

    Nem lenne egyszerűbb az időbélyegek különbsége alapján számolni?

    SQL szabvány szerint mint a dátum, mind az időbélyeg típusok kivonhatóak egymásból és akkor kapsz egy időintervallumot.
    Vagy dátum+időintervallum=dátum, időbélyeg+időintervallum=időbélyeg!

    Én legalábbis úgy nézném meg, hogy mi a legsűrűbben logolt környék, hogy önmagával összejoinolnám a táblát, hogy a második rekord időbélyege nagyobb legyen, mint az elsőé, és a különbségük egy percen belül legyen, aztán ezt a halmazt group by-olnám az első időbélyegre, és megszámolni, hány második tartozik hozzá.

    valami ilyesmire gondoltam:
    SELECT y.date, y.cnt
    FROM (
    SELECT x.date, count(x.date2) cnt
    FROM (
    SELECT a.date, b.date as date2
    FROM table a
    JOIN table b
    ON b.date > a.date
    AND b.date < a.date + interval '1' minute) x
    GROUP BY x.date) y
    ORDER BY y.cnt desc;

    Itt az erős join miatt csak azokat az dátumokat/időbélyegeket fogod visszakapni, ahol egy percen belül volt legalább egy másik bejegyzés.
    Magányos, kósza bejegyzéseket nem! (mondjuk a b.date >= a.date feltétellel azokat is figyelembe lehetne venni.)

Új hozzászólás Aktív témák