Статистика посещений

[invander]

Статистика посещений

Пишу партнерку, есть 3 таблицы

CREATE TABLE `stats` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`subaccount_id` int(11) DEFAULT NULL,
`site_id` int(11) DEFAULT NULL,
`date` int(11) DEFAULT NULL,
`ip` int(11) DEFAULT NULL,
`country_id` int(11) DEFAULT NULL,
`page` text,
`referer` text,
PRIMARY KEY (`id`),
KEY `stats_FI_1` (`subaccount_id`),
KEY `stats_FI_2` (`site_id`),
KEY `stats_FI_3` (`ip`),
KEY `stats_FI_4` (`country_id`)
) ENGINE=MyISAM AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;

CREATE TABLE `subaccounts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL,
`name` varchar(255) CHARACTER SET utf8 DEFAULT '',
`tb_link` varchar(255) CHARACTER SET utf8 DEFAULT '',
`default` tinyint(1) DEFAULT '0',
`created_at` int(10) DEFAULT NULL,
`updated_at` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

CREATE TABLE `sites` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`host` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

Необходимо выбрать количество уникальных посетителей, количество уникальных посетителей из одной определённой страны (России country_id=185) с группировкой по subaccount_name

Делаю так:
SELECT su.name as subaccount, st.name as site, DATE_FORMAT(FROM_UNIXTIME(s.date),'%d.%m.%Y')
as date_click, count(s.id) as click, count(DISTINCT s.ip) as unik FROM stats as s
LEFT JOIN subaccounts as su ON (su.id=s.subaccount_id)
LEFT JOIN sites as st ON (st.id=s.site_id)
WHERE su.user_id=1
AND date>=1246824000
AND date<=1246996799
group by subaccount

Но так неправильно происходит подсчёт уникальных посетителей (условно для каждого субаккаунта проставляется 1, хотя у одного субаккаунта точно не было посещений)

В общем я уже запутался как это всё организовать, может создать ещё одну таблицу или в несколько запросов пытаться это сделать. Наставьте на путь истинный пожалуйста

 

[zerkms]

count(DISTINCT s.ip) as unik FROM stats as s

эта конструкция, очевидно ведь, ни при каких условиях не вернёт 0 (разве что в s.ip может быть NULL)
вчитываться в твою тонну букв лень, чтобы дать какой-то конкретный совет - но вектор у тебя уже есть

 

[prolis]

тоже лень вчитываться, но замени left join на inner join

 

[invander]

Автор оригинала: prolis
тоже лень вчитываться, но замени left join на inner join

Ну INNER JOIN наверное вряд-ли поможет.

 

[prolis]

Автор оригинала: invander
Ну INNER JOIN наверное вряд-ли поможет.

а так:
[sql]
SELECT su.name as subaccount, count(s.id) as click, count(DISTINCT s.ip) as unik FROM stats as s
LEFT JOIN subaccounts as su ON (su.id=s.subaccount_id)
LEFT JOIN sites as st ON (st.id=s.site_id)
WHERE su.user_id=1
AND date between 1246824000 and 1246996799
group by subaccount
[/sql]

 

[x-yuri]

invander, а чего ты LEFT JOIN делаешь stats -> subaccounts? При текущем запросе для аккаунтов без посетителей вообще записи должны не возвращаться

 

[invander]

Автор оригинала: x-yuri
invander, а чего ты LEFT JOIN делаешь stats -> subaccounts? При текущем запросе для аккаунтов без посетителей вообще записи должны не возвращаться

Т.е. всё-таки INNER JOIN?

 

[x-yuri]

ты sql-запросы методом научного тыка составляешь? почитай про LEFT JOIN