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;

CASE – WYSYŁKA OFERTY PRENUMERATY, POST NR 3

Marcinie, 

I kontynuujemy.

Dla tych co by też chcieli, a nie wiedzą o co chodzi to polecam zajrzeć pod linki: 

Post nr 1: http://zielonysql.blogspot.com/2016/02/case-wysyka-oferty-prenumeraty-post-nr-1.html

Post nr 2: http://zielonysql.blogspot.com/2016/02/case-wysyka-oferty-prenumeraty-post-nr-2.html

Dzisiaj ustaliłem sam ze sobą, iż do realizacji oczekiwać moich będzie potrzebna jakaś tabela z dniami, taki  mój kalendarz.

Kwerenda poniżej daj mi informację o maksymalnej liczbie dni pomiędzy datą wysyłki a datą najpóźniejszej odpowiedz.

Stąd wiem, że to jest 125 dni, czyli kalendarz musi się zaczynać 31 grudnia 2014, mieć co najmniej 125 dni by móc w czasie zobaczyć rozkład zdobytych prenumerat, to ja będę miał tych dni 200 :-)

select

max(datediff (dd, data_wysylki, data_odpowiedzi))  as najpozniejsza_reakcja

from wysylka_1 where data_odpowiedzi is not null;

Wynik kwerendy powyżej to 125 dni.

A po linką poniżej katalog, w którym to znajdziesz kod do utworzenia kalendarza oraz narzędzie w excel konstruujące w prosty sposób „INSERT”. 

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

CASE – WYSYŁKA OFERTY PRENUMERATY, POST NR 2

Marcinie, 

I ćwiczymy zatem na danych zgromadzonych w tabeli wysylka_1

Dla tych co by też chcieli, a nie wiedzą o co chodzi to polecam zajrzeć pod linkę: 

http://zielonysql.blogspot.com/2016/02/case-wysyka-oferty-prenumeraty-post-nr-1.html

Na początek prosta kwestia:

• podaj podstawowe informacje o zebranych danych, ale już agregowane ze względu na datę wysyłki, płeć odbiorcy, województwo odbiorcy,
• podaj średni czas odpowiedzi, najszybszy czas reakcji, najdłuższy czas reakcji,
• podaj ilu ludzi nie zareagowała i ilu zareagowało na naszą wysyłkę.

Wszystkie odpowiedzi są do uzyskania zaprezentowaną na rysunku kwerendą. 

1 – W zasadzie tu nie musi być, bo musi tak naprawdę w sekcji po GROUP BY, ale tu też bardzo często występuje, bo podzielenie zbioru, wyliczenie wartości, a później brak informacji kogo dotyczy informacja… nieco bez sensu. 

A dlaczego? Bo zbiór wszystkich 200 wierszy podzielimy na podzbiory/grup według województwa, daty wysyłki, płci, na tym polega grupowanie. To tak jak w szkole, przecież mamy uczniów, oni są podzieleni na klasy i dopiero wówczas się liczy średnią ocen, grupowanie nastąpiło dawno temu, nawet nie pamiętamy kiedy J To bardzo częste w życiu, na co nie zwracamy nawet uwagi, jest to naturalne. Pracując z danymi musimy to czynić jawnie, komputer nie czyta nam w myślach. 

2 – To samo co wyżej oraz użyłem klauzulę CASE, przy jej pomocy określam wartość w dodatkowym polu, to dodatkowe pole (kolumna) będzie się nazywać  CZY_ODPOWIEDZ, będzie zawierać wartości dwie, BRAK lub OK. To według tych etykiet też podzielmy nasz zbiór danych, to też skopiuję do sekcji po GROUP BY. 

3 – Ta sekcja, to miejsce gdzie liczymy / podajemy jakie wartości dla każdego podzbioru określimy, a zatem policzymy ile wierszy w podzbiorze jest, policzymy średni czas odpowiedzi w podzbiorze, najdłuższą odpowiedź, najkrótszą odpowiedź. Tu zwracam uwagę na nowinkę, jeżeli chcemy uzyskać liczbę dni pomiędzy dwoma datami to musimy wykorzystać funkcję DATEDIFF (… , data1, data2). Jeżeli w miejscu kropek podamy dd, to uzyskamy liczbę dni pomiędzy datami, mm – to liczba miesięcy, a yy – to liczba lat. 

4 – Na początku tego wiersza są dwa myślniki, taki sposób zapisu powoduje, że wiersz jest traktowany jako komentarz do kwerendy, zatem zostanie pominięty przy wykonywaniu kwerendy, odkomentowanie, tj. usunięcie tych myślników, spowoduje że zostanie wiersz  odczytany, zostaną zgodnie z treścią pominięte z wyniku zapytania wiersze niemające wypełnionego pola DATA_ODPOWIEDZI. 

5 – tu musisz wiedzieć, że gdy pomiędzy SELECT a WHERE chcesz uzyskać wartości agregowane, np. liczby wierzy, średnie …. To musisz podpowiedzieć bazie jak ma podzielić zbiór, tu podajesz po GROUP BY, które pola są dla ciebie ważne, według nich podzielony zbiór. Tu bardzo często musisz po prostu zawrzeć to samo co jest pomiędzy SELECT a WHERE, a nie jest agregowanym polem. Dlaczego takie powtarzanie jest konieczne, ano dlatego że tak naprawdę nie musisz pomiędzy SELECT a WHRERE wyświetlać wartości np. pola „DATA_WYSYLKI”, a pogrupować możesz chcieć, co prawda nieczytelny staje się wynik, ale tak można, nie ma obowiązku powtarzania treści. 

I co widać, jak wykonasz kwerendę :-)

 

Zobacz czy to samo co mnie, jak masz pytania to daj znać.

I kwerenda byś nie musiał przepisywać:

select

data_wysylki
, plec
, wojewodztwo
, case when data_odpowiedzi is null then 'BRAK' else 'OK' end czy_odpowiedz
, count(*) liczba_obserwacji
, avg ( datediff (dd, data_wysylki, data_odpowiedzi) ) sredni_czas_odpowiedzi
, max ( datediff (dd, data_wysylki, data_odpowiedzi) ) najdluzsza_odpowiedz
, min ( datediff (dd, data_wysylki, data_odpowiedzi) ) najkrotsza_odpowiedz

 from wysylka_1
-- where data_odpowiedzi is not null
 group by data_wysylki, plec, wojewodztwo, case when data_odpowiedzi is null then 'BRAK' else 'OK' end
 order by 1, 2, 3;