Podstawowym mechanizmem wyszukiwania w bazie HgDB jest implementacja indeksu Apache Lucene. Lucene Core to biblioteka Java zapewniająca zaawansowane funkcje indeksowania i wyszukiwania, a także sprawdzanie pisowni, podświetlanie trafień i zaawansowane możliwości analizy. W niniejszym artykule zostaną opisane zasady tworzenia zapytań wyszukujących oraz agregujących jakie obowiązują w Mercury DB (HgDb) 3.0.

Na tej stronie:

Zapytania wyszukujące

Zapytania wyszukujące podlegają ogólnym zasadom tworzenia zapytań opisanych na stronie Apache Lucene - Query Parser Syntax.

Aby realizować zadania wyszukiwania spraw rekomenduje się wykorzystanie usługi REST CaseSearchExtRest#sarchByQuery(POST) i jej odpowiedników w implementacji SOAP i RMI.

W celu ułatwienia deweloperom przejścia z klauzul zapytań standardu SQL do nomenklatury charakterystycznej dla indeksu, rozbudowano mechanizm operatorów AND i OR pozwalających potencjalnie analogiczne tworzenie warunków złożonych w zapytaniach.

Operator AND

Składnia:

<warunek1> AND <warunek2> 

Operator AND przekształca kryterium wyszukiwania do dwóch warunków MUST: +<warunek1> +<warunek2>  .

Przykład: Warunki zapytania mrc_Case_id:12345 AND mrc_status:A  zostaną przekształcone do klauzuli Lucene: +mrc_Case_id:12345 +mrc_status:A .

Operator OR

Składnia:

<warunek1> OR <warunek2> 

Operator OR przekształca kryterium wyszukiwania do dwóch warunków SHOULD: <warunek1> <warunek2>  .

Przykład: Warunki zapytania mrc_Case_id:12345 OR mrc_status:A  zostaną przekształcone do klauzuli Lucene: mrc_Case_id:12345 mrc_status:A .

Zapytania złożone

Zapytania zwracają listę spraw, które zostały znalezione w wyniku wyszukiwania.

Przykłady, które wykorzystane są w poniższej instrukcji budowania zapytań, zostały przygotowane w oparciu o nazewnictwo pól indeksu Lucene w modelu 3.0 (zobacz Indeks Lucene).

Tworzenie zapytań złożonych opiszemy opierając się na przykładowej definicji sprawy:

Przykładowy obiekt sprawy złożonej 
{
	"mrcCaseHeader": {
		"typeCode": "ElixClient",
		"dirty": false,
		"pkPropertyName": "clientID"
	},
	"clientID": "22377",
	"clientName": "Dobry Klient",
	"clientLogo": "n/a",
	"clientAddress": {
		"mrcCaseHeader": {
			"typeCode": "ElixAddress",
			"dirty": false,
			"pkPropertyName": "correlationId"
		},
		"correlationId": "22377",
		"county": "pleszewski",
		"community": "Pleszew",
		"city": "Brzezie",
		"street": "ul. Zawidowicka",
		"buildingNumber": "5",
		"status": "W budowie",
		"isContract": "false",
		"coordinationMeetingPlanedDate": "17-10-2018",
		"orders": [
			{
				"mrcCaseHeader": {
					"typeCode": "ElixOrder",
					"dirty": false,
					"pkPropertyName": null
				},
				"orderNr": "SC/012121/XB",
				"orderType": "Umowa",
				"orderDate": "17-10-2018"
			},
			{
				"mrcCaseHeader": {
					"typeCode": "ElixOrder",
					"dirty": false,
					"pkPropertyName": null
				},
				"orderNr": "SC/012122/SP",
				"orderType": "Sprzedaż",
				"orderDate": "18-10-2018"
			}
		],
		"source": null
	}
}

Jest to sprawa złożona z trzecim poziomem zagnieżdżenia:

  • (1) sprawa główna typu ElixClient.
    • (2) clientAddress - pole wskazujące na sprawę podrzędną typu ElixAddress.
      • (3) orders - pole wskazujące na listę spraw podrzędnych typu ElixOrder.

Zasada 2 poziomu zagnieżdźenia

W przypadku gdy mamy do czynienia ze sprawami złożonymi, w których występują sprawy nadrzędne i podrzędne, konstruowanie zapytań ma ograniczenie do 2 poziomu zagnieżdżenia. W oparciu o analizowany przykład, sprawy złożonej ElixClient, co do zasady, nie uda nam się znaleźć budując kryteria wyszukiwania wynikające z referencji orders (ElixOrder).

Aby utworzyć zapytanie złożone, do nazw pól spraw podrzędnych należy dodać prefiks w oparciu o następującą zasadę:

<nazwa_pola_sprawy_nadrzędnej>.<nazwa_pola_sprawy_podrzędnej> np. clientAddress.status (zobacz przykładowa sprawa).

Podsumowując, aby znaleźć przykładową sprawę główną ElixClient w oparciu o warunki pól sprawy podrzędnej ElixAddress należy zadać następujące zapytanie.

Przykład 1
clientAddress.status:"W budowie" AND clientAddress.city:"Brzezie"

Opis przykładu: Znajdź wszystkie sprawy których adres klienta ma status "W budowie" i znajduje się w mieście "Brzezie".

Powyższe zapytanie, pomimo że prawidłowo skonstruowane i zwróci prawidłowe wyniki, jest zupełnie nie optymalne. Do zapytania należałoby dodać warunki zawężające kryteria sprawy nadrzędnej. W bazie danych mogą być inne typy spraw złożonych mających pole clientAddress, które są referencją do sprawy typu ElixAddress. Dlatego, by zapytanie było bardziej optymalne, należałoby dodać warunki `mrc_typeCodeValue:ElixClient` oraz `clientAddress.mrc_typeCodeValue:ElixAddress`. Zatem pełne zapytanie powinno wyglądać:

mrc_typeCodeValue:ElixClient AND clientAddress.mrc_typeCodeValue:ElixAddress AND clientAddress.status:"W budowie" AND clientAddress.city:"Brzezie"

Ogólnie, im więcej warunków dołożymy do kryterium wyszukiwania, tym zapytanie zrealizowane zostanie w bardziej optymalny sposób.

Nieoptymalna konstrukcja zapytań złożonych może spowodować, że dane, które wyszukujemy nie zostaną znalezione. Jedną z akcji zaradczych jest ustawienie odpowiednio dużego parametru `maxResults` w kontekście wykonywanej operacji, zobacz definicję obiektu Context.

Przykład 2
mrc_typeCodeValue:ElixAddress AND orders.orderType:Umowa

Opis przykładu: Znajdź wszystkie sprawy adresów (sprawy typu ElixAddress), z którymi powiązane są zamówienia typu "Umowa".

Klauzula PARENT

Specjalna klauzula zapytań złożonych. Zapytania zwracają listę spraw nadrzędnych (rodziców) spraw, które zostały znalezione w wyniku wyszukiwania.

Składnia:

(<zapytanie_złożone_wyszukujące_sprawy_podrzędne>) PARENT <nazwa_pola_sprawy_nadrzędnej>([dodatkowe_kryteria_dla_sprawy nadrzędnej])

W naszym przypadku:

  • <zapytanie_złożone_wyszukujące_sprawy_podrzędne> - (parametr wymagany) to będzie zapytanie wyszukujące sprawę typu ElixAddress.
  • <nazwa_pola_sprawy_nadrzędnej> - (parametr wymagany) to będzie będzie nazwa pola clientAddress w sprawie typu ElixClient.
  • [dodatkowe_kryteria_dla_sprawy nadrzędnej] - (parametr opcjonalny) to będzie dodatkowe zapytanie wyszukujące sprawy typu ElixClient.
Przykład 1
(mrc_typeCodeValue:ElixAddress) PARENT clientAddress()

Opis przykładu: znajdź wszystkie sprawy typu ElixAddress, a następnie zwróć rodziców (sprawy nadrzędne).

Przykład 2
(mrc_typeCodeValue:ElixAddress) PARENT clientAddress(mrc_typeCodeValue:ElixClient AND mrc_status:A)

Opis przykładu: znajdź wszystkie sprawy typu ElixAddress, a następnie zwróć rodziców (sprawy nadrzędne), które są typu ElixClient i ich status jest aktywny.

Przykład 3
(mrc_typeCodeValue:ElixAddress AND orders.orderType:Umowa) PARENT clientAddress(mrc_typeCodeValue:ElixClient AND mrc_status:A)

Opis przykładu: Znajdź wszystkie sprawy adresów (sprawy typu ElixAddress), z którymi powiązane są zamówienia typu "Umowa", a następnie zwróć rodziców (sprawy nadrzędne), które są typu ElixClient i ich status jest aktywny.

Zwróćmy uwagę, że klauzula PARENT praktycznie łamie zasadę ograniczenia 2 poziomu zagnieżdżenia budowania zapytań złożonych i daje możliwość dodania warunków na poziomie 3.

Wykorzystanie dodatkowego pola z zakresem dat

W niektórych usługach realizujących zadania wyszukiwania spraw możliwym jest zdefiniowanie dodatkowego kryterium na zakres dat jako wartość pola additionalDateRange (zobacz opis usługi CaseSearchExtRest). Zapytanie to zostanie dołączone do zapytania głównego <zapytanie_złożone_wyszukujące_sprawy_podrzędne>.

Klauzula CHILD

Specjalna klauzula zapytań złożonych. Zapytana zwracają listę spraw podrzędnych (dzieci) spraw, które zostały znalezione w wyniku wyszukiwania.

Składnia:

(<zapytanie_złożone_wyszukujące_sprawy_nadrzędnej>) CHILD <nazwa_pola_sprawy_nadrzędnej>()

W naszym przypadku:

  • <zapytanie_złożone_wyszukujące_sprawy_nadrzędnej> - (parametr wymagany) to będzie zapytanie wyszukujące sprawę typu ElixClient.
  • <nazwa_pola_sprawy_nadrzędnej> - (parametr wymagany) to będzie będzie nazwa pola clientAddress w sprawie, które jest referencją do sprawy podrzędniej (dziecka).

W wyniku zapytania otrzymamy listę spraw typu ElixAddress.

Przykład 1
(mrc_typeCodeValue:ElixClient AND clientAddress.status:\"W budowie\" AND clientAddress.city:Brzezie) CHILD clientAddress()

Opis przykładu: Znajdź wszystkie sprawy których adres klienta ma status "W budowie" i znajduje się w mieście "Brzezie" i zwróć przypisany do pola clientAddress obiekt sprawy podrzędnej.

Klauzula "Group By"

Usługi wykorzystujące klauzulę "Group By" zwracają wyniki zagregowanych danych. Zbiór danych, które poddane zostaną agregacji zawężamy odpowiednim zapytaniem do indeksu Lucene, które podajemy w osobnym argumencie usługi.

Aby realizować zadania agregacji danych rekomenduje się wykorzystanie usługi REST CaseSearchExtRest#groupByQuery(POST) i jej odpowiedników w implementacji SOAP i RMI. Zobacz również stronę z przykładami wykorzystania klauzuli "Group By" Przykłady agregacji danych.

Opisane funkcje agregujące dostępne jest w oprogramowaniu o wersji minimum 3.0.2.0.2. Aby sprawdzić wersję oprogramowania zobacz artykuł Jaka jest wersja mojego systemu?.

Implementacja klauzuli "Group By" wymaga by wartości pól, do których ją stosujemy, muszą być składowane w indeksie Lucene. Informację o tym, czy wartość danego pola jest przechowywana w indeksie, uzyskasz używając metod usługi CaseIndexerFieldsManagerRest. W odpowiedzi, obiekt IndexField opisujący pole wyszukiwania indeksu Lucene, zawiera dane informujące nas o tym czy wartość pola przechowywana jest w indeksie, czy też nie.  

Klauzula pozwala na korzystanie z funkcji przetwarzających i agregujących dane, które możemy podzielić na dwie kategorie:

  • funkcje przekształcające - funkcje, której wynikiem jest nowa wartość pola w wierszu.
  • funkcja zliczające - funkcje, których wynikiem jest agregacja danych składowanych w różnych wierszach.

Funkcje przekształcające

Do funkcji przekształcających zaliczamy implementacje następujących funkcji:

MATH

Funkcja MATH zwraca wynik wyrażenia matematycznego.

Składnia

Funkcja MATH ma następującą składnię:

 MATH( wyrażenie )

Parametry (argumenty):

  • wyrażenie - wyrażenie matematyczne. Obsługę wyrażenia matematycznego realizuje implementacja klasy Java pl.slawas.math.MathExpression (biblioteka sccommon-utils).

MathExpression

MathExpression - prosta obsługa wyrażeń arytmetycznych.

 Grammar:
 expression = term | expression `+` term | expression `-` term
 term = factor | term `*` factor | term `/` factor
 factor = `+` factor | `-` factor | `(` expression `)`
 | number | functionName factor | factor `^` factor

Wspierane funkcje i operatory matematyczne. Ważne są separatory spacji pomiędzy elementami wyrażenia z wyjątkiem potęgowania!

  • + dodawanie np. 1 + 2, a + b
  • - odejmowanie np. 2 - 1, a - b
  • * mnożenie np. 1 * 2, a * b
  • / dzielenie np. 1 / 2, a / b
  • sqrt pierwiastek kwadratowy np. sqrt(16), sqrt(a)
  • sin sinus, podstawą jest liczba stopni, np, sin(16), sin(a)
  • cos cosinus, podstawą jest liczba stopni, np, cos(16), cos(a)
  • tan tangens, podstawą jest liczba stopni, np, tan(16), tan(a)
  • ^potegowanie np. 1^2, a^b

Przykład

Poniżej przykład wykorzystania funkcji MATH:

"groupByClause": "id, MATH(mrc_endDate - mrc_createDate) as howLong",

CONCAT

Funkcja CONCAT zwraca wynik (ciąg) połączenia dwóch lub więcej wartości ciągu.

Składnia

Funkcja CONCAT ma następującą składnię:

DECODE( wyrażenie1, wyrażenie2 [, wyrażenie]... )

Parametry (argumenty):

  • wyrażenie1, wyrażenie2, wyrażenie - wartość ciągu do połączenia z innymi wartościami.

Przykład

Poniżej przykład wykorzystania funkcji CONCAT:

"groupByClause": "concat(clientID, \": \", clientName) as acronym"

DECODE

Funkcja DECODE ma funkcjonalność instrukcji IF-THEN-ELSE.

Składnia

Funkcja DECODE ma następującą składnię:

DECODE( wyrażenie, warunek , wynik [, warunek , wynik]... , domyślnie )

Parametry (argumenty):

  • wyrażenie - wartość do porównania. Jest on automatycznie konwertowany na typ danych pierwszej wartości wyszukiwania przed porównaniem. Wyrażeniem może może być również inna funkcja przekształcająca.
  • warunek - wartość porównywana z wyrażeniem. Wszystkie wartości wyszukiwania są automatycznie konwertowane na typ danych pierwszej wartości wyszukiwania przed porównaniem. Warunek jako kryterium wyszukania, może przyjmować również zakres danych np. [* TO 125000] - wyrażenie ma przyjmować wartości mniejsze lub równe 125000. 
  • wynik - wartość zwracana, jeśli wyrażenie jest spełnia warunek.
  • domyślnie - jeśli nie zostaną znalezione żadne dopasowania, funkcja DECODE zwróci wartość domyślną. Wartość domyślna jest wymagana.

Przykład

Poniżej przykłady wykorzystania funkcji DECODE:

Wartość pola jako wyrażenie
"groupByClause": "id, decode(months.costs, \"[* TO 125000]\", \"low\", \"[125001 TO 140000]\", \"medium\", \"high\") as cost, count(*) as count"
Funkcja MATH jako wyrażenie
"groupByClause": "id, decode(MATH(mrc_endDate - mrc_createDate), \"[* TO -1]\", \"still open\", \"[0 TO 86400000]\", \"1 day\", \"[86400001 TO 172800000]\", \"2 days\", \"very long\") as cost, count(*) as count"

TRUNC

Funkcja TRUNC (data) służy do pobierania daty z częścią obciętą do określonej jednostki miary. Działa według zasad kalendarza gregoriańskiego.

Składnia

Funkcja TRUNC ma następującą składnię:

 TRUNC ( data [, format ] )

Parametry (argumenty):

  • data - wartość daty do obcięcia.
  • format - opcjonalnie, określenie jednostki miary do jakiej ma być obcięta data. Wartość pola format może przyjmować następujące wartości:
    • YY - z dokładnością co do roku 
    • MO - z dokładnością co do miesiąca
    • WE - z dokładnością co do numeru tygodnia w roku
    • DD - z dokładnością co do dnia 
    • HD - z dokładnością co do połowy dnia
    • HH - z dokładnością co do godziny 
    • MI - z dokładnością co do minuty
    • SS - z dokładnością co do sekundy 

Przykład

Poniżej przykład wykorzystania funkcji TRUNC:

"groupByClause": "trunc(mrc_createDate, DD) as createDatePerMonth, count(1) as count"

Funkcje zliczające (agregujące)

Do funkcji zliczających (agregujących) zaliczamy implementacje następujących funkcji:

SUM

Funkcja SUM jest funkcją agregującą, która zwraca sumę wszystkich wartości w zestawie wartości.

Składnia

Funkcja SUM ma następującą składnię:

  SUM ( wyrażenie )

Parametry (argumenty):

  • wyrażenie - nazwa pola, którego wartości mają zostać sumowane.

Przykład

Poniżej przykład wykorzystania funkcji SUM:

"groupByClause": "id, SUM(months.costs) as sum"

MIN

Funkcja MIN jest funkcją agregującą zwracającą minimalną wartość wyrażenia.

Składnia

Funkcja MIN ma następującą składnię:

  MIN ( wyrażenie )

Parametry (argumenty):

  • wyrażenie - nazwa pola, na podstawie którego ma zostać wyznaczona wartość minimalna.

Przykład

Poniżej przykład wykorzystania funkcji MIN:

"groupByClause": "id, MIN(months.costs) as min"

MAX

Funkcja MAX jest funkcją agregującą zwracającą maksymalną wartość wyrażenia.

Składnia

Funkcja MAX ma następującą składnię:

  MAX( wyrażenie )

Parametry (argumenty):

  • wyrażenie - nazwa pola, na podstawie którego ma zostać wyznaczona wartość maksymalna.

Przykład

Poniżej przykład wykorzystania funkcji MAX:

"groupByClause": "id, MAX(months.costs) as max"

COUNT

Funkcja COUNT jest funkcją agregującą zwracającą liczbę wyrażenia.

Składnia

Funkcja COUNT ma następującą składnię:

   COUNT ( wyrażenie )

Parametry (argumenty):

  • wyrażenie - wartości, które mają być policzone.

Przykład

Poniżej przykład wykorzystania funkcji COUNT:

"groupByClause": "trunc(mrc_createDate, DD) as createDatePerMonth, COUNT(1) as count"

AVG

Funkcja AVG jest funkcją agregującą zwracającą średnią arytmetyczną wartość wyrażenia.

Składnia

Funkcja AVG ma następującą składnię:

   AVG ( wyrażenie )

Parametry (argumenty):

  • wyrażenie - nazwa pola, na podstawie którego ma zostać wyznaczona średnia arytmetyczna.

Przykład

Poniżej przykład wykorzystania funkcji MAX:

"groupByClause": "id, AVG(months.costs) as avg"

@2020 Sci Software Sławomir Cichy https://scisoftware.pl/ All rights reserved.