Niestandardowe sortowanie w SQL

Zaktualizowaliśmy ten tekst dla Ciebie!
Data aktualizacji: 18.12.2024
Autor aktualizacji: Jarosław Zembal

Bardzo często w aplikacjach, nad którymi pracujemy, w wielu różnych tabelach są przechowywane kolumny zawierające statusy. Przeważnie są one również podstawowymi atrybutami dla całej firmy.

Zamierzam przedstawić rozwiązanie dobrze znanego problemu według mnie (doświadczyłem go już w dwóch różnych firmach) i niektórzy z pracowników nie znali tego rozwiązania. Bardzo często w aplikacjach, nad którymi pracujemy, w wielu różnych tabelach są przechowywane kolumny zawierające statusy i przeważnie są one również podstawowymi atrybutami, nie boję się tego powiedzieć, dla całej firmy.

Pozwólcie, że podam przykład. Branża bankowa. Raportowanie w instytucji inwestycyjnej – obszar dokumentów. Takie dokumenty zawsze będą miały status. W moim samodzielnie przygotowanym przykładzie byłyby takie statusy:

• W_TRAKCIE
• OCZEKUJACE_NA_AKCEPTACJE
• ZAAKCEPTOWANE
• OPUBLIKOWANE

Teraz każdy administrator baz danych powie Ci na etapie projektowania:

-Przechowujecie te same cztery statusy w milionach rekordów w naszej bazie danych, czy moglibyście (to jest w rzeczywistości polecenie, a nie pytanie) zamienić je na liczby, aby zużycie pamięci dla tej kolumny zmniejszyło się o 99%?

-OK, możemy to zrobić

Prawdopodobnie stworzysz piękny Enum w swoim kodzie, wyglądający mniej więcej tak:

public enum ReportStatusEnum {

    APPROVED("Approved", 3),
    IN_PROGRESS("In Progress", 1),
    PENDING_APPROVAL("Pending Approval",2),
    PUBLISHED("Published", 4);

    private String guiName;
    private int dbStatus;

    ReportStatusEnum(String guiName, int dbStatus) {
        this.guiName = guiName;
        this.dbStatus = dbStatus;
    }
}

„Wszyscy zadowoleni? Nie do końca. Powiedziałbym, że w 99% takich przypadków statusy są naprawdę istotne z perspektywy użytkownika końcowego. Te statusy są kluczową częścią aplikacji, nie możesz polegać na liczbach, ponieważ użytkownicy nie znają twojego mapowania. Oczywiście, zamapujesz je za pomocą swojego enum. To dobrze. Ale co, jeśli będą potrzebowali je posortować lub będziesz musiał zaimplementować leniwe ładowanie po takim sortowaniu? Jeszcze gorzej, że kolejność sortowania nie jest taka sama jak wartości zdefiniowane w naszym enum.

W takiej sytuacji jedynym sposobem na osiągnięcie takiej funkcjonalności jest użycie instrukcji ORDER BY CASE. Oto kolejność, którą musimy zdefiniować na moim przykładzie, aby zaspokoić oczekiwania użytkowników końcowych:

Przyjrzyjmy się przykładowym danym z taką specyfikacją:

select * from report order by CASE
    when status = 1 then 'In progress'
    when status = 2 then 'Pending approval'
    when status = 3 then 'Approved'
    when status = 4 then 'Published'
END

Wynik po uruchomieniu takiego zapytania przedstawiony jest poniżej:

Instrukcja CASE może być również bardzo pomocna w różnych scenariuszach, powyższy jest tylko przykładowym.

Obawy związane z wydajnością:

Proszę zauważyć, że nawet jeśli ORDER BY CASE jest prawdopodobnie dobrym rozwiązaniem dla takich problemów, może powodować pewne spowolnienie po stronie bazy danych. W moim przypadku wydajność była zawsze lepsza niż w przypadku alternatywnej funkcji “DECODE”, która jest dostępna w Oracle, ale nie w MySQL.