Найти недостающие записи с группировкой

Question

Я пытаюсь реализовать SQL-запрос для определения пропущенных записей из двух таблиц HIVE на основе сценария группировки. Данные как ниже

Таблица 1 - Календарь

month_last_day
20190131
20190229
20190331
20190430

Таблица 2 - Предметы

itemid date
101    20190131
101    20190229
101    20190331
102    20190131
102    20190331
102    20190430

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

Мне нужен вывод, который отображает 2 строки как 101 20190430 и еще одну строку 102 20190229.

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

Answer 1

cross join календарь для отдельных предметов и left join таблица элементов, чтобы получить недостающие строки.

select i.itemid,c.month_last_day
from calendar c 
cross join (select distinct itemid from items) i
left join items it on it.itemid = i.itemid and c.month_last_day = it.dt
where it.dt is null 

Answer 2

Запрос в улье, используя перекрестное соединение и левое внешнее соединение.

with calendar as 
(select '20190131' last_day union all
 select '20190229' last_day union all
 select '20190331' last_day union all
 select '20190430' 
) 
,items as 
(select 101 itemid,'20190131' dt union all
 select 101 itemid,'20190229' dt union all
 select 101 itemid,'20190331' dt union all
 select 102 itemid,'20190131' dt union all
 select 102 itemid,'20190331' dt union all
 select 102 itemid,'20190430' dt
),
res1 as 
(select i.itemid, c.last_day from calendar c, (select distinct itemid from items) i)

select res1.itemid, res1.last_day from res1 left outer join items i on res1.itemid = i.itemid and res1.last_day=i.dt where i.dt is null;