Unikatowe odwiedziny Opcje

[Wilu88]

Witam mam taki problem.

Otóż chciałbym pobrać z tabeli "wejescia" liczbę ogólną kliknięć w dany banner, oraz unikalną liczbę wejść. Postanowiłem że za uniklane wejscie uznawał będe tylko jedno wejscie z danego Ip w jednym dniu.

Do tej pory mam zrobione pobieranie ogólnej liczby kliknięć:

[SQL] pobierz, plaintext 
SELECT sponsor_id,sponsor_nazwa,sponsor_baner,sponsor_rozpoczecie,sponsor_zakonczenie,sponsor_aktywny,sponsor_link,COUNT(wejscie_id) FROM sponsorzy LEFT OUTER JOIN wejscia ON wejscie_sponsor=sponsor_id WHERE sponsor_aktywny=1 AND sponsor_rozpoczecie<=CURRENT_DATE AND (sponsor_zakonczenie>CURRENT_DATE OR sponsor_zakonczenie="000-00-00") GROUP BY sponsor_id
[SQL] pobierz, plaintext 

czy macie jakiś pomysł jak dorzucić do tego zapytania jeszcze pobranie unikalnych wejść?

Kolumna z numerami IP w tabeli "wejscia" nazywa się: " wejscie_ip"

[darko]

Tutaj:

[SQL] pobierz, plaintext 
WHERE sponsor_aktywny=1 AND sponsor_rozpoczecie<=CURRENT_DATE AND (sponsor_zakonczenie>CURRENT_DATE OR sponsor_zakonczenie="00:00:00 000-00-00") GROUP BY sponsor_id
[SQL] pobierz, plaintext 

dodaj:

[SQL] pobierz, plaintext 
WHERE sponsor_aktywny=1 AND wejscie_ip=$IP AND sponsor_rozpoczecie<=CURRENT_DATE AND (sponsor_zakonczenie>CURRENT_DATE OR sponsor_zakonczenie="00:00:00 000-00-00") GROUP BY sponsor_id
[SQL] pobierz, plaintext 

gdzie zmienna $IP przechowuje ip, z którego klient wchodzi na stronę

Ten post edytował darko 23.03.2011, 19:31:06

[Wilu88]

Tutaj:

[SQL] pobierz, plaintext 
WHERE sponsor_aktywny=1 AND sponsor_rozpoczecie<=CURRENT_DATE AND (sponsor_zakonczenie>CURRENT_DATE OR sponsor_zakonczenie="00:00:00 000-00-00") GROUP BY sponsor_id
[SQL] pobierz, plaintext 

dodaj:

[SQL] pobierz, plaintext 
WHERE sponsor_aktywny=1 AND wejscie_ip=$IP AND sponsor_rozpoczecie<=CURRENT_DATE AND (sponsor_zakonczenie>CURRENT_DATE OR sponsor_zakonczenie="00:00:00 000-00-00") GROUP BY sponsor_id
[SQL] pobierz, plaintext 

gdzie zmienna $IP przechowuje ip, z którego klient wchodzi na stronę

No właśnie nie o to mi chodziło, może źle to opisałem.

To ma być zapytanie w module ze statystykami. Także muszę pobrać tym zapytaniem jednocześnie liczbę kliknięć danej reklamy czyli to zapytanie które napisałem wcześniej, oraz dołączyć do tego kliknięcia unikalne. Twój przykład podałby mi tylko wejścia unikalne jednego usera, czyli musiałbym to zapętlić i wygenerować setki zapytań

Wg. mnie powinno to wyglądać mniej więcej tak: Pobrać wszystkie rekordy z tabeli wejscia, jeżeli adres IP powtarza się w danym dniu to go pominąć zostawiając tylko 1, ale jak to połączyć z poprzednim zapytaniem to nie mam pomysłu :/

Ten post edytował Wilu88 23.03.2011, 19:44:47

[darko]

Bez struktury tabel niewiele pomożemy.

[Wilu88]

Ok to wygląda to tak:

[SQL] pobierz, plaintext 
--
-- Struktura tabeli dla  `cmslm_sponsorzy`
--
 
CREATE TABLE IF NOT EXISTS `cmslm_sponsorzy` (
  `sponsor_id` int(11) NOT NULL AUTO_INCREMENT,
  `sponsor_nazwa` varchar(100) character SET utf8 collate utf8_polish_ci NOT NULL DEFAULT '',
  `sponsor_baner` varchar(40) NOT NULL DEFAULT '',
  `sponsor_rozpoczecie` date NOT NULL DEFAULT '0000-00-00',
  `sponsor_zakonczenie` date NOT NULL DEFAULT '0000-00-00',
  `sponsor_aktywny` tinyint(1) NOT NULL DEFAULT '0',
  `sponsor_link` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY  (`sponsor_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin2 AUTO_INCREMENT=1 ;
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
--
-- Struktura tabeli dla  `cmslm_wejscia`
--
 
CREATE TABLE IF NOT EXISTS `cmslm_wejscia` (
  `wejscie_id` int(11) NOT NULL AUTO_INCREMENT,
  `wejscie_sponsor` int(11) NOT NULL DEFAULT '0',
  `wejscie_data` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `wejscie_ip` varchar(30) NOT NULL DEFAULT '',
  PRIMARY KEY  (`wejscie_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin2 AUTO_INCREMENT=1 ;
[SQL] pobierz, plaintext 

Czyli nikt nie ma pomysłu jak to rozwiązać?

W sumie można było by to zrobić drugim zapytaniem i w pętli porównać id. Tyle że to już będzie mniej wydaje przy większej ilości rekordów.

[darko]

Hej. Jeszcze aktualne? Mógłbyś wrzucić jakieś przykładowe dane (zrzut) żeby można było popróbować na localu?

[Wilu88]

[SQL] pobierz, plaintext 
--
-- Zrzut danych tabeli `sponsorzy`
--
 
INSERT INTO `sponsorzy` (`sponsor_id`, `sponsor_nazwa`, `sponsor_baner`, `sponsor_rozpoczecie`, `sponsor_zakonczenie`, `sponsor_aktywny`, `sponsor_link`) VALUES
(1, 'Testowe1', 'test1.jpg', '0000-00-00', '0000-00-00', 1, 'http://test1.com.pl/'),
(2, 'Testowe2', 'test2.jpg', '0000-00-00', '0000-00-00', 1, 'http://test2.com.pl/');
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
--
-- Zrzut danych tabeli `wejscia`
--
 
INSERT INTO `wejscia` (`wejscie_id`, `wejscie_sponsor`, `wejscie_data`, `wejscie_ip`) VALUES
(1, 1, '2010-09-19 18:02:00','111.111.5.3'),
(2, 1, '2010-09-19 18:09:00','111.111.5.3'),
(3, 1, '2010-09-19 21:13:00','111.111.5.4'),
(4, 1, '2010-09-20 01:49:00','111.111.5.3'),
(5, 2, '2010-09-20 05:21:00','111.111.5.3'),
(6, 2, '2010-09-20 07:01:00','111.111.5.3'),
(7, 1, '2010-09-20 08:04:00','111.111.5.3'),
(8, 1, '2010-09-20 08:06:00','111.111.5.3'),
(9, 2, '2010-09-20 09:30:00','111.111.5.3'),
(10, 1, '2010-09-20 11:15:00','111.111.5.3'),
(11, 2, '2010-09-20 11:16:00','111.111.5.3'),
(12, 2, '2010-10-28 04:30:00','111.111.5.4'),
(13, 1, '2010-10-28 09:02:00','111.111.5.3'),
(14, 1, '2010-10-28 16:59:00','111.111.5.3'),
(15, 1, '2010-10-28 19:35:00','111.111.5.5'),
(16, 1, '2010-10-28 19:38:00','111.111.5.5'),
(231, 2, '2010-10-29 16:27:00','111.111.5.5'),
(232, 2, '2010-10-29 21:35:00','111.111.5.3'),
(233, 2, '2010-10-30 12:39:00','111.111.5.3'),
(234, 1, '2010-10-30 13:53:00','111.111.5.3'),
(235, 1, '2010-10-30 19:07:00','111.111.5.3'),
(236, 1, '2010-10-31 09:43:00','111.111.5.3'),
(237, 2, '2010-10-31 15:13:00','111.111.5.3'),
(238, 1, '2010-10-31 23:42:00','111.111.5.3'),
(239, 1, '2010-11-01 00:49:00','111.111.5.3');
[SQL] pobierz, plaintext 

Adresy IP z palca wpisane tylko dla testów

Ten post edytował Wilu88 25.03.2011, 18:48:38

[darko]

Czy takie zapytanie mniej więcej oddaje to, co potrzebujesz?

[SQL] pobierz, plaintext 
SELECT DISTINCT
sponsor_id,sponsor_nazwa,sponsor_baner,sponsor_rozpoczecie,sponsor_zakonczenie,sponsor_aktywny,sponsor_link,COUNT(w.wejscie_id) AS cID, COUNT(w.wejscie_ip) AS cIP
FROM cmslm_wejscia w, cmslm_sponsorzy s 
LEFT OUTER JOIN cmslm_wejscia
ON wejscie_sponsor=sponsor_id 
WHERE 
DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= w.wejscie_data AND 
s.sponsor_aktywny=1 AND 
s.sponsor_rozpoczecie <= CURRENT_DATE AND 
(s.sponsor_zakonczenie > CURRENT_DATE OR s.sponsor_zakonczenie="000-00-00") 
GROUP BY s.sponsor_id
[SQL] pobierz, plaintext 

Pytam, bo nie mam jak sprawdzić.

Ten post edytował darko 25.03.2011, 22:15:17

[Wilu88]

Czy takie zapytanie mniej więcej oddaje to, co potrzebujesz?

[SQL] pobierz, plaintext 
SELECT DISTINCT
sponsor_id,sponsor_nazwa,sponsor_baner,sponsor_rozpoczecie,sponsor_zakonczenie,sponsor_aktywny,sponsor_link,COUNT(w.wejscie_id) AS cID, COUNT(w.wejscie_ip) AS cIP
FROM cmslm_wejscia w, cmslm_sponsorzy s 
LEFT OUTER JOIN cmslm_wejscia
ON wejscie_sponsor=sponsor_id 
WHERE 
DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= w.wejscie_data AND 
s.sponsor_aktywny=1 AND 
s.sponsor_rozpoczecie <= CURRENT_DATE AND 
(s.sponsor_zakonczenie > CURRENT_DATE OR s.sponsor_zakonczenie="000-00-00") 
GROUP BY s.sponsor_id
[SQL] pobierz, plaintext 

Pytam, bo nie mam jak sprawdzić.

Wywala Błąd zapytania :/ Podaj jeszcze to zapytanie osobne to pobrania unikalnych zobaczymy czy zadziała

Ten post edytował Wilu88 25.03.2011, 22:17:53

[darko]

Sprawdź tylko nazwy tabel, powinno działać. Jaki błąd wywala? To pierwsze zapytanie to było:

[SQL] pobierz, plaintext 
SELECT DISTINCT count(wejscie_ip) FROM cmslm_wejscia w WHERE DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= w.wejscie_data GROUP BY w.wejscie_ip
[SQL] pobierz, plaintext 

Pozmieniaj nazwy tabel na te, na których sprawdzasz i powinno być poprawne.

Ten post edytował darko 25.03.2011, 22:21:07

[Wilu88]

Sprawdź tylko nazwy tabel, powinno działać. Jaki błąd wywala? To pierwsze zapytanie to było:

[SQL] pobierz, plaintext 
SELECT DISTINCT count(wejscie_ip) FROM cmslm_wejscia w WHERE DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= w.wejscie_data GROUP BY w.wejscie_ip
[SQL] pobierz, plaintext 

Pozmieniaj nazwy tabel na te, na których sprawdzasz i powinno być poprawne.

Ten kod kompletnie źle działał bo wyświetlał łączną liczbę wyświetleń, przerobiłem go na taki:

[SQL] pobierz, plaintext 
SELECT DISTINCT count(wejscie_ip),wejscie_sponsor FROM cmslm_wejscia w WHERE DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= w.wejscie_data AND w.wejscie_ip != "000.000.000.000" GROUP BY w.wejscie_sponsor
[SQL] pobierz, plaintext 

No i teraz w tablicy lądują już oddzielone odwiedzenia poszczególnych sponsorów ale nadal liczy każde kliknięcie

[PHP] pobierz, plaintext 
Array ( [0] => Array ( [count(wejscie_ip)] => 1 [wejscie_sponsor] => 1 ) [1] => Array ( [count(wejscie_ip)] => 3 [wejscie_sponsor] => 2 ) ) 
[PHP] pobierz, plaintext 

[Rid]

Odwzorowywanie się na podstawie Ip ,nie daje w pełni właściwego rezultatu,a co z dynamicznym ip??

Ten post edytował Rid 25.03.2011, 22:39:42

[Wilu88]

Odwzorowywanie się na podstawie Ip ,nie daje w pełni właściwego rezultatu,a co z dynamicznym ip??

Wiem, ale nie jest to informacja która ma być ściśle dokładna co do odwiedzającego, to ma być tylko pogląd ile osób klika w to. Przeciętny użytkownik ze zmiennym ip zmienia je raz dziennie. Także limit dzienny według mnie jest całkiem ok.

Odgrzewam temat bo jestem coraz bliżej ale brakuje mi ostatniego warunku:

Oto kod:

[SQL] pobierz, plaintext 
SELECT DISTINCT count(wejscie_ip),wejscie_sponsor FROM cmslm_wejscia w WHERE DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= w.wejscie_data GROUP BY w.wejscie_sponsor
[SQL] pobierz, plaintext 

A w wyniku otrzymuje:

[PHP] pobierz, plaintext 
Array ( [0] => Array ( [count(wejscie_ip)] => 5 [wejscie_sponsor] => 1 ) [1] => Array ( [count(wejscie_ip)] => 2 [wejscie_sponsor] => 6 ) [2] => Array ( [count(wejscie_ip)] => 1 [wejscie_sponsor] => 22 ) ) 
[PHP] pobierz, plaintext 

I za każdym razem po kliknięciu w reklamę się zwiększa wartość. Z tego wnioskuje że warunek sprawdzający datę sprawdza tylko czy kliknięcia są z tego dnia i zlicza je wszystkie.

A jak zrobic żeby odrzucał powtarzające się rekordy w których IP i Data są takie same?