Блог

База данных Postgres, но любая логика SQL должна помочь.

Я извлекаю набор предложений по продажам, которые содержат данный продукт в спецификации. Я делаю это в два этапа: шаг 1, извлеките все ОТДЕЛЬНЫЕ номера котировок, которые содержат данный продукт (по номеру продукта).

На втором шаге извлеките полное предложение со всеми продуктами, перечисленными для каждого уникального номера предложения.

Пока все хорошо. Теперь сложный момент. Некоторые строки являются дубликатами, некоторые нет. Те, которые являются дубликатами (номер цитаты и версия цитаты и номер строки), могут иметь или не иметь обслуживания. Я хочу выбрать строку, которая имеет обслуживание больше 0. Повторяющиеся строки, которые я хочу исключить, — это те, которые имеют обслуживание 0. Проблема в том, что некоторые строки, которые не имеют дубликатов, имеют 0 обслуживания, поэтому я не могу просто фильтровать по обслуживанию.

Чтобы сделать это интересным, база данных хранит цитаты более 20 лет. И ребята из data scientists только что признали, что, возможно, в процессе ETL есть некоторые ошибки…

 --- step 0
--- cleanup the workspace
SET CLIENT_ENCODING TO 'UTF8';
DROP TABLE IF EXISTS product_quotes;

--- step 1
--- get list of Product Quotes
CREATE TEMPORARY TABLE product_quotes AS (
   SELECT DISTINCT master_quote_number
   FROM w_quote_line_d

   WHERE item_number IN ( << model numbers >> )
);

--- step 2
--- Now join on that list
SELECT 
d.quote_line_number,
d.item_number,
d.item_description,
d.item_quantity,
d.unit_of_measure,
f.ref_list_price_amount,
f.quote_amount_entered,
f.negtd_discount,
--- need to calculate discount rate based on list price and negtd discount (%)
CASE
    WHEN ref_list_price_amount > 0 
        THEN 100 - (ref_list_price_amount   negtd_discount) / ref_list_price_amount *100
    ELSE 0
END AS discount_percent,

f.warranty_months,
f.master_quote_number,
f.quote_version_number,
f.maintenance_months,
f.territory_wid,
f.district_wid,
f.sales_rep_wid,
f.sales_organization_wid,
f.install_at_customer_wid,
f.ship_to_customer_wid,
f.bill_to_customer_wid,
f.sold_to_customer_wid,
d.net_value,
d.deal_score,
f.transaction_date,
f.reporting_date

FROM w_quote_line_d d
INNER JOIN product_quotes pq ON (pq.master_quote_number = d.master_quote_number)

INNER JOIN w_quote_f f ON 
    (f.quote_line_number = d.quote_line_number 
    AND f.master_quote_number = d.master_quote_number 
    AND f.quote_version_number = d.quote_version_number)

WHERE d.net_value >= 0 AND item_quantity > 0
ORDER BY f.master_quote_number, f.quote_version_number, d.quote_line_number
  

Логика фильтрации повторяющихся строк выглядит следующим образом:
Для каждой пары master_quote_number / version_number проверьте, есть ли повторяющиеся номера строк. Если это так, выберите тот, у которого обслуживание> 0.

Даже в операторе CASE я не уверен, как это написать.

Мысли? База данных Postgres, но любая логика SQL должна помочь.

Я думаю, вы захотите использовать оконные функции. Одним словом, они потрясающие.

Вот запрос, который будет «дедуплицироваться» на основе ваших критериев:

 select *
from (
    select 
        * -- simplifying here to show the important parts
        ,row_number() over (
            partition by master_quote_number, version_number
            order by maintenance desc) as seqnum
    from w_quote_line_d d
    inner join product_quotes pq 
    on (pq.master_quote_number = d.master_quote_number)
    inner join w_quote_f f 
    on (f.quote_line_number = d.quote_line_number
      and f.master_quote_number = d.master_quote_number 
      and f.quote_version_number = d.quote_version_number)
) x
where seqnum = 1
  

Использование row_number() и выбранные partition by order by критерии и гарантируют, что только ОДНА строка для каждой комбинации quote_number/ version_number получит значение 1, и это будет строка с наибольшим значением в обслуживании (если ваши коллеги правы, в любом случае будет только одна со значением> 0).

Можете ли вы сделать что-то вроде…

 select
    *
from
    w_quote_line_d d
    inner join
        (
            select
                ...
                ,max(maintenance)
            from
                w_quote_line_d
            group by
                ...
        ) d1
   on 
       d1.id = d.id
       and d1.maintenance = d.maintenance;
  

Я правильно понимаю вашу проблему?

Редактировать: забыл группу by!

Я не уверен, но, возможно, вы могли Group By бы использовать все остальные столбцы и использовать MAX(Maintenance) , чтобы получить только наибольшее.

Что вы думаете?