Check if values are the same for each row over criteria in different other columns-CodePudding

DB-Fiddle

CREATE TABLE campaigns 
(
    id SERIAL PRIMARY KEY,
    campaign VARCHAR,
    supplier VARCHAR,
    plan_quantity DECIMAL
);

INSERT INTO campaigns (campaign, supplier, plan_quantity)
VALUES ('C001', 'supplier_a', '500'),
       ('C001', 'supplier_a', '500'),
       ('C001', 'supplier_b', '500'),

       ('C002', 'supplier_a', '600'),
       ('C002', 'supplier_b', '700'),
    
       ('C003', 'supplier_c', '100'),
       ('C003', 'supplier_c', '100'),
  
       ('C004', 'supplier_a', '900'),
       ('C004', 'supplier_c', '800'),
       ('C004', 'supplier_d', '250'),
       ('C004', 'supplier_d', '250'),

       ('C005', 'supplier_b', '380'),
       ('C005', 'supplier_b', '270'),
       ('C005', 'supplier_d', '590');

Expected result:

campaign  |   supplier   |   plan_quantity   |      check         |
----------|--------------|-------------------|--------------------|-------
  C001    |  supplier_a  |        500        |       same         |
  C001    |  supplier_a  |        500        |       same         |
  C001    |  supplier_b  |        500        |    non-relevant    |
----------|--------------|-------------------|--------------------|-------
  C002    |  supplier_a  |        600        |    non-relevant    |
  C002    |  supplier_b  |        700        |    non-relevant    | 
----------|--------------|-------------------|--------------------|-------
  C003    |  supplier_c  |        100        |       same         |
  C003    |  supplier_c  |        100        |       same         |
----------|--------------|-------------------|--------------------|-------
  C004    |  supplier_a  |        900        |    non-relevant    |
  C004    |  supplier_c  |        800        |    non-relevant    |
  C004    |  supplier_d  |        250        |       same         |
  C004    |  supplier_d  |        250        |       same         |
----------|--------------|-------------------|--------------------|-------
  C005    |  supplier_b  |        380        |     different      |
  C005    |  supplier_b  |        270        |     different      |
  C005    |  supplier_d  |        590        |    non-relevant    |

In case a supplier appears multiple times per campaign, I want to see in column check if the plan_quantity for this supplier is the same in every row.

I am getting close to the result when I modify the query from this question:

SELECT
    campaign AS campaign,
    supplier AS supplier,
    plan_quantity AS plan_quantity,
    (CASE 
         WHEN MIN(plan_quantity) OVER (PARTITION BY supplier, campaign) = MAX(plan_quantity) OVER (PARTITION BY supplier, campaign) 
             THEN 'same' 
             ELSE 'different' 
     END) AS check
 FROM
     campaigns
 ORDER BY 
     1, 2, 3;

However, I have no clue how I can add the description non-relevant to the query in case a supplier does not appear multiple times per campaign.

Do you have any idea?

CodePudding user response：

You can slightly modify the query with an additional case expression for when the check is the same and indicate any with a count of 1 are non-relevant:

select 
  campaign,
  supplier,
  plan_quantity, 
  case when 
      Min(plan_quantity) over(partition by campaign, supplier) 
        = Max(plan_quantity) over(partition by campaign, supplier) 
    then 
        case when Count(*) over(partition by campaign, supplier) = 1 
         then 'non-relevant' else 'same' 
        end
    else 'different'
    end as Check
from campaigns
order by 1,2,3;