Считать в месяц, если уникально
Я пытаюсь получить SQL-запрос для подсчета уникальных personid за месяц, является «вернувшимся» посетителем, если у него также нет записи «Новый» за месяц.
month | personid | visitstat
---------------------------------
January john new
January john returning
January Bill returning
Таким образом, в запросе, который я ищу, должно быть подсчитано количество для каждого уникального персонального идентификатора, который имеет «возвращение», если только «новый» не существует и для этого персонального идентификатора - в этом случае возвращается count, равное 1 для
January Bill returning
потому что Джон новичок в этом месяце.
Запрос, который я пробовал,
SELECT COUNT(distinct personid) as count FROM visit_info WHERE visitstat = 'Returning' GROUP BY MONTH(date) ORDER BY date
К сожалению, это считается «Вернувшимся», даже если для человека в этом месяце существует «Новая» запись.
Заранее спасибо, надеюсь, я объяснил это достаточно ясно.
Это больше, чем то, что я включил, но я подумал, что включить сюда будет большой беспорядок.
Ответы 2
Вы уже сами написали «волшебное» слово «существует». Вы можете использовать именно это, NOT EXISTS и коррелированный подзапрос.
SELECT count(DISTINCT vi1.personid) count
FROM visit_info vi1
WHERE vi1.visitstat = 'Returning'
AND NOT EXISTS (SELECT *
FROM visit_info vi2
WHERE vi2.personid = vi1.personid
AND year(vi2.date) = year(vi1.date)
AND month(vi2.date) = month(vi1.date)
AND vi2.visitstat = 'New')
GROUP BY year(vi1.date),
month(vi1.date)
ORDER BY year(vi1.date),
month(vi1.date);
Я также рекомендую включать год в выражение GROUP BY, поскольку в противном случае вы можете получить неожиданные результаты, если данные охватывают более одного года. Также используйте только выражения, включенные в предложение GROUP BY или переданные в функцию агрегирования в предложении ORDER BY. MySQL, в отличие от практически любой другой СУБД, может принять это иначе, но также может давать странные результаты.
Этот работает так же, как и другой. Есть ли у них способ сделать так, чтобы строки, из которых он не извлекает значения, заполнялись как нулевые? Спасибо!!
Я также столкнулся с одним из тех же сценариев, с которыми имел дело с базой данных. Возможный способ, которым я это сделал, заключался в использовании group by с предложением Have и подзапросом.
Это не так уж и много данных, не так ли?