Szukanie multipletów wg timestamp/SQLITE

Taka jest struktura bazy :

CREATE TABLE "detections"(
  device_id INT,
  frame_content TEXT,
  height INT,
  id INT,
  latitude REAL,
  lonngitude REAL,
  team_id INT,
  timestamp INT,
  user_id INT,
  visible TEXT,
  width INT,
  x INT,
  y INT
)

Poszukuję rekordów/zdarzeń z tej samej sekundy.
Poniższy kod rzekomo to robi, ale cholernie długo pracuje (na 350000 rekordów w bazie) .

SELECT datetime(a.timestamp/1000,"unixepoch") as Date,a.device_id,b.device_id 
FROM detections a
JOIN (SELECT  device_id,  timestamp, COUNT(*)
FROM detections 


GROUP BY  (timestamp ),  device_id
HAVING count(*) > 2) b
ON 

( (a.timestamp/1000  =  b.timestamp/1000 ) and (a.device_id<> b.device_id))

 ORDER BY datetime(a.timestamp/1000,"unixepoch")

A najbardziej interesuje mnie by jeśli zostanie znaleziony np.Triplet (lub większy) żeby mieć informację o wszystkich Device_ID, a powyższy kod wyrzuca zawsze 2.

Prosiłbym o ulepszenie kodu :)

Nie wiem czy ulepszony, bo i tak wszystko się rozbija o rozkład danych i plan zapytania, który baza wygeneruje. W każdym razie trochę inaczej:

with
  det_subset as (select device_id, timestamp/1000 ts from detections)
select
  a.device_id,
  b.device_id,
  a.ts
from 
  det_subset a join 
  (select count(0),device_id, ts from det_subset group by device_id,ts having count(0)>2) b on a.ts=b.ts 
where 
      a.device_id<>b.device_id 
  and a.ts=b.ts
order by
   a.ts
;

Wydaje mi się, że można też zamienić warunek a.device_id<>b.device_id na a.device_id<b.device_id - chyba, że koniecznie chcesz mieć pary <DEVICE_A,DEVICE_B> oraz <DEVICE_B,DEVICE_A>.

Można też spróbować posortować zbiory danych i mieć nadzieję, że sqlite potrafi w query plany (poniżej dorzucone sortowanie + warunek a.device_id<b.device_id)

with
  det_subset as (select device_id, timestamp/1000 ts from detections order by device_id)
select
  a.device_id,
  b.device_id,
  a.ts
from 
  det_subset a join 
  (select count(0),device_id, ts from det_subset group by device_id,ts having count(0)>2 order by device_id) b on a.ts=b.ts 
where 
     a.device_id<b.device_id 
 and a.ts=b.ts
 order by 
 	a.ts
 ;

Dzięki, muszę sprawdzić wyniki, bo każdy z trzech podaje inne. Twoje zdecydowanie szybsze.

Tylko pytanie. Co jeśli 3 (lub więcej) urządzenia Device_Id będą w tej samej sekundzie? Też zliczane są? Da się je dopisać do następnej kolumny?

A nie byłoby szybsze:

SELECT *, Count(*) Over(PARTITION BY device_id, timestamp) ilosc FROM detections

i filtr na ilosc?

W kolumnach nie da rady, bo nie znasz ich ilości. Może chodzi Ci o coś jak poniżej?

create table detections (
   ts	int,
   device_id text
 );
 
 
 insert into detections values (1,'DEV1');
 insert into detections values (1,'DEV2');
 insert into detections values (1,'DEV3');
 insert into detections values (2,'DEV3');
 insert into detections values (3,'DEV1');
 insert into detections values (4,'DEV5');
 insert into detections values (4,'DEV6');
 insert into detections values (4,'DEV6');
 insert into detections values (5,'DEV5');
 insert into detections values (5,'DEV6');
 insert into detections values (5,'DEV7');
 insert into detections values (5,'DEV8');

with 
  dev_unique as (select ts, device_id from detections group by ts, device_id order by ts)
select
  ts,
  group_concat(device_id,'_') 
from
  dev_unique
group by
  ts
having count(0)>1
order by ts
;

Marcin.Miga napisał(a):

A nie byłoby szybsze:

SELECT *, Count(*) Over(PARTITION BY device_id, timestamp) ilosc FROM detections

i filtr na ilosc?

Hmmm, a możesz mnie wyręczyć ? ;)

Dopisałem
Where ilosc>1 i błąd...

SELECT * FROM (SELECT *, Count(*) Over(PARTITION BY device_id, timestamp) ilosc FROM detections) x WHERE ilosc>1

Marcin.Miga napisał(a):

SELECT * FROM (SELECT *, Count(*) Over(PARTITION BY device_id, timestamp) ilosc FROM detections) x WHERE ilosc>1

Dzieki.
To bardziej skomplikowane. W tym wszystkim chodzi o poszukiwanie pewnego zjawiska. Nie będę się rozpisywał o teorii, ale spróbuję opisać zasade.

Jeśli znaleziona detekcja w danej sekundzie dla jednego Device_ID, wydarzyła się również w tej samej sekundzie dla innego lub najlepiej wielu innych Device_ID, otrzymujemy tzw.Multiplet.
I teraz.
Jeśli to powtórzy się w ciągu np.max 30minut dla tych samych Device_ID jest to zdarzenie do dalszej analizy.

I właśnie chcę by skrypt wskazywał mi co mam poddać dalszej analizie.

Baza jest ogromna.
Też nie wiem czy SQLITE się nadaje do tego.