Βοήθεια στη σύνταξη ενός query

philos

Λοιπόν, χρειάζομαι βοήθεια στη σύνταξη ενός query (βασικά δύο αλλά κάπως κοινά). Έχουμε τους ακόλουθους δύο πίνακες. Ο post περιέχει τα δεδομένα των μηνυμάτων ενός forum και ο thumbs περιέχει τα δεδομένα των ψήφων (1 ή -1) που έχουν δώσει οι χρήστες στα μηνύματα. Σας περιγράφω τα structures που μας αφορούν για την επίλυση του προβλήματος (ίσως αναφέρω και περιττά columns):
Πίνακας "post":
postid (auto_increment)
username
userid
dateline

Πίνακας "thumbs":
id (auto_increment)
thumb (= έχει τιμές 1 ή -1)
userid
username
dateline
to_userid
postid

Προσοχή στο ότι ο thumbs έχει συνήθως πάνω από ένα κοινά postids στο αντίστοιχο column, αφού ένα μήνυμα (postid) μπορεί να έχει πάνω από 1 ψήφους (id).
Ο column userid του post και ο to_userid του thumbs έχουν την ίδια πληροφορία αντίστοιχα.
Οι dateline έχουν την σφραγίδα χρόνου που δημιουργήθηκαν τα μηνύματα ή οι ψήφοι αντίστοιχα.

Τι θέλω να κάνω;
Θέλω ένα query που θα επιστρέφει τα top 10 πιο ψηφισμένα posts με θετικές (thumb=1) και ένα query με τα top 10 πιο ψηφισμένα posts με αρνητικές (thumb=-1), τις τελευταίες 7 μέρες.
Μπορεί κάποιος να βοηθήσει;
edit: πως σας φαίνεται το ακόλουθο;

κώδικας:

SELECT post.*,
(SELECT COUNT(thumb) FROM thumbs AS thumb_table WHERE thumb_table.postid = post.postid AND thumb_table.thumb=1) AS thumbs_count
FROM post AS post
LEFT JOIN thumbs AS thumbs USING (postid)
WHERE thumbs.dateline > CURDATE() - INTERVAL 1 WEEK
GROUP BY postid
ORDER BY thumbs_count DESC
LIMIT 10

Είμαι εδώ για οποιαδήποτε απορία ή διευκρίνηση σχετικά με τα δεδομένα του προβλήματος, έχετε!

dva_dev · Στις: 21 Ιουλ 2012 13:33 Θέμα:

Για δοκίμασε κάτι τέτοιο (για thumb=1)

κώδικας:

SELECT post.*
FROM post INNER JOIN
(
SELECT postid, ABS(SUM(thumb)) AS sum1
FROM thumbs
WHERE (dateline > CURDATE() - INTERVAL 1 WEEK) AND (thumb = 1)
GROUP BY postid
) tmp ON post.postid = tmp.postid
ORDER BY sum1 DESC, postid DESC
LIMIT 10

ή αλλιώς

κώδικας:

SELECT post.*
FROM post INNER JOIN
(
SELECT postid, COUNT(thumb) AS sum1
FROM thumbs
WHERE (dateline > CURDATE() - INTERVAL 1 WEEK) AND (thumb = 1)
GROUP BY postid
) tmp ON post.postid = tmp.postid
ORDER BY sum1 DESC, postid DESC
LIMIT 10

philos · Στις: 21 Ιουλ 2012 14:45 Θέμα:

Δοκίμασα και τα δύο, αλλά το sum1 δίνει το σύνολο των ψήφων (thumb=1) που έχει το κάθε post, γενικά και όχι τις ψήφουν των τελευταίων 7 ημερών.
Το έβαλα δοκιμαστικά ακόμα και σε limit 100 και εμφανίζει και μηνύματα από το 2011 με το σύνολο των ψήφων που έχουν από τότε.
Να σημειώσω ότι για να μου επιστρέψει και τον αριθμό των ψήφων, έβαλα στην πρώτη SELECT δίπλα από το post.*, το sum1
Κάποια τροποποίηση στο query?

dva_dev · Στις: 21 Ιουλ 2012 18:46 Θέμα:

Για κάνε έναν έλεγχο με αυτό:

κώδικας:

SELECT post.postid, IFNULL(A.sum1,0) '7 days sum', B.sum1 'total sum'
FROM post
LEFT OUTER JOIN
(
SELECT post.postid, sum1
FROM post INNER JOIN
(
SELECT postid, COUNT(thumb) AS sum1
FROM thumbs
WHERE (dateline > CURDATE() - INTERVAL 1 WEEK) AND (thumb = 1)
GROUP BY postid
) tmp ON post.postid = tmp.postid
ORDER BY sum1 DESC, postid DESC
) A
ON post.postid=A.postid
LEFT OUTER JOIN
(
SELECT post.postid, sum1
FROM post INNER JOIN
(
SELECT postid, COUNT(thumb) AS sum1
FROM thumbs
WHERE (thumb = 1)
GROUP BY postid
) tmp ON post.postid = tmp.postid
ORDER BY sum1 DESC, postid DESC
) B
ON post.postid=B.postid;

έβαλα λίγα δοκιμαστικά δεδομένα και μου βγάζει αυτό

κώδικας:

+--------+------------+-----------+
| postid | 7 days sum | total sum |
+--------+------------+-----------+
| 1 | 3 | 5 |
| 2 | 2 | 2 |
| 3 | 5 | 8 |
| 4 | 0 | 2 |
| 5 | 5 | 7 |
| 6 | 4 | 7 |
| 7 | 1 | 3 |
| 8 | 2 | 2 |
| 9 | 2 | 5 |
| 10 | 1 | 3 |
| 11 | 2 | 3 |
| 12 | 1 | 4 |
| 13 | 0 | 2 |
| 14 | 2 | 6 |
| 15 | 5 | 6 |
| 16 | 1 | 5 |
| 17 | 3 | 5 |
| 18 | 5 | 8 |
| 19 | 3 | 6 |
| 20 | 3 | 7 |
+--------+------------+-----------+

Και μου φαίνεται εντελώς λογικό αφού το query έχει (dateline > CURDATE() - INTERVAL 1 WEEK)

philos · Στις: 21 Ιουλ 2012 20:32 Θέμα:

Θα το δοκιμάσω και σε λίγες ώρες θα ενημερώσω για τα αποτελέσματα!

Ερώτηση: ένα query σαν αυτό που έγραψες είναι βαρύ για τον server? Θα ήταν εντάξει να το έχω να τρέχει σε μια σελίδα κάθε φορά που την επισκέπτεται ένας χρήστης;

Μιλάμε για site με 40.000 thumb=1!

Αν δεν λέει μπορώ να εισάγω τα δεδομένα σε βάση δεδομένων και να έχω ένα php αρχείο με το query να τα ανανεώνει κάθε 24 ώρες ξέρω 'γω.

edit: μπορείς να διορθώσεις το query βάζοντας σχετικά limits ώστε να επιστρέφει 10 αποτελέσματα order by 7 days sum DESC; Το forum έχει 75.000 μηνύματα και το query επιστρέφει τόσα αποτελέσματα!

dva_dev · Στις: 22 Ιουλ 2012 00:23 Θέμα:

Ακυρο.

Θέλεις τα 10 posts που έχουν καταχωρηθεί τις τελευταίες 7 ημέρες και έχουν τα περισσότερα +1/-1;
ή θέλεις
τα 10 posts που έχουν λάβει τις τελευταίες 7 ημέρες τα περισσότερα +1/-1 ανεξαρτήτως της ημερομηνίας που καταχωρήθηκε το post;

philos · Στις: 22 Ιουλ 2012 08:50 Θέμα:

dva_dev ανέφερε:

Ακυρο.

Θέλεις τα 10 posts που έχουν καταχωρηθεί τις τελευταίες 7 ημέρες και έχουν τα περισσότερα +1/-1;
ή θέλεις
τα 10 posts που έχουν λάβει τις τελευταίες 7 ημέρες τα περισσότερα +1/-1 ανεξαρτήτως της ημερομηνίας που καταχωρήθηκε το post;

Θέλω "τα 10 posts που έχουν λάβει τις τελευταίες 7 ημέρες τα περισσότερα +1/-1 ανεξαρτήτως της ημερομηνίας που καταχωρήθηκε το post". Το δεύτερο δηλαδή.

philos · Στις: 22 Ιουλ 2012 19:47 Θέμα:

Δοκίμασα και το ακόλουθο, αλλά μου επιστρέφει ως total όλα τα thumbs=1 που έχουν τα μηνύματα (σε μερικά μάλιστα μου επιστρέφει και 1 παραπάνω thumb). Σύμφωνα με τα αποτελέσματα, μάλλον επιστρέφει τα most thumbed μηνύματα που υπάρχουν γενικά στο forum!

κώδικας:

SELECT post.*, temp.*
FROM (SELECT postid, thumb, SUM(if(thumb = 1, 1, 0)) AS total
FROM thumbs
WHERE dateline > CURDATE() - INTERVAL 1 WEEK
GROUP BY thumb, postid) temp
LEFT JOIN post ON (temp.postid = post.postid)
ORDER BY temp.total DESC
LIMIT 10

dva_dev · Στις: 22 Ιουλ 2012 20:57 Θέμα:

Καλύτερα κάνε το έτσι, είναι λίγο πιο γρήγορο:

κώδικας:

SELECT post.*, sum1
FROM post INNER JOIN
(
SELECT postid, COUNT(thumb) AS sum1
FROM thumbs
WHERE (dateline > CURDATE() - INTERVAL 1 WEEK) AND (thumb = 1)
GROUP BY postid
LIMIT 10
) tmp ON post.postid = tmp.postid
ORDER BY sum1 DESC, postid DESC;

κώδικας:

SELECT post.*, sum1
FROM post INNER JOIN
(
SELECT postid, COUNT(thumb) AS sum1
FROM thumbs
WHERE (dateline > CURDATE() - INTERVAL 1 WEEK) AND (thumb = -1)
GROUP BY postid
LIMIT 10
) tmp ON post.postid = tmp.postid
ORDER BY sum1 DESC, postid DESC;

Τώρα αν είναι βαρύ ή όχι δεν ξέρω, αλλά αν βαραίνει μπορείς να φτιάξεις έναν προσωρινό πίνακα με τα αποτελέσματα και κάθε Χ αναγνώσεις να τον ενημερώνεις (ή ακόμα και κάθε 24 ώρες όπως είπες κι εσύ).

Πάντως έφτιαξα μια δοκιμαστική βάση, (τώρα ίσως να θέλουν παραπάνω τσεκάρισμα οι indexes ή να θέλει check το execution plan) και έβαλα μερικά τυχαία δεδομένα (αυτό ήταν το πιο χρονοβόρο).
Τα αποτελέσματα που έχω είναι τα εξής:

κώδικας:

mysql> use test;
Database changed
mysql> select count(*) from post;
+----------+
| count(*) |
+----------+
| 262144 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from thumbs;
+----------+
| count(*) |
+----------+
| 8388608 |
+----------+
1 row in set (0.00 sec)

mysql> select thumb,count(thumb) from thumbs group by thumb;
+-------+--------------+
| thumb | count(thumb) |
+-------+--------------+
| -1 | 4196250 |
| 1 | 4192358 |
+-------+--------------+
2 rows in set (8.25 sec)

mysql> SELECT post.*, sum1
-> FROM post INNER JOIN
-> (
-> SELECT postid, COUNT(thumb) AS sum1
-> FROM thumbs
-> WHERE (dateline > CURDATE() - INTERVAL 1 WEEK) AND (thumb = 1)
-> GROUP BY postid
-> LIMIT 10
-> ) tmp ON post.postid = tmp.postid
-> ORDER BY sum1 DESC, postid DESC;
+--------+----------+--------+------------+------+
| postid | username | userid | dateline | sum1 |
+--------+----------+--------+------------+------+
| 631 | NULL | 59 | 1996-05-12 | 1 |
| 609 | NULL | 1 | 1996-10-03 | 1 |
| 574 | NULL | 58 | 1996-04-01 | 1 |
| 545 | NULL | 88 | 1996-05-11 | 1 |
| 534 | NULL | 72 | 1996-03-25 | 1 |
| 519 | NULL | 32 | 1996-04-27 | 1 |
| 436 | NULL | 21 | 1996-04-16 | 1 |
| 419 | NULL | 42 | 1996-01-23 | 1 |
| 293 | NULL | 34 | 1996-06-04 | 1 |
| 212 | NULL | 53 | 1996-09-03 | 1 |
+--------+----------+--------+------------+------+
10 rows in set (0.06 sec)

mysql> SELECT post.*, sum1
-> FROM post INNER JOIN
-> (
-> SELECT postid, COUNT(thumb) AS sum1
-> FROM thumbs
-> WHERE (dateline > CURDATE() - INTERVAL 1 WEEK) AND (thumb = -1)
-> GROUP BY postid
-> LIMIT 10
-> ) tmp ON post.postid = tmp.postid
-> ORDER BY sum1 DESC, postid DESC;
+--------+----------+--------+------------+------+
| postid | username | userid | dateline | sum1 |
+--------+----------+--------+------------+------+
| 44108 | NULL | 50 | 1996-08-19 | 1 |
| 44014 | NULL | 11 | 1996-03-19 | 1 |
| 44005 | NULL | 57 | 1997-06-05 | 1 |
| 43969 | NULL | 91 | 1996-06-18 | 1 |
| 306 | NULL | 75 | 1996-10-24 | 1 |
| 265 | NULL | 48 | 1997-02-20 | 1 |
| 227 | NULL | 79 | 1996-01-08 | 1 |
| 213 | NULL | 11 | 1996-02-14 | 1 |
| 52 | NULL | 12 | 1996-04-09 | 1 |
| 42 | NULL | 60 | 1996-06-23 | 1 |
+--------+----------+--------+------------+------+
10 rows in set (0.07 sec)

Οπως βλέπεις με περίπου 250.000 posts και 8.000.000 thumbs (τα μισά +1 και τα μισά -1) οι χρόνοι είναι αρκετά καλοί.
Λογικά αν υπάρχουν προσθήκες συνέχεια ίσως οι χρόνοι να μην είναι τόσο καλοί γι αυτό υπάρχει και η άλλη λύση του προσωρινού πίνακα.

Επειδή στο sum1 φαίνεται παντού 1 (πριν λίγη ώρα ήταν διαφορετικά αλλά όσο περνάει η ώρα και δεν υπάρχουν καταχωρήσεις τροποποιείται το αποτέλεσμα) έβαλα και τα συνολικά thumbs για το postid 631 και 44108:

κώδικας:

mysql> select postid,thumb, count(*) sum1 from thumbs where postid in(631,44108) group by postid,thumb;
+--------+-------+------+
| postid | thumb | sum1 |
+--------+-------+------+
| 631 | -1 | 13 |
| 631 | 1 | 16 |
| 44108 | -1 | 13 |
| 44108 | 1 | 14 |
+--------+-------+------+
4 rows in set (0.00 sec)

philos · Στις: 22 Ιουλ 2012 21:33 Θέμα:

Σου παραθέτω τα δικά μου αποτελέσματα από το localhost μου. Οι πίνακες έχουν prefix και ελαφρώς άλλη ονομασία, αλλά θα καταλάβεις.
α) Δε μπορώ να καταλάβω πως επέστρεψε τόσο παλιά postids (πρέπει να είναι από τα πρώτα posts του forum με thumbs).
β) Παρατηρώ επίσης ότι η console σου στο dateline επιστρέφει ημερομηνία, ενώ εμένα είναι σφραγίδες χρόνου. Μήπως φταίει αυτό για τα λάθος αποτελέσματα; Αν ναι, πως αλλάζω το query για να κάνει συγκρίσεις βάσει timestamp;
γ) Αν παρατηρήσεις, το query επιστρέφει το ίδιο sum1 και στην περίπτωση που ζητάμε τα των τελευταίων 7 ημερών, αλλά και στην περίπτωση count του συνόλου των ψήφων.

κώδικας:

mysql> use vb4;
Database changed
mysql> SELECT COUNT(*) FROM _vbpost;
+----------+
| COUNT(*) |
+----------+
| 74076 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM _vbsc_thumbs_post;
+----------+
| COUNT(*) |
+----------+
| 34694 |
+----------+
1 row in set (0.02 sec)

mysql> SELECT post.postid, post.username, post.userid, post.dateline, sum1
-> FROM _vbpost AS post INNER JOIN
-> (
-> SELECT postid, COUNT(thumb) AS sum1
-> FROM _vbsc_thumbs_post
-> WHERE (dateline > CURDATE() - INTERVAL 1 WEEK) AND (thumb = 1)
-> GROUP BY postid
-> LIMIT 10
-> ) tmp ON post.postid = tmp.postid
-> ORDER BY sum1 DESC, postid DESC;
+--------+----------+--------+------------+------+
| postid | username | userid | dateline | sum1 |
+--------+----------+--------+------------+------+
| 243 | Scandal | 1 | 1299519259 | 13 |
| 253 | Scandal | 1 | 1299592324 | 6 |
| 240 | Scandal | 1 | 1299430413 | 5 |
| 236 | Scandal | 1 | 1299429346 | 3 |
| 248 | Scandal | 1 | 1299590919 | 2 |
| 238 | Scandal | 1 | 1299430151 | 2 |
| 267 | Scandal | 1 | 1299596752 | 1 |
| 241 | Scandal | 1 | 1299430877 | 1 |
| 239 | Scandal | 1 | 1299430169 | 1 |
| 237 | Scandal | 1 | 1299429537 | 1 |
+--------+----------+--------+------------+------+
10 rows in set (0.00 sec)

mysql> SELECT postid, thumb, COUNT(*) sum1 FROM _vbsc_thumbs_post WHERE postid i
n (243,248) GROUP BY postid,thumb;
+--------+-------+------+
| postid | thumb | sum1 |
+--------+-------+------+
| 243 | 1 | 13 |
| 248 | 1 | 2 |
+--------+-------+------+
2 rows in set (0.00 sec)

edit: κάνω δοκιμές με το

κώδικας:

(FROM_UNIXTIME(dateline) > CURDATE() - INTERVAL 1 WEEK) AND (thumb = 1)

, θα ενημερώσω σύντομα!

dva_dev · Στις: 22 Ιουλ 2012 22:20 Θέμα:

Δοκιμασε με την CURRENT_TIMESTAMP()

dva_dev · Στις: 22 Ιουλ 2012 23:50 Θέμα:

Διόρθωση: Αφού μπήκε μέσα στο subquery το Limit θέλει και το order by και μέσα στο subquery

κώδικας:

SELECT post.*, sum1
FROM post INNER JOIN
(
SELECT postid, COUNT(thumb) AS sum1
FROM thumbs
WHERE (dateline > CURDATE() - INTERVAL 1 WEEK) AND (thumb = 1)
GROUP BY postid
ORDER BY sum1 DESC, postid DESC
LIMIT 10
) tmp ON post.postid = tmp.postid
ORDER BY sum1 DESC, postid DESC;

κώδικας:

SELECT post.*, sum1
FROM post INNER JOIN
(
SELECT postid, COUNT(thumb) AS sum1
FROM thumbs
WHERE (dateline > CURDATE() - INTERVAL 1 WEEK) AND (thumb = -1)
GROUP BY postid
ORDER BY sum1 DESC, postid DESC
LIMIT 10
) tmp ON post.postid = tmp.postid
ORDER BY sum1 DESC, postid DESC;

philos · Στις: 23 Ιουλ 2012 02:03 Θέμα:

Στην αρχή είχα αφαιρέσει εντελώς τη LIMIT από το subquery (ώστε να τσεκάρει όλα τα αποτελέσματα) και την είχα μετακινήσει στο βασικό query, αλλά η διόρθωσή σου είναι σωστή, δεν το σκέφτηκα να βάλω order by στο subquery

Τελικά με την FROM_UNIXTIME(dateline) έφτιαξαν επιτέλους τα αποτελέσματα!

Σε ευχαριστώ πολύ για τη βοήθεια!