Блог

Главная - Вопросы по программированию - Отсутствующие строки в полном внешнем соединении

Отсутствующие строки в полном внешнем соединении

#sql #apache-spark-sql #outer-join

Вопрос:

Я пытаюсь подсчитать, сколько пользователей наблюдается в каждый из 3 дней подряд. Каждая из 3 промежуточных таблиц ( t0 , t1 , t2 ) имеет 2 столбца: uid (уникальный идентификатор) и d0 (или d1 или d2 , который равен 1 и указывает, что пользователь наблюдается в этот день).

Следующий запрос:

 select d0,d1,d2, count(*) as user_count from (
select uid, 1 as d0
from my_table
where day=5 and uid is not Null
group by uid
) as t0 full outer join (
select uid, 1 as d1
from my_table
where day=6 and uid is not Null
group by uid
) as t1 on t0.uid = t1.uid
full outer join (
select uid, 1 as d2
from my_table
where day=7 and uid is not Null
group by uid
) as t2 on t0.uid = t2.uid and t1.uid = t2.uid
group by d0,d1,d2 order by d0,d1,d2

производит этот вывод из spark.sql(q).toPandas().set_index(["d0","d1","d2"]) :

           user_count
d0 d1 d2            
0  0  1        73455
   1  0        53345
1  0  0        49254
   1  0         8234
      1        78455

Очевидно, отсутствуют две строки: 0 1 1 и 1 0 1 . Почему?!

PS1. Я понимаю, почему 0 0 0 пропал.

PS2. my_table выглядит примерно так:

 create table my_table (uid integer, day integer);
insert into my_table values
 (1, 5), (1, 6), (1, 7),
 (2, 5), (2, 6),
 (3, 5), (3, 7),
 (4, 6), (4, 7),
 (5, 5),
 (6, 6),
 (7, 7);

Для этой таблицы я ожидаю, что запрос вернет

           user_count
d0 d1 d2            
0  0  1        1      --- uid = 7
   1  0        1      --- uid = 6
      1        1      --- uid = 4
1  0  0        1      --- uid = 5
      1        1      --- uid = 3
   1  0        1      --- uid = 2
      1        1      --- uid = 1

Ответ №1:

Используйте два уровня агрегации вместо full join :

 select d0, d1, d2, count(*)
from (select uid,
             max(case when day = 5 then 1 else 0 end) as d0,
             max(case when day = 6 then 1 else 0 end) as d1,
             max(case when day = 7 then 1 else 0 end) as d2
      from my_table
      where uid is not Null
      group by uid
     ) u
group by d0, d1, d2;

Комментарии:

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

2. @sds . . . Если вам просто нужен флаг, а не счетчик, то используйте max() вместо более сложного выражения. Это гораздо лучший способ решения проблемы-два объединения, а не четыре объединения и два объединения.

Ответ №2:

Что касается исходного запроса, то последний FULL JOIN , который следует учитывать t0.uid , может быть равен нулю из-за первого FULL JOIN , поэтому он должен быть ИЛИ нет И.

 select d0,d1,d2, count(*) as user_count 
from (
   select uid, 1 as d0
   from my_table
   where day=5 and uid is not Null
   group by uid
) as t0 
full outer join (
   select uid, 1 as d1
   from my_table
   where day=6 and uid is not Null
   group by uid
) as t1 on t0.uid = t1.uid
full outer join (
   select uid, 1 as d2
   from my_table
   where day=7 and uid is not Null
   group by uid
) as t2 on t0.uid = t2.uid or t1.uid = t2.uid
group by d0,d1,d2 
order by d0,d1,d2;

База данных SQL Server<>скрипка

Лично я бы придерживался решения Гордона Линоффа.

Метки: