i have 2 tables (mysql)

1. tbl_products
2. tbl_counting

I have collected "rack" & "stock" information by 3 users in tbl_counting table. I want to update "tbl_products.rack" & "tbl_products.stock" with tbl_counting data with this condition (like Result):

• Each "Rack" & "Stock" information must be collected by at least 3 users
  
• tbl_products.rack" & "tbl_products.stock" must update with highest frequency (At least 2 times) of "Rack" or "Stock" information
• If "Rack" or "Stock" information was repeated less than 2 times, specify field with "Unknow". (like result)

Thanks

1. tbl_products (befor update)

2. tbl_counting

Result: tbl_products(after UPDATE)

select 
tbl_counting.product_id,
tbl_counting.rack,
MAX(tbl_counting.stock),
count(*) as freq
from tbl_counting 

group by tbl_counting.product_id
having count(*) =(select max(freq) 
from (select product_id,count(*) as freq 
from tbl_counting group by product_id) tbl_counting)

CodePudding user response：

Here is one possible solution. The first two cte gets you the count of users and distinct racks by product, which are used in the next cte to filter the records that meet the given criteria.

WITH uses_count AS
(
    SELECT product_id,              
           COUNT(DISTINCT user_id) AS num_users         
    FROM tbl_counting 
    GROUP BY product_id
)
, rack_count AS
(
    SELECT product_id,   
           rack,
           COUNT(*) AS freq         
    FROM tbl_counting 
    GROUP BY product_id, rack
)
,rank_result AS 
(
  SELECT T.product_id,
         T.rack,
         T.stock,
         U.num_users,
         R.freq,
         ROW_NUMBER() OVER(PARTITION BY T.product_id ORDER BY R.freq DESC, T.stock DESC) AS RN
  FROM tbl_counting T
  LEFT JOIN uses_count U
    ON T.product_id = U.product_id
       AND CASE WHEN U.num_users >= 3 THEN TRUE ELSE FALSE END
  LEFT JOIN rack_count R
    ON T.product_id = R.product_id
       AND CASE WHEN R.freq >= 2 THEN TRUE ELSE FALSE END    
        
)
UPDATE tbl_products P
JOIN rank_result R 
  ON P.Id = R.product_id
     AND RN = 1
SET P.stock = R.stock,
    P.rack = CASE WHEN R.freq IS NULL THEN 'Unknown' ELSE R.rack END;
    

CodePudding user response：

You Can check this

SELECT
    product_id p,
    shelf,
    floor,
    line,
    stock 
FROM
    user_product 
GROUP BY
    product_id,
    stock,
    floor,
    line,
    shelf
HAVING
    stock = ( SELECT stock FROM user_product WHERE product_id = p GROUP BY stock ORDER BY count(*) DESC LIMIT 1 ) AND
    floor = ( SELECT floor FROM user_product WHERE product_id = p GROUP BY floor ORDER BY count(*) DESC LIMIT 1 ) AND
    line = ( SELECT line FROM user_product WHERE product_id = p GROUP BY line ORDER BY count(*) DESC LIMIT 1 ) AND
    shelf = ( SELECT shelf FROM user_product WHERE product_id = p GROUP BY shelf ORDER BY count(*) DESC LIMIT 1 );