У меня есть 2 таблицы следующим образом:
Table1:
ID Date
1 2022-01-01
2 2022-02-01
3 2022-02-05
Table2
ID Date Amount
1 2021-08-01 15
1 2022-02-10 15
2 2022-02-15 20
2 2021-01-01 15
2 2022-02-20 20
1 2022-03-01 15
Я хочу выбрать строки в Table2
таким образом, чтобы в Date
были выбраны только строки после Table1
в Table2
, и вычислить сумму количеств каждого подмножества и max(date)
в Table2
для каждого подмножества grouped
по идентификатору.
Таким образом, результат будет выглядеть
ID Date Amount
1 2022-03-01 30
2 2022-02-20 40
Новичок в SQL здесь... Я попробовал внутреннее соединение, но не смог передать фильтр даты...
Пробовал запрос:
with table1 as (select * from table1)
,table2 as (select * from table2)
select * from table1 a
inner join table2 b on (a.id=b.id)
Спасибо!
Лично не знаком со Snowflake, но стандартный SQL-запрос, который должен работать, будет таким:
select id, Max(date) Date, Sum(Amount) Amount
from Table2 t2
where exists (
select * from Table1 t1
where t1.Id = t2.Id and t1.Date < t2.Date
)
group by Id;
Обратите внимание, что, поскольку вам требуются данные только из таблицы 2, существуют предпочтительнее внутреннего соединения и почти во всех случаях будет более производительным, чем соединение, в худшем случае таким же.
Вот как я бы сделал это со Снежинкой:
--create the tables and load data
--table1
CREATE TABLE TABLE1 (ID NUMBER, DATE DATE);
INSERT INTO TABLE1 VALUES (1, '2022-01-01');
INSERT INTO TABLE1 VALUES (2 , '2022-02-01');
INSERT INTO TABLE1 VALUES (3 , '2022-02-05');
--table 2
CREATE TABLE TABLE2 (ID NUMBER, DATE DATE, AMOUNT NUMBER);
INSERT INTO TABLE2 VALUES(1, '2021-08-01', 15);
INSERT INTO TABLE2 VALUES(1, '2022-02-10', 15);
INSERT INTO TABLE2 VALUES(2, '2022-02-15', 20);
INSERT INTO TABLE2 VALUES(2, '2021-01-01', 15);
INSERT INTO TABLE2 VALUES(2, '2022-02-20', 20);
INSERT INTO TABLE2 VALUES(1, '2022-03-01', 15);
Теперь получите данные с помощью выбора
SELECT TABLE1.ID, MAX(TABLE2.DATE), SUM(AMOUNT)
FROM TABLE1, TABLE2
WHERE TABLE1.ID = TABLE2.ID
AND TABLE1.DATE < TABLE2.DATE
GROUP BY TABLE1.ID
Полученные результаты
Я БЫ | МАКС(ТАБЛИЦА2.ДАТА) | СУММА(СУММА) |
---|---|---|
1 | 2022-03-01 | 30 |
2 | 20.02.2022 | 40 |
Как и Пол, я бы использовал JOIN, но я бы поместил предложения в ON, поэтому, если вы присоединяетесь к большему количеству таблиц, оптимизатору SQL будет проще увидеть, что является намерением для каждой таблицы/объединения. Я бы также использовал псевдонимы для таблиц и использовал псевдоним, чтобы не было места путанице, откуда берется значение, что опять же по привычке облегчает жизнь при составлении более сложного SQL или вырезании и вставке в большие блоки кода. .
поэтому с некоторыми CTE для данных:
WITH table1(id, date) AS (
SELECT * FROM VALUES
(1, '2022-01-01'),
(2 , '2022-02-01'),
(3 , '2022-02-05')
), table2(id, date, amount) AS (
SELECT * FROM VALUES
(1, '2021-08-01'::date, 15),
(1, '2022-02-10'::date, 15),
(2, '2022-02-15'::date, 20),
(2, '2021-01-01'::date, 15),
(2, '2022-02-20'::date, 20),
(1, '2022-03-01'::date, 15)
)
Следующий SQL:
SELECT a.id,
max(b.date) as max_date,
sum(b.amount) as sum_amount
FROM table1 AS a
JOIN table2 AS b
ON a.id = b.id AND a.date <= b.date
GROUP BY 1
ORDER BY 1;
Я БЫ | MAX_DATE | SUM_AMOUNT |
---|---|---|
1 | 2022-03-01 | 30 |
2 | 20.02.2022 | 40 |