SQL подсчитывает все строки со значением в диапазоне от соединения
Я пытаюсь реализовать версию того, на что был дан ответ здесь: Получение подсчета строк, значения которых находятся в диапазоне значений MySQL. Но строки, которые я пытаюсь посчитать, находятся внутри объединения, и, насколько я могу судить, мне нужно иметь возможность назначить объединение псевдониму, чего я не могу понять, как это сделать. Кстати, я использую DuckDB, но проблем быть не должно, поскольку он принимает запросы SQL.
Скажем, мои данные такие:
Таблица 1:
Таблица 2:
И я хочу получить количество всех слов в пределах 1 значения. Я предполагаю, что после внутреннего соединения идентичных идентификаторов и агрегирования результат должен выглядеть следующим образом:
Соединенная и посчитанная таблица:
По аналогии с исходным вопросом, мой вопрос заключается в следующем. Обратите внимание, что я заключил часть INNER JOIN в квадратные скобки, пытаясь присвоить ее переменной/псевдониму, чтобы я мог ссылаться на нее во время LEFT JOIN:
SELECT
id,
val,
words,
COUNT(*) AS cnt
FROM (Table1
INNER JOIN Table2
ON Table1.id=Table2.id) t1
LEFT JOIN t1 t2
ON ABS(t1.val - t2.val) <= 1
GROUP BY
t1.val, t1.id;
Ответы 2
DuckDb поддерживает CTE, поэтому сначала вы обрабатываете t1 и используете его в качестве источника для своих расчетов.
WITH t1 AS
( SELECT t1.id, t1.val,t2.words
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.id=t2.id)
SELECT
t1.id,
t1.val,
t1.words,
COUNT(*) AS cnt
FROM t1
LEFT JOIN t1 t2
ON ABS(t1.val - t2.val) <= 1
GROUP BY
t1.val, t1.id,t1.words;
t1 из списка загорается в выборе ниже, вы можете называть сказки по своему усмотрению, имя cte должно быть таким же, как в выборе ниже
Для непосвященных (таких как я), CTE = общее табличное выражение uckdb.org/docs/sql/query_syntax/with.html
Можно ли это также записать как Коррелированный подзапрос?
with t1 as (
from Table1 join Table2 using (id)
)
from t1 t2 -- the `t2` alias here makes it a correlated subquery
select
*,
(select count(*) from t1 where abs(t1.val - t2.val) <= 1) count
┌───────┬───────┬─────────┬───────┐
│ id │ val │ words │ count │
│ int64 │ int64 │ varchar │ int64 │
├───────┼───────┼─────────┼───────┤
│ 1 │ 1 │ foo │ 4 │
│ 1 │ 1 │ bar │ 4 │
│ 2 │ 1 │ baz │ 4 │
│ 3 │ 3 │ nu │ 2 │
│ 4 │ 2 │ olde │ 5 │
└───────┴───────┴─────────┴───────┘
Когда я использовал это в своем неигровом приложении, оно работало очень хорошо. Я не виню в этом @nbk (поскольку у nbk не было доступа к моим реальным данным), но для сравнения решение nbk было намного медленнее и, как ни странно, привело к получению таблицы с менее чем половиной ожидаемых записей.
Стоит ли ради читабельности беспокоиться о очевидном повторном использовании переменной
t1в нескольких точках?