Musimy zrobić pewne raporty na temat wartości, które zwykle są mieszanymi ciągami cyfr i liter, które należy sortować „naturalnie”. Rzeczy takie jak np. „P7B18” lub „P12B3”. @ Ciągi będą w większości sekwencjami liter, a następnie naprzemiennie liczb. Liczba tych segmentów i długość każdego mogą się jednak różnić.

Chcielibyśmy, aby ich części liczbowe były sortowane w kolejności numerycznej. Oczywiście, jeśli po prostu ORDER BYobsłużę te wartości ciągów bezpośrednio , wtedy „P12B3” pojawi się przed „P7B18”, ponieważ „P1” jest wcześniejszy niż „P7”, ale chciałbym, aby było odwrotnie, ponieważ „P7” naturalnie poprzedza „P12”.

Chciałbym także móc porównywać zakresy, np. @bin < 'P13S6'Lub niektóre podobne. Nie muszę obsługiwać liczb zmiennoprzecinkowych ani liczb ujemnych; będą to ściśle nieujemne liczby całkowite, z którymi mamy do czynienia. Długości łańcuchów i liczba segmentów mogą być dowolne, bez ustalonych górnych granic.

W naszym przypadku osłonka łańcucha nie jest ważna, chociaż jeśli istnieje sposób, aby to zrobić w sposób uwzględniający układanie, inni mogą uznać to za przydatne. Najbrzydszą częścią tego wszystkiego jest to, że chciałbym móc zarówno porządkować, jak i filtrować zakresy w WHEREklauzuli.

Gdybym robił to w języku C #, byłoby to dość proste zadanie: parsować, aby oddzielić alfę od liczb, zaimplementować IComparable, i gotowe. Oczywiście SQL Server nie wydaje się oferować podobnej funkcjonalności, przynajmniej o ile mi wiadomo.

Czy ktoś zna jakieś dobre sztuczki, aby to zadziałało? Czy istnieje jakaś słabo rozpowszechniona możliwość tworzenia niestandardowych typów CLR, które implementują IComparable i zachowują się zgodnie z oczekiwaniami? Nie jestem również przeciwny głupim sztuczkom XML (patrz także: konkatenacja listy), a także mam funkcje dopasowania / wyodrębniania / zastępowania wyrażeń regularnych CLR dostępne również na serwerze.

EDYCJA: Jako nieco bardziej szczegółowy przykład chciałbym, aby dane zachowywały się w ten sposób.

SELECT bin FROM bins ORDER BY bin

bin
--------------------
M7R16L
P8RF6JJ
P16B5
PR7S19
PR7S19L
S2F3
S12F0

tzn. podziel ciągi znaków na tokeny wszystkich liter lub wszystkich cyfr i posortuj je odpowiednio alfabetycznie lub numerycznie, przy czym najbardziej znaczące słowo sortujące stanowią tokeny skrajnie lewe. Jak wspomniałem, bułka z masłem w .NET, jeśli implementujesz IComparable, ale nie wiem jak (lub czy) możesz zrobić coś takiego w SQL Server. Na pewno nie jest to coś, z czym się zetknąłem przez 10 lat.

Chcesz rozsądnego i wydajnego sposobu sortowania liczb w ciągach jako liczb rzeczywistych? Rozważ głosowanie na moją sugestię Microsoft Connect: Obsługa „naturalnego sortowania” / DIGITSASNUMBERS jako opcji sortowania

Nie ma łatwych do tego wbudowanych sposobów, ale istnieje możliwość:

Znormalizuj łańcuchy, formatując je na segmenty o stałej długości:

• Utwórz kolumnę sortowania typu VARCHAR(50) COLLATE Latin1_General_100_BIN2. Maksymalna długość 50 może wymagać dostosowania w oparciu o maksymalną liczbę segmentów i ich potencjalne maksymalne długości.
• Podczas gdy normalizacja może być wykonana bardziej efektywnie w warstwie aplikacji, obsługa tego w bazie danych za pomocą T-SQL UDF pozwoliłaby na umieszczenie skalarnej UDF w AFTER [or FOR] INSERT, UPDATEWyzwalaczu, dzięki czemu masz gwarancję prawidłowego ustawienia wartości dla wszystkich rekordów, nawet tych przychodzące za pośrednictwem zapytań ad hoc itp. Oczywiście, skalarny UDF można również obsługiwać za pomocą SQLCLR, ale trzeba by go przetestować, aby ustalić, który z nich był bardziej wydajny. ^**
• UDF (niezależnie od tego, czy jest w T-SQL lub SQLCLR) powinien:
  • Przetwarzaj nieznaną liczbę segmentów, odczytując każdy znak i zatrzymując się, gdy typ zmieni się z alfa na numeryczny lub numeryczny na alfa.
  • Dla każdego segmentu powinien zwrócić ciąg znaków o stałej długości do maksymalnej możliwej liczby znaków / cyfr dowolnego segmentu (lub może maksymalnie + 1 lub 2, aby uwzględnić przyszły wzrost).
  • Segmenty alfa powinny być wyrównane do lewej i wypełnione spacją.
  • Segmenty numeryczne powinny być wyrównane do prawej i wypełnione lewymi zerami.
  • Jeśli znaki alfabetu mogą występować jako małe i wielkie litery, ale kolejność nie musi uwzględniać wielkości liter, zastosuj tę UPPER()funkcję do końcowego wyniku wszystkich segmentów (tak, że trzeba to zrobić tylko raz, a nie dla każdego segmentu). Umożliwi to prawidłowe sortowanie, biorąc pod uwagę binarne zestawienie kolumny sortującej.
• Utwórz AFTER INSERT, UPDATEwyzwalacz w tabeli, która wywołuje UDF, aby ustawić kolumnę sortowania. Aby zwiększyć wydajność, należy użyć UPDATE()funkcji celu ustalenia, czy ta kolumna kod jest nawet w SETklauzuli UPDATErachunku (po prostu RETURNjeśli fałsz), a następnie połączyć INSERTEDi DELETEDpseudo-tabel na kolumnie kodu tylko wiersze procesowych, które mają zmiany w wartości kodu . Należy podać COLLATE Latin1_General_100_BIN2ten warunek JOIN, aby zapewnić dokładność w określeniu, czy nastąpiła zmiana.
• Utwórz indeks w nowej kolumnie sortowania.

Przykład:

P7B18   -> "P     000007B     000018"
P12B3   -> "P     000012B     000003"
P12B3C8 -> "P     000012B     000003C     000008"

W tym podejściu możesz sortować według:

ORDER BY tbl.SortColumn

Możesz także filtrować zasięg za pomocą:

WHERE tbl.SortColumn BETWEEN dbo.MyUDF('P7B18') AND dbo.MyUDF('P12B3')

lub:

DECLARE @RangeStart VARCHAR(50),
        @RangeEnd VARCHAR(50);
SELECT @RangeStart = dbo.MyUDF('P7B18'),
       @RangeEnd = dbo.MyUDF('P12B3');

WHERE tbl.SortColumn BETWEEN @RangeStart AND @RangeEnd

Zarówno filtr, jak ORDER BYi WHEREfiltr powinny korzystać z sortowania binarnego zdefiniowanego dla SortColumnze względu na pierwszeństwo sortowania .

Porównania równości byłyby nadal wykonywane w kolumnie wartości pierwotnej.

Inne przemyślenia:

• Użyj SQLCLR UDT. Może to zadziałać, choć nie jest jasne, czy wykazuje zysk netto w porównaniu z podejściem opisanym powyżej.

  Tak, SQLCLR UDT może zostać zastąpiony przez operatory porównania niestandardowymi algorytmami. Obsługuje to sytuacje, w których wartość jest porównywana z inną wartością, która jest już tego samego typu niestandardowego, lub taką, która wymaga niejawnej konwersji. To powinno obsłużyć filtr zakresu w określonych WHEREwarunkach.

  W odniesieniu do sortowania UDT jako zwykłego typu kolumny (nie kolumny obliczanej), jest to możliwe tylko wtedy, gdy UDT jest „uporządkowane bajtowo”. Bycie „uporządkowanym w bajtach” oznacza, że ​​binarna reprezentacja UDT (którą można zdefiniować w UDT) naturalnie sortuje się w odpowiedniej kolejności. Zakładając, że reprezentacja binarna jest obsługiwana podobnie do podejścia opisanego powyżej dla kolumny VARCHAR (50), która ma segmenty o stałej długości, które są wypełnione, to kwalifikuje się. Lub, jeśli nie było łatwo zapewnić, że reprezentacja binarna zostanie naturalnie uporządkowana we właściwy sposób, możesz ujawnić metodę lub właściwość UDT, która generuje wartość, która byłaby odpowiednio uporządkowana, a następnie utworzyć PERSISTEDna tej podstawie kolumnę obliczeniową metoda lub właściwość. Metoda musi być deterministyczna i oznaczona jako IsDeterministic = true.

  Korzyści z tego podejścia to:

  • Nie ma potrzeby stosowania pola „pierwotna wartość”.
  • Nie trzeba dzwonić do UDF, aby wstawić dane lub porównać wartości. Zakładając, że Parsemetoda UDT przyjmuje P7B18wartość i konwertuje ją, powinieneś być w stanie po prostu wstawić wartości naturalnie jako P7B18. A przy metodzie domyślnej konwersji ustawionej w UDT warunek GDZIE pozwoliłby również na użycie po prostu P7B18`.

  Konsekwencje tego podejścia są następujące:

  • Po prostu wybranie pola zwróci reprezentację binarną, jeśli użyjesz bajtu UDT uporządkowanego jako typ danych kolumny. Lub jeśli używasz PERSISTEDkolumny obliczeniowej we właściwości lub metodzie UDT, wówczas zwracana jest reprezentacja przez właściwość lub metodę. Jeśli chcesz oryginalną P7B18wartość, musisz wywołać metodę lub właściwość UDT, która jest zakodowana, aby zwrócić tę reprezentację. Ponieważ i tak musisz przesłonić tę ToStringmetodę, jest to dobry kandydat do tego.

  • Nie jest jasne (przynajmniej dla mnie teraz, ponieważ nie przetestowałem tej części), jak łatwo / trudno byłoby wprowadzić zmiany w reprezentacji binarnej. Zmiana zapisanej reprezentacji sortowalnej może wymagać usunięcia i ponownego dodania pola. Ponadto upuszczenie zestawu zawierającego UDT nie powiedzie się, jeśli zostanie użyty w jakikolwiek sposób, dlatego należy upewnić się, że poza tym UDT nie ma nic innego w zestawie. Możesz ALTER ASSEMBLYzastąpić definicję, ale istnieją pewne ograniczenia.

    Z drugiej strony, VARCHAR()pole to dane, które są odłączone od algorytmu, więc wymagałoby jedynie aktualizacji kolumny. A jeśli są dziesiątki milionów wierszy (lub więcej), można to zrobić w trybie wsadowym.

• Zaimplementuj bibliotekę ICU, która faktycznie umożliwia przeprowadzanie tego sortowania alfanumerycznego. Choć wysoce funkcjonalna, biblioteka jest dostępna tylko w dwóch językach: C / C ++ i Java. Co oznacza, że ​​możesz potrzebować kilku poprawek, aby działało w Visual C ++, lub istnieje szansa, że ​​kod Java można przekonwertować na MSIL przy użyciu IKVM . Na tej stronie znajduje się jeden lub dwa projekty poboczne .NET, które zapewniają interfejs COM, do którego można uzyskać dostęp w kodzie zarządzanym, ale wierzę, że od jakiegoś czasu nie były aktualizowane i nie próbowałem ich. Najlepszym rozwiązaniem byłoby poradzenie sobie z tym w warstwie aplikacji w celu wygenerowania kluczy sortowania. Klucze sortowania zostaną następnie zapisane w nowej kolumnie sortowania.

  To może nie być najbardziej praktyczne podejście. Jednak nadal jest bardzo fajnie, że taka zdolność istnieje. Bardziej szczegółowy opis tego przykładu podałem w następującej odpowiedzi:

  Czy istnieje sortowanie do sortowania następujących ciągów w następującej kolejności 1,2,3,6,10,10A, 10B, 11?

  Ale wzorzec omawiany w tym pytaniu jest nieco prostszy. Aby zobaczyć przykład pokazujący, że rodzaj wzorca opisanego w tym pytaniu również działa, przejdź do następującej strony:

  ICU Collation Demo

  W „Ustawieniach” ustaw opcję „numeryczne” na „włączone”, a wszystkie pozostałe powinny być ustawione na „domyślne”. Następnie, po prawej stronie przycisku „sortuj”, usuń zaznaczenie opcji „siły różnic” i zaznacz opcję „klucze sortowania”. Następnie zastąp listę elementów w polu tekstowym „Input” następującą listą:

  P12B22
  P7B18
  P12B3
  as456456hgjg6786867
  P7Bb19
  P7BA19
  P7BB19
  P007B18
  P7Bb20
  P7Bb19z23

  Kliknij przycisk „sortuj”. Pole tekstowe „Wyjście” powinno zawierać następujące elementy:

  as456456hgjg6786867
      29 4D 0F 7A EA C8 37 35 3B 35 0F 84 17 A7 0F 93 90 , 0D , , 0D .
  P7B18
      47 0F 09 2B 0F 14 , 08 , FD F1 , DC C5 DC 05 .
  P007B18
      47 0F 09 2B 0F 14 , 08 , FD F1 , DC C5 DC 05 .
  P7BA19
      47 0F 09 2B 29 0F 15 , 09 , FD FF 10 , DC C5 DC DC 05 .
  P7Bb19
      47 0F 09 2B 2B 0F 15 , 09 , FD F2 , DC C5 DC 06 .
  P7BB19
      47 0F 09 2B 2B 0F 15 , 09 , FD FF 10 , DC C5 DC DC 05 .
  P7Bb19z23
      47 0F 09 2B 2B 0F 15 5B 0F 19 , 0B , FD F4 , DC C5 DC 08 .
  P7Bb20
      47 0F 09 2B 2B 0F 16 , 09 , FD F2 , DC C5 DC 06 .
  P12B3
      47 0F 0E 2B 0F 05 , 08 , FD F1 , DC C5 DC 05 .
  P12B22
      47 0F 0E 2B 0F 18 , 08 , FD F1 , DC C5 DC 05 .

  Pamiętaj, że klucze sortowania mają strukturę złożoną z wielu pól oddzielonych przecinkami. Każde pole musi być posortowane niezależnie, aby pojawił się kolejny mały problem do rozwiązania, jeśli trzeba zaimplementować to w SQL Server.

^** Jeśli istnieją jakiekolwiek obawy dotyczące wydajności w związku z korzystaniem z funkcji zdefiniowanych przez użytkownika, należy pamiętać, że proponowane metody wykorzystują je minimalnie. W rzeczywistości głównym powodem przechowywania znormalizowanej wartości było unikanie wywoływania funkcji UDF dla każdego wiersza każdego zapytania. W podstawowym podejściu, UDF jest używany do ustawiania wartości SortColumn, i jest to wykonywane tylko po INSERTi UPDATEza pomocą Triggera. Wybieranie wartości jest znacznie częstsze niż wstawianie i aktualizowanie, a niektóre wartości nigdy nie są aktualizowane. Dla każdego SELECTzapytania, które korzysta z SortColumnfiltra zakresu w WHEREklauzuli, funkcja UDF jest potrzebna tylko jeden raz dla każdej wartości range_start i range_end, aby uzyskać wartości znormalizowane; UDF nie jest nazywany na wiersz.

W odniesieniu do UDT użycie jest w rzeczywistości takie samo jak w przypadku skalarnego UDF. Oznacza to, że wstawianie i aktualizacja wywoływałaby metodę normalizacji raz na każdy wiersz w celu ustawienia wartości. Następnie metoda normalizacyjna byłaby wywoływana raz na zapytanie dla każdego parametru_start i zakres_wartości w filtrze zakresu, ale nie dla wiersza.

Punktem przemawiającym za całkowitą obsługą normalizacji w SQLCLR UDF jest to, że biorąc pod uwagę, że nie robi on dostępu do danych i jest deterministyczny, jeśli jest oznaczony jako IsDeterministic = true, może uczestniczyć w planach równoległych (co może pomóc w operacjach INSERTi UPDATE), podczas gdy T-SQL UDF uniemożliwi korzystanie z planu równoległego.