SQL запрос...

[WiZZard]

MySQL 5 ветка
Есть таблицы user, table1 и table2. Две последние связаны с user внешним ключом по полю user_id, кроме того каждая содержит поле time. Нужно сделать запрос, который возвращает количество записей в table1 и table2 для каждого пользователя из таблицы

[WiZZard]

...блин...
При этом нужно сгруппировать все по полю time(по месяцам, годам...). Пример:
Период   | Имя    | Кол-во1 | Кол-во2|
Январь   | Сергей| 10          | 20         |
Февраль | Сергей| 11          | 22         |
Февраль | Иван   | 22          | 3         |
Весь резалтсет должен содержать инфу по всем месяцам... В запросе я считаю количество ... для каждого пользователя и группирую по месяцу(GROUP BY), затем по пользователю. Как группировать по table1.time и table2.time "одновременно"? Чтобы в каждой строке количество считалось за нужный период. Если была бы одна таблица(только table1 или только table2), то проблем бы не было.
Запрос должен быть один!
Возможно, все элементарно, но что-то не получается у меня
Надеюсь, понятно, что я хочу... =)

[HandKot]

MySQl не знаю, но на MS SQL можно так

Код:

SELECT
  Period,
  Name,
  T1.Kol,
  T2.Kol
FROM
  user
LEFT OUTER JOIN (SELECT user_id, Period, COUNT() as [Kol] FROM Table1 GROUP BY user_id, Period) T1 ON T1.user_ID = user.user_id AND T1.Period = user.Period
LEFT OUTER JOIN (SELECT user_id, Period, COUNT() as [Kol] FROM Table2 GROUP BY user_id, Period) T2 ON T2.user_ID = user.user_id AND T2.Period = user.Period

[Dimka]

HandKot, во-первых, в user нет никакого периода. Во-вторых, в MS SQL Server это можно и без подзапросов написать:

Код: (Text)

select
   year(coalesce(t1.time, t2.time)) as year,
   month(coalesce(t1.time, t2.time)) as month,
   coalesce(t1.user_id, t2.user_id) as user_id,
   count(t1.id) as quantity1,
   count(t2.id) as quantity2
from
  table1 t1 full outer join table2 t2 on year(t1.time) = year(t2.time) and month(t1.time) = month(t2.time) and t1.user_id = t2.user_id
group by year(coalesce(t1.time, t2.time)), month(coalesce(t1.time, t2.time)), coalesce(t1.user_id, t2.user_id)

Но! В результате будут пропущены те месяцы, на которые не приходится ни одной записи. А это не то, что нужно WiZZard'у. Нужно сгенерировать таблицу месяцев в диапазоне от min(table1.time, table2.time) до max(table1.time, table2.time) и к ней джойнить результат, выдаваемый приведённым выше запросом.

P.S. Не знаю, есть ли в MySQL аналог оператора coalesce(expr1, expr2, ..., exprN), который есть макрос конструкции

Код: (Text)

case
  when expr1 is not null then expr1
  when expr2 is not null then expr2
  ...
  when exprN is not null then exprN
  else null
end

Но именно этот оператор сводит поля двух таблиц в одно поле.

Ещё обращаю внимание на связь по full outer join, которая является одновременным применением left outer join и right outer join. Одним left или right не обойтись, потому что таблицы равноправны.

[WiZZard]

coalesce(list) в MySQL есть. Он возвращает первый не NULL аргумент. Но как это поможет?
Проблема состоит в том, чтобы при разбиении по месяцам(годам и пр) в каждой строке резалтсета считались записи за table1 и table2 за указанный интервал разбиения. Как построить GROUP BY?

[Dimka]

WiZZard, ты предложенный выше запрос (хотя бы с left join, если в MySQL нет full join) пробовал, прежде чем спрашивать? outer join'ы соединяют таблицы так, что в случае ненахождения соответствия поля из второй (или обоих) таблицы в результирующем отношении забиваются NULL'ами. Агрегатная функция count работает таким образом, что count(*) считает записи, а count(<поле>) считает лишь те записи, в которых в поле хранится не NULL.

Могу выдать полный скрипт эксперимента (для MS SQL Server):

Код: (Text)

use tempdb
go

create table my_users(user_id int primary key)
create table my_t1(t1_id int identity(1,1) primary key, user_id int foreign key references my_users(user_id), time datetime)
create table my_t2(t2_id int identity(1,1) primary key, user_id int foreign key references my_users(user_id), time datetime)
go

insert my_users (user_id) values (1)
insert my_users (user_id) values (2)
insert my_users (user_id) values (3)
go

insert my_t1 (user_id, time) values (1, '01/01/05')
insert my_t1 (user_id, time) values (2, '01/01/05')
insert my_t1 (user_id, time) values (2, '01/01/05')
insert my_t1 (user_id, time) values (1, '02/02/05')
insert my_t1 (user_id, time) values (2, '04/04/05')
insert my_t1 (user_id, time) values (3, '04/04/05')
insert my_t1 (user_id, time) values (1, '01/01/06')
insert my_t1 (user_id, time) values (2, '01/01/06')
insert my_t1 (user_id, time) values (2, '01/01/06')
insert my_t1 (user_id, time) values (1, '02/02/06')
insert my_t1 (user_id, time) values (2, '04/04/06')
insert my_t1 (user_id, time) values (3, '04/04/06')

insert my_t2 (user_id, time) values (1, '01/01/05')
insert my_t2 (user_id, time) values (1, '02/02/05')
insert my_t2 (user_id, time) values (2, '02/02/05')
insert my_t2 (user_id, time) values (2, '03/03/05')
insert my_t2 (user_id, time) values (2, '03/03/05')
insert my_t2 (user_id, time) values (3, '03/03/05')
insert my_t2 (user_id, time) values (1, '01/01/06')
insert my_t2 (user_id, time) values (1, '02/02/06')
insert my_t2 (user_id, time) values (2, '02/02/06')
insert my_t2 (user_id, time) values (2, '03/03/06')
insert my_t2 (user_id, time) values (2, '03/03/06')
insert my_t2 (user_id, time) values (3, '03/03/06')
go

select * from my_users
select * from my_t1
select * from my_t2

select
        year(coalesce(t1.time, t2.time)) as year,
        month(coalesce(t1.time, t2.time)) as month,
        coalesce(t1.user_id, t2.user_id) as user_id,
        count(t1.t1_id) as quantity1,
        count(t2.t2_id) as quantity2
from
        my_t1 t1 full outer join my_t2 t2 on year(t1.time) = year(t2.time) and month(t1.time) = month(t2.time) and t1.user_id = t2.user_id
group by year(coalesce(t1.time, t2.time)), month(coalesce(t1.time, t2.time)), coalesce(t1.user_id, t2.user_id)
go

drop table my_t2
drop table my_t1
drop table my_users
go

И результат запроса:

Код:

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
user_id
-----------
1
2
3

(3 row(s) affected)

t1_id       user_id     time
----------- ----------- -----------------------
1           1           2005-01-01 00:00:00.000
2           2           2005-01-01 00:00:00.000
3           2           2005-01-01 00:00:00.000
4           1           2005-02-02 00:00:00.000
5           2           2005-04-04 00:00:00.000
6           3           2005-04-04 00:00:00.000
7           1           2006-01-01 00:00:00.000
8           2           2006-01-01 00:00:00.000
9           2           2006-01-01 00:00:00.000
10          1           2006-02-02 00:00:00.000
11          2           2006-04-04 00:00:00.000
12          3           2006-04-04 00:00:00.000

(12 row(s) affected)

t2_id       user_id     time
----------- ----------- -----------------------
1           1           2005-01-01 00:00:00.000
2           1           2005-02-02 00:00:00.000
3           2           2005-02-02 00:00:00.000
4           2           2005-03-03 00:00:00.000
5           2           2005-03-03 00:00:00.000
6           3           2005-03-03 00:00:00.000
7           1           2006-01-01 00:00:00.000
8           1           2006-02-02 00:00:00.000
9           2           2006-02-02 00:00:00.000
10          2           2006-03-03 00:00:00.000
11          2           2006-03-03 00:00:00.000
12          3           2006-03-03 00:00:00.000

(12 row(s) affected)

year        month       user_id     quantity1   quantity2
----------- ----------- ----------- ----------- -----------
2005        1           1           1           1
2005        1           2           2           0
2005        2           1           1           1
2005        2           2           0           1
2005        3           2           0           2
2005        3           3           0           1
2005        4           2           1           0
2005        4           3           1           0
2006        1           1           1           1
2006        1           2           2           0
2006        2           1           1           1
2006        2           2           0           1
2006        3           2           0           2
2006        3           3           0           1
2006        4           2           1           0
2006        4           3           1           0
Warning: Null value is eliminated by an aggregate or other SET operation.

(16 row(s) affected)

[WiZZard]

Проверял, конечно. Проверял с LEFT JOIN. Получилось неверно. Буду искать баги.
У тебя резалтсет, вроде бы, верный. Как тот же запрос будут выглядеть в случае большего числа таблиц table1, table2, table3, table4, ...?

[WiZZard]

У меня получается неверное разбиение. Часть записей считается не там, где нужно. Нет строк, типа
Период, Юзер, 0, 1.

[WiZZard]

При использовании group by year(coalesce(t1.time, t2.time)), month(coalesce(t1.time, t2.time)), coalesce(t1.user_id, t2.user_id) получаю результат такой же, как при group by year(t1.time), month(t1.time), coalesce(t1.user_id, t2.user_id).
При этом использую Left Join...

[Dimka]

Проверял с LEFT JOIN. Получилось неверно. Буду искать баги.

Я в самом начале сказал, что с left join правильного результата не будет, нужен full join. Если full join в MySQL нет, придётся думать, как его реализовать. С другой стороны, если ты сформируешь таблицу месяцев, то можно обойтись одними left join каждой tableI к этой таблице месяцев.

Как тот же запрос будут выглядеть в случае большего числа таблиц table1, table2, table3, table4, ...?

Если исходить из вышеприведённой схемы (без таблицы месяцев), то расширение необходимо будет реализовывать по накопительной схеме: объединить две таблицы, потом к результату присоединить третью таблицу и т.д.

Код:

from
  T1 
    full join T2 on <связь T1 c T2>
    full join T3 on <связь join'а между T1 и T2 c T3>
    ... 
    full join TN on <связь join'а между T1, T2, ... и TN-1 c TN> 

Неверной будет схема последовательного соединения:

Код:

from
  T1
    full join T2 on <связь T1 с T2>
    full join T3 on <связь T1 или T2 (с одной на выбор) с T3>
    ...
    full join TN on <cвязь T1 или T2 ... или TN-1 (с одной на выбор) с TN>    

Чтобы упростить условия связок, можно делать подзапросы по каждой таблице (примерно, как выше предлагал HandKot), тогда не придётся дублировать формулы вычисления месяцев и годов, но в остальном схема сохранится.

Последний вариант (если table1, ..., tableN очень много) - работа с временной таблицей. Тогда запрос по table1 записывается во временную таблицу, а запросы по table2, ..., tableN делаются дважды: в одном случае ты выбираешь записи с отсутствующими во временной таблице year, month и user_id и делаешь insert select, а в другом случае для имеющихся записей делаешь update. Думаю, что такая процедура будет работать быстрее, чем единичный очень большой запрос по множеству таблиц.

При использовании group by year(coalesce(t1.time, t2.time)), month(coalesce(t1.time, t2.time)), coalesce(t1.user_id, t2.user_id) получаю результат такой же, как при group by year(t1.time), month(t1.time), coalesce(t1.user_id, t2.user_id).
При этом использую Left Join...

Это верно из-за left join, но в целом результат неверный из-за left join (см. выше).

Результат full join без группировки:

Код:

t1_id       user_id     time                    t2_id       user_id     time
----------- ----------- ----------------------- ----------- ----------- -----------------------
1           1           2005-01-01 00:00:00.000 1           1           2005-01-01 00:00:00.000
4           1           2005-02-02 00:00:00.000 2           1           2005-02-02 00:00:00.000
NULL        NULL        NULL                    3           2           2005-02-02 00:00:00.000
NULL        NULL        NULL                    4           2           2005-03-03 00:00:00.000
NULL        NULL        NULL                    5           2           2005-03-03 00:00:00.000
NULL        NULL        NULL                    6           3           2005-03-03 00:00:00.000
7           1           2006-01-01 00:00:00.000 7           1           2006-01-01 00:00:00.000
10          1           2006-02-02 00:00:00.000 8           1           2006-02-02 00:00:00.000
NULL        NULL        NULL                    9           2           2006-02-02 00:00:00.000
NULL        NULL        NULL                    10          2           2006-03-03 00:00:00.000
NULL        NULL        NULL                    11          2           2006-03-03 00:00:00.000
NULL        NULL        NULL                    12          3           2006-03-03 00:00:00.000
8           2           2006-01-01 00:00:00.000 NULL        NULL        NULL
9           2           2006-01-01 00:00:00.000 NULL        NULL        NULL
6           3           2005-04-04 00:00:00.000 NULL        NULL        NULL
2           2           2005-01-01 00:00:00.000 NULL        NULL        NULL
3           2           2005-01-01 00:00:00.000 NULL        NULL        NULL
11          2           2006-04-04 00:00:00.000 NULL        NULL        NULL
12          3           2006-04-04 00:00:00.000 NULL        NULL        NULL
5           2           2005-04-04 00:00:00.000 NULL        NULL        NULL

И примени к нему своё условие. Очевидно, что для записей с t1.time = NULL группировка будет неверной, поэтому нужно coalesce.

Неправильный результат left join

Код:

t1_id       user_id     time                    t2_id       user_id     time
----------- ----------- ----------------------- ----------- ----------- -----------------------
1           1           2005-01-01 00:00:00.000 1           1           2005-01-01 00:00:00.000
2           2           2005-01-01 00:00:00.000 NULL        NULL        NULL
3           2           2005-01-01 00:00:00.000 NULL        NULL        NULL
4           1           2005-02-02 00:00:00.000 2           1           2005-02-02 00:00:00.000
5           2           2005-04-04 00:00:00.000 NULL        NULL        NULL
6           3           2005-04-04 00:00:00.000 NULL        NULL        NULL
7           1           2006-01-01 00:00:00.000 7           1           2006-01-01 00:00:00.000
8           2           2006-01-01 00:00:00.000 NULL        NULL        NULL
9           2           2006-01-01 00:00:00.000 NULL        NULL        NULL
10          1           2006-02-02 00:00:00.000 8           1           2006-02-02 00:00:00.000
11          2           2006-04-04 00:00:00.000 NULL        NULL        NULL
12          3           2006-04-04 00:00:00.000 NULL        NULL        NULL

Видишь разницу?

[WiZZard]

ОК, все верно!
Тогда остается вопрос по поводу реализации Full outer join в Mysql? Причем есть table1, ..., table6

[Dimka]

Тогда остается вопрос по поводу реализации Full outer join в Mysql? Причем есть table1, ..., table6

Лучше подумай, как получить списко месяцев, тогда, с одной стороны, full join вообще не понадобится, а с другой стороны будут ликвидированы разрывы в тех месяцах, для которых нет записей ни в одной из таблиц.

[WiZZard]

Месяцы, для которых нет ни одной записи уже не нужны...

[WiZZard]

Допустим, я могу сформировать временную таблицу с месяцами. Как она должна выглядеть? Какие поля содержать? Какой запрс в итоге получится?

[Dimka]

WiZZard, теперь у тебя "все карты на руках" - действуй.

[WiZZard]

Я все таки не понял, как с помощью вспомогательной таблицы сформировать нужный запрос...Требуется пример...

[Dimka]

Я все таки не понял, как с помощью вспомогательной таблицы сформировать нужный запрос...Требуется пример...

Я сторонник того, чтобы давать удочку, а не рыбу.

Из твоих вопросов и описания твоих действий я вижу, что тебе нужен не пример, а понимание того, как работают запросы (в понятиях реляционной алгебры). Потому что, если исходная задача (предполагающая full join) ещё является довольно редкой и потому требует некоторых умственных усилий, то после указания этого решения остальное достаточно тривиально.

Поэтому начни с того, какие у тебя будут исходные данные (отношение с месяцами и отношения с событиями пользователей), и как эти данные между собой связать так, чтобы ничего существенного для решения задачи не потерялось (не пропадали бы месяцы, года, пользователи и их события), и в то же время даты событий соответствовали бы месяцам.