Przemysław Kantyka
Starszy specjalista ds. szkoleń Oracle / Konsultant Oracle / Trener Oracle / Specjalista Oracle
Wypowiedzi
-
Wpis na blogu na temat pierwszego, drugiego i n-tego rekordu pokazuje tez alternatywną metodę osiągnięcia wyniku: http://blog.dataconsulting.pl/2012/03/pierwszy-drugi-n...
-
Aby mieć możliwość przeczytania tego posta musisz być członkiem grupy EXCEL w praktyce
-
Ogólny opis na blogu:
http://blog.dataconsulting.pl/2012/03/pierwszy-drugi-n...
Czyli u Ciebie będzie:
SELECT klient, pierwszy, pierwszy_data, drugi, drugi_data FROM (
SELECT
klient,
row_number() OVER w lp,
first_value(sklep) OVER w pierwszy,
first_value(data_transakcji) OVER w pierwszy_data,
nth_value(sklep, 2) OVER w drugi,
nth_value(data_transakcji, 2) OVER w drugi_data,
FROM
transakcje t
WINDOW w AS (PARTITION BY klient ORDER BY data_transakcji DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )) q WHERE lp = 1
PozdrawiamPrzemysław Kantyka edytował(a) ten post dnia 28.03.12 o godzinie 22:59
-
create table clients (client_id serial);
create table products (product_id serial, name text);
create table invoices (invoice_id serial, client_id int);
create table invoices_rows(invoice_row_id serial, invoice_id int, product_id int, price double precision);
insert into clients values (1);
insert into clients values (2);
insert into products values (1, 'Produkt A');
insert into products values (2, 'Produkt B');
insert into products values (3, 'Produkt C');
insert into invoices values (100, 1);
insert into invoices values (200, 2);
insert into invoices_rows values (1000, 100, 1, 100);
insert into invoices_rows values (1001, 100, 2, 101);
insert into invoices_rows values (1002, 100, 3, 103);
insert into invoices_rows values (1003, 200, 1, 103);
insert into invoices_rows values (1004, 200, 2, 102);
insert into invoices_rows values (1005, 200, 3, 101);
insert into invoices_rows values (1006, 200, 3, 102);
select invoice_id, client_id, min(price), min(pierwsza_cena) as pierwsza_cena from (
select i.invoice_id, client_id, price, first_value(price) over (partition by client_id order by i.invoice_id) as pierwsza_cena
from invoices i, invoices_rows r
where i.invoice_id = r.invoice_id and product_id = 3
) as q group by invoice_id, client_id;
Pozdrawiam
-
Aleksander Olszewski:
Przemysław Kantyka:
OFFTOP:
Aleksander Olszewski:
[...]
Potrzebujemy NIP, PESEL, Imię, Nazwisko/Nazwa, Adres zamieszkania/Siedziby, być może numer telefonu, ewentualnie e-mail oraz wszystko to co się znajdzie w fakturze. Po normalizacji dostaniemy relacje oraz klucze główne/klucze obce, które są już indeksowane i uwaga: nie trzeba na nie zakładać indeksów, bo będą się dublować.
Warto zaznaczyć że to jest prawda o ile fiksujemy się na MySQL'u bo np. w Oracle już niekoniecznie. Ponieważ Pan opisuje wysoki poziom abstrakcji warto wspomnieć, że Pana uwaga dotyczy tylko konkretnej technologii.
Mam na myśli wizualne modelowanie w narzędziu CASE. Tak więc wszystko jedno do jakiej technologii to wszystko będzie później przenoszone. Jak CASE do MySQL nie obsłuży np. ORACLE, to ORACLE ma CASE z reverse engineering. Najgłówniejsze aby model był "ogarnięty".
OFFTOP:
Chyba się nie zrozumieliśmy - mnie chodzi o to, że w Oracle klucze obce nie są domyślnie indeksowane, a Pan napisał, że nie trzeba ich zakładać. W MySQL to prawda, ale w Oracle (jeśli narzędzie tego nie dopilnuje) stworzy Pan w ten sposób nieoptymalny system. Dodałem zatem, że Pana uwaga tyczy się już konkretnej implementacji, lub (to dodaję teraz) uwzględnia narzędzie które samo tego pilnuje.
Z tym "wszystko jedno do jakiej technologii" - takie stwierdzenie zawsze zapala u mnie w mózgu żółte światełko. Na pewnym poziomie abstrakcji to oczywiście prawda, ale zazwyczaj diabeł tkwi w szczegółach - np. wspomniane indeksowanie kluczy.
Pozdrawiam
-
W tej pracy widzę dwa wzywania, nad którymi należałoby się skupić tak aby możliwie wcześnie przeanalizować sposób ich implementacji. Problemy to routing oraz wizualizacja 3d.
Do wizualizacji 2d masz gotowy komponent w ADF (ADF Geographic Map) jednak uzyskanie efektu 3d nie będzie proste., nawet tylko w odniesieniu do budynków - może jednym z rozwiązań jest uzyskanie izometrycznego rzutu mapy i samodzielne dodanie warstwy 3d. Dokument o Geographic Map do poczytania: http://download.oracle.com/otndocs/products/spatial/pd... oraz całość dokumentacji http://www.oracle.com/technetwork/middleware/mapviewer...
Co do routingu - jeśli nie chcesz samodzielnie implementować algorytmów routingu a musisz wykorzystać Oracle to poczytaj o Oracle Spatial Routing Engine (http://docs.oracle.com/cd/B28359_01/appdev.111/b28400/....
PozdrawiamPrzemysław Kantyka edytował(a) ten post dnia 24.03.12 o godzinie 11:44
-
OFFTOP:
Aleksander Olszewski:
[...]
Potrzebujemy NIP, PESEL, Imię, Nazwisko/Nazwa, Adres zamieszkania/Siedziby, być może numer telefonu, ewentualnie e-mail oraz wszystko to co się znajdzie w fakturze. Po normalizacji dostaniemy relacje oraz klucze główne/klucze obce, które są już indeksowane i uwaga: nie trzeba na nie zakładać indeksów, bo będą się dublować.
Warto zaznaczyć że to jest prawda o ile fiksujemy się na MySQL'u bo np. w Oracle już niekoniecznie. Ponieważ Pan opisuje wysoki poziom abstrakcji warto wspomnieć, że Pana uwaga dotyczy tylko konkretnej technologii.Przemysław Kantyka edytował(a) ten post dnia 23.03.12 o godzinie 14:21
-
O Oracle Spatial dużo jest w dokumentacji: http://www.oracle.com/technetwork/database/options/spa...
Warto dodać, że do Twoich celów musisz dysponować Oracle w edycji Enterprise. Taki np. Oracle XE posiada jedynie podzbiór funkcjonalności w postaci Oracle Locator.
Pytanie - czy planujesz trzymać w bazie dane geograficzne czy przestrzenne? Czy aplikacja ma generować wizualizacje samodzielnie czy w grę wchodzi eksport danych i ich wykorzystanie w zewnętrznym narzędziu renderującym? Pytam bo zależnie od rodzaju wizualizowanych danych w grę mogą wchodzić albo narzędzia zewnętrzne renderujące mapę z danych, albo narzędzia rysujące obiekty 3d.
-
Najlepiej to wyjaśnić jeśli NULL nie tyle uznamy za wartość nieokreśloną, a za wartość nieznaną (czytaj NULL jako NIEWIADOMOCO). Problem dotyka w szczególności zapytań NOT IN z podzapytaniami. Szerzej odpisałem Ci na blogu:
http://blog.dataconsulting.pl/2012/03/wartosc-null-i-p...
Pozdrawiam
-
Zapraszam do darmowego kursu Oracle APEX online, który nauczy Cię tworzenia internetowych aplikacji bazodanowych w oparciu o bazę Oracle. Dowiesz się także jak tworzyć raporty, formularze, wykresy i inne składniki interfejsu użytkownika.
http://blog.dataconsulting.pl/darmowy-kurs-oracle-apex...
-
Czy zbyt duży bufor danych (BUFFER CACHE) w Oracle może spowodować spowolnienie systemu?
http://blog.dataconsulting.pl/2012/03/latch-cache-buff...- 8.03.2012, 17:34
-
Nowe wpisy na blogu: dynamiczna liczba kolumn w tabelach przestawnych w Oracle http://blog.dataconsulting.pl/2012/03/dynamiczna-liczb...
- 7.03.2012, 11:25
-
Dynamiczna liczba kolumn w tabelach przestawnych w Oracle
http://blog.dataconsulting.pl/2012/03/dynamiczna-liczb...- 6.03.2012, 20:36
- Skomentuj
-
Witam:
Jeśli chodzi o konieczność deklaracji zmiennej lCount - mamy różne ustawienie. U mnie opcja Explicit jest wyłączona. Pan zapewne ma Option Explicit.
Co do błędu wywołania - w poprzedniej wersji kodu znajdował się bug, który wymagał, aby zaznaczył Pan obszar wynikowy o ilości kolumn nie większej jak zakres danych źródłowych. Pod takim warunkiem funkcja działała poprawnie (proszę starą wersję wywołać zaznaczając obszar G1:H7. Dziękuję bardzo za wskazanie tego błędu.
Zamiat
ReDim Result(rRange.Rows.Count, 1)
powinno być
ReDim Result(Application.Caller.Rows.Count, 1)
Poprawna treść kodu:
Function InCollection(col As Collection, key As String) As Boolean
Dim var As Variant
Dim errNumber As Long
InCollection = False
Set var = Nothing
Err.Clear
On Error Resume Next
var = col.Item(key)
errNumber = CLng(Err.Number)
On Error GoTo 0
If errNumber = 5 Then
InCollection = False
Else
InCollection = True
End If
End Function
Function GroupBy(rRange As Range, iCol As Long) As Variant
Dim RowNdx, ColNdx As Long
Dim Result() As Variant
Dim Data As Collection
Dim Keys As Collection
Dim tmp As Variant
ReDim Result(Application.Caller.Rows.Count, 1)
Set Data = New Collection
Set Keys = New Collection
lCount = 0
For RowNdx = 1 To rRange.Rows.Count
If InCollection(Data, rRange(RowNdx, 1)) Then
tmp = Data(rRange(RowNdx, 1))
Data.Remove rRange(RowNdx, 1)
Data.Add tmp & "," & rRange(RowNdx, iCol), rRange(RowNdx, 1)
Else
Keys.Add rRange(RowNdx, 1)
Data.Add rRange(RowNdx, iCol), rRange(RowNdx, 1)
End If
Next RowNdx
RowNdx = 0
For Each tmp In Keys
Result(RowNdx, 0) = tmp
Result(RowNdx, 1) = Data(tmp)
RowNdx = RowNdx + 1
Next tmp
For RowNdx = RowNdx To Application.Caller.Rows.Count
Result(RowNdx, 0) = ""
Result(RowNdx, 1) = ""
Next RowNdx
GroupBy = Result
End Function
Wywołanie funkcji wymaga przeznaczenia 2 kolumn i dowolnej liczby wierszy na wynik.
Wynik:
Optymalizację tej funkcji proponuję przedyskutować kiedyś przy piwie :)
Pozdrawiam
-
Poniżej treść funkcji, która realizuje grupowanie w przekazanym zakresie po pierwszej kolumnie, jednocześnie realizując funkcjonalność WM_CONCAT na kolumnie, której numer przekazano w drugim argumencie. Nie wymaga sortowania wstępnego rekordów (wykorzystuje hashowanie elementów). Na pewno rozwiązanie to jest nietrywialne i funkcję da się zoptymalizować, jednak moim celem było w miarę przejrzyste pokazanie, że to się da zrobić:
Function InCollection(col As Collection, key As String) As Boolean
Dim var As Variant
Dim errNumber As Long
InCollection = False
Set var = Nothing
Err.Clear
On Error Resume Next
var = col.Item(key)
errNumber = CLng(Err.Number)
On Error GoTo 0
If errNumber = 5 Then
InCollection = False
Else
InCollection = True
End If
End Function
Function GroupBy(rRange As Range, iCol As Long) As Variant
Dim RowNdx, ColNdx As Long
Dim Result() As Variant
Dim Data As Collection
Dim Keys As Collection
Dim tmp As Variant
ReDim Result(rRange.Rows.Count, 1)
Set Data = New Collection
Set Keys = New Collection
lCount = 0
For RowNdx = 1 To rRange.Rows.Count
If InCollection(Data, rRange(RowNdx, 1)) Then
tmp = Data(rRange(RowNdx, 1))
Data.Remove rRange(RowNdx, 1)
Data.Add tmp & "," & rRange(RowNdx, iCol), rRange(RowNdx, 1)
Else
Keys.Add rRange(RowNdx, 1)
Data.Add rRange(RowNdx, iCol), rRange(RowNdx, 1)
End If
Next RowNdx
RowNdx = 0
For Each tmp In Keys
Result(RowNdx, 0) = tmp
Result(RowNdx, 1) = Data(tmp)
RowNdx = RowNdx + 1
Next tmp
For RowNdx = RowNdx To Application.Caller.Rows.Count
Result(RowNdx, 0) = ""
Result(RowNdx, 1) = ""
Next RowNdx
GroupBy = Result
End Function
Wywołanie: jako funkcję zwracającą tablicę =GroupBy(zakres, nr_kolumny)
Skleja jedną kolumnę (dla uproszczenia) - aby skleić ich kilka trzeba jeszcze wykonać dodatkową konkatenację.
A więc da się funkcjonalność WM_COCNAT uzyskać zarówno z użyciem filtrów, sortowania i formuł jak i UDF w VBA. Jak Pan widzi funkcja zmniejsza ilości wierszy wyniku względem danych źródłowych.
Temat uważam za zamknięty
PozdrawiamPrzemysław Kantyka edytował(a) ten post dnia 06.03.12 o godzinie 13:47
-
Wojciech Gardziński:
Reasumując
1. Rozwiązanie Excelowe - zrobione dla 7 wierszy mojego przykładu, ale, że tak powiem bardzo nieelegancko. Ponieważ jednak wymaga każdorazowej ingerencji użytkownika w proces - 0 punktów
Zrobił Pan rozwiązanie:
1. Bardzo SKOMPLIKOWANE
Ooo. Mamy progres. Już nie, że się nie da, tylko da się, ale nieelegancko... Zwracam uwagę, że:
Funkcje JEŻELI można ograniczyć. Napisałem to rozwlekle dla większej czytelności.
Do tego Pana rozwiązanie:
1. Również wymaga co najmniej kilku kliknięć i interakcji z użytkownikiem, wcześniejszego sformatowania danych w odpowiedni zakres komórek i przesyłania danych do bazy i z powrotem. Baza po drodze musi te dane przejrzeć dla każdego wiersza i na poziomie każdego z nich zrealizować algorytm, który ja wykonałem tylko i wyłącznie z użyciem funkcjonalności dostępnej w Excel'u (tu uwaga - ja wykonałem grupowanie z użyciem sortowania, a np. Oracle zrobi podczas grupowania tylko haszowanie). Nie twierdzę, że Excel (i funkcja JEŻELI) będzie szybszy (chociaż MySQL bez indeksów też się będzie musiał namęczyć, dodatkowo dochodzi tutaj przesyłanie danych protokołem sieciowym). Jak zależy nam na szybkości i chcemy koniecznie mieć bazę SQL to zamiast MySQL na odległym serwerze lepiej użyć np. jakiejś bazy typu SQLite + sterownik ODBC na naszym komputerze. SQLite ma też (w przeciwieństwie do tego co Pan twierdzi) funkcję group_concat - zachęcam do poczytania.
Pisze Pan, że rozwiązanie jest skomplikowane. Jeśli użycie sortowania, kilku formuł i filtra nazywa Pan skomplikowanym to chyba Pan żartuje.
Wię, jednym słowem, każe Pan użytkownikowi najpierw posortować, potem wpisać funkcje, potem użyć autofiltra. Automatyzacja, że hej!
Oczywiście można całość "zwinąć" w jedno kliknięcie przy użyciu makra. I niech Pan nie udaje, że AFIN.NET to coś innego niż właśnie takie (jeszcze bardziej opasłe) makro.
2. OGRANICZONE ilością wierszy Excela
Czyli Pan przed kliknięciem magicznego przycisku wysyłającego dane do bazy i z powrotem ma opracowany sposób na obejście ograniczenia ilości wierszy przez Excela?
3. WYMAGAJĄCE każdorazowego użycia autofiltra
Natomiast Pana rozwiązanie każdorazowo wymaga użycia makra i wtyczki, którą trzeba wcześniej zainstalować. Do tego wymaga uruchomionej i skonfigurowanej bazy danych.
2. Rozwiązania VBA się nie (jeszcze) doczekaliśmy
Nie wypada chyba, żebym uczył "Trenera MS Excel" pisać funkcji w VBA. Proszę wskazać jaką przeszkodę techniczną widzi Pan w zaproponowanym przeze mnie rozwiązaniu to będziemy dyskutować merytorycznie.
3. ad mojej uwagi, że tylko MySQL - ok., sprawdzę, na razie punkt dla Pana, bo nie znam w tej chwili odpowiedzi. Na pewno jednak nie zadziała ani w Excelu, ani w Accessie.
Ale, nawet gdyby zadziałało, przyjmijmy nawet, że tak jest!... to w niczym to nie umniejsza sensu mojego przykładu. Mając dane w Excelku, WARTO, wysłać je do innej bazy, by zrobić takie agregacje.
Szanowny Panie. Proszę czytać uważnie i bez uprzedzeń (bo mam wrażenie, że poczuł się Pan dotknięty moją krytyką). Ja napisałem, że pomysł przetwarzania danych przy użyciu bazy jest dobry (polecam do tego w szczególności Oracle;)), ale napisanie, że nie da się zadania, które Pan przedstawił, zrealizować inaczej, wywołało uzasadnioną krytykę tego arbitralnego (i fałszywego) twierdzenia.
No to 1:1. :)
Czekam na rozwiązanie viżualbejzikowe.
Ja nie gram na punkty. Podałem metodę jaką ja bym to napisał. Range jako argument funkcji, która przejrzy ten zakres i zwróci tablicę po pogrupowaniu - proszę spróbować. Wierzę, że VBA jest Panu nieobcy, ale jak się nie uda, proszę napisać gdzie jest bariera nie do przeskoczenia. Coś poradzimy ;)
PS. Kaszanka o której Pan pisze, wynika z (celowego jak mniemam) pominięcia sortowania danych na początku. Sortowanie (jak Pan zapewne wie) to w Excel'u funkcjonalność wbudowana i dostępna pod jednym kliknięciem.Przemysław Kantyka edytował(a) ten post dnia 05.03.12 o godzinie 22:04
-
Bycie pewnym siebie to dobra cecha, o ile faktycznie ma się rację i nie przekracza się granicy:
a) Zakładając, że dane mamy wstawione do zakresu A1:E7 sortujemy je po pierwszej kolumnie. Poniżej podaję wartości formuł do wstawienia:
w komórce A1: =A2
w komórce F2: =JEŻELI(A2=A1;F1&JEŻELI(F1<>"";F1&",";"")&B2&","&C2&","&D2&","&E2;B2&","&C2&","&D2&","&E2)
Formułę przeciągamy do końca zestawu danych - komórki F7
Formuła ta wstawia do komórki wiersz poprzedni i bieżący pod warunkiem, że cały czas jesteśmy w tej samej "grupie" ID.
w komórce G2: JEŻELI(A2<>A3;F2;"")
Formułę przeciągamy do końca zestawu danych - komórki G7
Formuła ta wstawia do komórki wartość "posumowanego" tekstu na granicy grup ID, oraz wartość pustą w środku "grupy".
"Jak Pan się zapewne domyśla, podpuszczam Pana, bo ja wiem, że nie da się tego zrobić ani formułami, ani funkcją VBA, bo ani formuły, ani funkcje VBA nie mają możliwości zmniejszenia ilości wierszy wyniku względem danych źródłowych." - no to proszę - definiujemy standardowy filtr danych i wywalamy te wiersze dla których wartość w kolumnie G jest niepusta.
Efekt do pobrania z http://blog.dataconsulting.pl/wp-content/uploads/2012/... Można sobie ładnie wynik np. skopiować.
b) "ani funkcje VBA nie mają możliwości zmniejszenia ilości wierszy wyniku względem danych źródłowych' - a funkcje otrzymujące w argumencie zakres danych (Range) + zwracające tablice?
"Nieprawda. Wiem, że w jakimś tam DODATKU do Oracle'a też jest podobna funkcja WM_CONCAT (podaję za przykładem z grupy Oracle'owej), ale w MS JET SQL na pewno nie ma (Jeżeli się jednak mylę, to proszę, żeby ktoś mnie naprostował!)" - z całym szacunkiem ale na MS JET SQL świat się nie kończy (osobiście nie specjalizuję się w tym dialekcie). Ale w innych bazach niż MySQL:
- Oracle: WM_CONCAT jest i owszem, ale jest też LISTAGG oraz kilka innych metod uzyskania tego wyniku - do poczytania na http://blog.dataconsulting.pl/2012/03/implementacja-li...
- MS SQL Server pozwala uzyskać ten wynik poprzez FOR XML: http://stackoverflow.com/questions/1621747/concat-fiel...
- PostgreSQL: Wprost dostępna funkcja string_aggPrzemysław Kantyka edytował(a) ten post dnia 05.03.12 o godzinie 20:21
-
Pomysł angażowania akurat do takiej sprawy zewnętrznego serwera MySQL wydaje mi się absurdalny. Można to dużo prościej zrobić:
a) po prostemu i Excel'owemu - kilka dodatkowych kolumn z odpowiednimi formułami (na upartego nawet 1 na wynik :) ) + standardowe filtrowanie i wynik gotowy
b) po skomplikowanemu - UDF w VBA i na moje oko powinniśmy spokojnie uzyskać GROUP_CONCAT w Excelu.
"tylko MySQL dysponuje funkcją GROUP_CONCAT" - to jest po prostu nieprawda. Gdzie indziej inaczej się nazywa, ale też jest. To chyba był skrót myślowy, że Excel tego nie ma "by default".
Natomiast pomysł ze stosowaniem zewnętrznych baz danych jak najbardziej ok.
Reasumując - wiem, że się czepiam bo chodziło o przykład. Pomysł do pewnych zastosowań ok, ale przykład nietrafiony.
-
Kamil Stawiarski:
Od 11g można użyć faktycznie LISTAGG(kolumna,'separator') WITHIN GROUP (order by costam) - PIVOT zresztą też jest dopiero od 11g.
A jak szaleć to szaleć! Wpis na bloga "LISTAGG w Oracle 10g i 9i, oraz WM_CONCAT i inne metody konkatenacji tekstów w trakcie agregacji": http://blog.dataconsulting.pl/2012/03/implementacja-li...
jednak odpowiednie przechwycenie tego do kolekcji będzie wymagało, tak czy inaczej, pewnych statycznych definicji.
Z tym jednak nie mogę się do końca zgodzić - jesteś w stanie napisać kod PL/SQL'a który wyciągnie dowolny wynik z zapytania bez kolekcji i statycznych definicji - np. w pętli kolejno kolumnami i wierszami po jednej wartości. Ale to już na blogu innym razem ;)
Autorko - wybacz off top.Przemysław Kantyka edytował(a) ten post dnia 02.03.12 o godzinie 00:49
-
Warto pamiętać, że niezależnie od tego czy szkolenie jest autoryzowane czy nie, prawdziwą jakość tworzy zawsze trener. Bywają szkolenia słabe autoryzowane i rewelacyjne nieautoryzowane. Trzeba oczywiście oddać honor, że Oracle ma pewne kryteria wyboru trenerów, ale z uwagi na fakt, że wiedza merytoryczna czasem nie chodzi w parze z zaangażowaniem lub po prostu umiejętnościami metodycznymi to i tak najważniejsza jest osoba prowadząca: jej podejście, doświadczenie, wiedza. Zatem - postaraj się dowiedzieć kim jest osoba prowadząca.Przemysław Kantyka edytował(a) ten post dnia 01.03.12 o godzinie 22:06
- 1
- 2
- 3
- Następna »
