SQL: Как выполнить подзапрос с использованием внешнего псевдонима с выражением регистра
Как создать подзапрос, использующий значение внешнего столбца для подзапроса, использующего выражение регистра?
Пример SQL:
SELECT
name,
average,
crnt,
goodFactor
FROM
(
"Crispy Taco" AS name,
( SELECT avg(x)
FROM (
SELECT COUNT() x
FROM tacos
WHERE orderDate > DATEADD(DAY, -1, GETDATE())) <--- THE AVERAGE PRIOR TO TODAY
) AS average,
COUNT(*) AS crnt,
(
SELECT
CASE WHEN crnt < average <---- PROBLEM HERE : Missing columns 'crnt' and 'average'
THEN "not good"
ELSE "pretty good"
END
) AS goodFactor
)
from tacos
Проблема:
«crnt» и «average» — это «отсутствующие столбцы» для подзапроса «goodFactor».
Пример данных: (записи базы данных)
+-------------+---------+---------+
| Name | Ordered | Date |
+-------------+---------+---------+
| Crispy Taco | 1 | 8/29/24 |
| Crispy Taco | 1 | 8/29/24 |
| Crispy Taco | 1 | 8/29/24 |
| Crispy Taco | 1 | 8/29/24 |
| Crispy Taco | 1 | 8/29/24 |
| Crispy Taco | 1 | 8/30/24 |
| Crispy Taco | 1 | 8/30/24 |
| Crispy Taco | 1 | 8/30/24 |
| Crispy Taco | 1 | 8/30/24 |
| Crispy Taco | 1 | 8/30/24 |
| Crispy Taco | 12 | 8/31/24 | <---- ASSUME QUERY INVOKED THIS DAY PRODUCING THE TABLE BELOW
+-------------+---------+---------+
Ожидаемые результаты:
если значение «crnt» больше вычисленного «average»
затем верните «довольно хорошо», иначе верните «не хорошо»
Ожидаемый результат
+-------------+---------------+---------+--------------------+
| Name | Daily Average | Current | GoodFactor |
+-------------+---------------+---------+--------------------+
| Crispy Taco | 5 | 12 | pretty Good |
+-------------+---------------+---------+--------------------+
минимально воспроизводимый пример должен включать в себя как данные таблицы образцов, так и ожидаемый результат. Также добавьте тег для используемой базы данных. (Не все из них поддерживают латеральные производные таблицы.)
Слишком много проблем выше. Делайте меньшие шаги. Кстати, current — это зарезервированное слово ISO/ANSI SQL. en.wikipedia.org/wiki/List_of_SQL_reserved_words
Ваш SQL имеет настолько мало смысла, что его невозможно исправить с помощью обратного проектирования. Пожалуйста, поделитесь примерами данных, которые содержатся в ваших таблицах, которые приводят к желаемым результатам. В нынешнем виде нет абсолютно никакой причины помещать выражение case в подзапрос, но я подозреваю, что мы упускаем что-то важное.
Добавлены примеры записей базы данных @JNeville.
Я не вижу необходимости в подзапросе: SELECT Name, Avg(Ordered) Average, COUNT(*) as Current, CASE WHEN Avg(Ordered) < Count(*) THEN 'Pretty Good' ELSE 'not good' END as GoodFactor FROM yourtable GROUP BY Name; В зависимости от вашей СУБД вам также может сойти с рук CASE WHEN Average < Current THEN 'Pretty Good' ELSE 'Not Good' END
@JNeville Я понимаю, что этого можно добиться без использования подзапроса. Я хочу знать, как заставить подзапрос работать для более сложных операторов SQL. Результатом моего плохо составленного вопроса являются попытки сделать более простую задачу для зрителей вопроса.
В таком случае, можете ли вы поделиться, какая СУБД (и версия)? Моя интуиция подсказывает мне, что любая проблема, с которой вы можете столкнуться и, по вашему мнению, будет частью решения, скорее всего, будет решена с помощью другого шаблона sql. За 15 лет работы с данными мне ни разу не приходилось ссылаться на родительский столбец в подзапросе (за исключением коррелированного подзапроса, да и то для решения проблем, которые лучше решать с помощью оконных функций).
Ответы 1
Это должно работать во всех стандартных СУБД - сегодня 31 августа 2024 года, иначе вы
придется везде заменить CURRENT_DATE на 31 августа 2024 года такими примерными данными:
WITH
-- your input , don't use in final query
indata(name,ordered,date) AS (
SELECT 'Crispy Taco', 1,DATE '08/29/24'
UNION ALL SELECT 'Crispy Taco', 1,DATE '08/29/24'
UNION ALL SELECT 'Crispy Taco', 1,DATE '08/29/24'
UNION ALL SELECT 'Crispy Taco', 1,DATE '08/29/24'
UNION ALL SELECT 'Crispy Taco', 1,DATE '08/29/24'
UNION ALL SELECT 'Crispy Taco', 1,DATE '08/30/24'
UNION ALL SELECT 'Crispy Taco', 1,DATE '08/30/24'
UNION ALL SELECT 'Crispy Taco', 1,DATE '08/30/24'
UNION ALL SELECT 'Crispy Taco', 1,DATE '08/30/24'
UNION ALL SELECT 'Crispy Taco', 1,DATE '08/30/24'
UNION ALL SELECT 'Crispy Taco',12,DATE '08/31/24'
)
-- real query starts here, replace following comma with "WITH"
,
-- need a query grouping by day to get the daily sums
-- before today
daily AS (
SELECT
name
, date
, SUM(ordered) AS per_day
FROM indata
WHERE date < CURRENT_DATE
GROUP BY
name
, date
)
,
-- and one summing the current day
current AS (
SELECT
name
, date
, SUM(ordered) AS current
FROM indata
WHERE date=CURRENT_DATE
GROUP BY
name
, date
)
SELECT
daily.name
, AVG(per_day) AS "Daily Average"
, MAX(current) AS current
, CASE WHEN MAX(current) < AVG(per_day)
THEN 'not good'
ELSE 'pretty good'
END AS good_factor
FROM daily
JOIN current ON daily.name = current.name
GROUP BY
daily.name;
LATERAL derived tableможет ссылаться на столбцы из предыдущих таблиц/подзапросов в том же предложении FROM.