не являюсь специалистом по запросам в mysql, потому прошу коллег о помощи

дано

таблица 1, которая содержит столбцы time (int), user_id, value
time в формате unixtime, содердит секунды, минуты, часы, дни, месяцы, годы. конвертится в пригодный вид функцией FROM_UNIXTIME. дата вытаскивается фунцкией DATE. поле value — туда я пишу разные данные, в принципе, не важно какие

таблица 2, которая содержит юзеров id, username, email, etc

требуется составить выборку, которая
1. в первом стоблце будет содержать дату по убыванию (просто дату, без времени)
2. далее нужны user_id, username, email
3. далее нужно COUNT полей value, за конкретную дату. важно не содержание, а какое количество раз юзер в это поле писал

пример итоговой таблицы. в пределах одной даты юзер повторяться не должен (вот здесь я и завис)

31 мая | юзер 1 | count = 10
31 мая | юзер 2 | count = 3
31 маф | юзер 3 | count = 100

30 мая | юзер 2 | count = 1
30 мая | юзер 5 | count = 5

29 мая | юзер 1 | count = 50
29 мая | юзер 5 | count = 1

заранее благодарю за помощь

Здравствуйте, zverjuga, Вы писали:

Z>не являюсь специалистом по запросам в mysql, потому прошу коллег о помощи

Z>дано

Z>...
Запрос-рыба – необходимо правильно обработать время-дату (time), может еще что-то всплывет в синтаксисе MySQL.

select a.time, a.user_id, t2.username, t2.email, a.cnt
from
(
    select date(time) as time, user_id, count(value) as cnt
    from table1 t1
    group by user_id, date(time)
) a
join table2 t2 on a.user_id = t2.id
order by time desc

Z>пример итоговой таблицы. в пределах одной даты юзер повторяться не должен (вот здесь я и завис)

Использовать группировку по пользователю user_id и по дате, которая отделена от времени из поля time (...group by user_id, date(time))

к сожалению, этот скрипт не полностью решает задачу

1. из-за группировки по user_id юзер показывает только один раз на одну дату. то есть, для остальных дат его не показывает
2. счетчик count тоже считает не правильно. он считает за все время, а надо, чтобы он считал на конекретную дату

собственно, именно с этими тредносями я и столкнулся

Здравствуйте, zverjuga, Вы писали:

Z>к сожалению, этот скрипт не полностью решает задачу

Z>1. из-за группировки по user_id юзер показывает только один раз на одну дату. то есть, для остальных дат его не показывает
Z>2. счетчик count тоже считает не правильно. он считает за все время, а надо, чтобы он считал на конекретную дату

Z>собственно, именно с этими тредносями я и столкнулся

Тогда либо я не правильно понял задачу, либо существует проблема с данными. Предположим у нас в таблицах следующие записи:

insert into table2 select 1, 'Petrov', 'Petrov@mail.ru'
insert into table2 select 2, 'Invanov', 'Ivanov@mail.ru'

insert into table1(user_id, time, value)
select 1, '2013-01-01', 'a' union all select 1, '2013-01-01', 'b' union all select 1, '2013-01-01', 'c'
union all
select 2, '2013-01-01', 'd' union all select 2, '2013-01-01', 'e'
union all
select 1, '2013-01-02', 'f'
union all
select 2, '2013-01-03', 'g' union all select 2, '2013-01-03', 'h' union all select 2, '2013-01-03', 'i'

Запрос представленный выше должен вернуть — за каждую дату и для каждого пользователя, кол-во не NULL значений поля value. Результат запроса будет выглядеть так:

time | user_id | name | email | cnt
2013-01-03 | 2 | Invanov | Ivanov@mail.ru | 3
2013-01-02 | 1 | Petrov | Petrov@mail.ru | 1
2013-01-01 | 1 | Petrov | Petrov@mail.ru | 3
2013-01-01 | 2 | Invanov | Ivanov@mail.ru | 2

Поправьте меня, если я где-то ошибся. Может быть, у вас есть свой пример данных, на котором видно, что на входе и что получаем в результате выполнения запроса?

вроди как по структуре то же самое. только есть одно отличие. у меня поле time хранится в формате unixtime, то есть int. и оно включает в себя и дату, и время (часы, минуты, секунды). то есть,в течении одного дня могут быть зарегистрированы активности в разные времена, а в репорте нужно выводить только дату.

я вот подумал, что поле value можно убрать из выборки. достаточно просто посчитать, сколько количество раз встречается user.id в определенную дату. то есть, таблица 1 содержит

date 1
----------------------
user.id=1 | datetime (date1 1:30)
user.id=2 | datetime (date1 2:30)
user.id=1 | datetime (date1 3:30)

date 2
----------------------
user.id=2 | datetime (date2 1:30)
user.id=2 | datetime (date2 2:30)
user.id=2 | datetime (date2 3:30)

таблица 2
userid | username |email

в конечном репорте надо

(дата)|user|count
date1 | 1 | 2
date1 | 2 | 1
date2 | 2 | 3

Здравствуйте, zverjuga, Вы писали:

Z>вроди как по структуре то же самое. только есть одно отличие. у меня поле time хранится в формате unixtime, то есть int. и оно включает в себя и дату, и время (часы, минуты, секунды). то есть,в течении одного дня могут быть зарегистрированы активности в разные времена, а в репорте нужно выводить только дату.

Z>я вот подумал, что поле value можно убрать из выборки. достаточно просто посчитать, сколько количество раз встречается user.id в определенную дату. то есть, таблица 1 содержит

Я думаю, агрегирующая функция не сильно повлияет на результат работы запроса, подозреваю, что проблема кроется в правильности обработки поля типа unixtime. Посмотрите в документации как корректно из него извлечь дату. Не исключаю, что запрос должен выглядеть как-то так…

select user_id, date(from_unixtime(time)), count(*) as cnt
from table1 t1
group by user_id, date(from_unixtime(time))

теперь все работае. спасибо!