Как получить значение из запроса другой таблицы для создания нового столбца (postgresql)

#sql #postgresql #case

#sql #postgresql #случай

Вопрос:

Я новичок в postgres и хочу иметь возможность устанавливать значение Y, если order (таблица заказов) — это заказ за первый месяц (таблица заказов за первый месяц)

таблица заказов за первый месяц приведена ниже. Он покажет только заказ, размещенный пользователем в первый раз за месяц:

 customer_id | order_date                | order_id
--------------------------------------------------
a1          | December 6, 2015, 8:30 PM | orderA1
 

таблица заказов приведена ниже. Он показывает все записи порядка:

 customer_id | order_date                 | order_id
-----------------------------------------------------
a1          | December 6, 2020, 8:30 PM  | orderA1 
a1          | December 7, 2020, 8:30 PM  | orderA2 
a2          | December 11, 2020, 8:30 PM | orderA3 
 

Чтобы получить столбец заказа за первый месяц в таблице заказов, я попытался использовать регистр, как показано ниже. Но тогда это выдаст ошибку более чем на одну строку, возвращаемую подзапросом.

 SELECT DISTINCT ON (order_id) order_id, customer_id,
(CASE when (select distinct order_id from first_month_order_table) = order_id then 'Y' else 'N'
 END)
FROM order_table
ORDER BY order_id;
 

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

 SELECT DISTINCT ON (order_id) order_id, customer_id,
(CASE when (select count order_id from first_month_order_table) then 'Y' else 'N'
 END)
FROM order_table
ORDER BY order_id;
 

Как я могу определить, является ли заказ заказом первого месяца, и эффективно установить значение как Y для каждого заказа в таблице заказов?

Ответ №1:

Используйте left join следующее:

 SELECT o.order_id, o.customer_id,
       CASE when f.order_id is not null then 'Y' else 'N' END as flag
FROM order_table o left join first_month_order_table f
  on f.order_id = o.order_id 
ORDER BY o.order_id;
 

Ответ №2:

Если у вас есть все заказы в orders таблице, вам не нужна вторая таблица. Просто используйте оконные функции. Следующее возвращает логическое значение, которое я нахожу гораздо более удобным, чем символьный флаг:

 select o.*,
       (row_number() over (partition by customer_id, date_trunc('month', order_date order by order_date) = 1) as flag
from orders o;
 

Если вам нужен символьный флаг, то вам нужно case :

 select o.*,
       (case when row_number() over (partition by customer_id, date_trunc('month', order_date order by order_date) = 1
             then 'Y' else 'N'
        end) as flag
from orders o;
 

Комментарии:

1. @tiredqa_18 . , , я предоставил этот ответ, потому что я думаю, что это гораздо более простое решение.