Soru LAG işlevi ve GROUP BY


Benim böyle bir masam var.

 event_id |          date          
----------+------------------------
  1703702 | 2013-06-25 07:50:57-04
  3197588 | 2013-06-25 07:51:57-04
 60894420 | 2013-06-25 07:52:57-04
 60894420 | 2013-06-25 07:53:57-04
   183503 | 2013-06-25 07:54:57-04
 63116743 | 2013-06-25 07:55:57-04
 63110451 | 2013-06-25 07:56:57-04
 63116743 | 2013-06-25 07:57:57-04
 63116743 | 2013-06-25 07:58:57-04

Herhangi bir özel event_id arasındaki zaman aralıklarını bulabilmem için gecikme işlevini değil, aynı zamanda bir grubu da uygulamak istiyorum.

Böyle bir şey isterim:

SELECT event_id, difference
FROM ( 
  SELECT event_id, date - lag(date) over (order by date) as
  difference FROM table GROUP BY event_id
) t;

Ancak GROUP BY'yi LAG fonksiyonu ile kullanamıyorum. Aşağıdakine benzer bir sonuç istiyorum:

63116743, {120, 60}
60894420, {60}
...
...

Yani, ilk kimlik için olaylar ile ikinci kimlik için 60s penceresi arasında 120 ve 60'lık bir pencere vardı.

Bunu yapmanın bir yolu var mı? Çıkış formatı, sonunda bir diziye girebildiğim sürece çok önemli değil. Postgres 9.1 kullanıyorum


18
2018-06-25 15:47


Menşei


Üzgünüm, ama çıktının ne anlama geldiğini anlamıyorum. 120? 60? ne? Gösterdiğiniz verilerden gelmiyor.
Sorunuzdaki alt seçen, sorguya hiçbir şey eklemiyor - etkin bir şekilde SELECT * ondan, sadece iç sorguyu çalıştırmakla aynı şey. - IMSoP
@depesz Saniyeler içinde ifade edilen bir aralıktır: '2013-06-25 07: 55: 57-04' ve '2013-06-25 07: 57: 57-04' arasında 120'ler, daha sonra bununla 60'lar arasında '2013-06 -25 07: 58: 57-04 ', sadece aynı kimliğe sahip olayları karşılaştırıyor. - IMSoP
İyi soru için teşekkürler - Pg sürümü, örnek veriler, beklenen sonuçlar. - Craig Ringer


Cevaplar:


WITH diffs as (
    SELECT
        event_id,
        date - lag(date) over (partition BY event_id ORDER BY date) as difference
    FROM
        TABLE
)
SELECT
    event_id,
    array_agg( difference ) as all_diffs
FROM
    diffs
GROUP BY event_id;

Çalışmalı.


25
2018-06-25 16:09



Ve bunu kanıtlamak için bir SQLFiddle var: sqlfiddle.com/#!1/c22fc/4 (array_agg SQLFiddle'ı bir sebepten dolayı kırıyor gibi görünüyor. string_agg sonucu kontrol etmek için) - IMSoP
Oh, ve saniyeler içinde ifade etmek EXTRACT('epoch' FROM ...): sqlfiddle.com/#!1/c22fc/6 - IMSoP
Soruyu yanlış anladım ve çok önemli değil PARTITION BY; Bunu çözmek için SQLFiddle'ı hazırlıyordum, ama sen beni buna dövüyordun :) - IMSoP
Harika çocuklar çok teşekkürler. - jjames