SQL Ερώτηση

[katina]

Καλησπέρα σας,

 

έχω 2 πίνακες σε sql.

 

users(id,name)

 

milestones(id, name, description, due_date, assigned_to_user_id, completed_on, completed_by_id, created_on, created_by_id)

 

Θα ήθελα να εμφανίζεται το Όνομα του Χρήστη και το Ποσοστό των εργασιών που ολοκληρώθηκαν μέσα στα χρονικά πλαίσια (δηλαδή due_date>completed_on). Έχετε καμιά ιδέα;

 

Δοκίμασα αυτό αλλά μάταια.

 

SELECT

users.display_name,

(COUNT(datediff(due_date, completed_on)<0)/(count(milestones.created_on))*100)

FROM users,milestones

WHERe users.id=milestones.assigned_to_user_id

 

Ευχαριστώ εκ των προτέρων

[taazz]

Μη γνωρίζοντας εάν το datediff επιστρέφει αρνητικές τιμές ή μόνο θετικές και από την στιγμή που δεν αναφέρεις πιο είναι το πρόβλημα με την υπάρχον sql εντολή το μόνο που μπορώ να κάνω είναι να προσθέσω το μόνο κομάτι που βλέπω ότι λήπει.

 

>
SELECT
 users.display_name,
 (COUNT(datediff(due_date, completed_on)<0)/(count(milestones.created_on))*100)
FROM users, milestones
WHERΕ users.id = milestones.assigned_to_user_id
GROUP BY users.display_name

[ΠάρηςΓ]

Δε νομιζω οτι μπορεις να βαλεις τετοια εκφραση μεσα στο count.

Εκτός αν επιστρεφει null .

 

Αρα αν υποστηριζει ο server σου το CASE τοτε ειναι ετσι.

 

SELECT assigned_to_user_id, count(CASE WHEN due_date > completed_on THEN true END) / count(id)

FROM milestones

GROUP BY assigned_to_user_id

[defacer]

Η COUNT είναι aggregate function, που σημαίνει ότι μπορεί να χρησιμοποιηθεί μόνο πάνω σε colums στα οποία έχεις κάνει GROUP BY (αντίστοιχα, αν κάνεις GROUP BY τότε μπορείς να κάνεις SELECT μόνο τα columns πάνω στα οποία έκανες GROUP BY -- στα υπόλοιπα πρέπει να εφαρμόσεις πρώτα κάποιο aggregate function).

 

Το ότι πρέπει να κάνεις GROUP BY πάντως προκύπτει εύκολα και από το γεγονός ότι θέλεις να πάρεις στο αποτέλεσμα μόνο μία row για κάθε χρήστη, ενώ έτσι που έχεις γραμμένο το query (σβήσε τελείως τα σχετικά με COUNT) θα πάρεις για κάθε χρήστη τόσα rows όσα και τα milestones του.

 

Edit: όπως είπε πολύ σωστά ο Πάρης, το να βάλεις συνθήκη μέσα στο COUNT δεν λειτουργεί οπότε το query πρέπει να γίνει π.χ. ως εξής:

 

>SELECT u.display_name, intime, total FROM users u
LEFT JOIN (SELECT assigned_to_user_id, COUNT(*) AS total FROM milestones GROUP BY assigned_to_user_id) tcount ON u.id = tcount.assigned_to_user_id
LEFT JOIN (SELECT assigned_to_user_id, COUNT(*) AS intime FROM milestones WHERE due_date >= completed_on GROUP BY assigned_to_user_id) icount ON u.id = icount.assigned_to_user_id

 

Επειδή αυτό αρχίζει να γίνεται βαρύ, θα το εξηγήσω λίγο. Καταρχήν ξεκινάμε από τα subqueries:

 

>(SELECT assigned_to_user_id, COUNT(*) AS total FROM milestones GROUP BY assigned_to_user_id) tcount

 

Αυτό απλά μετράει πόσα milestones έχει "χρεωμένα" ο κάθε χρήστης, και τον εικονικό πίνακα με τα αποτελέσματα τον ονομάζουμε tcount.

 

>(SELECT assigned_to_user_id, COUNT(*) AS intime FROM milestones WHERE due_date >= completed_on GROUP BY assigned_to_user_id) icount

 

Αυτό μετράει πόσα milestones του κάθε χρήστη ολοκληρώθηκαν εντός προθεσμίας, και τον εικονικό πίνακα με τα αποτελέσματα τον ονομάζουμε icount.

 

Το συνολικό query είναι:

 

>SELECT u.display_name, intime, total FROM users u
LEFT JOIN (...) tcount ON u.id = tcount.assigned_to_user_id
LEFT JOIN (...) icount ON u.id = icount.assigned_to_user_id

 

Οπότε εδώ κάνουμε LEFT JOIN τους χρήστες με τους εικονικούς πίνακες που πήραμε παραπάνω, για να αντιστοιχίσουμε τον αριθμό των συνολικών milestones (total) και των εμπρόθεσμων milestones (intime) με κάθε χρήστη. Απο κει και πέρα μπορούμε να αλλάξουμε το query σε ό,τι βολεύει για το τελικό αποτέλεσμα, π.χ. SELECT (1 - (intime / total)) * 100 AS percentage_delayed κλπ κλπ.

Επεξ/σία 5 Δεκεμβρίου 2011 από defacer

[ΠάρηςΓ]

Defacer

 

 

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_count

 

Εδω αναφερει οτι μετρα τα non nullo.

 

COUNT(datediff(due_date, completed_on)<0) αυτο ποτε δε θα γυρισει null.

 

Επισης γιατι groupby με το String που μπορει να υπαρχει δυο φορες και οχι με το assigned_to_user_id ;

[Aztec]

Μια χαρά τα λένε τα παιδιά. Να επισημάνω οτι η MySQL επιτρέπει και αναφορά σε columns που δεν είναι στο group by . Για άλλους λόγους φυσικά απλά είπα να το αναφέρω για όποιον ενδιαφέρεται. θελει προσοχη η συγκεκριμένη λειτουργικότητα για να μην είναι απρόβλεπτα τα αποτελεσματα

[defacer]

Defacer

 

 

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_count

 

Εδω αναφερει οτι μετρα τα non nullo.

 

COUNT(datediff(due_date, completed_on)<0) αυτο ποτε δε θα γυρισει null.

 

Επισης γιατι groupby με το String που μπορει να υπαρχει δυο φορες και οχι με το assigned_to_user_id ;

 

Για το πρώτο έχεις απόλυτο δίκιο, θα έπρεπε να έχω διαβάσει προσεκτικότερα πριν απαντήσω. Θα κάνω edit την απάντηση παραπάνω (edit: το έκανα).

 

Για το δεύτερο (γιατί όχι group by με user id), βασικά για ευκολία. Το σωστότερο θα ήταν να κάνεις group by με username και με userid, ούτως ώστε πρώτον να γίνει group με το id όπως λες και δεύτερον να μη χρειάζεται να κάνεις join για να πάρεις το username για κάθε id. Δες και παρακάτω που απαντάω στον Aztec γιατί σχετίζεται και αυτό.

 

 

Να επισημάνω οτι η MySQL επιτρέπει και αναφορά σε columns που δεν είναι στο group by . Για άλλους λόγους φυσικά απλά είπα να το αναφέρω για όποιον ενδιαφέρεται. θελει προσοχη η συγκεκριμένη λειτουργικότητα για να μην είναι απρόβλεπτα τα αποτελεσματα.

 

Η MySql το επιτρέπει αλλά τα αποτελέσματα είναι τελείως απρόβλεπτα (γιατί δεν έχει λογικό νόημα αυτό που ζητάς αν κάνεις κάτι τέτοιο) οπότε είναι όχι απλά άχρηστη λειτουργία αλλά επιπλέον και παραπλανητική. Η μόνη περίπτωση που θα δουλέψει με σιγουριά είναι όταν το non-grouped column έχει την ίδια τιμή για όλα ανεξαιρέτως τα rows που έγιναν group, αλλά σ' αυτή την περίπτωση το ίδιο ακριβώς αποτέλεσμα έχεις και αν προσθέσεις και αυτό το column στο group by (και μάλιστα το query θα παίζει και σε οποιαδήποτε άλλη database).

[Aztec]

 

Η MySql το επιτρέπει αλλά τα αποτελέσματα είναι τελείως απρόβλεπτα (γιατί δεν έχει λογικό νόημα αυτό που ζητάς αν κάνεις κάτι τέτοιο) οπότε είναι όχι απλά άχρηστη λειτουργία αλλά επιπλέον και παραπλανητική. Η μόνη περίπτωση που θα δουλέψει με σιγουριά είναι όταν το non-grouped column έχει την ίδια τιμή για όλα ανεξαιρέτως τα rows που έγιναν group, αλλά σ' αυτή την περίπτωση το ίδιο ακριβώς αποτέλεσμα έχεις και αν προσθέσεις και αυτό το column στο group by (και μάλιστα το query θα παίζει και σε οποιαδήποτε άλλη database).

 

 

 

Δεν διαφωνούμε σε κάτι. Άχρηστη όμως δεν είναι . Νομίζω ότι απλά το άφησα ανοιχτό για όποιον έχει ανησυχίες performance

[defacer]

Δεν διαφωνούμε σε κάτι. Άχρηστη όμως δεν είναι . Νομίζω ότι απλά το άφησα ανοιχτό για όποιον έχει ανησυχίες performance

 

Νομίζω πως διαφωνούμε στο αν είναι άχρηστη...

 

Αλλά παραμένω ανοιχτός σε αντεπιχειρήματα.

[Aztec]

Μα δεν υπάρχει λόγος αντεπιχειρημάτων . Η mysql ουσιαστικά δίνει την δυνατότητα να πάρεις τα details ενός πεδίου που έχεις στο group by έχοντας καλύτερο Performance.

 

Δηλαδή μπορείς να γράψεις το εξής

 

>SELECT sa.fk_cust_id, cu.customer_det1, cu.customer_det2, cu.customer_det3,cu.customer_det4,cu.ustomer_det5,cu.customer_det6,cu.customer_det7,cu.customer_det8,COUNT(sa.fk_cust_id)
FROM sale AS sa , customer AS cu 
WHERE sa.fk_cust_id=cu.customer_id
GROUP BY sa.fk_cust_id, cu.customer_det1, cu.customer_det2, cu.customer_det3,cu.customer_det4,cu.ustomer_det5,cu.customer_det6,cu.customer_det7,cu.customer_det8;

ώς εξής

 

>SELECT sa.fk_cust_id, cu.customer_det1, cu.customer_det2, cu.customer_det3,cu.customer_det4,cu.ustomer_det5,cu.customer_det6,cu.customer_det7,cu.customer_det8,COUNT(sa.fk_cust_id)
FROM sale AS sa , customer AS cu
WHERE sa.fk_cust_id=cu.customer_id 
GROUP BY sa.fk_cust_id;

Απλά στην δευτερη περίπτωση απο το να αρχίσουν τα calculations(ή κάποιο έχτρα join) επιλέγει τις τιμές στην τυχη και εφόσον μιλάμε για το ίδιο customer_id per group ότι τιμή και να πάρει θα είναι ίδια.

 

Ο σκοπός αναφοράς μου ήταν περισσότερο πληροφοριακός για όποιον ενδιαφερόταν να ψάξει κάτι παραπάνω. Για αυτό και αναφέρθηκα γενικά