Poszukiwany sposób lub algorytm
Witam,
mam problem w osiągnięciu następującego wyniku.
Posiadam kilkanaście rekordów zawierających 4 kolumny. Każda kolumna zawiera literę alfabetu.
I teraz muszę wyciągnąć z tego statystykę:
- najczęściej powtarzająca się litera w dowolnej kolumnie
- najczęściej powtarzające się dwie litery w dowolnych kolumnach (i tu zaczynam mieć problem)
- najczęściej powtarzające się trzy litery w dowolnych kolumnach (tu mam również problem)
- najczęściej powtarzające się cztery litery w dowolnych kolumnach (to jest proste)
Ma ktoś jakieś sugestie co do sposobu wyznaczania tych elementów?
ElNinho ==> Mam dane tak, jak przedstawiłem.
Data , Litera1, Litera2, Litera3, Litera4
2010-01-01 , A , B , C , D
2010-01-02 , D , B , A , E
2010-01-03 , A , B , D , F
I teraz chciałbym otrzymać następujący wynik:
Najczęściej powtarzające się dwie litery to A i B - 3 razy. Opieram się na zwykłym SQLu, ale zapytanie sobie jakoś stworzę, o ile znajdzie się sposób na sprawdzenie wszystkiego.
codeine - bo z czterema możesz je choćby nawet posortować, a następnie szukasz pełnego gotowca.
W przypadku pośredniej wartości dla np. 2 czy 3 może mieć miejsce sytuacja, gdzie trzecią literę może poprzedzać 10 innych nieszukanych, więc wzorcem tego nie zrobisz. A ważna jest ilość tych wystąpień.
Edit:
Mości Elrondzie, odezwij się do mnie na FB.
Zastosuj konstrukcję UNION ALL.
Np. dla 2 najczęściej występujących liter składnia byłaby następująca:
SELECT TOP 2 LITERA, COUNT(*) ILE FROM
(SELECT KOLUMNA1 AS LITERA FROM TABELA
UNION ALL
SELECT KOLUMNA2 FROM TABELA
UNION ALL
SELECT KOLUMNA3 FROM TABELA
UNION ALL
SELECT KOLUMNA4 FROM TABELA)S1
GROUP BY LITERA
ORDER BY ILE DESC
Niestety na razie w pracy jestem, więc FB nie za bardzo mogę odpalać :(
reik ==> Ale to, co podałeś nie da mi rozwiązania mojego problemu :)
Masz 34 litery, z których każda może znaleźć się w dowolnej kolumnie. I nie wiesz w związku z tym, czy interesować Cię będzie N i O, czy K i R.
Tu trzeba będzie chyba jednak zrobić to iloczynem kartezjańskim.... :/
Wyliczenie wszystkich kombinacji :/
Lechu, ale zapytanie reika daje właśnie to co chciałeś
wewnętrzne unie robią Ci jakby kartezjan, a zewnętrzny select grupuje je malejąco po występowaniu
nie o to chodziło ?
Dobra, jak zwykle pominąłem chyba najważniejszy fakt, przez który nie pasuje mi rozwiązanie reika (dzięki tak czy inaczej, bo spełnia jeden z wymogów).
Potrzebuję przykładowo 5 wyników najczęstszych par liter:
1) 8787 - A,B
2) 3345 - D,O
3) 2212 - E,K
4) 1233 - G,A
5) 987 - M,O
Ilość wystąpień niezależnie od kolejności kolumn, podany wynik już po sorcie.
Dokładnie, ile razy padła kombinacja tych liter w tabeli. Tzn rekordów zawierających jeden i drugi znak.
aaaa to teraz się klaruje :)
spadam do domu zaraz, wieczorem pomyślę ale nic nie obiecuję ;)
Ok :)
Myślę póki co o pregenerowanych tablicach z wynikami dla wszystkich kombinacji, typu
Kolumna pierwszej | Kolumna drugiej | (Kolumna trzeciej) | Ilosc
Wtedy szybki select i jest ok, bez obciążania raportu.
jedyne co przyszło mi do głowy to napisanie/użycie jakiejś funkcji sortującej ciąg znaków, tak żeby posortować te 4 kolumny, czyli coś a'la
string_sort(kol1,kol2,kol3,kol4)
wtedy możesz zastosować konstrukcję podaną przez reika, gdzie wewnętrzne unie selektów będą wyglądać mniej więcej tak (elem12 to elementy 1 i 2 z powyższego posortowanego stringa):
SELECT elem12 (string_sort(kol1,kol2,kol3,kol4)) FROM TABELA
UNION ALL
SELECT elem13 (string_sort(kol1,kol2,kol3,kol4)) FROM TABELA
UNION ALL
SELECT elem14 (string_sort(kol1,kol2,kol3,kol4)) FROM TABELA
UNION ALL
SELECT elem23 (string_sort(kol1,kol2,kol3,kol4)) FROM TABELA
UNION ALL
SELECT elem24 (string_sort(kol1,kol2,kol3,kol4)) FROM TABELA
UNION ALL
SELECT elem34 (string_sort(kol1,kol2,kol3,kol4)) FROM TABELA
nie wiem czy to zrozumiale napisałem, bo sam się zacząłem zastanawiać :)
no i zbyt wydajne to to nie jest, aczkolwiek dla niezbyt dużej ilości danych powinno robić
dla 3 liter analogicznie
YogiYogi ==> To nie ma znaczenia :)
Wyniki będę i tak wyświetlać selectem z przygotowanej tablicy.
Całość mogę albo obrobić kodem, albo zostawić po stronie SQLa.
Jak coś wyjdzie, to po czasie wykonania będę wiedział gdzie zostawić.
Ale generalnie i tak SQL Express 2008 R2
No i od tego trzeba bylo zaczac :) - moj ulubiony dialekt:
Rozwiazanie podaje na szybciora dla statystyk dwoch literek - analogicznie dolozysz sobie do 3 i 4 itd.
Czy dziala dobrze ? Chyba tak - bo znalazlo rowniez w Twoim przykladzie ze B,D tez 3 razy sie powtarza :)
Jedziemy (przepraszam z gory za mix angielsko-polski):
-- najpierw Twoja tabelka testowa
use tempdb
create table X
(
Data date,
Litera1 varchar,
Litera2 varchar,
Litera3 varchar,
Litera4 varchar
)
insert into X (Data, Litera1, Litera2, Litera3, Litera4)
values
('2010-01-01' , 'A' , 'B' , 'C' , 'D'),
('2010-01-02' , 'D' , 'B' , 'A' , 'E'),
('2010-01-03', 'A' , 'B' , 'D' , 'F' )
GO
-- Zapytanie (uruchamiasz jako jedno zapytanie do konca):
with Znaki as
(
select 1 as ColNo, Data, Litera1 from X
union
select 2, Data, Litera2 from X
union
select 3, Data, Litera3 from X
union
select 4, Data, Litera4 from X
)
, ZnakiSorted as
(
select ROW_NUMBER() over (partition by Data order by (litera1)) as RN , Data, Litera1 from Znaki
)
, Znaki_2 as
(
select z1.Litera1 as L1, z2.Litera1 as L2, z1.Data from ZnakiSorted z1 cross apply ZnakiSorted z2 where z1.Data = z2.Data and z1.RN < z2.RN
)
-- znaki_3 as ..... analogicznie znaki_2 cross apply znakisorted
select COUNT(*), L1, L2 from Znaki_2
group by L1, L2
Edit:
Oczywiscie jesli potrzebne jakies wyjasnienie to sluze, mam nadzieje ze sie przyda :D
- Zalozylem ze 'Data' jest kluczem dla wiersza, ale jesli nie to musisz to pole zastapic jakims unikatowym ID
- Bedzie wydajne bo funkcje rankujace sa pierunsko szybkie
- Rozwiazanie uzwglednia dowolna kolenosc liter
- Nie sprawdzilem dla powtarzajacych sie liter w jednym wierszu - ale chcialem cos zostawic dla Ciebie :D
Edit 2:
- Dla powtarzajacych sie literek w wierszu wykryje te dodatkowe kombinacje i zliczy je. Jesli nie chcesz zeby tak bylo to musisz zapewne w odpowiednim miejscu wstawic w zapytaniu 'Znaki' distincta
YogiYogi ==> Dzięki :)
Mam jeszcze jedno zastrzeżenie - jak uniknąć wybrania tych samych liczb? Tzn 3 i 3? Bo powtórzenia nie wchodzą w grę.
Edit:
Dobra, już mam - select z1.[Litera1] as L1, z2.[Litera1] as L2, z1.Data from ZnakiSorted z1 cross apply ZnakiSorted z2 where z1.Data = z2.Data and z1.RN < z2.RN AND z1.[Litera1] <> z2.[Litera1])
YogiYogi ==> Nie za bardzo kumam konstrukcję pod trzecią literę.
Działa dla tabelki i danych YogiYogi
select top 5 COUNT(*) as Ile, tmp1.l1, tmp1.l2
from
(
select
case when cast(tmp.l1 as CHAR) < cast(tmp.l2 as char) then tmp.l1 else tmp.l2 end as l1,
case when cast(tmp.l1 as CHAR) > cast(tmp.l2 as CHAR) then tmp.l1 else tmp.l2 end as l2
--tmp.l2
from
(
select litera1 as l1, litera2 as l2 from X
union all
select litera1 as l1, litera3 as l2 from X
union all
select litera1 as l1, litera4 as l2 from X
union all
select litera2 as l1, litera3 as l2 from X
union all
select litera2 as l1, litera4 as l2 from X
union all
select litera3 as l1, litera4 as l2 from X
) tmp
) as tmp1 group by tmp1.l1, tmp1.l2 order by Ile desc;
DarkStar ==> Dzięki.
Rozwiązanie Yogiego jest super, nie za bardzo widzę jednak, jak dorzucić trzecią literę.
Alez prosze dla 3:
(generowanie tabelki jak poprzednio a zapytanie ponizej)
with Znaki as
(
select 1 as ColNo, Data, Litera1 from X
union
select 2, Data, Litera2 from X
union
select 3, Data, Litera3 from X
union
select 4, Data, Litera4 from X
)
, ZnakiSorted as
(
select ROW_NUMBER() over (partition by Data order by (litera1)) as RN , Data, Litera1 from Znaki
)
, Znaki_2 as
(
select z1.Litera1 as L1, z2.Litera1 as L2, z1.Data from ZnakiSorted z1 cross apply ZnakiSorted z2 where z1.Data = z2.Data and z1.RN < z2.RN
)
, Znaki_3 as
(
select z1.Litera1 as L1, z2.Litera1 as L2, z3.Litera1 as L3, z1.Data from ZnakiSorted z1 cross apply ZnakiSorted z2 cross apply ZnakiSorted z3
where (z1.Data = z2.Data and z2.Data = z3.Data) and z1.RN < z2.RN and z2.RN < z3.RN
)
select COUNT(*), L1, L2, L3 from Znaki_3
group by L1, L2, L3
A tutaj wersja bez powtorzen (mala zmiana w jednej linii)
with Znaki as
(
select 1 as ColNo, Data, Litera1 from X
union
select 2, Data, Litera2 from X
union
select 3, Data, Litera3 from X
union
select 4, Data, Litera4 from X
)
, ZnakiSorted as
(
-- tutaj mala zmiana zeby nie bylo powtorzen
select distinct * from ( select dense_rank() over (partition by Data order by (litera1)) as RN , Data, Litera1 from Znaki ) as X
)
, Znaki_2 as
(
select z1.Litera1 as L1, z2.Litera1 as L2, z1.Data from ZnakiSorted z1 cross apply ZnakiSorted z2 where z1.Data = z2.Data and z1.RN < z2.RN
)
, Znaki_3 as
(
select z1.Litera1 as L1, z2.Litera1 as L2, z3.Litera1 as L3, z1.Data from ZnakiSorted z1 cross apply ZnakiSorted z2 cross apply ZnakiSorted z3
where (z1.Data = z2.Data and z2.Data = z3.Data) and z1.RN < z2.RN and z2.RN < z3.RN
)
select COUNT(*), L1, L2, L3 from Znaki_3
group by L1, L2, L3
Zauwaz ze mozesz to sobie skalowac na 4, 5, .... wartosci zmieniajac tylko tresc
zapytania zdefiniowanego w Znaki () oraz potem analogicznie Znaki_3 () ...
Dodajesz po prostu cross apply przy zalozeniu ze kolejne wartosci naleza do tego samego wiersza (z1.Data = z2 Data itd...) AND kazda "doklejana" wartosc RN (numer wiersza) do tego zlaczenia jest wieksza niz poprzednia - czyli z1.RN < z2.RN and z2.RN < z3.RN and....
Przy okazji - dzieki za lamiglowke, ja mialem rozrywke a bede nia katowal potencjalnych kandydatow na rozmowach kwalifikacyjnych...
Dzięki wielkie :) Potrzebuję to do konkretnej rzeczy, ale dam Ci namiar jak skończę :)
Ja znowu w SQLu dawno już nie siedziałem, dostałem łamigłówkę i tak wyszło, że czasu w robocie mało, a dobrze było zakończyć "projekcik" :)
A jednak da sie prosciej (zapomnialem ze w poprzedniej wersji zostawilem niepotrzebnie smieci w pierwszym zapytaniu - numerki kolumn 1-4, przez co union nie usuwal duplikatow).
Po poprawieniu - distinct nie jest potrzebny, przez co sie wszystko upraszcza a nie ma duplikatow:
with Znaki as
(
select 1 as ColNo, Data, Litera1 from X
union
select 1, Data, Litera2 from X
union
select 1, Data, Litera3 from X
union
select 1, Data, Litera4 from X
)
, ZnakiSorted as
(
select dense_rank() over (partition by Data order by (litera1)) as RN , Data, Litera1 from Znaki
)
, Znaki_2 as
(
select z1.Litera1 as L1, z2.Litera1 as L2, z1.Data from ZnakiSorted z1 cross apply ZnakiSorted z2 where z1.Data = z2.Data and z1.RN < z2.RN
)
, Znaki_3 as
(
select z1.Litera1 as L1, z2.Litera1 as L2, z3.Litera1 as L3, z1.Data from ZnakiSorted z1 cross apply ZnakiSorted z2 cross apply ZnakiSorted z3
where (z1.Data = z2.Data and z2.Data = z3.Data) and z1.RN < z2.RN and z2.RN < z3.RN
)
select COUNT(*), L1, L2, L3 from Znaki_3
group by L1, L2, L3
legrooch - trzymam za slowo, podeslij namiar na gryonline @ elieli.oib.com bo nie wiem czy bede sledzil ten watek :)
YogiYogi --->
Twój SQL jest bardzo fajny, ale zakłada, że data jest unikalna. W przypadku data martowych / hurotwnianych tabel faktów, na których trzepie się podobne raporty, niestety nie zadziała, bo może się tam znaleźć kilkaset identycznych rekordów na przykładowo tą samą datę.
Dlatego podkreslalem ze zamiast daty wybierz sobie jakis wyroznik wiersza - i to nie musi byc data, ale najlepiej jakis int lub bigint
Nie wspomniales ze to hurtownia :)
Pewnie w tabeli faktow nie masz zadnego klucza unikatowego (zlozonego albo prostego ?)
jesli masz takie pole (wbrew pozorom czasem w tabelach faktow daje sie klucze) to zmodyfikuj lekko zapytanie na poczatku:
with Znaki as
(
select 1 as ColNo, twoje pole unikatowego klucza as Data, Litera1 from X
union
select 1, twoje pole unikatowego klucza as Data, Litera2 from X
union
select 1, twoje pole unikatowego klucza as Data, Litera3 from X
union
select 1, twoje pole unikatowego klucza as Data, Litera4 from X
)
....
i dalej bez zmian (chyba ze nazwe kolumny Data zmienisz sobie na cos bardziej odpowiadajacego zawartosci tej nowej kolumny to wtedy po prostu w calym zapytaniu Data zmieniasz na te nowa nazwe)
Przy okazji - katujesz hurtownie relacyjnym zapytaniem czy potrzebujesz je tylko dla cuba ? ;)
Gdzieś legrooch wspomniał o raporcie, dlatego sam sobie przełożyłem na BI:)
Generalnie tabele faktów nie muszą mieć klucza głównego, jednak dobrą zasadą jest posiadanie takowego, czy to w postaci surogata, czy specjalnej kolumny w której trzymany jest np. pk z rekordu źródłowego.
W kontekscie SQL Servera nie wiem czy SSASowego cuba można przykatować czym innym niż mdxem, jednak np. w Cognosie relacyjne zapytanie jak najbardziej.
DarkStar ->
SSASa mozna przykatowac relacyjnie jesli to ROLAP ;) wzglednie HOLAP - co prawda nie wprost...ale liczy sie efekt.
A tak tylko z powodu zboczenia zawodowego delikatnie dodam do tego co powiedziales, ze
- business keya (bo chyba jego miales na mysli w kotekscie przechowywania klucza zrodlowego) mozna dodac jesli fakty rzeczywiscie da sie zamapowac do systemu zrodlowego - czesto jednak niekoniecznie sa to relacje 1:1 ;). W tym jednak przypadku business key nie przeda sie bo nadal moze sie zdarzyc kilka faktow o tym samym BK.
- natomiast surrogate key - jesli bylby np. generowany jako zwykle identity - spelnilby powyzsze kryteria i zapytanie SQLowe spokojnie z nim zadziala
;)
YogiYogi --->
Przypomniało mi się, że relacyjnymi zapytaniami można pojechać nawet MOLAP przy użyciu proactive caching i jego kiepskiej konfiguracji:)
Mam podobne do Ciebie zboczenie zawodowe:) Chodziło mi o tzw. degenrate dimension, czyli klucze naturalne pozwalające jednoznacznie określić rekord źródłowy (przydatne m. in. przy błędach i identyfikowaniu ich źródła).
Dzięki za dyskusję:) Zmykam do domu.
YogiYogi ==> Co uzupełnić? :) Nie mam maila, a we wcześniejszych postach nie widzę info.
legrooch - > Kurcze, wyslalem Ci 2 maile, na adres o2 .
Zerknij w spam :) moze utknelo.
Generalnie podeslij prosze zawartosc tabelki, dla ktorej testujesz to.
Podeślę Ci później.
Maila spróbuj pchnąć na gmail. Początek identyczny :)
GRYOnline.pl:
tvgry.pl:
