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');
Я пытаюсь ответить на этот вопрос:
- проблема: полное соединение my_table1 и my_table2 на основе имени. после объединения сохраняйте только те строки, в которых дата из my_table2 больше даты из my_table1. когда это будет сделано (т.е. в объединенном файле), для каждой строки из my_table1 подсчитайте, сколько строк из моей table_2 было сохранено
Я не был уверен, как поступить:
SELECT
t1.*,
t2.var3,
t2.var4,
COUNT(t2.name) OVER (PARTITION BY t1.name, t1.var1) as count
FROM
my_table1 t1
INNER JOIN
my_table2 t2
ON
t1.name = t2.name AND
EXTRACT(YEAR FROM t1.var1) = EXTRACT(YEAR FROM t2.var3) AND
t2.var3 > t1.var1;
Окончательный ответ должен выглядеть так:
name date var2 count
alex 2010-01-01 96 2
alex 2018-01-01 96 2
sara 2005-01-01 94 1
sara 2006-01-01 90 1
john 2010-01-01 94 0
john 2010-01-04 106 0
john 2015-01-01 99 0
tim 1999-01-01 101 0
Это правильный способ работы над этими проблемами?
Ответы 2
вы можете получить желаемый результат, используя предложение Group by с LEFT JOIN:
SELECT
t1.name,
t1.var1 AS date,
t1.var2,
COALESCE(COUNT(t2.name), 0) AS count
FROM
my_table1 t1
LEFT JOIN
my_table2 t2 ON t1.name = t2.name AND t1.var1 < t2.var3
GROUP BY
t1.name, t1.var1, t1.var2
ORDER BY
t1.name, t1.var1;
При использовании агрегатных функций, таких как count, если вы хотите, чтобы желаемый результат отображал значения/количества/количества, вам нужно добавить предложения group by, а если вы хотите добавить условия с помощью Group by, то вы используете предложение HAVING.
проблема: полное соединение my_table1 и my_table2 на основе имени. после объединения сохраняйте только те строки, в которых дата из my_table2 больше даты из my_table1. когда это будет сделано (т.е. в объединенном файле), для каждой строки из my_table1 подсчитайте, сколько строк из моей table_2 было сохранено
Чтобы сохранить только самую последнюю строку, самый простой способ: 1: выполнить ОБЪЕДИНЕНИЕ двух таблиц, а затем 2: сделать вложенный выбор, чтобы получить только самые верхние даты. И 3: наконец, сгруппируйте их по имени, чтобы получить подсчет.
SELECT name, max(var1), var2 from (SELECT t1.name as name,t1.var1 as var1,t1.var2 as var2, count(name) FROM t1 union select t2.name as name, t2.var3 as var1, t2.var4 as var2 from t2) group by name
Это должно сработать. UNION соединяет две таблицы в одну, поэтому вы можете выбирать из обеих таблиц как одну. Не уверен, можно ли выполнить команду max() непосредственно при первом выборе, поэтому, чтобы убедиться, что я использовал для этого вложенный SELECT.
Пожалуйста, ограничьте свой вопрос только одним запросом.