I have a pricing table that contains the pricing data for products. There are around 600 unique product_id, each currently having 4 days worth of pricing data, which will eventually go up to 30 days. The table below is a small subset of the data to represent that table structure:

So in my script, for each product_id I am trying to get yesterdays price_trend and todays price_trend and then calculate the price_delta between the two. I then order by price_delta and limit the results to 5.

I am having some issues as in some cases yesterdays price_trend is 0 and then todays price trend is 0.50 for example. This does not mean that the price trend has increased, but mostly likely that price_trend was not gathered yesterday for whatever reason.

Now I would like to remove any records where price_trend for today or yesterday equals 0, however, when I add AND pricing.trend_price > 0 the value return is just null instead.

Script:

SELECT 
    magic_sets_cards.name,
    (SELECT pricing.trend_price 
     FROM pricing 
     WHERE pricing.product_id = magic_sets_cards_identifiers.mcm_id 
        AND pricing.date = (SELECT MAX(date) - INTERVAL '2 DAY' FROM pricing)
        AND pricing.trend_price > 0) AS price_yesterday,
    (SELECT pricing.trend_price 
    FROM pricing 
    WHERE pricing.product_id = magic_sets_cards_identifiers.mcm_id
        AND pricing.date = (SELECT MAX(date) FROM pricing)
        AND pricing.trend_price > 0) AS price_today, 
    ((SELECT pricing.trend_price 
      FROM pricing 
      WHERE pricing.product_id = magic_sets_cards_identifiers.mcm_id 
        AND pricing.date = (SELECT MAX(date) FROM pricing)) - 
    (SELECT pricing.trend_price 
     FROM pricing 
     WHERE pricing.product_id = magic_sets_cards_identifiers.mcm_id 
        AND pricing.date = (SELECT MAX(date) - INTERVAL '2 DAY' FROM pricing))) AS price_delta
FROM magic_sets
    JOIN magic_sets_cards ON magic_sets_cards.set_id = magic_sets.id
    JOIN magic_sets_cards_identifiers ON magic_sets_cards_identifiers.card_id = magic_sets_cards.id
    JOIN pricing ON pricing.product_id = magic_sets_cards_identifiers.mcm_id
WHERE magic_sets.code = '2X2'
    AND pricing.date = (SELECT MAX(date) FROM pricing)
ORDER BY price_delta DESC
LIMIT 5

Results:

I would like to get it so that the "Fiery Justice" in this example is just ignored.

CodePudding user response：

with the use of rank() you can get the output ., Look into...

Query without null rows :

with cte as (Select
product_id, 
SUM(CASE WHEN rank = 1 THEN price_trend ELSE null END) today, 
SUM(CASE WHEN rank = 2 THEN price_trend ELSE null END) yesterday,
SUM(CASE WHEN rank = 1 THEN price_trend ELSE 0 END) -
SUM(CASE WHEN rank = 2 THEN price_trend ELSE 0 END) as diff
FROM (
SELECT
product_id,
price_trend, 
date, 
rank() OVER (PARTITION BY product_id ORDER BY date DESC) as rank
FROM tableName where price_trend>0 and  date between current_date-5 and current_date-4) p
WHERE rank in (1,2) 
GROUP BY product_id
) select * from cte where (case when today is null or yesterday is null then 'NULL' else 'VALID' end)!='NULL'

Query with null values :

Select
product_id, 
SUM(CASE WHEN rank = 1 THEN price_trend ELSE 0 END) today, 
SUM(CASE WHEN rank = 2 THEN price_trend ELSE 0 END) yesterday,
SUM(CASE WHEN rank = 1 THEN price_trend ELSE 0 END) -
SUM(CASE WHEN rank = 2 THEN price_trend ELSE 0 END) as diff
FROM (
SELECT
product_id,
price_trend, 
date, 
rank() OVER (PARTITION BY product_id ORDER BY date DESC) as rank
FROM tableName where date between current_date-5 and current_date-4) p
WHERE rank in (1,2)
GROUP BY product_id

Change the condition :

where date between current_date-3 and current_date-2

OUTPUT :

product_id  today   yesterday   diff
1   0.06    0.02    0.04
2   0.64    0.62    0.02

CREATE TABLE tableName 
(
    date    date,
    product_id  int,
    price_trend numeric(9,2)
);

INSERT INTO tableName (date ,product_id ,price_trend) VALUES ('2022-08-21 ', '1 ', '0.02');
INSERT INTO tableName (date ,product_id ,price_trend) VALUES ('2022-08-22 ', '1 ', '0.06');
INSERT INTO tableName (date ,product_id ,price_trend) VALUES ('2022-08-23 ', '1 ', '0.10');
INSERT INTO tableName (date ,product_id ,price_trend) VALUES ('2022-08-24 ', '1 ', '0.13');
INSERT INTO tableName (date ,product_id ,price_trend) VALUES ('2022-08-25 ', '1 ', '0.18');
INSERT INTO tableName (date ,product_id ,price_trend) VALUES ('2022-08-26 ', '1 ', '0.30');

INSERT INTO tableName (date ,product_id ,price_trend) VALUES ('2022-08-21 ', '2 ', '0.62');
INSERT INTO tableName (date ,product_id ,price_trend) VALUES ('2022-08-22 ', '2 ', '0.64');
INSERT INTO tableName (date ,product_id ,price_trend) VALUES ('2022-08-23 ', '2 ', '0.69');
INSERT INTO tableName (date ,product_id ,price_trend) VALUES ('2022-08-24 ', '2 ', '0.78');
INSERT INTO tableName (date ,product_id ,price_trend) VALUES ('2022-08-25 ', '2 ', '0.88');
INSERT INTO tableName (date ,product_id ,price_trend) VALUES ('2022-08-26 ', '2 ', '0.90');