Update a field of the same table with Subquery that creates ranking

Question

Update a field of the same table with Subquery that creates ranking

Navigation

#1 by (1 votes)

-1

Objective: Create a ranking day by day, which is the warehouse that sells the most, it will be number 1, and so on.

The fields in the table are:

Date, Store, Sales, Rank_Sales_By_Date

I would like to know if it is possible with a single query, or with what method, I can update the column Rank_Sales_By_Date, with this information, at the moment that I have a partial query, filtered with a date ('2017-02-19' ), but the object is that it does so, classified by date, for all dates.

Then the query that I have,:

UPDATE 
    scraper_data_twitter AS T1,
(
    SELECT
      Ntweets AS Ntweets_var,
    (
      SELECT 
        COUNT(Ntweets) + 1
      FROM 
        scraper_data_twitter 
      WHERE 
        (NTweets > Ntweets_var) AND fecha = '2017-02-19'
    ) AS rank
   FROM 
      scraper_data_twitter 
   WHERE 
      fecha = '2017-02-19'
   GROUP BY SITE, fecha
   ORDER BY NTweets_var DESC
) AS A
SET T1.rnk_Ntweets =  rank

It always saves me the value 1.

asi:

I know that it is likely to improve the query, so your suggestions regarding the improvement of the query are Welcome.

mysql

asked by Leo 24.02.2017 в 05:53

source

1 answer

Error when consulting Remote Database PHP 7 Prohibit special characters in my registry

score 1 · Answer 1

Hello I found the solution I share:

set @Lastfecha = NULL, @Lastntweets = NULL, @CurRange = NULL;
update scraper_data_twitter t
set rnk_ntweets= if(@Lastfecha<=>t.fecha, 
                               if(t.NTweets<=>@Lastntweets, 
                                     @CurRange, 
                                     @CurRange:=@CurRange+1),
                               @CurRange:=1 + cast((@Lastfecha:=t.fecha) as DATE) - T.fecha + (@Lastntweets:=T.NTweets) - T.NTweets);