EXCEL: wyszukaj pionowo…

SQL i EXCEL to niejako rewers i awers.

Z różnych możliwości dzisiaj Marcinie patent na łączenie danych.

Kto wymyślił coś takiego, nie mam pojęcia, ale pewno musiał dostawać różne tabelki i szybko je łączyć, robić z danych  wydruki, korespondencję seryjną, to wymyślił WYSZUKAJ PIONOWO.

I obrazek oraz plik excel pokazują jak to działa.

Należy wskazać w nawiasie:

1. w zestawieniu 1 pole, które zawiera wartość szukaną w zestawieniu 2 (musi być w pierwszym polu zestawienia 2),
2.  następnie definicja całego zestawienia 2 poprzez wskazanie narożników obszaru (lewy górny róg : prawy dolny róg)
3. teraz z którego pola zestawienia 2 ma się pobrać wartość co przedłużonego zestawienia 1,
4. fałsz, ta klauzula jest ważna, ponieważ tylko dokładne dopasowania są brane pod uwagę, jeżeli nie ma tej klauzuli, to różne podobne będą podkładane, nie polecam.

I jest, i od razu widać, że w zestawieniu 2 nie ma danych dla umowy o ID A103. Nie wszystko musi być przy pomocy SQL. Nie próbuj Marcinie otwierać drzwi kwerendą, prościej wstać i tradycyjnie to zrobić. 

https://drive.google.com/folderview?id=0B0DNBH1DOPAfNUhsUTloNG1zb3c&usp=sharing

Jak używamy operator IN, jak to zadziała fajnie na Oracle, a jak nie na MS SQL...

Operator IN przydaje się, jak nie szukamy jednego konkretnego rekordu, ale wielu, których pewna wartość jest w zbiorze. Na przykład: znajdź wszystkie produkty w sklepie, które są marki, sony lub iphone. 

Select * from produkty where marka in ('sony' , 'iphone') ;

Właśnie dlatego, że więcej niż jednego producenta, to trzeba użyć operatora IN i w nawiasach podać listę wartości. Na bazie ORACLE lista może być, jak to powiedzieć: dwupolowa, a nawet więcej polowa. Gdybyśmy chcieli znaleźć najdroższe produkty wyżej wymienionych marek, to można to uzyskać w następujący sposób.

Select * from produkty

where (marka, cena)

in

(select marka, max(cena)

       from produkty

       where marka in ('sony' , 'iphone') group by marka ) ;

Ten drugi patent nie zadziała na MS SQL Serwerze. To zatem jak? Pokazuję na przykładzie tabeli z cenami mieszkań, w latach, kwartałach, miastach, dane z: https://www.nbp.pl/publikacje/rynek_nieruchomosci/ceny_mieszkan.xls

I chcę wydobyć dane o mieście, roku, kwartale dla każdego maksa w danym roku.

W tej lokalizacji jest plik do załadowania danych oraz plik z rozwiązaniem ww. problemu. 

https://drive.google.com/folderview?id=0B0DNBH1DOPAfV0txWUF1Qm5iYU0&usp=sharing

Jak wynik zapisać w nowej tabeli na MS SQL Serverze.

Marcinie,

Nieco cicho siedzę, bo zmieniłem orientację… na MS SQL Server. System bazodanowy ORACLE mnie porzucił, stąd zmiana. 

I jak każdy widzi, oczywista oczywistość, wynik zapytania, to jak nie spojrzeć to tabela. Wynik można sobie skopiować i zapisać, ale jak to zapisać na bazie, jako nowa tabela? Bo to się opłaca, jeżeli się chce często sięgać do wyniku, a jego pozyskanie jest czasochłonne i nie wymaga częstej aktualizacji.

W ORACLE jest moim zdaniem intuicyjnie, bardzo blisko sposobu tworzenia tabeli, trzeba tak:

CREATE TABLE nowa_tabela as SELECT….

I ten nasz wynik zapytania będzie w nowej tabeli i tyle, takie proste. Tak to nie działa na MS SQL Serverze :-)

Trzeba sobie napisać:

SELECT ………….. INTO NOWA_TABELA FROM ……..

Czyli wybierz do nowej tabeli z .... Pomino,  że definiowanie tabel działa tak samo jak na Oracle.

Sprawdziłem to zadziałało. Obrazki to dowodzą.

Pozdrawiam,

W. 

CASE – WYSYŁKA OFERTY PRENUMERATY, POST NR 5

Marcinie, 

Nad użyciem słowa "case" w tytule ubolewam, taka refleksja na początek. Jednak stało się, trudno, z tym trzeba żyć, przy okazji może poprawię na "przykład".

Znowu "tylko" będzie. Dzisiaj wklejam kwerendę, bardzo krótka, ale jej wynik przeklejam do excel i bawię się tabelą przestawną, mam już całkiem spore możliwości badania problemu skuteczności wysyłek. Polecam przeklinać udostępniony arkusz w excel. Jakie "czary" można uzyskać widać na obrazkach. 
Szersze opisy będą wkrótce :-)

Kwerenda:

  select

    dn_od_wysylki

  , plec

  , wojewodztwo

  , sum(zn_prenumeraty_kum) prenumeraty_skum

  , convert(int, sum (zlicz) ) l_obserwacji

 

   from KOSTKA

   group by

     dn_od_wysylki

     , plec

     , wojewodztwo

   order by 1

   ;

Linka do arkusza z tabelą przestawną:

https://drive.google.com/file/d/0B0DNBH1DOPAfSUd3QVlleVJYdUU/view?usp=sharing

 

CASE – WYSYŁKA OFERTY PRENUMERATY, POST NR 4

Utworzyłem dwa widoki, bo tak łatwiej.

Widok STANY - bo jak chcę zliczyć prenumeraty, to muszę de facto sprawdzić każdego klienta, jego zachowanie każdego dnia od dnia wysyłki.

W tabeli z wysyłkami mam tylko jedną informację, a widok STANY ułatwia mi dalszą pracę.

Drugi widok to KOSTKA danych, do każdego wiersza w widoku STANY uzupełniam informację o dane z tabeli WYSYLKA_1, dopisuję dane surowe i przetworzone nieco.

W ten sposób KOSTKA ma informację o każdym klienci, każdego dnia od dnia wysyłki i dalej.

Powstała KOSTKA dzienna danych, zgadza się?

I można zliczać, spełniać oczekiwania?

Tu podaję rozwiązanie, ale do niego wrócę, dokładnie opiszę rozwiązania zastosowane. Teraz dla bardziej oblatanych od razu podaję rozwiązanie, a może są lepsze, znacie?

W. 

-- WIDOK STANY

   create  view STANY as

    select

     *

    from

    (select data from kalendarz) d, (select nr_klienta from wysylka_1) k;

-- WIDOK KOSTKA

create view KOSTKA as

       select

         s.data

       , w.nr_klienta

       , w.plec

       , w.wojewodztwo

       , w.data_wysylki

       , datediff (dd, w.data_wysylki, s.data) dn_od_wysylki

       , case when s.data=w.data_odpowiedzi then 1.00 else 0.00 end zn_prenumeraty

       , case when s.data>=w.data_odpowiedzi then 1.00 else 0.00 end zn_prenumeraty_kum

       , 1.00 zlicz

        from stany s, wysylka_1 w

       where s.nr_klienta=w.nr_klienta and s.data>=w.data_wysylki;

-- A teraz winiki, jak chcesz grupuj,  np. dodatkowo wydobądź płeć, województwo...

 select

    dn_od_wysylki

  , data_wysylki

  , sum(zlicz) liczba_obserwacji

  , sum(zn_prenumeraty) prenumeraty_biezace

  , sum(zn_prenumeraty) / sum(zlicz) * 100 pr_prenumerat

  , sum(zn_prenumeraty_kum) prenumeraty_skum

  , sum(zn_prenumeraty_kum) / sum(zlicz) * 100 pr_prenumerat_skum

  from KOSTKA

  group by dn_od_wysylki, data_wysylki;