статистика изменения посещаемости - outer join

[grigori]

статистика изменения посещаемости - outer join

Есть у меня статистика посещаемости ресурсов по датам - где сколько пользователей было.

CREATE TABLE redirect_log (
stamp date,
proxy_id int,
count int
)

Надо посчитать разницу между количеством посещений за вчера и поза-вчера, и вывести информацию о ресурсах, в посещаемости которых были самые большие изменения.

Хочу сделать, как привык:
подзапросом выборку за вчера, full outer join на выборку за позавчера,
разницу посещаемости посчитать и по модулю отсортировать.

select t1.proxy_id, t1.count as t1c , t2.count as t2c, CAST(t1.count-t2.count as SIGNED) as diff
from(
    select count , proxy_id
    from redirect_log
    where CURRENT_DATE - INTERVAL 2 day = stamp
) as t1
full outer join (
    select count , proxy_id
    from redirect_log
    where CURRENT_DATE- INTERVAL 1 day = stamp

)as t2 on t1.proxy_id=t2.proxy_id

Нет в mysql full outer join - читаю, надо делать union из right join на left join.
Это получится 4 подзапроса. MySQL подзапросы, насколько я помню, не оптимизирует...

Пробую вынести данные во временные таблицы и сделать по ним union - пишет "ERROR 1137 (HY000): Can't reopen table: 't1'"

MySQL не может временную таблицу 2 раза открыть в одном запросе?
Чем больше узнаю MySQL - тем больше люблю Postgres ...

Похоже, что решение - 3я временная таблица и 2 отдельных insert select вместо union, а затем - select distinct:

drop TEMPORARY table if exists t1;
drop TEMPORARY table if exists t2;
drop TEMPORARY table if exists t3;

CREATE TEMPORARY TABLE t1
    select count , proxy_id from redirect_log
    where CURRENT_DATE - INTERVAL 2 day = stamp;
    
CREATE TEMPORARY TABLE t2
    select count , proxy_id from redirect_log
    where CURRENT_DATE- INTERVAL 1 day = stamp;


create TEMPORARY table t3
select t2.proxy_id, 
@a:=IFNULL(t1.count,0) as yest, @b:=t2.count as dby, CAST(@a-@b as SIGNED) as diff
from t1 right join t2 on t1.proxy_id=t2.proxy_id;

insert INTO t3
select t1.proxy_id, 
@a:=t1.count, @b:=IFNULL(t2.count,0), CAST(@a-@b as SIGNED)
from t1 left join t2 on t1.proxy_id=t2.proxy_id;

select distinct * from t3 order by abs(diff) desc;

Может, кто-то знает правильное решение, а я иду не тем путем, и можно 1 запрос не переписывать через 8?

 

[alpine]

grigori

Нет в mysql full outer join - читаю, надо делать union из right join на left join.
Это получится 4 подзапроса. MySQL подзапросы, насколько я помню, не оптимизирует...

Тоесть через UNION ты не пробовал и решил сразу пойти путем временных таблиц?

 

[chira]

grigori
попробуй:

SELECT proxy_id
 , SUM(IF(CURRENT_DATE - INTERVAL 2 day = stamp, count, -count)) diff
FROM redirect_log
where stamp IN (CURRENT_DATE - INTERVAL 2 day,CURRENT_DATE- INTERVAL 1 day)
GROUP BY proxy_id

 

[grigori]

Автор оригинала: alpine
grigori

Тоесть через UNION ты не пробовал и решил сразу пойти путем временных таблиц?

То есть я не лентяй и сделал все, до чего додумался сам. Для сомнивающихся - вот запрос:

explain select t1.proxy_id, t1.count as t1c , t2.count as t2c, CAST(t1.count-t2.count as SIGNED) as diff
from (
    select count , proxy_id from redirect_log
    where CURRENT_DATE - INTERVAL 2 day = stamp
) as t1 
right join (
    select count , proxy_id from redirect_log
    where CURRENT_DATE- INTERVAL 1 day = stamp
) as t2 on t1.proxy_id=t2.proxy_id
union
select t1.proxy_id, t1.count as t1c , t2.count as t2c, CAST(t1.count-t2.count as SIGNED) as diff
from (
    select count , proxy_id from redirect_log
    where CURRENT_DATE - INTERVAL 2 day = stamp
) as t1 left join (
    select count , proxy_id from redirect_log
    where CURRENT_DATE- INTERVAL 1 day = stamp
) as t2 on t1.proxy_id=t2.proxy_id

Получается 4-кратная полная выборка одной таблицы.

Сделал через временные таблицы - union не работает.

А вот если, ты теперь дашь разумный совет, alpine - я буду очень удивлен и благодарен.

-~{}~ 16.03.07 14:10:

chira - спасибо! это как-раз то решение, о существовании которого я подозревал, но не мог додуматься!