GoldenLine
Zaloguj się
Edyta Ratajczyk

Edyta Ratajczyk
programista/bazodano
wiec/analityk
biznesowy IT

Temat: Jaki był najdłuższy ciąg...

danego zjawiska.
A teraz do rzeczy. W tabeli mam zapisane daty zakupu danego produktu przez klientów.
ID ID_KLIENTA PRODUKT DATA_ZAKUPU
1 1 A 2007-01-01
2 2 A 2007-01-01
3 3 B 2007-01-01
4 1 A 2007-01-01
5 1 B 2007-01-01
6 1 A 2007-01-01
7 2 C 2007-01-01
8 1 A 2007-01-01
9 2 A 2007-01-01
Interesuje mnie np. ile razy (maksymalnie) klient zakupił produkt "A", zanim zdecydował się na inny.
(dla powyższego przykładu - klient 1 kupił A 3 razy, zanim wziął B)
Oczywiście zapytania mogą być różne - ile razy kupował inne (nie koniecznie tylko A) zanim wziął B itd.
Nie wiem, czy to na relacyjnej się da, czy podchodzi już bardziej pod hurtownię danych. (danych nie jest mało, ale nie jest też gigantycznie wiele)
Czy macie pomysł na zapytanie?

Pozdrawiam
Link do wypowiedziLink
Jacek Gużewski

Jacek Gużewski Architekt systemu

Temat: Jaki był najdłuższy ciąg...

Witam Panią.

Zrobiłem naprędce (jak się później wczytałem o trochę coś innego Ci chodziło- ale da sie to poprzerabiać tak aby było dobre dla każdego wariantu) wyliczenie dla poszczególnych klientów iloe razy i jaki produkt kupili jako pierwszy zanim zdecydowali się na drugi. Niestety musiałem uzyć pętli, ale może można zrobić to prościej,

BEGIN
if exists (select * from dbo.sysobjects where id=Object_id('dbo.WYNIKI') and type in ('U','S'))
DROP TABLE dbo.WYNIKI
CREATE TABLE dbo.WYNIKI (ID_KLIENTA int NOT NULL,PRODUKT varchar(1) NOT NULL,ILOSC int NOT NULL)

DECLARE @IDKLIENTA INT, @PRODUKT_PIERWSZY VARCHAR(1), @PRODUKT_AKTUALNY VARCHAR(1), @ILOSC INT

DECLARE KURSOR_ZEW CURSOR FOR SELECT DISTINCT ID_KLIENTA FROM ZAKUPY ORDER BY ID_KLIENTA

OPEN KURSOR_ZEW
FETCH NEXT FROM KURSOR_ZEW
INTO @IDKLIENTA
WHILE @@FETCH_STATUS = 0
BEGIN

BEGIN
SET @PRODUKT_PIERWSZY = NULL
SET @ILOSC = 0
DECLARE KURSOR_WEW CURSOR FOR
SELECT PRODUKT FROM ZAKUPY WHERE ID_KLIENTA = @IDKLIENTA ORDER BY ID
-- TUTAJ NALEŻAŁO BY WSTAWIĆ TAK SPOSÓB SORTOWANIA JAKI UZNAMY ZA KOLEJNOŚĆ ZAKUPÓW

OPEN KURSOR_WEW
FETCH NEXT FROM KURSOR_WEW
INTO @PRODUKT_AKTUALNY
WHILE @@FETCH_STATUS = 0 AND (@PRODUKT_PIERWSZY = @PRODUKT_AKTUALNY OR @PRODUKT_PIERWSZY IS NULL)
BEGIN --
IF @PRODUKT_PIERWSZY IS NULL SET @PRODUKT_PIERWSZY = @PRODUKT_AKTUALNY
SET @ILOSC = @ILOSC +1
FETCH NEXT FROM KURSOR_WEW
INTO @PRODUKT_AKTUALNY
END --
CLOSE KURSOR_WEW

INSERT INTO dbo.WYNIKI
SELECT @IDKLIENTA, @PRODUKT_PIERWSZY, @ILOSC
END
DEALLOCATE KURSOR_WEW -- ALE NIE JESTEM PIEWIEN CZY TY MA BYĆ
FETCH NEXT FROM KURSOR_ZEW
INTO @IDKLIENTA
END
CLOSE KURSOR_ZEW
DEALLOCATE KURSOR_ZEW
SELECT * FROM dbo.WYNIKI
END

Używając twoich przykładów danych dostaje takie wyniki:

ID_KLIENTA PRODUKT ILOSC
----------- ------- -----------
1 A 2
2 A 1
3 B 1
Może Ci to pomoże, a jak nie to będziemy coś kombinować.
Link do wypowiedziLink
Michał O.

Michał O. Kierownik Działu
Zarządzania Jakością
Produktów, Polska
T...

Temat: Jaki był najdłuższy ciąg...

może tak?

select max(ile_razy)
from
(
select id_klienta, count(produkt) ile_razy
from table t1
where produkt = 'A' and data_zakupu <=
(select min(data_zakupu) from table t2 where produkt = 'B' and t1.id_klienta = t2.id_klienta)
group by id_klienta
)
Link do wypowiedziLink
Norbert M.

Norbert M. Nobody's perfect.
Call me Nobody ;)

Temat: Jaki był najdłuższy ciąg...

Nie wiem czy o to chodziło ale ja bym to zrobił tak:

1. odczytać MIN(Id) dla danego klienta i produktu (B) (w tym wypadku 5)

SELECT MIN(ID)
FROM tabela
WHERE (ID_KLIENTA = 1) AND (PRODUKT = 'B'))

2. zliczył ile jest produktów A dla tego klienta gdzie id jest mniejsze niż wynik z poprzedniego (w tym wypadku 2 nie 3)

SELECT COUNT(*)
FROM tabela
WHERE (ID < 5) AND (ID_KLIENTA = 1) AND (PRODUKT = 'A')

Można to zawrzeć w jednym zapytaniu i uwzględnić przedziały czasowe.
Oczywiście taki algorytm jest czas rośnie zgodnie z ID.
Pozdrawiam.
Link do wypowiedziLink
Jacek Gużewski

Jacek Gużewski Architekt systemu

Temat: Jaki był najdłuższy ciąg...

A tu wersja z liczeniem wszystkich "ciągów zakupowych" (ilość razy kupna danego produktu przez klienta zanim kupi następny):
BEGIN
if exists (select * from dbo.sysobjects where id=Object_id('dbo.WYNIKI1') and type in ('U','S'))
DROP TABLE dbo.WYNIKI1
CREATE TABLE dbo.WYNIKI1
(ID_KLIENTA int NOT NULL,PRODUKT_KUPOWANY varchar(1) NOT NULL,PRODUKT_NASTEPNY VARCHAR(1),ILOSC int NOT NULL)

DECLARE @IDKLIENTA INT, @PRODUKT_POPRZEDNI VARCHAR(1), @PRODUKT_KUPOWANY VARCHAR(1), @ILOSC INT
DECLARE KURSOR_ZEW CURSOR FOR SELECT DISTINCT ID_KLIENTA FROM ZAKUPY ORDER BY ID_KLIENTA
OPEN KURSOR_ZEW
FETCH NEXT FROM KURSOR_ZEW
INTO @IDKLIENTA
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
SET @PRODUKT_KUPOWANY = NULL
SET @PRODUKT_POPRZEDNI = NULL
SET @ILOSC = 0
DECLARE KURSOR_WEW CURSOR FOR
SELECT PRODUKT FROM ZAKUPY WHERE ID_KLIENTA = @IDKLIENTA ORDER BY ID
-- TUTAJ NALEŻAŁO BY WSTAWIĆ TAK SPOSÓB SORTOWANIA JAKI UZNAMY ZA KOLEJNOŚĆ ZAKUPÓW
OPEN KURSOR_WEW
FETCH NEXT FROM KURSOR_WEW
INTO @PRODUKT_KUPOWANY
WHILE @@FETCH_STATUS = 0
BEGIN
IF @PRODUKT_POPRZEDNI IS NULL OR @PRODUKT_KUPOWANY = @PRODUKT_POPRZEDNI SET @ILOSC = @ILOSC +1
IF (@PRODUKT_KUPOWANY <> @PRODUKT_POPRZEDNI AND @PRODUKT_POPRZEDNI IS NOT NULL)
BEGIN
INSERT INTO dbo.WYNIKI1
SELECT @IDKLIENTA, @PRODUKT_POPRZEDNI,@PRODUKT_KUPOWANY, @ILOSC
SET @ILOSC = 1 END
SET @PRODUKT_POPRZEDNI = @PRODUKT_KUPOWANY
FETCH NEXT FROM KURSOR_WEW
INTO @PRODUKT_KUPOWANY
END --
CLOSE KURSOR_WEW

INSERT INTO dbo.WYNIKI1
SELECT @IDKLIENTA, @PRODUKT_POPRZEDNI,@PRODUKT_KUPOWANY, @ILOSC
END
DEALLOCATE KURSOR_WEW
FETCH NEXT FROM KURSOR_ZEW
INTO @IDKLIENTA
END
CLOSE KURSOR_ZEW
DEALLOCATE KURSOR_ZEW
SELECT * FROM dbo.WYNIKI1
END

Dostałem taki wynik (na podstawie twoich przykładowych danych):

ID_KLIENTA PRODUKT_KUPOWANY PRODUKT_NASTEPNY ILOSC
----------- ---------------- ---------------- -----------
1 A B 2
1 B A 1
1 A A 2
2 A C 1
2 C A 1
2 A A 1
3 B B 1Jacek Gużewski edytował(a) ten post dnia 09.01.08 o godzinie 12:22
Link do wypowiedziLink
Edyta Ratajczyk

Edyta Ratajczyk
programista/bazodano
wiec/analityk
biznesowy IT

Temat: Jaki był najdłuższy ciąg...

Ok... danych może być wiele.
Skupię się na jednym kliencie. Kupował po kolei:
A
A
B
A
A
A
A
C
A
B
B
B
A
Ile razy maksymalnie kupił A zanim B? Spodziewać się chcę odpowiedzi 5 *a nie 4 jak wcześniej napisałam). (nie ważne, że wcześniej kupił B po 2xA, później aż 4 razy kupił A)
Ile razy kupił inne niż B? 6 (A przleplecione z C)
Więc nie o MIN() chodzi, bo nie o pierwsze B pytam, tylko o najdłuższy ciąg.
Znowu namieszałam? :)
Kursor - tak. Ale czy selectem by się dało? :)Edyta Ratajczyk edytował(a) ten post dnia 09.01.08 o godzinie 12:03
Link do wypowiedziLink
Tadeusz Pyś

Tadeusz Pyś ..czyli samo zuo ;)

Temat: Jaki był najdłuższy ciąg...

przyznam, rozwiazania pierwszego z kursorem nie sprawdzalem.. ale mam wiele niecheci do nich :)

rozwiazanie Michala wyglada ok, ale przy probach uogolnienia go do dowolnego zestawu produktow (nie tylko dwoch!) cos mi sie sypnelo..

rozwiazanie Norbeta hm.. a co z seriami? zliczanie rekordow gdzie ID jest < a produkt sie zgadza nie gwarantuje ze to bedzie CIAGLA SERIA zakupow tego produktu!!

w miedzy czasie wysmazylem cos takiego.. jest dluuugie i kilkuetapowe, ale to sa tylko selecty do tablic tymczasowych, wiec jak sie kto uprze, to mozna to nawet zlepic w .. jeden olbrzymi select :)

ah.. pisane na MSSQL2005. jedyna specyficzna rzecz jaka jest uzywana to row_number() over (order by ..) -- da sie to zamienic na dowolny ROWNUM z sortowaniem

create table #temp(id int,klient int,prod char,data datetime)
insert into #temp select 1, 1, 'A', '2007-01-01'
insert into #temp select 2, 2, 'A', '2007-01-01'
insert into #temp select 3, 3, 'B', '2007-01-01'
insert into #temp select 4, 1, 'A', '2007-01-01'
insert into #temp select 5, 1, 'B', '2007-01-01'
insert into #temp select 6, 1, 'A', '2007-01-01'
insert into #temp select 7, 2, 'C', '2007-01-01'
insert into #temp select 8, 1, 'A', '2007-01-01'
insert into #temp select 9, 2, 'A', '2007-01-01'

--

select id, klient, prod, row_number() over (order by klient,id) as numerzakupu
into #listazakupow
from #temp

select *, row_number() over (order by t.klient, t.numerzakupu) as numerzmiany
into #listazmianwyboru
from
( select tmp.klient, tmp.numerzakupu
from #listazakupow tmp
left join #listazakupow tmp2 on tmp2.klient = tmp.klient and tmp2.numerzakupu = tmp.numerzakupu+1
where tmp.prod <> tmp2.prod
union all
select klient, min(numerzakupu)-1
from #listazakupow
group by klient
union all
select klient, max(numerzakupu)
from #listazakupow
group by klient
) t

select tmp.klient, zak.prod, tmp2.numerzakupu - tmp.numerzakupu as ilosc_pod_rzad
into #seriezakupow
from #listazmianwyboru tmp
join #listazmianwyboru tmp2 on tmp2.klient = tmp.klient and tmp2.numerzmiany = tmp.numerzmiany+1
left join #listazakupow zak on zak.numerzakupu = tmp2.numerzakupu

select s.klient, s.prod, s.ilosc_pod_rzad
from
( select klient, max(ilosc_pod_rzad) as ilosc_pod_rzad
from #seriezakupow
group by klient
) t
join #seriezakupow s on s.klient = t.klient and s.ilosc_pod_rzad = t.ilosc_pod_rzad
order by 1,3,2

--
drop table #seriezakupow
drop table #listazmianwyboru
drop table #listazakupow
drop table #temp

edit: wybacz formatowanie.. [code] ani <pre> nie dziala:/[edited]Tadeusz Pyś edytował(a) ten post dnia 09.01.08 o godzinie 12:05[/edited]
Link do wypowiedziLink
Tadeusz Pyś

Tadeusz Pyś ..czyli samo zuo ;)

Temat: Jaki był najdłuższy ciąg...

Edyta Ratajczyk:
Znowu namieszałam? :)

tak..
Edyta Ratajczyk:
Ile razy maksymalnie kupił A zanim B? Spodziewać się chcę odpowiedzi 5 *a nie 4 jak wcześniej napisałam). (nie ważne, że
wcześniej kupił B po 2xA, później aż 4 razy kupił A)

chcesz odpowiedzi 5, poniewaz jest wazne ze pozniej kupil 4 razy A..?? najdluzszy ciag A wynosi 4 przeciez, skad 5? :)
Ile razy kupił inne niż B? 6 (A przleplecione z C)

a wiec jednak nie najdluzszy ciag A, tylko najdluzszy ciag nie-B? w takim razie nie 4, nie 5, tylko 6..

zdecyduj sie :)

do rzeczy..
Edyta Ratajczyk:
Ok... danych może być wiele.
Skupię się na jednym kliencie. Kupował po kolei:
A
A
B
A 1 2 3
A 1 2 3
A 1 2 3
A 1 2 3
C . 2 .
A . 2 3
B
B
B
A

oznaczylem na cytacie cyframi poszczegolne ciagi:
1) najdluzszy ciag zakupow A wynosi 4
2) najdluzszy ciag zakupow nie-B wynosi 6
3) najdluzszy ciag nie-B ale z liczeniem ograniczonym do A = 5

chodzi Ci o 1) czy o 2) czy o 3)?
moj skrypt z poprzedniego postu realizuje 1), tzn. zwroci:

klient-produkt-ile
1-A-4Tadeusz Pyś edytował(a) ten post dnia 09.01.08 o godzinie 12:53
Link do wypowiedziLink
Edyta Ratajczyk

Edyta Ratajczyk
programista/bazodano
wiec/analityk
biznesowy IT

Temat: Jaki był najdłuższy ciąg...

:) no tak, jak pisałam, zapytania różne...
interesują mnie i 1) i2) i3) i pewnie jeszcze jakies, o których w tej chwili nawet nie myślę...

nie przeedytowałam wszystkiego i stąd zamieszanie. przepraszam.
jeszcze raz:
A
A
B
A
A
A
A
C
A
B
B
B
A
Ile razy maksymalnie kupił A zanim B? Spodziewać się chcę odpowiedzi 5. (nie ważne, że wcześniej kupił B po 2xA, później aż 5 razy kupił A) (typ 3)
Ile razy kupił inne niż B? 6 (A przleplecione z C) (typ 2)
Ile maksymalnie A zanim cokolwiek innego? 4 (typ 1)
Link do wypowiedziLink
Michał O.

Michał O. Kierownik Działu
Zarządzania Jakością
Produktów, Polska
T...

Temat: Jaki był najdłuższy ciąg...

To jeszcze napisz, co chcesz mieć w wyniku - jak ma wyglądać raport.

Bo to się wszystko imho da zrobić selectem z podzapytaniami skorelowanymi (czyli takim jak napisałem) ale musi to byc jakoś skonkretyzowane, bo nie da się napisac takiego selecta, który będzie zwracał różne wyniki w zależności od tego co Ci przyjdzie na myśl.
Jeśli Twoje oczekiwania są zróżnicowane i potrzebujesz różnych raportów w zależności od sytuacji to nie pozostaje Ci IMHO nic innego niż nauczyć się modyfikować zapytania pod konkretne potrzeby ;-)
Nie jest to takie trudne ;-P
Link do wypowiedziLink
Mirosław Serwaczyński

Mirosław Serwaczyński Analityk programista

Temat: Jaki był najdłuższy ciąg...

Ja bym (przy tych konkretnych warunkach: "Ile razy maksymalnie kupił A zanim B") zrobił tak (mamy tabelę Zakupy[Id,Towar]):

SELECT Id,Towar INTO #T1
FROM Zakupy
WHERE Towar='B'

SELECT #T1.Id as Id1,T.Id as Id2 INTO #T2
FROM #T1
LEFT JOIN Zakupy T ON T.Id<#T1.Id AND T.Towar='A'

SELECT row_number() over (order by Id1) as Nr,Id1,count(Id1) AS Ile
INTO #T3
FROM #T2
GROUP BY Id1

SELECT max(A1.Ile-isnull(A2.Ile,0)) as wynik
FROM #T3 A1 left join #T3 A2 ON A1.Nr=A2.Nr+1

Zastosowałem tabele tymczasowe dla większej czytelności, hardcorowcy mogą to ubrać w jedno zapytanie :-)
Link do wypowiedziLink

konto usunięte

Temat: Jaki był najdłuższy ciąg...

A co jeśli klient\ka kupował\a kilka produktów jednocześnie.
:)
Myslę, że najważniejsze pytanie już padło. Co tak naprawdę ma przedstawić twoje query.
Najważniejsze to dokładnie roztrzaskać z logicznej strony (podobno żółte kartki do tego się nadają)
Wtedy ubrać to w SQL.
Link do wypowiedziLink

konto usunięte

Temat: Jaki był najdłuższy ciąg...

zzKonrad Sekuła edytował(a) ten post dnia 21.04.08 o godzinie 00:10
Link do wypowiedziLink
Edyta Ratajczyk

Edyta Ratajczyk
programista/bazodano
wiec/analityk
biznesowy IT

Temat: Jaki był najdłuższy ciąg...

Dziękuję wszystkim za zainteresowanie tematem i za pomoc.
Pozdrawiam
Link do wypowiedziLink

konto usunięte

Temat: Jaki był najdłuższy ciąg...

To jeden z tych przypadków, gdy SQL nie powinien być stosowany.
Jedyny zysk z niego tutaj to ustalenie porządku. Reszta powinna być normalnie przetworzona w kursorze lub pliku zewn.
Link do wypowiedziLink
Mirosław Serwaczyński

Mirosław Serwaczyński Analityk programista

Temat: Jaki był najdłuższy ciąg...

Piotr Likus:
To jeden z tych przypadków, gdy SQL nie powinien być stosowany.
Jedyny zysk z niego tutaj to ustalenie porządku. Reszta powinna
być normalnie przetworzona w kursorze lub pliku zewn.

Spójrz parę postów wyżej - jak na razie jeszcze się udało w SQL-u :-)
Link do wypowiedziLink

konto usunięte

Temat: Jaki był najdłuższy ciąg...

Mirosław Serwaczyński:
Piotr Likus:
To jeden z tych przypadków, gdy SQL nie powinien być stosowany.
Jedyny zysk z niego tutaj to ustalenie porządku. Reszta powinna
być normalnie przetworzona w kursorze lub pliku zewn.

Spójrz parę postów wyżej - jak na razie jeszcze się udało w SQL-u :-)

owszem da sie w sql'u ale poco obciazac baze jak mozna dane 'sciagnac" i obrobic je na wlsnej maszynie jakims prostym batchem
Zbyt mocno baze obciazysz a jesli jest ona czesto uzywana ...
Link do wypowiedziLink
Grzegorz G.

Grzegorz G. ASE / Systems
Architect, Syniverse

Temat: Jaki był najdłuższy ciąg...

Baza to nie żubr, żeby ją trzymać pod ochroną. Po to bazy danych zostały stworzone, żeby je obciążać i wyszukiwać dane. A SQL jest ich natywnym językiem - więc jeżeli da się coś w nim sensownie zapisać to najprawdopodobniej to jest właściwa ścieżka (nie mylić z językami proceduralnymi typu T-SQL).

;-)
Link do wypowiedziLink
Mieczyslaw B.

Mieczyslaw B. e-Health, project
management

Temat: Jaki był najdłuższy ciąg...

Grzegorz G.:
Baza to nie żubr, żeby ją trzymać pod ochroną. Po to bazy danych zostały stworzone, żeby je obciążać i wyszukiwać dane. A SQL jest ich natywnym językiem - więc jeżeli da się coś w nim sensownie zapisać to najprawdopodobniej to jest właściwa ścieżka (nie mylić z językami proceduralnymi typu T-SQL).

Wybacz za mój cięty język, ale nie mogę się z Tobą zgodzić. Z jednej strony masz rację, nie ma co bazy trzymać "pod ochroną"... A co zrobisz kiedy będziesz miał co kilkanaście/dziesiąt sekund niewielką liczbę 500 requestów (dla Oracle niech będzie 5000) w tym samym czasie, a zapytanie, które rzeczywiście dało się zrobić w SQL
będzie rzeźbiło po bazie 10 sekund? Zarżniesz bazę, kupisz super serwer, będziesz klastrować, czy ściągniesz dane na zewnątrz i przetworzysz je kilkoma prostymi pętlami lub w inny sposób? A jak komar lata, to z RPG się strzela?:) Nie każda baza to Oracle b. często wyręczający swoimi mechanizmami programistę od myślenia lub optymalizacji.. i nie do każdego projektu ekonomicznie przystaje:PMieczyslaw B. edytował(a) ten post dnia 12.01.08 o godzinie 00:57
Link do wypowiedziLink
Karol Z.

Karol Z. Programista,
elektronik

Temat: Jaki był najdłuższy ciąg...

Od niedawna mam do czynienia z bazami danych, ale na tyle długo, by rozwiązanie Pawła Ch. uznać za nonsensowne. Oczywiście, w przypadku lokalnej maszyny to ma sens, non stop odpytywać, przyjmować/odrzucać warunki i w zależności od tego ponawiać zapytanie. Jednak po to ktoś mądry wymyślił mechanizmy RDBMS by danymi w logiczny sposób zarządzać po stronie serwera. A tworzenie dodatkowego ruchu na linii klient - serwer niewiele daje korzyści. Chyba, że chodzi o cel "sprawdźmy ile sieć zniesie".
Link do wypowiedziLink
« Wróć do tematów
Odpowiedz

Podobne tematy





Wyślij zaproszenie do
Anuluj