Условный возврат данных столбца SQL (на основе кортежа)

Я создал SQL-запрос, который возвращает мне элементы из таблицы фильмов, удовлетворяющие одному из двух условий. Это либо самые прибыльные (брутто-бюджетные), либо самые дешевые фильмы.

Однако я хотел добавить в возвращаемый запрос столбец, в котором говорилось «наиболее выгодно» или «наименее дорого» в зависимости от того, какое из условий удовлетворяет кортеж. Я попытался найти решение в Интернете; и я решил попробовать использовать CASE WHEN в части SELECT запроса.

Исходный sql-запрос:

SELECT DISTINCT Films.title, Films.year
FROM Films,
(
    SELECT DISTINCT (MAX(Films.gross-Films.budget))AS profit FROM Films
) AS Temp1
WHERE 
(
    (Films.gross-Films.budget)=Temp1.profit) 
    OR (Films.budget)=(SELECT DISTINCT Min(Films.budget) FROM Films)
)

СЛУЧАЙ, КОГДА попытка:

SELECT DISTINCT 
    CASE WHEN Temp1.profit=Max(Temp1.profit) THEN 'most profitable' ELSE 'least expensive' END AS feature, 
    Films.title, 
    Films.year
FROM Films,
(
    SELECT DISTINCT (MAX(Films.gross-Films.budget))AS profit FROM Films
) AS Temp1
WHERE 
(
    (Films.gross-Films.budget)=Temp1.profit) 
    OR (Films.budget)=(SELECT DISTINCT Min(Films.budget) FROM Films)
)

Однако это дало всевозможные ошибки, такие как: столбец «temp1.profit» должен присутствовать в предложении GROUP BY или использоваться в агрегатной функции.

Я не уверен, почему он спрашивает об этом, но я, тем не менее, попытался дать ему то, что он хотел, добавив строку

GROUP BY TEMP1.profit,Films.title,Films.year;

Это заставило ошибку исчезнуть, но теперь все строки получают одно и то же «наиболее прибыльное» значение в столбце функций, даже если они там не для этого!

Я не знаю, есть ли способ заставить это работать, но даже совершенно другой способ заставить эту колонку работать был бы большим подспорьем.

Посмотрите на CTE.

Adrian Klaver 18.11.2022 18:52

distinct во внутреннем запросе ("temp1") бесполезен, так как этот запрос никогда не вернет более одной строки.

a_horse_with_no_name 19.11.2022 10:14
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
2
69
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Ответ принят как подходящий

Вы не можете напрямую повторно использовать вычисление из предложения WHERE в предложении SELECT, но вы можете получить этот эффект, используя WITH запросы или переместив предложение WHERE во внешний выбор. В вашем случае я думаю, что объединение обеих стратегий дает самый чистый результат:

WITH max_profit AS (SELECT MAX(gross-budget) FROM Films),
min_cost AS (SELECT MIN(budget) FROM Films)
SELECT title, year, most_profitable, least_expensive FROM
  (SELECT 
      Films.title, Films.year, 
      (Films.gross-Films.budget = (SELECT * FROM max_profit)) AS most_profitable,
      (Films.budget = (SELECT * FROM min_cost)) AS least_expensive
   FROM Films
  )
  WHERE most_profitable OR least_expensive;

Вывод здесь немного отличается от того, что вы пытались получить: он должен иметь столбцы с именами most_profitable и наименее_дорого, для которых установлено значение true или false, причем хотя бы один из них всегда имеет значение true. Мне кажется, это лучше, потому что, возможно, один и тот же фильм может быть и самым дешевым, и самым прибыльным, но вы можете поместить оператор case обратно во внешнее предложение SELECT, если хотите:

WITH max_profit AS (SELECT MAX(gross-budget) FROM Films),
min_cost AS (SELECT MIN(budget) FROM Films)
SELECT title, year, (CASE WHEN most_profitable THEN 'most profitable' ELSE 'least expensive' END) FROM
  (SELECT 
      Films.title, Films.year, 
      (Films.gross-Films.budget = (SELECT * FROM max_profit)) AS most_profitable,
      (Films.budget = (SELECT * FROM min_cost)) AS least_expensive
   FROM Films
  )
  WHERE most_profitable OR least_expensive;

Это здорово, но я бы очень хотел научиться помещать эту информацию в один столбец. Это может быть потому, что я новичок в sql, но я не понимаю, как использовать внешнее ГДЕ для этого. не мог бы ты показать мне?

Matteo Spinelli 18.11.2022 22:53

Отредактировано, чтобы сделать его более понятным, надеюсь, что это поможет.

histocrat 18.11.2022 23:09

Пришлось добавить псевдоним AS1 после подзапроса, чтобы обойти ошибку «Подзапрос в FROM должен иметь псевдоним». Кроме этого, это как раз то, что я искал. Спасибо!

Matteo Spinelli 19.11.2022 12:23

Для начала: вам не нужны эти многочисленные подзапросы к таблице фильмов, чтобы определить наиболее прибыльные и наименее дорогие элементы! Вы можете просто использовать оконные функции. Вот запрос, который ранжирует все фильмы по (уменьшению) прибыли и (возрастанию) бюджета:

select f.title, f.year,
    row_number() (order by gross - budget desc) rn_profit,
    row_number() (order by budget) rn_budget
from films f

Затем мы можем превратить его в подзапрос и отфильтровать фильмы, которые нам нужны:

select *
from (
    select f.title, f.year,
        row_number() (order by gross - budget desc) rn_profit,
        row_number() (order by budget) rn_budget
    from films f
) f
where 1 in (rn_profit, rn_budget)

Это дает вам две строки (или только одну, если один фильм возглавляет обе категории); строка, в которой столбец rn_profit имеет значение 1, указывает на самый прибыльный фильм, а значение 1 в rn_budget указывает на менее дорогой. Вы можете интерпретировать эту информацию в выражении case в предложении select по своему усмотрению.

Я бы, вероятно, использовал dense_rank(), чтобы включить фильмы с одинаковой прибылью или бюджетом (хотя не уверен, насколько это вероятно)

a_horse_with_no_name 19.11.2022 10:16

Другой вариант — использовать ограничивающее предложение после сортировки по прибыли или багдету:

(
  select *, 'most profitable'
  from films
  order by gross - budget desc
  fetch first 1 rows with ties
)
union all
(
  select *, 'least expensive'
  from films
  order by budget
  fetch first 1 rows with ties
)

Обычно нет необходимости заключать запросы объединения в круглые скобки, но в этом случае это необходимо, потому что мы должны применить ORDER BY и ограничить каждый из них в отдельности. союз всех

Другие вопросы по теме