Απλό MySQL query που κάνει DELETE FROM και κρατάει τις τελευταίες X εγγραφές βάση column

[philos]

Καλησπέρα!

Έστω ότι έχουμε τον πίνακα log_entries:

log_id (auto_increment)

entry_title (string)

dateline (unix time)

Θέλω να τρέξω ένα DELETE FROM MySQL query που θα κρατήσει μόνο τις τελευταίες 60 εγγραφές για κάθε entry_title και μάλιστα τις πιο πρόσφατες (log_id DESC ή dateline DESC υποθέτω το ίδιο είναι).

Μπορεί κάποιος να μου γράψει αυτό το απλό query γιατί αν αρχίσω τις προσπάθειες σε test data δε θα τελειώσω ποτέ;

Η δυσκολία μου δεν είναι τόσο στο LIMIT 60 και ORDER BY dateline DESC όσο στο groupάρισμα.

Επεξ/σία 20 Νοεμβρίου 2022 από philos

[tsofras]

Υπάρχουν πάνω απο μία εγγραφές για το ίδιο entry_title ? Γιατί κάπως μπερδεύτηκα

Μπορείς να γράψεις το select που θα σου φέρει τα data που θες να κρατήσεις ?

Αν ναι τότε μπορείς απλά να βάλεις το select να επιστρέφει το log_id και να προσθέσεις στο delete where log_id not in (select log_id .....)

24 λεπτά πριν, philos είπε

Καλησπέρα!

Έστω ότι έχουμε τον πίνακα log_entries:

log_id (auto_increment)

entry_title (string)

dateline (unix time)

Θέλω να τρέξω ένα DELETE FROM MySQL query που θα κρατήσει μόνο τις τελευταίες 60 εγγραφές για κάθε entry_title και μάλιστα τις πιο πρόσφατες (log_id DESC ή dateline DESC υποθέτω το ίδιο είναι).

Μπορεί κάποιος να μου γράψει αυτό το απλό query γιατί αν αρχίσω τις προσπάθειες σε test data δε θα τελειώσω ποτέ;

Η δυσκολία μου δεν είναι τόσο στο LIMIT 60 και ORDER BY dateline DESC όσο στο groupάρισμα.

 

[philos]

Ναι υπάρχουν πολλές εγγραφές με το ίδιο entry_title. Εμείς θέλουμε να κρατήσουμε τις 60 τελευταίες για κάθε entry_title.

Το αν είναι τελευταίες μπορεί να κριθεί είτε από το log_id που είναι auto increment είτε από το dateline που είναι η ημερομηνία unix που εισήχθηκε η εγγραφή.

Αν ας πούμε αντί για 60 θέλαμε να κρατήσουμε τις ΔΥΟ (2) και είχαμε τα δεδομένα

log_id - entry_title

1 - σκύλος

2 - σκύλος

3 - γάτα

4 - σκύλος

5- γάτα

6 - γάτα

7- γάτα

8 - σκύλος

9 - σκύλος

10 - γάτα

11 - χελώνα

12 - χελώνα

13 - πουλί

14 - σκύλος

 

Θα έπρεπε να διαγραφούν όλα εκτός των:

14 - σκύλος

13 - πουλί

12 - χελώνα

11 - χελώνα

10 - γάτα

9 - σκύλος

7 - γάτα

 

Μπορεί να έχω μία εγγραφή πουλί, όμως την κρατάει γιατί δεν είναι πάνω από το όριο 2.

Το παραπάνω λοιπόν θέλω να το κάνω για 60. Απλά είναι λίγο δύσκολο το debugging γι αυτό θα ήθελα βοήθεια από κάποιον που ξέρει τη MySQL από το να το κάνω μόνος μου.

 

Επεξ/σία 20 Νοεμβρίου 2022 από philos

[elvizakos]

Αυτό που θες δεν νομίζω πως γίνεται με απλό delete query. Ένας τρόπος είναι να κάνεις ένα subquery, με τις τελευταίες 60 εγγραφές και συγκρίνοντας με αυτές να διαγράψεις ότι δεν κάνει match. πχ

DELETE FROM log_entries tbl
WHERE tbl.log_id NOT IN (
  SELECT tblmatch.log_id
  	FROM log_entries tblmatch
  	ORDER BY tblmatch.dateline DESC
  	LIMIT 0, 60
);

[philos]

Ναι καταλαβαίνω τη λογική του subquery σου, όμως πως ακριβώς θα κάνω το group by entry_title?

Δε θέλω απλά να κρατήσω τις τελευταίες 60 εγγραφές έτσι γενικά και αόριστα, αλλά τις τελευταίες 60 για κάθε entry_title.

Άρα αν ο πίνακας ήταν φουλ γεμάτος με όλα τα ζώα (σκύλος, γάτα, χελώνα, πουλί), ο μέγιστος αριθμός εγγραφών που θα μένανε θα ήταν 4 ζώα * 60 = 240 εγγραφές στον πίνακα που θέλουμε να παραμείνουν.

Το παραπάνω παράδειγμα με τα ζώα στο προηγούμενο post είναι για 2 αντί για 60 για να το απλοποιήσω.

[tsofras]

12 λεπτά πριν, elvizakos είπε

Αυτό που θες δεν νομίζω πως γίνεται με απλό delete query. Ένας τρόπος είναι να κάνεις ένα subquery, με τις τελευταίες 60 εγγραφές και συγκρίνοντας με αυτές να διαγράψεις ότι δεν κάνει match. πχ

DELETE FROM log_entries tbl
WHERE tbl.log_id NOT IN (
  SELECT tblmatch.log_id
  	FROM log_entries tblmatch
  	ORDER BY tblmatch.dateline DESC
  	LIMIT 0, 60
);

Δεν θα του δουλέψει αυτό γιατί δεν θέλει απλά τις τελευταίες 60

Είναι πιο πολύπλοκο , θέλεις τις τελευταίες 60 ανα ομάδα (γκρουπαρισμένες ανα entry_title αν έχω καταλάβει σωστά)

@philos Είναι λίγο πολύπλοκο το query (τουλάχιστον για τις δικές μου γνώσεις)

[philos]

Ναι σωστά, τις θέλω group αρισμένες!

Κοιτάξτε, με php σε μια επανάληψη τρέξιμο DELETE FROM για κάθε entry_title ξεχωριστά πιθανώς μπορώ να το κάνω, αλλά το προτιμώ σε ένα query για λόγους απόδοσης.

Ευχαριστώ όποιον ασχοληθεί!

[tsofras]

8 λεπτά πριν, philos είπε

Ναι σωστά, τις θέλω group αρισμένες!

Κοιτάξτε, με php σε μια επανάληψη τρέξιμο DELETE FROM για κάθε entry_title ξεχωριστά πιθανώς μπορώ να το κάνω, αλλά το προτιμώ σε ένα query για λόγους απόδοσης.

Ευχαριστώ όποιον ασχοληθεί!

Το παλεύω στο SqlFiddle τόση ώρα με limit , having , group_concat δεν είναι το δυνατό μου σημείο , με κώδικα θα το είχα φτιάξει ήδη

Επίσης η MySql δεν υποστηρίζει limit σε subqueries αλλιώς κάτι θα μπορούσε να γίνει

 

[philos]

Προβλέπω να το κάνω με PHP loop και μικρά queries τελικά, αν δε βρεθεί λύση

[elvizakos]

sorry, ναι και δεν κατάλαβα την ερώτηση και το query θα ήταν πιο απλό με LIMIT 60 χωρίς το subquery...

anyway, δοκίμασα αυτό και με τα παραπάνω δεδομένα, δουλεύει:

DELETE FROM log_entries WHERE log_id NOT IN (
  WITH tmpdeletesql AS (
    SELECT log_id, ROW_NUMBER() OVER( PARTITION BY entry_name ORDER BY d DESC ) AS rown
    FROM log_entries
  )
  SELECT log_id FROM tmpdeletesql WHERE rown < 3
);

δημιουργεί ένα νέο πίνακα με τα στοιχεία του log_entries ενα παραπάνω πεδίο, το rown στο οποίο αυξάνει κατά 1 γιά κάθε εγγραφή με το ίδιο όνομα (πχ το πρώτο 'dog' θα έχει rown 1, το δεύτερο 2, το πρώτο 'cat' rown 1, το δεύτερο 2 κλπ). Το select επιστρέφει τα log_id με rown μικρότερο του 3 (δλδ 1 και 2, οπότε εδώ εσύ αυτό θα το αλλάξεις σε 61 ή <= 60) και κάνει delete όπου δεν ταιριάζει το log_id του πίνακα log_entries με το log_id του πίνακα tmpdeletesql.

Για το παράδειγμα που έδωσες:

CREATE TABLE animals (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	name TEXT,
	d INTEGER
);

INSERT INTO animals ( name, d ) VALUES ( 'dog', 1), ('dog',2), ('cat',3), ('dog',4), ('cat',5),('cat',6),('cat',7),('dog',8),('dog',9),('cat',10),('turtle',11),('turtle',12),('bird',13),('dog',14);

DELETE FROM animals WHERE id NOT IN ( WITH tmpdeletesql AS ( SELECT id, ROW_NUMBER() OVER( PARTITION BY name ORDER BY d DESC ) AS rown FROM animals ) SELECT id FROM tmpdeletesql WHERE rown < 3 );

 

Επεξ/σία 20 Νοεμβρίου 2022 από elvizakos

[tsofras]

13 λεπτά πριν, elvizakos είπε

sorry, ναι και δεν κατάλαβα την ερώτηση και το query θα ήταν πιο απλό με LIMIT 60 χωρίς το subquery...

anyway, δοκίμασα αυτό και με τα παραπάνω δεδομένα, δουλεύει:

DELETE FROM log_entries WHERE log_id NOT IN (
  WITH tmpdeletesql AS (
    SELECT log_id, entry_name, dateline, ROW_NUMBER() OVER( PARTITION BY name ORDER BY d DESC ) AS rown
    FROM log_entries
  )
  SELECT log_id FROM tmpdeletesql WHERE rown < 3
);

 

Το έκανες σε MySql και δούλεψε? Γιατί το δοκίμασα και εγώ νωρίτερα αλλά το ROW_NUMBER δεν παίζει σε MySql

Επεξ/σία 20 Νοεμβρίου 2022 από tsofras

[elvizakos]

Το δοκίμασα και σε mysql 8.0.31 (με create temporary table) και σε sqlite3 και επιστρέφει θα παραπάνω απο τελέσματα και στα δυο.

[tsofras]

25 λεπτά πριν, elvizakos είπε

Το δοκίμασα και σε mysql 8.0.31 (με create temporary table) και σε sqlite3 και επιστρέφει θα παραπάνω απο τελέσματα και στα δυο.

Ωραίος το δοκίμαζα στο sql fiddle που είναι με 5.6 και βλέπω ότι μπήκε στην 8.0.0

[philos]

1 ώρα πριν, elvizakos είπε

sorry, ναι και δεν κατάλαβα την ερώτηση και το query θα ήταν πιο απλό με LIMIT 60 χωρίς το subquery...

anyway, δοκίμασα αυτό και με τα παραπάνω δεδομένα, δουλεύει:

DELETE FROM log_entries WHERE log_id NOT IN (
  WITH tmpdeletesql AS (
    SELECT log_id, ROW_NUMBER() OVER( PARTITION BY entry_name ORDER BY d DESC ) AS rown
    FROM log_entries
  )
  SELECT log_id FROM tmpdeletesql WHERE rown < 3
);

δημιουργεί ένα νέο πίνακα με τα στοιχεία του log_entries ενα παραπάνω πεδίο, το rown στο οποίο αυξάνει κατά 1 γιά κάθε εγγραφή με το ίδιο όνομα (πχ το πρώτο 'dog' θα έχει rown 1, το δεύτερο 2, το πρώτο 'cat' rown 1, το δεύτερο 2 κλπ). Το select επιστρέφει τα log_id με rown μικρότερο του 3 (δλδ 1 και 2, οπότε εδώ εσύ αυτό θα το αλλάξεις σε 61 ή <= 60) και κάνει delete όπου δεν ταιριάζει το log_id του πίνακα log_entries με το log_id του πίνακα tmpdeletesql.

Για το παράδειγμα που έδωσες:

CREATE TABLE animals (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	name TEXT,
	d INTEGER
);

INSERT INTO animals ( name, d ) VALUES ( 'dog', 1), ('dog',2), ('cat',3), ('dog',4), ('cat',5),('cat',6),('cat',7),('dog',8),('dog',9),('cat',10),('turtle',11),('turtle',12),('bird',13),('dog',14);

DELETE FROM animals WHERE id NOT IN ( WITH tmpdeletesql AS ( SELECT id, ROW_NUMBER() OVER( PARTITION BY name ORDER BY d DESC ) AS rown FROM animals ) SELECT id FROM tmpdeletesql WHERE rown < 3 );

 

Ευχαριστώ πολύ!!

Έτρεξα τα queries σου σε phpmyadmin και είχα κι εγώ σωστά αποτελέσματα!

Άρα για την περίπτωση που είναι να κρατήσω τα τελευταία 60, απλά αλλάζω σε WHERE rown < 61, σωστά;

[elvizakos]

Παρακαλώ!

4 ώρες πριν, philos είπε

Άρα για την περίπτωση που είναι να κρατήσω τα τελευταία 60, απλά αλλάζω σε WHERE rown < 61, σωστά;

Ναι, σωστά!