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

• #5008 kojakhu újonc kw3v865 #4995

  Új Válasz 2021-06-08 19:49:15 #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 2021-06-08 17:57:09 #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

  LOGOUT blog

  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 2021-06-08 15:49:38 #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