Πρέπει να ανακτήσω όλες τις γραμμές από έναν πίνακα όπου 2 στήλες σε συνδυασμό είναι όλες διαφορετικές. Θέλω λοιπόν όλες τις πωλήσεις που δεν έχουν άλλες πωλήσεις που συνέβησαν την ίδια ημέρα για την ίδια τιμή. Οι πωλήσεις που είναι μοναδικές με βάση την ημέρα και την τιμή θα ενημερωθούν σε ενεργή κατάσταση.
Οπότε σκέφτομαι:
UPDATE sales
SET status = 'ACTIVE'
WHERE id IN (SELECT DISTINCT (saleprice, saledate), id, count(id)
FROM sales
HAVING count = 1)
Αλλά το μυαλό μου πονάει όταν πάω πιο μακριά από αυτό.
SELECT DISTINCT a,b,c FROM t
είναι σχεδόν ισοδύναμο με:
SELECT a,b,c FROM t GROUP BY a,b,c
Είναι καλή ιδέα να συνηθίσετε τη σύνταξη GROUP BY, καθώς είναι πιο ισχυρή.
Για το ερώτημά σας, θα το έκανα ως εξής:
UPDATE sales
SET status='ACTIVE'
WHERE id IN
(
SELECT id
FROM sales S
INNER JOIN
(
SELECT saleprice, saledate
FROM sales
GROUP BY saleprice, saledate
HAVING COUNT(*) = 1
) T
ON S.saleprice=T.saleprice AND s.saledate=T.saledate
)
Αν συγκεντρώσετε τις μέχρι τώρα απαντήσεις, τις καθαρίσετε και τις βελτιώσετε, θα καταλήξετε σε αυτό το ανώτερο ερώτημα:
UPDATE sales
SET status = 'ACTIVE'
WHERE (saleprice, saledate) IN (
SELECT saleprice, saledate
FROM sales
GROUP BY saleprice, saledate
HAVING count(*) = 1
);
Το οποίο είναι πολύ πιο γρήγορο από οποιοδήποτε από αυτά. Νουκάρει την απόδοση της επί του παρόντος αποδεκτής απάντησης κατά παράγοντα 10 - 15 (στις δοκιμές μου σε PostgreSQL 8.4 και 9.1).
Αλλά αυτό εξακολουθεί να απέχει πολύ από το βέλτιστο. Χρησιμοποιήστε μια NOT EXISTS
(αντι-)ημι-ένωση για ακόμα καλύτερες επιδόσεις. Η EXISTS
είναι τυπική SQL, υπάρχει από πάντα (τουλάχιστον από την PostgreSQL 7.2, πολύ πριν τεθεί αυτή η ερώτηση) και ταιριάζει απόλυτα στις απαιτήσεις που παρουσιάστηκαν:
UPDATE sales s
SET status = 'ACTIVE'
WHERE NOT EXISTS (
SELECT FROM sales s1 -- SELECT list can be empty for EXISTS
WHERE s.saleprice = s1.saleprice
AND s.saledate = s1.saledate
AND s.id <> s1.id -- except for row itself
)
AND s.status IS DISTINCT FROM 'ACTIVE'; -- avoid empty updates. see below
db<>fiddle here Old SQL Fiddle
Αν δεν έχετε ένα πρωτεύον ή μοναδικό κλειδί για τον πίνακα (id
στο παράδειγμα), μπορείτε να το αντικαταστήσετε με τη στήλη του συστήματος ctid
για τους σκοπούς αυτού του ερωτήματος (αλλά όχι για κάποιους άλλους σκοπούς):
AND s1.ctid <> s.ctid
Κάθε πίνακας πρέπει να έχει ένα πρωτεύον κλειδί. Προσθέστε ένα αν δεν έχετε, ακόμα, ένα. Προτείνω μια στήλη serial
ή μια στήλη IDENTITY
στο Postgres 10+.</sub>,
Σχετικά με το θέμα αυτό: "Ορίστε η απάντηση":
Το υποερώτημα στην αντι-ημι-ένωση EXISTS
μπορεί να σταματήσει να αξιολογείται μόλις βρεθεί το πρώτο αντίγραφο (δεν υπάρχει λόγος να ψάχνουμε περαιτέρω). Για έναν βασικό πίνακα με λίγα αντίγραφα αυτό είναι μόνο ελαφρώς πιο αποδοτικό. Με πολλά αντίγραφα αυτό γίνεται πολύ πιο αποδοτικό.
Για γραμμές που έχουν ήδη status = 'ACTIVE'
αυτή η ενημέρωση δεν θα άλλαζε τίποτα, αλλά εξακολουθεί να εισάγει μια νέα έκδοση γραμμής με πλήρες κόστος (ισχύουν μικρές εξαιρέσεις). Κανονικά, δεν το θέλετε αυτό. Προσθέστε μια άλλη συνθήκη WHERE
όπως φαίνεται παραπάνω για να το αποφύγετε αυτό και να το κάνετε ακόμα πιο γρήγορο:
Εάν το status
έχει οριστεί ως NOT NULL
, μπορείτε να απλοποιήσετε την κατάσταση ως εξής:
AND status <> 'ACTIVE';
Αυτό το ερώτημα (σε αντίθεση με την επί του παρόντος αποδεκτή απάντηση από τον Joel) δεν αντιμετωπίζει τις τιμές NULL ως ίσες. Οι ακόλουθες δύο γραμμές για (saleprice, saledate)
θα μπορούσαν να χαρακτηριστούν ως "διαφορετικές" (αν και φαίνονται ίδιες στο ανθρώπινο μάτι):
(123, NULL)
(123, NULL)
Επίσης, περνάει σε ένα μοναδικό ευρετήριο και σχεδόν οπουδήποτε αλλού, αφού οι τιμές NULL δεν συγκρίνονται ίσες σύμφωνα με το πρότυπο SQL. Βλ:
Αντίθετα, τα GROUP BY
, DISTINCT
ή DISTINCT ON ()
αντιμετωπίζουν τις τιμές NULL ως ίσες. Χρησιμοποιήστε το κατάλληλο στυλ ερωτήματος ανάλογα με το τι θέλετε να επιτύχετε. Μπορείτε ακόμα να χρησιμοποιήσετε αυτό το γρηγορότερο ερώτημα με IS NOT DISTINCT FROM
αντί για =
για οποιαδήποτε ή όλες τις συγκρίσεις για να κάνετε τη σύγκριση NULL ίση. Περισσότερα:
Εάν όλες οι στήλες που συγκρίνονται ορίζονται ως NOT NULL
, δεν υπάρχει περιθώριο για διαφωνία.
Το πρόβλημα με το ερώτημά σας είναι ότι όταν χρησιμοποιείτε μια ρήτρα GROUP BY (την οποία ουσιαστικά κάνετε με τη χρήση της distinct) μπορείτε να χρησιμοποιήσετε μόνο στήλες που ομαδοποιείτε με ή συναρτήσεις συνάθροισης. Δεν μπορείτε να χρησιμοποιήσετε τη στήλη id επειδή υπάρχουν δυνητικά διαφορετικές τιμές. Στην περίπτωσή σας υπάρχει πάντα μόνο μία τιμή λόγω της ρήτρας HAVING, αλλά τα περισσότερα RDBMS δεν είναι αρκετά έξυπνα ώστε να το αναγνωρίζουν αυτό.
Ωστόσο, αυτό θα πρέπει να λειτουργήσει (και δεν χρειάζεται join):
UPDATE sales
SET status='ACTIVE'
WHERE id IN (
SELECT MIN(id) FROM sales
GROUP BY saleprice, saledate
HAVING COUNT(id) = 1
)
Θα μπορούσατε επίσης να χρησιμοποιήσετε MAX ή AVG αντί για MIN, είναι σημαντικό μόνο να χρησιμοποιήσετε μια συνάρτηση που επιστρέφει την τιμή της στήλης εάν υπάρχει μόνο μία αντίστοιχη γραμμή.