Przenoszenie średnia pl sql

Spis treści williamrobertson Konfiguracja PLSQL Developer, część 2 Ten artykuł został napisany dla PLSQL Developer 8.0.4 z wykorzystaniem Oracle 11.2 i Windows XP, w wirtualnej maszynie Parallels na moim Macu, dlatego też zrzuty ekranów pokazują mieszankę okien XP Silver i Aqua. Programista PLSQL jest jednym z kilku zintegrowanych środowisk programistycznych (IDE), które są dostępne dla Oracle. Jedną z rzeczy, które podoba mi się w tym, jest konfigurowalność - możesz zmienić prawie wszystko, a dzięki wtyczkom do pobrania, takim jak rozszerzenie przeglądarki, możesz dodać własną funkcjonalność. Po kilkukrotnym przeniesieniu komputerów i konieczności ponownej instalacji PLSQL Developer za każdym razem, stwierdziłem, że są pewne dostosowania, na które nie mogłem żyć bez, i myślałem, że je udokumentuję. Część pierwsza obejmowała preferencje, takie jak czcionki i układ ekranu, a część 2 obejmuje przeglądarkę sesji. Miałem zamiar uwzględnić niestandardowe operacje prawego klawisza myszki, które dodałem za pomocą rozszerzenia przeglądarki. ale jest tyle rzeczy, które możesz zrobić z przeglądarką Sesji, którą będę musiał zostawić dla części 3. Rozszerzanie przeglądarki sesji 1. Ułatw znalezienie wyszukiwarki sesji w domyślnym layoucie, zakopanym w dół listy pod Menu narzędzi, ale jako coś, co będziesz używać cały czas, znacznie lepiej mieć przycisk do niego. W przypadku pominięcia go w części 1. można dostosować pasek narzędzi, dodając ikonę przeglądarki sesji. Oto, co możesz zrobić: Domyślny pasek narzędzi Dostosowany pasek narzędzi Zauważ, że ikona klawiszy krzyżowych znajduje się po lewej stronie od spersonalizowanego paska narzędzi. 2. Spojrzenie na ustawienia domyślne Otwórz teraz Przeglądarkę Sesji i spójrz na domyślne ustawienia. (Właściwie nie całkiem domyślnie - Ive zmienił czcionkę na Corbel 8pt, która pasuje do większej ilości informacji na ekranie, a także jest bardziej atrakcyjna niż domyślna, Tahoma też działa dobrze.) Będziesz często patrzeć na ten ekran, w końcu). Ekran jest raportem szczegółowym, a zarówno zapytania główne, jak i szczegółowe są konfigurowalne za pomocą ikony klucza. Główna kwerenda w górnej części okna jest zdefiniowana w sekcji Filtry i dostępne są niektóre odmiany opcji select od wersji vsession. W sekcji Szczegóły znajdują się cztery bardzo podstawowe zapytania dla kursorów otwartych, bieżącego zapytania SQL, statystyk sesji i blokad: Zwróć uwagę na zmienną powiązania: sid w zapytaniu Cursors. Fajną rzeczą w zapytaniach dotyczących szczegółów sesji w sesji jest to, że możesz odwoływać się do bieżącej wartości dowolnej kolumny z górnej sekcji jako zmiennej wiążącej w zapytaniach szczegółowych. Tak więc, dopóki zapytanie główne zawiera kolumnę o nazwie sid, możemy używać wyrażeń, takich jak where sessionid: sid w dowolnym zapytaniu szczegółowym. (Oznacza to jednak, że może być konieczne dołączenie kilku kolumn w zapytaniu głównym, aby używać ich jako kluczy w zapytaniach szczegółowych). Jeszcze jeden punkt uwagi na temat pola zapytania szczegółowego polega na tym, że dodawanie tekstu jest konkatenacją po zapytaniu Programista PLSQL łączy wszystkie linie wyjścia w jeden duży blok. Chociaż jest to ciekawa funkcja, zapobiega to przewijaniu, więc uważam to za mieszane błogosławieństwo. 3. Napisz własne zapytania VSESSION Aktywne sesje Domyślne zapytania są wybierane z Vsession gdzie. . co oczywiście jest sensownym ustawieniem domyślnym, które będzie działać we wszystkich wersjach Oracle. Nowe i użyteczne atrybuty są dodawane do programu vsession w każdym wydaniu, a oczywiście ich jawne kodowanie w połączeniach i wyszukiwaniach oznacza, że ​​zapytanie może nie działać we wcześniejszej wersji.1 Jeśli pracujesz z wieloma wersjami Oracle, może być konieczne zapisanie więcej niż jedno zapytanie w sekcji Filtry i wybierz odpowiedni w razie potrzeby (niestety Programista PLSQL nie może sprawdzić wersji i wybrać dla siebie). Jest jeszcze lepsze zapytanie o Active kwerendy dla Oracle 10.2.0.2 (zwróć uwagę na kolumny plsqlententobjectid i plsqlentrysubprogramid, które zostały dodane w tej wersji, więc nie będzie działać w Oracle 10g XE). Wszystkie sesje, które są obecnie aktywne (z wyjątkiem procesów Oracle w tle, takich jak Log Writer) lub które blokują inne sesje lub są własnością mnie. Ich rodzic, jeśli jest częścią zapytania równoległego Obiekt aktualnie oczekiwany (zwykle tabela lub indeks) - sprawdził z dbaobjects za pomocą rowwaitobj. Wpis PLSQL i aktualne procedury - sprawdzono z dbaprocedures przy użyciu kolumn plsql dodanych w Oracle 10.2.0.2. Niektóre statystyki dotyczące procesora, odczytów, użycia pamięci i parsowania zapytań, z vsessmetric. Po wyświetleniu wyników możesz kliknąć te kolumny, aby sortować sesje według użycia procesora. Każda sesja, która blokuje inną, niezależnie od jej statusu, oprócz aktywnych aktywnych sesji pierwszoplanowych. Instancja RAC dla klastrów wielowęzłowych. Jeśli masz tylko jedną instancję, będzie to 1 (możesz przenieść ją na koniec listy, aby zrobić miejsce dla innych kolumn). Widoki GV dla RAC Widoki v (właściwie synonimy dla widoków sys. v) mają wersje z prefiksami g - na przykład gvsession - które zawierają numer instancji, do wykorzystania w systemach RAC. W systemach z jedną instancją zawsze będzie to 1. Dokumentacja zawiera tylko wersję v, więc jeśli chcesz wiedzieć o gvsession. na przykład po prostu spójrz na vsession i załóżmy, że będzie jedna dodatkowa kolumna o nazwie instid. Użyłem zamiennie nazw regularnych v i RV-ready gv. Skopiuj poniższe zapytanie do pola Zapytanie (po przetestowaniu go w oknie SQL, aby upewnić się, że działa z wersją Oracle i uprawnieniami - aby uzyskać dostęp do widoków V, potrzebujesz SELECTCATALOGROLE). Zwróć uwagę, że na końcu nie ma średnika. Możesz również przejrzeć go w stosunku do vsession na wypadek, gdyby były użyteczne użyteczne kolumny. Moje sesje W pracowitym systemie, czasami po prostu chcesz zobaczyć własne sesje i wykluczyć wszystko inne. Do tego używam kwerendy Moje sesje, która jest taka sama jak powyższa z wyjątkiem klauzuli WHERE, która jest: Wszystkie sesje Czasami jest także przydatna, aby mieć jedną wersję, która pokazuje wszystkie sesje, w tym program rejestrujący Oracle, monitor procesu itp. Zrób kolejną kopię powyższego zapytania i po prostu pomiń klauzulę WHERE. 4. Teraz dodaj własne zakładki szczegółów Używa vsqlstats do wyświetlania szczegółowych statystyk wykonania sesji dotyczących bieżących instrukcji SQL (identyfikowanych przez sqlid). Zwróć uwagę, że odnosi się do wszystkich wystąpień kursora, a nie tylko do bieżących sesji. (Również, ponieważ widoki v odzwierciedlają tylko to, co w pamięci teraz, mogą się różnić od tego, co widzisz w widokach DBBA, jeśli masz pakiet diagnostyczny.) Pomysł procentów pokazuje, jak całkowity czas, który upłynął, rozkłada się do CPU, IO, czekania na współbieżność itd. Jest to tylko przybliżone i nie zawsze sumują się do 100, ponieważ mogą być inne czynniki niezwiązane z takimi, jak czasy transferu sieci i przetwarzanie aplikacji, ale dają one wyobrażenie o tym, jak oświadczenie jest obrobiony. Teraz powinieneś otrzymać zakładkę Statystyki SQL, jak na poniższym zrzucie ekranu dla każdej sesji wykonującej SQL. (Wiązania, poprzedni SQL itd. To inne zakładki Za chwilę definiuję.) Historia perfekcyjna tego kursora Jeśli instrukcja SQL zajmuje dużo czasu, możesz sprawdzić jej historię wydajności (z dbahistsqlstats), aby sprawdzić, czy jest to normalne dla kursor lub czy coś się zmieniło. Pierwsze zapytanie poniżej przedstawia odrębne plany wykonania i odpowiadające im statystyki środowiska wykonawczego, zagregowane dla całej historii kursora, dzięki czemu można zobaczyć średni czas wykonania i czy istnieje wiele planów. (Zauważ, że dołączenie do gvsqlplan - g wskazuje wersję z obsługą RAC - co wydaje się być najbardziej niezawodny sposób na znalezienie planu wykonania, który jest obecnie używany, ponieważ zawiera on numer potomny. Jako vsqlstats raportuje tylko jeden wiersz na odrębne sqlid może nie wyświetlać planu dla aktualnie wykonywanej wersji.) Druga wersja - którą etykietuję jako Perf historię tego kursora według daty - dzieli te same informacje według dnia, dzięki czemu można zobaczyć, czy przebiegała szybko w ostatni wtorek, czy też plan został zmieniony dziś rano: Poniższe zapytanie wyświetli listę wszystkich zmiennych wiążących przechowywanych w funkcji vsqlbindcapture dla bieżącej instrukcji SQL. Ive przefiltrowane wyniki, aby wykluczyć duplikaty. Zauważ, że Oracle nie przechwytuje każdej pojedynczej wartości wiązania, i po prostu przechowuje ostatnią wartość przechwyconą w interwałowym kursorbindcaptureinterval i w zależności od ilości dostępnej przestrzeni do optymalizacji pojemności kursora. Alternatywą jest pobranie danych wiązania w czasie analizy z programu vsqlplan. chociaż wymaga to dekodowania w formacie RAW w kolumnie XML - patrz blog blogu Jonathan Lewis Bind Capture. który łączy się z Tworzenie skryptów testowych ze zmiennymi wiązania z Kerry Osborne i Śledzenie wartości wiązania z Dion Cho. To doprowadziło mnie do następującego zapytania, używając pomysłu Kyle'a Hailey'a w komentarzach do postu Jonathana Lewisa: W moich testach przy użyciu Oracle 11.2.0.2 pomija to nazwy powiązań. W każdym razie przechwytywanie wartości powiązań jest dużym tematem, więc pozwolę sobie z powyższymi zapytaniami, aby eksperymentować z i przejść dalej. Poprzedni SQL, poprzednie statystyki SQL Czasami przydatne jest sprawdzenie poprzedniej instrukcji. vsession zawiera kilka kolumn prev, więc wystarczy powielić zakładki szczegółów dla SQL Text i SQL Stats, ale zastąpić prevsqlid i prevchildnumber. Statystyki obiektu Podczas badania problemu z wydajnością często chcesz sprawdzić bieżący stan statystyk tabel biorących udział w kwerendzie. Poniższe zapytanie łączy się z vsqlplanstatisticsall z dbatabstatistics, aby wyświetlić te informacje - nie jest to idealne, jeśli partycjonowane tabele są zaangażowane, ponieważ problem może polegać na statystykach dla poszczególnych partycji lub podpartycji, ale jest to początek. Zamień domyślne zapytanie kursora (wybierz z vopencursor gdzie sid: sid) na poniższe, aby dodać statystyki aktywności. (Zauważ, że statystyki wykonania odnoszą się do wszystkich sesji, a nie tylko do bieżącej sesji.) Obecny plan Wbudowane narzędzie Planowania PLSQL (F5) jest dobre i dobre, ale może być równie dobre, jak wyjaśnić plan. Oznacza to, że narzędzie wykorzystuje plan wyjaśniający do przewidywania planu wykonania, a następnie wyświetla wyniki w postaci graficznej. Czasami nie jest to to samo, co faktyczny plan działania. Przeglądając aktualnie wykonywane sesje, lubię korzystać z dbmsxplan. displaycursor (), aby zobaczyć, co właściwie robi baza danych. Zdefiniuj zakładkę Bieżący plan, używając następujących poleceń: Łączny komentarz spowoduje, że Programista PLSQL zawinie wszystkie wiersze danych wyjściowych z zapytania do jednego dużego bloku. Ułatwia to czytanie, chociaż zapobiega również przewijaniu, więc nie jestem pewien, czy jest to przydatne. (Niestety nie możesz podać czcionki o stałej szerokości dla pojedynczego elementu menu, więc domyślny wyświetlacz nie jest zbyt dobry.) Najlepszym sposobem na odczytanie jest skopiowanie i wklejenie do nowego okna SQL. Jest to łatwiejsze, jeśli zdefiniowałeś skrót klawiszowy, taki jak Alt-S dla pliku gt gt Nowe okno dialogowe gt gt, co zasugerowałem w części 1. (Mam również rozszerzenie przeglądarki do rozszerzenia, aby to zrobić za pomocą jednego kliknięcia prawym przyciskiem myszy, które przyjdzie później. ) Używam również innej odmiany tego zapytania, które mam nazywane Current plan GPS (Gather Plan Statistics), chociaż być może plan Extended byłby lepszym imieniem teraz o tym myślę). Wykorzystuje to parametr ALLSTATS LAST w argumencie format do dbmsxplan. displaycursor, aby uzyskać szacunkową i rzeczywistą liczbę wierszy (liczność), jeśli zapytanie wykorzystało wskazówkę dotyczącą gromadzących się statystyk lub, jeśli parametr stan statystyki został ustawiony na WSZYSTKO dla sesji. Nieco trudną częścią tego jest to, że nie możesz go używać dopóki zapytanie nie zostanie zakończone (ponieważ faktyczny numer wiersza nie jest jeszcze znany), ale kiedy zostanie zakończony, nie jest już aktualnie wykonywanym zapytaniem i dlatego znika z vsession, a więc kiedy odśwież swoją przeglądarkę sesji, której już nie ma. Zamiast tego należy odświeżyć ekran podczas wykonywania kwerendy, ale poczekaj, aż zakończy się przed przejściem do karty Bieżący plan GPS. Bieżące oczekiwanie Chociaż bieżący stan oczekiwania sesji jest już pokazany w kwerendzie głównej, powyżej, lubię mieć informacje również w jej własnej karcie. Ive oznaczono, że obiekt oczekiwania wyszukał z wiersza-wiersza jako prawdopodobnie niepowiązany jako przypomnienie, że chociaż jest to najnowszy obiekt, na który czekała sesja, przetwarzanie mogło teraz przejść do czegoś innego (np. Sortowanie danych wyjściowych lub oczekiwanie na aplikację przetwarzanie danych wyjściowych zapytania) i sesja aktualnie nie uzyskuje dostępu do tego obiektu. Ostatnie 10 czeków Poniżej znajduje się krótki przegląd ostatnich aktywności sesji za pomocą werionowej historii (czas oczekiwania jest w setnych częściach sekundy): vsessionlongops wyświetla stan różnych operacji, które trwają dłużej niż 6 sekund. Operacje te obejmują obecnie wiele funkcji tworzenia kopii zapasowych i odzyskiwania, gromadzenia statystyk i wykonywania zapytań, a kolejne operacje są dodawane do każdej wersji Oracle. Jeśli kwerenda używa operacji mieszania lub sortowania, skanowania tabeli, operacji na partycji itd., Które trwają dłużej niż 6 sekund, operacje te pojawią się w Vsessionlongops i można śledzić ich postępy. (Należy pamiętać, że są to tylko pojedyncze operacje, które są śledzone, a nie całe zapytania.) Wiele długotrwałych procesów Oracle jest również wyposażonych w urządzenia, o czym wspomina podręcznik. Inne niewymienione powyżej obejmują operacje Database Replay i SQL Performance Analyzer (11g) i zadania importu datapump - i oczywiście dowolny z własnych procesów, w których zawarłeś wywołania dbmsapplicationinfo. setsessionlongops, aby zarejestrować całą pracę i ilość przetworzoną do tej pory. Definiuję również kartę Long ops, która jest kopią powyższej, ale z dodatkowym filtrem, aby ograniczyć ją do aktualnie wykonywanej operacji: Podsumowanie ASH - sesja vactivesessionhistory jest migawką vsession pobraną raz na sekundę, ograniczony okres (zwykle od 30 do 60 minut), a następnie przechowywany w historii dbahilaktycznej. (Aby go użyć, potrzebujesz pakietu diagnostycznego, więc upewnij się, że masz licencję, nawet jeśli działa - nie chcesz, aby Twój szef dostał nieoczekiwany rachunek po audycie Oracle.) Jest wiele twórczych sposobów na wydobycie tych informacji, a ja używam trzech zapytań śledzić bieżącą sesję. Ponieważ próbki ASH pobierają co sekundę, przydatne może być podsumowanie za pomocą instrukcji SQL i wyświetlenie wyników według czasu wykonania. Jeśli oglądasz procedurę lub grupę wywołującą kilka instrukcji, daje to przegląd miejsca, w którym sesja spędza swój czas (trochę jak śledzenie sesji). Następująca kwerenda daje jeden wiersz na sqlid. w malejącej kolejności łącznego czasu, z sumą na dole. Podsumowanie ASH - egzekucje Mam również bardziej szczegółową wersję możliwą w 11g przez kolumnę sqlexecstart w vactivesessionhistory. co pozwala mi zobaczyć pojedyncze wykonanie instrukcji SQL zamiast pojedynczego zagregowanego wiersza. Podsumowanie ASH - obiekt kursora czasu Raport przedstawiający obiekty oczekiwane przez wszystkie instrukcje SQL dla określonej sesji. Jest to szybki sposób sprawdzenia, na co poświęciła sesję, w kategoriach obiektów, a nie zapytań. ASH podsumowuje to zapytanie z wywołującym Dalej Mam zapytanie GROUP-BY dla bieżącego sqlid. w malejącej kolejności liczenia próbek. Chodzi o to, aby zobaczyć, gdzie spędza się czas w aktualnie wykonywanej instrukcji (zamiast tego, które instrukcje zajęły czas w bieżącej sesji). Ponieważ Historia Aktywnej Sesji wykorzystuje 1-sekundowy interwał sondowania, coś, co występuje w 10 próbkach, prawdopodobnie zajęło około 10 sekund. Zauważ, że filtruje tylko w sqlid. więc wiele wykonań tego samego zapytania przez sesję zostanie zebranych razem. (W 11g można użyć nowej kolumny sqlexecid, aby rozróżnić egzekucje.) Należy również pamiętać, że ASH może próbkować aktywność jako Na CPU razem z obiektem bazy danych - oznacza to tylko ostatni obiekt, do którego dostęp uzyskano w chwili pobrania próbki, a nie że procesor był koniecznie związany z tym obiektem. Mam dwa smaki tego, zi bez szczegółów dotyczących wywołującej procedury PLSQL. ASH podsumowuje to zapytanie tylko SQL Jest to to samo, co poprzednie zapytanie, ale bez wywołujących szczegółów PLSQL, które dają bardziej przejrzysty widok dostępu do bazy danych. ASH szczegółowo omawia tę sesję Wreszcie, mam prostą listę vactivesessionhistory dla bieżącej sesji, dzięki czemu możesz zorientować się, co aktualnie robi: Domyślna konfiguracja pochodzi z zakładką Blokady. Powiedzmy, że sesja wykonuje następujące czynności: Domyślna zakładka Blokady wyświetla to: Zmiana na następujące zapewnia więcej szczegółów: Optymalizator nietypowy Uważam, że jest to przydatne do sprawdzenia, jakie ustawienia optymalizatora są używane przez określoną sesję (która może nie być takie same ustawienia, jak sesja lub domyślne ustawienia instancji). Łączy on vysoptimizerenv (parametry systemu związane z optymalizatorem) z vsesoptimizerenv (parametry sesji związane z optymalizatorem, początkowo odziedziczone z ustawień na poziomie systemu, ale odzwierciedlające wszelkie zmiany wprowadzone przez komendy zmieniające sesję) i raportuje różnice. Temp space Ile miejsca tymczasowego ma sesja wykorzystująca do mieszania, sortuje itp. Kopiowanie William Robertson 2017 Subskrybuj artykuły Subskrybuj kod i skrypty22 SQL do analizy i raportowania Oracle rozszerzył możliwości przetwarzania analitycznego SQL, wprowadzając nową rodzinę analitycznych funkcji SQL. Te funkcje analityczne umożliwiają obliczenie: Rankingów i percentyle Przeprowadzanie obliczeń w oknie Statystyki regresji liniowej Funkcje rankingowe obejmują skumulowane rozkłady, procentową ocenę i N-kafle. Przenoszenie obliczeń okna pozwala znaleźć ruchome i zbiorcze agregacje, takie jak sumy i średnie. Analiza Laglead umożliwia bezpośrednie odniesienia między wierszami, dzięki czemu można obliczyć zmiany okresowe. Analiza Firstlast umożliwia znalezienie pierwszej lub ostatniej wartości w uporządkowanej grupie. Inne ulepszenia SQL obejmują wyrażenie CASE i partycjonowane sprzężenie zewnętrzne. Wyrażenia CASE zapewniają logikę if-then przydatną w wielu sytuacjach. Partycjonowane sprzężenie zewnętrzne jest rozszerzeniem składni zewnętrznego łączenia ANSI, które umożliwia użytkownikom selektywne zagęszczanie pewnych wymiarów przy jednoczesnym zachowaniu rzadkości innych. Dzięki temu narzędzia do raportowania mogą selektywnie zagęszczać wymiary, na przykład te, które pojawiają się w raportach w wielu tabelach, a jednocześnie są rzadkie. Aby zwiększyć wydajność, funkcje analityczne można zrównoleglić: wiele procesów może jednocześnie wykonywać wszystkie te instrukcje. Dzięki tym funkcjom obliczenia są łatwiejsze i bardziej wydajne, co zwiększa wydajność bazy danych, skalowalność i prostotę. Funkcje analityczne są klasyfikowane zgodnie z opisem w Tabeli 22-1. Tabela 22-1 Funkcje analityczne i ich zastosowania Aby wykonać te operacje, funkcje analityczne dodają kilka nowych elementów do przetwarzania SQL. Te elementy opierają się na istniejącym SQL, aby umożliwić elastyczne i wydajne wyliczenia obliczeniowe. Z kilkoma wyjątkami funkcje analityczne mają te nowe elementy. Strumień przetwarzania jest przedstawiony na rysunku 22-1. Rysunek 22-1 Kolejność przetwarzania Podstawowe pojęcia używane w funkcjach analitycznych to: Przetwarzanie zapytania za pomocą funkcji analitycznych odbywa się w trzech etapach. Najpierw wszystkie sprzężenia, GDZIE. Klauzule GROUP BY i HAVING są wykonywane. Po drugie, zestaw wyników jest udostępniany funkcjom analitycznym, a wszystkie ich obliczenia mają miejsce. Po trzecie, jeśli zapytanie ma klauzulę ORDER BY na końcu, ORDER BY jest przetwarzany, aby umożliwić precyzyjne sortowanie danych wyjściowych. Kolejność przetwarzania jest pokazana na rysunku 22-1. Partycje zestawu wyników Funkcje analityczne pozwalają użytkownikom dzielić zestawy wyników zapytania na grupy wierszy zwanych partycjami. Należy zauważyć, że termin partycje używane z funkcjami analitycznymi nie jest związany z funkcją partycji tabeli. W całym tym rozdziale termin "przegrody" odnosi się tylko do znaczenia związanego z funkcjami analitycznymi. Partycje tworzone są po grupach zdefiniowanych za pomocą klauzul GROUP BY, dzięki czemu są dostępne dla dowolnych zagregowanych wyników, takich jak sumy i średnie. Podziały podziałów mogą opierać się na dowolnych kolumnach lub wyrażeniach. Zestaw wyników zapytania może być podzielony na jedną partycję zawierającą wszystkie wiersze, kilka dużych partycji lub wiele małych partycji zawierających po kilka wierszy. Dla każdego wiersza w partycji można zdefiniować przesuwne okno danych. To okno określa zakres wierszy używanych do wykonywania obliczeń dla bieżącego wiersza. Rozmiary okien mogą być oparte na fizycznej liczbie wierszy lub interwału logicznym, takim jak czas. Okno ma wiersz początkowy i wiersz końcowy. W zależności od definicji okno może się poruszać na jednym lub obu końcach. Na przykład, okno zdefiniowane dla funkcji sumy sumarycznej miałoby wiersz początkowy ustalony w pierwszym rzędzie jego partycji, a wiersz końcowy przesuwałby się od punktu początkowego aż do ostatniego wiersza partycji. W przeciwieństwie do tego, okno zdefiniowane dla średniej ruchomej będzie miało zarówno punkt początkowy, jak i punkt końcowy przesuwane, tak że utrzymują stały fizyczny lub logiczny zakres. Okno może być ustawione tak duże, jak wszystkie wiersze w partycji lub po prostu okno przesuwne z jednego wiersza wewnątrz partycji. Gdy okno znajduje się blisko granicy, funkcja zwraca wyniki tylko dla dostępnych wierszy, zamiast ostrzegać, że wyniki nie są zgodne z oczekiwaniami. Podczas korzystania z funkcji okna bieżący wiersz jest uwzględniany podczas obliczeń, więc powinieneś tylko określać (n -1), gdy masz do czynienia z n pozycjami. Każde obliczenie wykonywane za pomocą funkcji analitycznej opiera się na bieżącym wierszu w obrębie partycji. Bieżący wiersz służy jako punkt odniesienia określający początek i koniec okna. Na przykład, wyśrodkowane obliczenia średniej ruchomej można zdefiniować za pomocą okna, które przechowuje bieżący wiersz, sześć poprzednich wierszy i sześć kolejnych wierszy. W ten sposób powstaje przesuwne okno z 13 rzędami, jak pokazano na rysunku 22-2. Rysunek 22-2 Przykład rankingu, okienkowania i funkcji raportowania w okienku przesuwnym W tej sekcji przedstawiono podstawowe funkcje analityczne dotyczące rankingu, okien i raportów. Przykładowe obliczenia regresji liniowej W tym przykładzie obliczamy prostą regresji o najmniejszych kwadratach, która wyraża ilość sprzedanego produktu jako funkcję liniową ceny katalogowej produktów. Obliczenia są pogrupowane według kanału sprzedaży. Wartości ZWYKŁA. INTCPT. RSQR to odpowiednio nachylenie, przecięcie i współczynnik wyznaczania linii regresji. Wartość (liczba całkowita) COUNT to liczba produktów w każdym kanale, dla których dostępna jest zarówno ilość sprzedanych, jak i cena katalogowa. Statistical Aggregates Oracle udostępnia zestaw funkcji statystycznych SQL i pakiet statystyk DBMSSTATFUNCS. W tej sekcji wymieniono niektóre z nowych funkcji wraz z podstawową składnią. Statystyki opisowe Możesz obliczyć następujące statystyki opisowe: Mediana trybu zbioru danych zbioru danych Możesz obliczyć następujące statystyki parametryczne: Współczynnik kręgu Spendmans rho Współczynnik tau-b Oprócz funkcji, ta wersja ma pakiet PLSQL, DBMSSTATFUNCS . Zawiera opisową funkcję statystyczną SUMMARY wraz z funkcjami wspierającymi dopasowanie dystrybucji. Funkcja SUMMARY podsumowuje kolumnę liczbową tabeli z różnymi statystykami opisowymi. Pięć funkcji dopasowania dystrybucji obsługuje rozkład normalny, jednolity, Weibulla, Poissona i wykładniczy. Agregaty zdefiniowane przez użytkownika Oracle oferuje możliwość tworzenia własnych funkcji, zwanych funkcjami agregującymi zdefiniowanymi przez użytkownika. Funkcje te są napisane w językach programowania, takich jak PLSQL, Java i C, i mogą być używane jako funkcje analityczne lub agregaty w widokach zmaterializowanych. Więcej informacji na temat składni i ograniczeń można znaleźć w przewodniku dla programistów baz danych Oracle Database. Zaletami tych funkcji są: Bardzo złożone funkcje mogą być programowane przy użyciu w pełni proceduralnego języka. Większa skalowalność niż w przypadku innych technik, gdy funkcje zdefiniowane przez użytkownika są zaprogramowane do przetwarzania równoległego. Typy danych obiektów mogą być przetwarzane. Jako prosty przykład funkcji agregującej zdefiniowanej przez użytkownika należy wziąć pod uwagę statystyki skośne. Obliczenie to mierzy, czy zbiór danych ma nierówny rozkład na temat jego średniej. Powie Ci, czy jeden ogon rozkładu jest znacznie większy od drugiego. Jeśli utworzyłeś agregację zdefiniowaną przez użytkownika o nazwie udskew i zastosujesz ją do danych limitu kredytowego w poprzednim przykładzie, instrukcja SQL i wyniki mogą wyglądać następująco: Przed utworzeniem zdefiniowanych przez użytkownika funkcji agregujących powinieneś rozważyć, czy Twoje potrzeby mogą zostać spełnione w zwykłym SQL. Wiele skomplikowanych obliczeń można wykonać bezpośrednio w SQL, szczególnie za pomocą wyrażenia CASE. Utrzymywanie regularnego SQL umożliwi prostsze rozwijanie, a wiele operacji zapytań jest już dobrze zrównoleglonych w SQL. Nawet wcześniejszy przykład, statystyki skośne, można utworzyć przy użyciu standardowego, aczkolwiek długiego kodu SQL. Operacje przestawiania Wartość d ata zwracana przez zapytania analizy biznesowej jest często najbardziej użyteczna, jeśli jest prezentowana w formacie tabelarycznym. Pivotclause instrukcji SELECT umożliwia pisanie zapytań o przesłuchanie, które obracają wiersze w kolumny, agregując dane w procesie rotacji. Obracanie jest kluczową techniką w hurtowniach danych. W nim przekształca się wiele wierszy danych wejściowych w mniej i ogólnie szersze wiersze w hurtowni danych. Podczas przestawiania jest stosowany operator agregacji dla każdego elementu na liście wartości kolumny przestawnej. Kolumna przestawna nie może zawierać arbitralnego wyrażenia. Jeśli chcesz obrócić na wyrażenie, powinieneś podać alias wyrażenia w widoku przed operacją PIVOT. Podstawowa składnia jest następująca: Aby zilustrować użycie obrotu, utwórz następujący widok jako podstawę dla późniejszych przykładów: Przykład: Obracanie Poniższa instrukcja ilustruje typową oś w kolumnie kanału: Należy zauważyć, że dane wyjściowe utworzyły cztery nowe kolumny z aliasami , SPRZEDAŻ BEZPOŚREDNIA. INTERNETSALES. CATALOGSALES. i TELESALES. po jednej dla każdej z wartości przestawnych. Wynik jest sumą. Jeśli nie podano żadnego aliasu, nagłówek kolumny będzie wartościami na liście-IN. Obracanie na wielu kolumnach Można obracać w więcej niż jednej kolumnie. Poniższa instrukcja ilustruje typowy pivot z wieloma kolumnami: Zwróć uwagę, że ten przykład określa wielokolumnową listę-IN z nagłówkami kolumn zaprojektowanymi tak, aby pasowały do ​​członków listy-IN. Obracanie: wiele agregacji Możesz przestawiać z wieloma agregacjami, jak pokazano w następującym przykładzie: Zauważ, że zapytanie tworzy nagłówki kolumn przez połączenie wartości przestawnych (lub aliasu) z aliasem funkcji agregującej plus podkreślenie. Odróżnianie wartości NULL generowanych przez PIV od wartości Null w danych źródłowych Można rozróżnić wartości puste, które są generowane przez użycie PIVOT i wartości istniejące w danych źródłowych. Poniższy przykład ilustruje wartości null generowane przez PIVOT. Następująca kwerenda zwraca wiersze z 5 kolumnami, kolumna prodid. i przestawne kolumny wynikowe Q1. Q1COUNTTOTAL. Q2. Q2COUNTTOTAL. Dla każdej unikalnej wartości prodid. Q1COUNTTOTAL zwraca całkowitą liczbę wierszy, których wartość qtr wynosi Q1. to znaczy, a Q2COUNTTOTAL zwraca całkowitą liczbę wierszy, których wartość qtr wynosi Q2. Załóżmy, że mamy tabelę sales2 o następującej strukturze: Z wyniku wiemy, że dla prodid 100 są dwa rzędy sprzedaży za pierwszy kwartał. i 1 wiersz sprzedaży za kwartał Q2 dla prodid 200, jest 1 wiersz sprzedaży za kwartał Q1. i bez wiersza sprzedaży za kwartał Q2. To samo, w Q2COUNTTOTAL. możesz określić, że NULLlt1gt pochodzi z wiersza w oryginalnej tabeli, której miara ma wartość pustą, podczas gdy NULLlt2gt jest spowodowane tym, że żaden wiersz nie jest obecny w oryginalnej tabeli dla prodid 200 w kwartale Q2. Operacje Unpivoting Univivot nie odwraca operacji PIVOT. Zamiast tego obraca dane z kolumn do wierszy. Jeśli pracujesz z danymi przestawnymi, operacja UNPIVOT nie może odwrócić żadnych agregacji, które zostały wykonane przez PIVOT lub w jakikolwiek inny sposób. Aby zilustrować niepodzielenie, najpierw utwórz tabelę przestawną, która zawiera cztery kolumny, na ćwierć roku: Treść tabel jest podobna do następującej: Następująca operacja UNPIVOT obraca kolumny ćwiartkowe w wiersze. Dla każdego produktu będą cztery rzędy, po jednym na każdy kwartał. Zwróć uwagę na użycie INCLUDE NULLS w tym przykładzie. Możesz także użyć EXCLUDE NULLS. która jest ustawieniem domyślnym. Ponadto można również rozpakować dane przy użyciu dwóch kolumn, tak jak w poniższym przykładzie: Zastępowanie symboli wieloznacznych i podkwerendy za pomocą operacji XML Jeśli chcesz użyć argumentu wieloznacznego lub podkwerendy w kolumnach przestawnych, możesz to zrobić za pomocą składni PIVOT XML. W przypadku PIVOT XML dane wyjściowe operacji są poprawnie sformatowane w formacie XML. Poniższy przykład ilustruje użycie słowa kluczowego wieloznacznego DOWOLNY. Wydaje kod XML zawierający wszystkie wartości kanałów w widoku sprzedaży: Należy zauważyć, że słowo kluczowe DOWOLNY jest dostępne w operacjach PIVOT tylko jako część operacji XML. Dane wyjściowe obejmują dane dla przypadków, w których kanał istnieje w zbiorze danych. Zwróć też uwagę, że funkcje agregujące muszą określać klauzulę GROUP BY, aby zwracać wiele wartości, ale oś obrotu nie zawiera jawnie klauzuli GROUP BY. Zamiast tego pivotclause wykonuje niejawne GROUP BY. Poniższy przykład ilustruje użycie podzapytania. Wyprowadza XML, który zawiera wszystkie wartości kanałów i dane sprzedaży odpowiadające każdemu kanałowi: Dane wyjściowe zagęszczają dane, aby uwzględnić wszystkie możliwe kanały dla każdego produktu. Densyfikacja danych do raportowania danych jest zwykle przechowywana w formie sparse. Oznacza to, że jeśli nie istnieje żadna wartość dla danej kombinacji wartości wymiaru, w tabeli faktów nie ma żadnego wiersza. Można jednak wyświetlić dane w gęstej formie, z wierszami dla wszystkich kombinacji wartości wymiarów wyświetlanych, nawet jeśli nie istnieją dla nich żadne dane faktyczne. Jeśli na przykład produkt nie został sprzedany w określonym przedziale czasu, możesz nadal wyświetlać produkt dla tego okresu z zerową wartością sprzedaży obok niego. Co więcej, obliczenia szeregów czasowych mogą być wykonywane najłatwiej, gdy dane są gęste w wymiarze czasowym. Dzieje się tak, ponieważ gęste dane wypełniają spójną liczbę wierszy dla każdego okresu, co z kolei ułatwia korzystanie z analitycznych funkcji okienkowania z fizycznymi przesunięciami. Zagęszczanie danych jest procesem przekształcania rzadkich danych w gęstą formę. Aby przezwyciężyć problem niesprawności, można użyć podzielonego na partycje sprzężenia zewnętrznego, aby wypełnić luki w szeregu czasowym lub dowolnym innym wymiarze. Takie połączenie rozszerza konwencjonalną składnię zewnętrznego sprzężenia poprzez zastosowanie zewnętrznego sprzężenia do każdej logicznej partycji zdefiniowanej w zapytaniu. Oracle logicznie dzieli wiersze w zapytaniu na podstawie wyrażenia określonego w klauzuli PARTITION BY. Wynikiem podzielonego na partycje sprzężenia zewnętrznego jest UNION z zewnętrznych sprzężeń każdej z partycji w logicznie podzielonym stole z tabelą po drugiej stronie łączenia. Pamiętaj, że możesz użyć tego rodzaju łączenia, aby wypełnić luki w dowolnym wymiarze, a nie tylko w wymiarze czasu. Większość przykładów tutaj koncentruje się na wymiarze czasu, ponieważ jest to wymiar najczęściej używany jako podstawa porównań. Partition Join Syntax Składnia partycjonowanego sprzężenia zewnętrznego rozszerza klauzulę JINU ANSI SQL o wyrażenie PARTYCJA BY, po którym następuje lista wyrażeń. Wyrażenia na liście określają grupę, do której stosowane jest połączenie zewnętrzne. Poniżej przedstawiono dwie formy składni zwykle używane do partycjonowanego sprzężenia zewnętrznego: Należy zwrócić uwagę, że funkcja FULL OUTER JOIN nie jest obsługiwana przez partycjonowane sprzężenie zewnętrzne. Sample of Sparse Data A typi cal situation with a sparse dimension is shown in the following example, which computes the weekly sales and year-to-date sales for the product Bounce for weeks 20-30 in 2000 and 2001: In this example, we would expect 22 rows of data (11 weeks each from 2 years) if the data were dense. However, we get only 18 rows because weeks 25 and 26 are missing in 2000, and weeks 26 and 28 in 2001. Filling Gaps in Data We can take the sparse data of the preceding query and do a partitioned outer join with a dense set of time data. In the following query, we alias our original query as v and we select data from the times table, which we alias as t. Here we retrieve 22 rows because there are no gaps in the series. The four added rows each have 0 as their Sales value set to 0 by using the NVL function. Note that in this query, a WHERE condition was placed for weeks between 20 and 30 in the inline view for the time dimension. This was introduced to keep the result set small. Filling Gaps in Two Dimensions N-dimensional data is typically displayed as a dense 2-dimensional cross tab of (n - 2) page dimensions. This requires that all dimension values for the two dimensions appearing in the cross tab be filled in. The following is another example where the partitioned outer join capability can be used for filling the gaps on two dimensions: In this query, the WITH subquery factoring clause v1 summarizes sales data at the product, country, and year level. This result is sparse but users may want to see all the country, year combinations for each product. To achieve this, we take each partition of v1 based on product values and outer join it on the country dimension first. This will give us all values of country for each product. We then take that result and partition it on product and country values and then outer join it on time dimension. This will give us all time values for each product and country combination. Filling Gaps in an Inventory Table An inventory table typically tracks quantity of units available for various products. This table is sparse: it only stores a row for a product when there is an event. For a sales table, the event is a sale, and for the inventory table, the event is a change in quantity available for a product. For example, consider the following inventory table: The inventory table now has the following rows: For reporting purposes, users may want to see this inventory data differently. For example, they may want to see all values of time for each product. This can be accomplished using partitioned outer join. In addition, for the newly inserted rows of missing time periods, users may want to see the values for quantity of units column to be carried over from the most recent existing time period. The latter can be accomplished using analytic window function LASTVALUE value. Here is the query and the desired output: The inner query computes a partitioned outer join on time within each product. The inner query densifies the data on the time dimension (meaning the time dimension will now have a row for each day of the week). However, the measure column quantity will have nulls for the newly added rows (see the output in the column quantity in the following results. The outer query uses the analytic function LASTVALUE. Applying this function partitions the data by product and orders the data on the time dimension column ( timeid ). For each row, the function finds the last non-null value in the window due to the option IGNORE NULLS. which you can use with both LASTVALUE and FIRSTVALUE. We see the desired output in the column repeatedquantity in the following output: Computing Data Values to Fill Gaps Examples in previous section illustrate how to use partitioned outer join to fill gaps in one or more dimensions. However, the result sets produced by partitioned outer join have null values for columns that are not included in the PARTITION BY list. Typically, these are measure columns. Users can make use of analytic SQL functions to replace those null values with a non-null value. For example, the following q uery computes monthly totals for products 64MB Memory card and DVD-R Discs (product IDs 122 and 136) for the year 2000. It uses partitioned outer join to densify data for all months. For the missing months, it then uses the analytic SQL function AVG to compute the sales and units to be the average of the months when the product was sold. If working in SQLPlus, the following two commands wraps the column headings for greater readability of results: Time Series Calculations on Densified Data Densificatio n is not just for reporting purpose. It also enables certain types of calculations, especially, time series calculations. Time series calculations are easier when data is dense along the time dimension. Dense data has a consistent number of rows for each time periods which in turn make it simple to use analytic window functions with physical offsets. To illustrate, let us first take the example on Filling Gaps in Data. and lets add an analytic function to that query. In the following enhanced version, we calculate weekly year-to-date sales alongside the weekly sales. The NULL values that the partitioned outer join inserts in making the time series dense are handled in the usual way: the SUM function treats them as 0s. Period-to-Period Comparison for One Time Level: Example How do we use this feature to compare values across time periods Specifically, how do we calculate a year-over-year sales comparison at the week level The following query returns on the same row, for each product, the year-to-date sales for each week of 2001 with that of 2000. Note that in this example we start with a WITH clause. This improves readability of the query and lets us focus on the partitioned outer join. If working in SQLPlus, the following command wraps the column headings for greater readability of results: In the FROM clause of the inline view densesales. we use a partitioned outer join of aggregate view v and time view t to fill gaps in the sales data along the time dimension. The output of the partitioned outer join is then processed by the analytic function SUM. OVER to compute the weekly year-to-date sales (the weeklyytdsales column). Thus, the view densesales computes the year-to-date sales data for each week, including those missing in the aggregate view s. The inline view yearoveryearsales then computes the year ago weekly year-to-date sales using the LAG function. The LAG function labeled weeklyytdsalesprioryear specifies a PARTITION BY clause that pairs rows for the same week of years 2000 and 2001 into a single partition. We then pass an offset of 1 to the LAG function to get the weekly year to date sales for the prior year. The outermost query block selects data from yearoveryearsales with the condition yr 2001. and thus the query returns, for each product, its weekly year-to-date sales in the specified weeks of years 2001 and 2000. Period-to-Period Comparison for Multiple Time Levels: Example While the prior example shows us a way to create comparisons for a single time level, it would be even more useful to handle multiple time levels in a single query. For example, we could compare sales versus the prior period at the year, quarter, month and day levels. How can we create a query which performs a year-over-year comparison of year-to-date sales for all levels of our time hierarchy We will take several steps to perform this task. The goal is a single query with comparisons at the day, week, month, quarter, and year level. The steps are as follows: We will create a view called cubeprodtime. which holds a hierarchical cube of sales aggregated across times and products . Then we will create a view of the time dimension to use as an edge of the cube. The time edge, which holds a complete set of dates, will be partitioned outer joined to the sparse data in the view cubeprodtime . Finally, for maximum performance, we will create a materialized view, mvprodtime. built using the same definition as cubeprodtime . For more information regarding hierarchical cubes, see Chapter 21, SQL for Aggregation in Data Warehouses. The materialized view is defined in Step 1 in the following section. Step 1 Create the hierarchical cube view The materialized view shown in the following may already exist in your system if not, create it now. If you must generate it, note that we limit the query to just two products to keep processing time short: Because this view is limited to two products, it returns just over 2200 rows. Note that the column HierarchicalTime contains string representations of time from all levels of the time hierarchy. The CASE expression used for the HierarchicalTime column appends a marker (0, 1. ) to each date string to denote the time level of the value. A 0 represents the year level, 1 is quarters, 2 is months, and 3 is day. Note that the GROUP BY clause is a concatenated ROLLUP which specifies the rollup hierarchy for the time and product dimensions. The GROUP BY clause is what determines the hierarchical cube contents. Step 2 Create the view edgetime, which is a complete set of date values edgetime is the source for filling time gaps in the hierarchical cube using a partitioned outer join. The column HierarchicalTime in edgetime will be used in a partitioned join with the HierarchicalTime column in the view cubeprodtime. The following statement defines edgetime : Step 3 Create the materialized view mvprodtime to support faster performance The materialized view definition is a duplicate of the view cubeprodtime defined earlier. Because it is a duplicate query, references to cubeprodtime will be rewritten to use the mvprodtime materialized view. The following materialized may already exist in your system if not, create it now. If you must generate it, note that we limit the query to just two products to keep processing time short. Step 4 Create the comparison query We have now set the stage for our comparison query. We can obtain period-to-period comparison calculations at all time levels. It requires applying analytic functions to a hierarchical cube with dense data along the time dimension. Some of the calculations we can achieve for each time level are: Sum of sales for prior period at all levels of time. Variance in sales over prior period. Sum of sales in the same period a year ago at all levels of time. Variance in sales over the same period last year. The following example performs all four of these calculations. It uses a partitioned outer join of the views cubeprodtime and edgetime to create an inline view of dense data called densecubeprodtime. The query then uses the LAG function in the same way as the prior single-level example. The outer WHERE clause specifies time at three levels: the days of August 2001, the entire month, and the entire third quarter of 2001. Note that the last two rows of the results contain the month level and quarter level aggregations. Note that to make the results easier to read if you are using SQLPlus, the column headings should be adjusted with the following commands. The commands will fold the column headings to reduce line length: Here is the query comparing current sales to prior and year ago sales: The first LAG function ( salespriorperiod ) partitions the data on gidp. cat. subcat. prod. gidt and orders the rows on all the time dimension columns. It gets the sales value of the prior period by passing an offset of 1. The second LAG function ( salessameperiodprioryear ) partitions the data on additional columns qtrnum. monnum. and daynum and orders it on yr so that, with an offset of 1, it can compute the year ago sales for the same period. The outermost SELECT clause computes the variances. Creating a Custom Member in a Dimension: Example In many analytical SQL tasks, it is helpful to define custom members in a dimension. For instance, you might define a specialized time period for analyses. You can use a partitioned outer join to temporarily add a member to a dimension. Note that the new SQL MODEL clause is suitable for creating more complex scenarios involving new members in dimensions. See Chapter 23, SQL for Modeling for more information on this topic. As an example of a task, what if we want to define a new member for our time dimension We want to create a 13th member of the Month level in our time dimension. This 13th month is defined as the summation of the sales for each product in the first month of each quarter of year 2001. The solution has two steps. Note that we will build this solution using the views and tables created in the prior example. Two steps are required. First, create a view with the new member added to the appropriate dimension. The view uses a UNION ALL operation to add the new member. To query using the custom member, use a CASE expression and a partitioned outer join. Our new member for the time dimension is created with the following view: In this statement, the view timec is defined by performing a UNION ALL of the edgetime view (defined in the prior example) and the user-defined 13th month. The gidt value of 8 was chosen to differentiate the custom member from the standard members. The UNION ALL specifies the attributes for a 13th month member by doing a SELECT from the DUAL table. Note that the grouping id, column gidt. is set to 8, and the quarter number is set to 5. Then, the second step is to use an inline view of the query to perform a partitioned outer join of cubeprodtime with timec. This step creates sales data for the 13th month at each level of product aggregation. In the main query, the analytic function SUM is used with a CASE expression to compute the 13th month, which is defined as the summation of the first months sales of each quarter. The SUM function uses a CASE to limit the data to months 1, 4, 7, and 10 within each year. Due to the tiny data set, with just 2 products, the rollup values of the results are necessarily repetitions of lower level aggregations. For more realistic set of rollup values, you can include more products from the Game Console and Y Box Games subcategories in the underlying materialized view. Miscellaneous Analysis and Reporting Capabilities This section illustrates the following additional analytic capabilities: WIDTHBUCKET Function For a given expression, the WIDTHBUCKET function returns the bucket number that the result of this expression will be assigned after it is evaluated. You can generate equiwidth histograms with this function. Equiwidth histograms divide data sets into buckets whose interval size (highest value to lowest value) is equal. The number of rows held by each bucket will vary. A related function, NTILE. creates equiheight buckets. Equiwidth histograms can be generated only for numeric, date or datetime types. So the first three parameters should be all numeric expressions or all date expressions. Other types of expressions are not allowed. If the first parameter is NULL. the result is NULL. If the second or the third parameter is NULL. an error message is returned, as a NULL value cannot denote any end point (or any point) for a range in a date or numeric value dimension. The last parameter (number of buckets) should be a numeric expression that evaluates to a positive integer value 0, NULL. or a negative value will result in an error. Buckets are numbered from 0 to ( n 1). Bucket 0 holds the count of values less than the minimum. Bucket( n 1) holds the count of values greater than or equal to the maximum specified value. WIDTHBUCKET Syntax The WIDTHBUCKET takes four expressions as parameters. The first parameter is the expression that the equiwidth histogram is for. The second and third parameters are expressions that denote the end points of the acceptable range for the first parameter. The fourth parameter denotes the number of buckets. Consider the following data from table customers. that shows the credit limits of 17 customers. This data is gathered in the query shown in Example 22-24 . In the table customers. the column custcreditlimit contains values between 1500 and 15000, and we can assign the values to four equiwidth buckets, numbered from 1 to 4, by using WIDTHBUCKET (custcreditlimit, 0, 20000, 4). Ideally each bucket is a closed-open interval of the real number line, for example, bucket number 2 is assigned to scores between 5000.0000 and 9999.9999. sometimes denoted 5000, 10000) to indicate that 5,000 is included in the interval and 10,000 is excluded. To accommodate values outside the range 0, 20,000), values less than 0 are assigned to a designated underflow bucket which is numbered 0, and values greater than or equal to 20,000 are assigned to a designated overflow bucket which is numbered 5 (num buckets 1 in general). See Figure 22-3 for a graphical illustration of how the buckets are assigned. Figure 22-3 Bucket Assignments You can specify the bounds in the reverse order, for example, WIDTHBUCKET ( custcreditlimit. 20000. 0. 4 ). When the bounds are reversed, the buckets will be open-closed intervals. In this example, bucket number 1 is ( 15000,20000 , bucket number 2 is ( 10000,15000 , and bucket number 4, is ( 0 ,5000 . The overflow bucket will be numbered 0 ( 20000. infinity ), and the underflow bucket will be numbered 5 (- infinity. 0 . It is an error if the bucket count parameter is 0 or negative. Example 22-24 WIDTHBUCKET The followin g query shows the bucket numbers for the credit limits in the customers table for both cases where the boundaries are specified in regular or reverse order. We use a range of 0 to 20,000. Linear Algebra Linear algebra is a branch of mathematics with a wide range of practical applications. Many areas have tasks that can be expressed using linear algebra, and here are some examples from several fields: statistics (multiple linear regression and principle components analysis), data mining (clustering and classification), bioinformatics (analysis of microarray data), operations research (supply chain and other optimization problems), econometrics (a nalysis of consumer demand data), and finance (asset allocation problems). Various libraries for linear algebra are freely available for anyone to use. Oracles UTLNLA package exposes matrix PLSQL data types and wrapper PLSQL subprograms for two of the most popular and robust of these libraries, BLAS and LAPACK. Linear algebra depends on matrix manipulation. Performing matrix manipulation in PLSQL in the past required inventing a matrix representation based on PLSQLs native data types and then writing matrix manipulation routines from scratch. This required substantial programming effort and the performance of the resulting implementation was limited. If developers chose to send data to external packages for processing rather than create their own routines, data transfer back and forth could be time consuming. Using the UTLNLA package lets data stay within Oracle, removes the programming effort, and delivers a fast implementation. Example 22-25 Linear Algebra Here is an example of how Oracles linear algebra support could be used for business analysis. It invokes a multiple linear regression application built using the UTLNLA package. The multiple regression application is implemented in an object called OLSRegression. Note that sample files for the OLS Regression object can be found in ORACLEHOMEplsqldemo . Consider the scenario of a retailer analyzing the effectiveness of its marketing program. Each of its stores allocates its marketing budget over the following possible programs: media advertisements ( media ), promotions ( promo ), discount coupons ( disct ), and direct mailers ( dmail ). The regression analysis builds a linear relationship between the amount of sales that an average store has in a given year ( sales ) and the spending on the four components of the marketing program. Suppose that the marketing data is stored in the following table: Then you can build the following sales-marketing linear model using coefficients: This model can be implemented as the following view, which refers to the OLS regression object: Using this view, a marketing program manager can perform an analysis such as Is this sales-marketing model reasonable for year 2004 data That is, is the multiple-correlation greater than some acceptable value, say, 0.9 The SQL for such a query might be as follows: You could also solve questions such as What is the expected base-line sales revenue of a store without any marketing programs in 2003 or Which component of the marketing program was the most effective in 2004 That is, a dollar increase in which program produced the greatest expected increase in sales See Oracle Database PLSQL Packages and Types Reference for further information regarding the use of the UTLNLA package and linear algebra. CASE Expressions Oracle now supports simple and searched CASE statements. CASE statements are similar in purpose to the DECODE statement, but they offer more flexibility and logical power. They are also easier to read than traditional DECODE statements, and offer better performance as well. They are commonly used when breaking categories into buckets like age (for example, 20-29, 30-39, and so on). The syntax for simple CASE statements is: Simple CASE expressions test if the expr value equals the comparisonexpr . The syntax for searched CASE statements is: You can use any kind of condition in a searched CASE expression, not just an equality test. You can specify only 65,535 arguments and each WHEN. THEN pair counts as two arguments. To avoid exceeding this limit, you can nest CASE expressions so that the returnexpr itself is a CASE expression. Example 22-26 CASE Suppose you wanted to find the average salary of all employees in the company. If an employees salary is less than 2000, you want the query to use 2000 instead. Without a CASE statement, you might choose to write this query as follows: Note that this runs against the hr sample schema. In this, foo is a function that returns its input if the input is greater than 2000, and returns 2000 otherwise. The query has performance implications because it needs to invoke a function for each row. Writing custom functions can also add to the development load. Using CASE expressions in the database without PLSQL, this query can be rewritten as: Using a CASE expression lets you avoid developing custom functions and can also perform faster. Example 22-27 CASE for Aggregating Independent Subsets Using CASE inside aggregate functions is a convenient way to perform aggregates on multiple subsets of data when a plain GROUP BY will not suffice. For instance, the preceding example could have included multiple AVG columns in its SELECT list, each with its own CASE expression. We might have had a query find the average salary for all employees in the salary ranges 0-2000 and 2000-5000. It would look like: Although this query places the aggregates of independent subsets data into separate columns, by adding a CASE expression to the GROUP BY clause we can display the aggregates as the rows of a single column. The next section shows the flexibility of this approach with two approaches to creating histograms with CASE . Creating Histograms You can use the CASE statement when you want to obtain histograms with user-defined buckets (both in number of buckets and width of each bucket). The following are two examples of histograms created with CASE statements. In the first example, the histogram totals are shown in multiple columns and a single row is returned. In the second example, the histogram is shown with a label column and a single column for totals, and multiple rows are returned. Example 22-28 Histogram Example 1 Example 22-29 Histogram Example 2 Frequent Itemsets Instead of counting how often a given event occurs (for example, how often someone has purchased milk at the grocery), you may find it useful to count how often multiple events occur together (for example, how often someone has purchased both milk and cereal together at the grocery store). You can count these multiple events using what is called a frequent itemset, which is, as the name implies, a set of items. Some examples of itemsets could be all of the products that a given customer purchased in a single trip to the grocery store (commonly called a market basket), the web pages that a user accessed in a single session, or the financial services that a given customer utilizes. The practical motivation for using a frequent itemset is to find those itemsets that occur most often. If you analyze a grocery stores point-of-sale data, you might, for example, discover that milk and bananas are the most commonly bought pair of items. Frequent itemsets have thus been used in business intelligence environments for many years, with the most common one being for market basket analysis in the retail industry. Frequent itemset calculations are integrated with the database, operating on top of relational tables and accessed through SQL. This integration provides the following key benefits: Applications that previously relied on frequent itemset operations now benefit from significantly improved performance as well as simpler implementation. SQL-based applications that did not previously use frequent itemsets can now be easily extended to take advantage of this functionality. Frequent itemsets analysis is performed with the PLSQL package DBMSFREQUENTITEMSETS. See Oracle Database PLSQL Packages and Types Reference for more information. In addition, there is an example of frequent itemset usage in Frequent itemsets . Scripting on this page enhances content navigation, but does not change the content in any way. SQL Correlated Subquery Summary . in this tutorial, you will learn about the SQL correlated subquery . which is a subquery that depends on the outer query. This tutorial requires a good knowledge of subquery. If you don8217t know anything about the subquery. check it out the subquery tutorial before moving forward with this tutorial. Introduction to SQL correlated subquery A correlated subquery is a subquery that depends on the outer query. It means that the WHERE clause of the correlated subquery uses the data of the outer query. The main difference between a correlated subquery and a non-correlated subquery is that you cannot execute a correlated subquery alone like a non-correlated subquery. In addition, a correlated subquery executes once for each selected row from the outer query. A correlated subquery is also known as repeating subquery or synchronized subquery. SQL correlated subquery examples Lets take a look at some examples to understand the idea of the correlated subquery. SQL correlated subquery in the SELECT clause example The following query selects top five customers by sales: