CODEWITHSUNDEEP
mysql
merlin
merlin

Reputation: 2917

How to add sorting position to MySQL records?

I have a MySQL 8 InnoDB Table that stores prices from price comparison sites for specific products of a particular day. Usually prices are sorted ASC so I thought it is not neccessary to save the SERP (position) to the record.

The functionality to save the position has been added now and I am looking for a way to add this information for the old records. It can be identified by sorting after price.

Shema:

create table shops_prices
(
    DATE        date               not null,
    SKU         char(10)           not null,
    MERCHANT_ID tinyint unsigned   not null,
    SHOP_ID     mediumint unsigned not null,
    PRICE       decimal(10, 2)     null,
    SERP        tinyint unsigned   null,
    primary key (DATE, SKU, MERCHANT_ID, SHOP_ID)
)
    comment 'Prices from shops within price comparison merchants like e.g. Idealo
';

Get results for one product for one day:

SELECT *
FROM tbl
WHERE date = currdate() and SKU = 123
ORDER BY price

Desired result example:

   date        SKU  price SERP
   2021-09-02  123  12.99 1
   2021-09-02  123  13.99 2
   2021-09-02  123  14.99 3
   2021-09-03  123  12.99 1
   2021-09-03  123  13.99 2
   2021-09-03  124  18.99 1

How could I update the existing records to add the position to all datasets? (1,2,3,..)

Upvotes: 0

Views: 256

Answers (2)

ysth
ysth

Reputation: 98398

Prior to mysql 8, you would use variables for this:

set @last_date=date(0);
set @last_sku='';  # may need to set collation for this string
set @last_serp=0;
update shops_prices
    set serp=@last_serp:=if(
        @last_date=date && @last_sku=sku,
        @last_serp+1,
        if((@last_date:=date) && (@last_sku:=sku),1,1)
    )
order by date,sku;

Upvotes: 0

Rahul Biswas
Rahul Biswas

Reputation: 3467

ROW_NUMBER() is used for given a serial based on sku and date where smaller price comes first. If WHERE condition needed for particular searching then enable it otherwise disable WHERE clause. use shop_id and merchant_id at partition column along with other two if needed. Use CTE for ranking the position then Update with main table.

-- MySQL (v5.8)
WITH t_cte AS (
      SELECT *
           , ROW_NUMBER() OVER (PARTITION BY sku, date ORDER BY price) row_num
      FROM shops_prices
      WHERE sku = 123 AND date <= '2021-09-05'
)
UPDATE shops_prices s
INNER JOIN t_cte t
        ON s.Date = t.Date
        AND s.SKU = t.SKU
        AND s.MERCHANT_ID = t.MERCHANT_ID
        AND s.SHOP_ID = t.SHOP_ID
        AND s.price = t.price
SET s.SERP = t.row_num

Please check from url https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=51c3eecd19a5b6367722c6905235a5ed

Upvotes: 2

Related Questions