sqlite i usuwanie z bazy duplikatów i nie tylko

0

Witam.
W zasadzie bawię się w Lazarus Pascal (jako hobby).
Chcę rozkminić pewien problem i pomyślałem że w Sqlite może być łatwiej i szybciej.

Baza jest duża, jest to kilkadziesiąt plików po ok 250Mb, przekonwertowanych z Json. Są to dane z projektu CREDO.

Baza w skrócie wygląda tak.

     userNO   PictureBase64       Timestamp

1 - 200 xxxxx 1577701009853
2 - 1100 xxxxx 1577703002900
3 - 3021 xxxxx 1577802114428
...
55 - 200 xxxxx 1577803009857
56 - 200 xxxxx 1577803009858

Problem opisze na przykładzie gry.
Userzy wysyłają co kilka minut zdjęcie na serwer. Odstępy kilkuminutowe są ok. Jeśli jednak zdjęcie od jednego usera wpłynie częściej niż 1 na 10 sekund to znaczy że on oszukuje i te rekordy są do usunięcia. W powyższym przykładzie linie 55 i 56 gdyż odstęp czasowy jest krótki. Bywa i tak że jeden User w ciągu sekundy wyśle 20 zdjęć. Wszystkie są do skasowania.

Czy to w ogóle do zrobienia w SQL? Ja kompletnie nie ogarniam tego języka, od kilku dni przyglądam się touturialom, ale obejmują głównie podstawy.

Post grzecznościowy:)

1

Podstawowe pytanie - na jakim etapie chcesz wylapywać zbyt małe odstępy: czy podczas dodawania wpisu do bazy, czy chcesz ich szukać w treściach już będących w bazie?

A po drugie - w tytule masz coś o duplikatach, ale w treści posta piszesz tylko o wpisach dodawanych zbyt często. To tak właściwie o co Ci chodzi? ;)

2

Ja bym się nie bawił w SQLa a juz na pewno nie z SQLlitem. Raczej nie wile ci pomoże zwłaszcza że dane nie są relacyjne. Zwykły skrypt w czymkolwiek pozwoli na przejechanie po danych i wyrzucenie tych błednych.
EDIT: Jest jeszcze cos takiego jak Drill: https://drill.apache.org/docs/querying-plain-text-files/ Tam można zapuścić SQLa na zwykłych plikach bez ładowania do bazy.

1

Ograniczeniem jest maksymalny rozmiar BLOBa w danej bazie

Ale przede wszystkim (wiem, że nie jest to żadne odkrycie, ale może OP nie ma tego świadomości i dobrze będzie to zaznaczyć) SQLite jest wygodne, ale nie można w ogóle tego porównywać z "prawdziwymi" SQL'ami. Dlatego raczej znacznie lepszą opcją będzie nie trzymanie danych jako BLOB, ale jedynie linków do plików, które będą zapisywane w jakimś osobnym katalogu.

2

Nie wiem dlaczego baza miałaby być niepomocna, aby wyciągnąć id zdjęć które były wrzucane np, szybciej niż 60 sekund to prostym zapytaniem:

select 
	id 
from (
	select 
  		*
		,Timestamp - LAG(Timestamp,1) OVER (PARTITION BY userNO ORDER BY Timestamp) diff
	from 
  		tab) d
where
	diff < 61

https://www.db-fiddle.com/f/6eEZ9QFCnQ1BMkXCJbsEdj/0

0
cerrato napisał(a):

Podstawowe pytanie - na jakim etapie chcesz wylapywać zbyt małe odstępy: czy podczas dodawania wpisu do bazy, czy chcesz ich szukać w treściach już będących w bazie?

A po drugie - w tytule masz coś o duplikatach, ale w treści posta piszesz tylko o wpisach dodawanych zbyt często. To tak właściwie o co Ci chodzi? ;)

Chcę to wyłapywać u siebie na komputerze, w bazie którą sam tworzę z plików JSON skryptem w Pythonie, innym skryptem konwertuję do SQL i przeglądam w DB BROWSER (SQLITE) ma być tylko dla mnie i wszystko jest offline.
W domowy sposób chcę analizować te dane. Te które chcę usunąć to tzw Fejki. Tak traktowane są Zdjęcia które częściej niż raz na np.minutę są wysyłane przez konkretnego urzytkownika.

Tu trzeba analizować każdego Usera z osobna.

0

Nie mogę tego uruchomić w SQLITESTUDIO. :/
W DBBrowser zadziałało, ale żeby sprawdzić czy jest ok, wygenerowaną tabelę muszę zapisać do pliku SQLITE i wrzucić w mój program w Lazarus Pascal by przeglądnąć w formie graficznej treść FRAME_CONTENT . W DBBrowser nie widzę opcji do takiego zapisu, dlatego próbuję w SQLITESTUDIO.
To wszystko zupełnie nowe jest dla mnie.

(Załączniki)

2

https://github.com/pawelsalawa/sqlitestudio/issues/3471 ostatnia odpowiedź:

Pobierz ze strony https://www.sqlite.org/download.html plik: sqlite-dll-win32-x86-3300100.zip

I podmień dll w folderze sqllitestudio

0
Panczo napisał(a):

Nie wiem dlaczego baza miałaby być niepomocna, aby wyciągnąć id zdjęć które były wrzucane np, szybciej niż 60 sekund to prostym zapytaniem:

select 
	id 
from (
	select 
  		*
		,Timestamp - LAG(Timestamp,1) OVER (PARTITION BY userNO ORDER BY Timestamp) diff
	from 
  		tab) d
where
	diff < 61

https://www.db-fiddle.com/f/6eEZ9QFCnQ1BMkXCJbsEdj/0

Ten algorytm kasuje następny wiersz po porównaniu z poprzednim?

Tak myślę, że może dla porównania wyników, interesujące by było gdyby skrypt sprawdzał np 3 wiersze do przodu i jeśli między każdym z nich mniej niż minuta kasował wszystkie łącznie z pierwszym. Dałbyś radę?

Jaka jest różnica między LAG(Timestamp,1) a LAG(Timestamp,1,3) ?

Ogólnie dobrze to działa, filtruje na oko 90% fejków, jednak niektóre są przepuszczane.
Nie widzę fizycznej możliwości porównania bazy przed i po , by ręcznie sprawdzić co znikło a co zostało, a to by wiele pomogło czy mój pomysł dobry.

2

A dlaczego ładujesz te pliki do bazy i później chcesz usuwać duplikaty? Nie prościej trzymać per user datę ostatniego ładowania i wprowadzić ograniczenie w aplikacji: "Nie możesz ładować częściej niż XYZ". ?

1

Ten algorytm kasuje następny wiersz po porównaniu z poprzednim?
Nie, to zapytanie wybiera z bazy id porównując z poprzednim, nic nie kasuje.

Tak myślę, że może dla porównania wyników, interesujące by było gdyby skrypt sprawdzał np 3 wiersze do przodu i jeśli między każdym z nich mniej niż minuta kasował wszystkie łącznie z pierwszym. Dałbyś radę?

Skoro myślisz, że to interesujące, to nie pozostaje nic innego jak napisac i sprawdzić. Ja dałbym radę, ale nie bardzo widzę sens, bo Ty zgadujesz, a nie analizujesz... Takie stweirdzenie utwierdza mnie w przekonaniu, że nie za bardzo masz pjęcie jak dojść do tego, które rekordy są "złe"

Jaka jest różnica między LAG(Timestamp,1) a LAG(Timestamp,1,3) ?

https://www.sqlite.org/windowfunctions.html#biwinfunc

Nie widzę fizycznej możliwości porównania bazy przed i po , by ręcznie sprawdzić co znikło a co zostało, a to by wiele pomogło czy mój pomysł dobry.

Robisz kopię tabeli z której usuwasz, później left joinem wyciagasz co usunąłeś

0
Panczo napisał(a):

Takie stweirdzenie utwierdza mnie w przekonaniu, że nie za bardzo masz pjęcie jak dojść do tego, które rekordy są "złe".

Dokładnie. Nie ma metody na właściwe określenie rekord zły/dobry. Idę na ślepo szukając wskazówek. Jedną była analiza graficzna treści Base64, druga to właśnie powyższe "czasy". To są sita z coraz mniejszymi frakcjami ;) . Męczyłem to w Pascalu i wyniki podobne były. Zainteresował mnie SQL stąd pytania bo kompletnie nie znam środowiska.
Idea projektu CREDO IFJ jest ciekawa ale odkąd zrobiono ranking dla userów niektórzy przeginają dla punktów i analiza danych staje się bezsensowna.

Dzięki za pomoc :)

1 użytkowników online, w tym zalogowanych: 0, gości: 1