SQL; Обеспечение выполнения условия после соединения
У меня есть эти две таблицы:
CREATE TABLE my_table1 (
name VARCHAR(50),
var1 DATE,
var2 INT
);
INSERT INTO my_table1 (name, var1, var2) VALUES
('john', '2010-01-01', 94),
('john', '2010-01-04', 106),
('john', '2015-01-01', 99),
('alex', '2010-01-01', 96),
('alex', '2018-01-01', 96),
('sara', '2005-01-01', 94),
('sara', '2006-01-01', 90),
('tim', '1999-01-01', 101);
CREATE TABLE my_table2 (
name VARCHAR(50),
var3 DATE,
var4 CHAR(1)
);
INSERT INTO my_table2 (name, var3, var4) VALUES
('john', '2001-01-01', 'a'),
('john', '2002-01-01', 'b'),
('alex', '2021-01-01', 'c'),
('alex', '2022-01-01', 'd'),
('sara', '1999-01-01', 'e'),
('sara', '2023-01-01', 'f');
Я пытаюсь ответить на эту проблему:
проблема 1: для каждого имени в my_table2 найти самую последнюю строку (по дате). присоедините эту строку к my_table1. однако после объединения убедитесь, что дата из my_table2 больше даты из my_table1 (если нет, то удалите). Конечный результат должен иметь то же количество строк, что и my_table1.
Для проблемы 1 я попытался решить проблему следующим образом:
# problem 1
SELECT t1.*, t2.*
FROM my_table1 t1
JOIN (
SELECT name, var3, var4
FROM (
SELECT name, var3, var4,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY var3 DESC) as rn
FROM my_table2
) tmp
WHERE rn = 1
) t2
ON t1.name = t2.name
WHERE t1.var1 < t2.var3;
Код частично выдает правильный результат, но я не могу понять, как включить сюда строку NA для Джона и Тима.
Может кто-нибудь, пожалуйста, покажите мне, как это сделать правильно?
Спасибо!
Примечание. Я попробовал подход с функцией COALESCE. Это вызвало скандал у Тима, но не вызовет скандала у Джона:
SELECT t1.*, COALESCE(t2.name, 'NA') as name, t2.var3, t2.var4
FROM my_table1 t1
LEFT JOIN (
SELECT name, var3, var4
FROM (
SELECT name, var3, var4,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY var3 DESC) as rn
FROM my_table2
) tmp
WHERE rn = 1
) t2
ON t1.name = t2.name
WHERE t1.var1 < COALESCE(t2.var3, '9999-12-31');
@ charlieface: ты имеешь в виду вот так?
"SELECT t1.*, t2.* FROM my_table1 t1 LEFT JOIN (SELECT name, var3, var4 FROM ( SELECT name, var3, var4, ROW_NUMBER() OVER (PARTITION BY name ORDER BY var3 DESC) as rn FROM my_table2) tmp WHERE rn = 1 ) t2 ON t1.name = t2.name AND t1.var1 < t2.var3;
Выборочные данные — это здорово, но минимально воспроизводимый пример также должен включать ожидаемый результат.
Ответы 3
Вы можете получить ожидаемые результаты, если добавите предложениеwhere в левое предложение соединения:
SELECT t1.*, COALESCE(t2.name, 'NA') as name, t2.var3, t2.var4
FROM my_table1 t1
LEFT JOIN (
SELECT name, var3, var4
FROM (
SELECT name, var3, var4,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY var3 DESC) as rn
FROM my_table2
) tmp
WHERE rn = 1
) t2
ON t1.name = t2.name and t1.var1 < COALESCE(t2.var3, '9999-12-31');
Это даст вам такой результат:
name var1 var2 name var3 var4
john 2010-01-01 94 NA null null
john 2010-01-04 106 NA null null
john 2015-01-01 99 NA null null
alex 2010-01-01 96 alex 2022-01-01 d
alex 2018-01-01 96 alex 2022-01-01 d
sara 2005-01-01 94 sara 2023-01-01 f
sara 2006-01-01 90 sara 2023-01-01 f
tim 1999-01-01 101 NA null null
Надеюсь, это то, что вам нужно.
| для каждого имени в my_table2 найдите самую последнюю строку (по дате)
В my_table2 есть 3 разных имени, поэтому вам нужны 3 самые последние строки данных.
Select *
From ( Select NAME, VAR3, VAR4,
Max(VAR3) Over(Partition By NAME) "LAST_DATE"
From my_table2 )
Where VAR3 = LAST_DATE
--
/* R e s u l t :
NAME VAR3 VAR4 LAST_DATE
------------------- -------- ---- ---------
alex 01.01.22 d 01.01.22
john 01.01.02 b 01.01.02
sara 01.01.23 f 01.01.23 */
| присоедините эту строку к my_table1
| после присоединения — убедитесь, что дата из my_table2 больше даты из my_table1
| (если нет, то удалите)
| Конечный результат должен иметь то же количество строк, что и my_table1
.
Эти инструкции противоречивы. Если вы хотите, чтобы количество возвращаемых строк было таким же, как количество строк в my_table1, тогда:
Select t1.*, t2.VAR3, t2.VAR4
From my_table1 t1
Left Join ( Select *
From ( Select NAME, VAR3, VAR4,
Max(VAR3) Over(Partition By NAME) as LAST_DATE
From my_table2
)
Where VAR3 = LAST_DATE
) t2 ON t2.name = t1.name And
t2.LAST_DATE > t1.VAR1
/* R e s u l t :
NAME VAR1 VAR2 VAR3 VAR4
------------------ ---------- ---------- ---------- -------
alex 01.01.10 96 01.01.22 d
alex 01.01.18 96 01.01.22 d
sara 01.01.05 94 01.01.23 f
sara 01.01.06 90 01.01.23 f
john 01.01.10 94
john 04.01.10 106
john 01.01.15 99
tim 01.01.99 101 */
Но если задача состоит в том, чтобы удалить не совпадающие строки, то можно:
- поместите INNER JOIN вместо Left Join.
- оставьте левое соединение и переместите условие даты из предложения ON соединения в предложение Where запроса
Select t1.*, t2.VAR3, t2.VAR4
From my_table1 t1
Left Join ( Select *
From ( Select NAME, VAR3, VAR4,
Max(VAR3) Over(Partition By NAME) as LAST_DATE
From my_table2
)
Where VAR3 = LAST_DATE
) t2 ON t2.name = t1.name
Where t2.LAST_DATE > t1.VAR1
Оба удалит строки Джона и Тима из результата. Первый внутри соединения (предложение ON) и второй после соединения (если это то, что означает одна из инструкций) с использованием предложения Where.
/* R e s u l t :
NAME VAR1 VAR2 VAR3 VAR4
------------------ ---------- ---------- ---------- -------
alex 01.01.10 96 01.01.22 d
alex 01.01.18 96 01.01.22 d
sara 01.01.05 94 01.01.23 f
sara 01.01.06 90 01.01.23 f */
Я использовал подход, аналогичный предыдущим, но для лучшей читаемости я отформатировал код с использованием CTE:
WITH CTE AS
(
SELECT name, var3, var4,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY var3 DESC) as rn
FROM my_table2
), tmp as
(
SELECT name, var3, var4 FROM CTE WHERE rn = 1
)
SELECT t1.*, COALESCE(t2.name, 'NA') as name, t2.var3, t2.var4
FROM my_table1 t1 LEFT JOIN tmp t2
ON t1.name = t2.name and t1.var1 < COALESCE(t2.var3, '9999-12-31');
Результат
Верните последний
WHEREобратно в подзапрос и используйтеLEFT JOIN? Трудно сказать без ожидаемых результатов.