Η σελίδα μας αναβαθμίστηκε, γι' αυτό τον λόγο τα μέλη μας θα πρέπει να ζητήσουν νέο κωδικό πρόσβασης από την υπηρεσία "Αποστολή κωδικού πρόσβασης".
Εάν το email με τον νέο κωδικό δεν έρθει στο inbox κοιτάξτε και στο spam folder. Ο server είναι φρέσκος και δεν έχει το reputation που του αξίζει.

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

Συζητήσεις για την βάση δεδομένων MySQL και το phpMyAdmin

Συντονιστές: WebDev Moderators, Super-Moderators

Απάντηση
Άβαταρ μέλους
philos
Δημοσιεύσεις: 256
Εγγραφή: 30 Αύγ 2007 23:32

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

Δημοσίευση από philos » 20 Ιούλ 2012 15:39

Λοιπόν, χρειάζομαι βοήθεια στη σύνταξη ενός 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
Script Master
Δημοσιεύσεις: 3790
Εγγραφή: 16 Σεπ 2005 01:32
Επικοινωνία:

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

Δημοσίευση από 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
Δημοσιεύσεις: 256
Εγγραφή: 30 Αύγ 2007 23:32

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

Δημοσίευση από philos » 21 Ιούλ 2012 14:45

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

Άβαταρ μέλους
dva_dev
Script Master
Δημοσιεύσεις: 3790
Εγγραφή: 16 Σεπ 2005 01:32
Επικοινωνία:

Βοήθεια στη σύνταξη ενός 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
Δημοσιεύσεις: 256
Εγγραφή: 30 Αύγ 2007 23:32

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

Δημοσίευση από 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
Script Master
Δημοσιεύσεις: 3790
Εγγραφή: 16 Σεπ 2005 01:32
Επικοινωνία:

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

Δημοσίευση από dva_dev » 22 Ιούλ 2012 00:23

Ακυρο.

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

Άβαταρ μέλους
philos
Δημοσιεύσεις: 256
Εγγραφή: 30 Αύγ 2007 23:32

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

Δημοσίευση από philos » 22 Ιούλ 2012 08:50

dva_dev έγραψε:Ακυρο.

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

Άβαταρ μέλους
philos
Δημοσιεύσεις: 256
Εγγραφή: 30 Αύγ 2007 23:32

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

Δημοσίευση από 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
Script Master
Δημοσιεύσεις: 3790
Εγγραφή: 16 Σεπ 2005 01:32
Επικοινωνία:

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

Δημοσίευση από 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
Δημοσιεύσεις: 256
Εγγραφή: 30 Αύγ 2007 23:32

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

Δημοσίευση από 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
Script Master
Δημοσιεύσεις: 3790
Εγγραφή: 16 Σεπ 2005 01:32
Επικοινωνία:

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

Δημοσίευση από dva_dev » 22 Ιούλ 2012 22:20

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

Άβαταρ μέλους
dva_dev
Script Master
Δημοσιεύσεις: 3790
Εγγραφή: 16 Σεπ 2005 01:32
Επικοινωνία:

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

Δημοσίευση από 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
Δημοσιεύσεις: 256
Εγγραφή: 30 Αύγ 2007 23:32

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

Δημοσίευση από philos » 23 Ιούλ 2012 02:03

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

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

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

Απάντηση

Επιστροφή στο “MySQL”

Μέλη σε σύνδεση

Μέλη σε αυτήν τη Δ. Συζήτηση: Δεν υπάρχουν εγγεγραμμένα μέλη και 1 επισκέπτης