MySQL - 几个月内的每月排名 [英] MySQL - Rank per month across several months

问题描述

我正在使用 MySQL 数据库.我希望在过去 6 个月中逐月生成客户排名.

I'm using MySQL database. I'm looking to generate the rank of customers month by month for the last 6 months.

我刚刚使用以下查询来确定客户在月度调查中的排名.仅当日期范围在一个月内时，才会正确报告排名.

I just got the following query to work to determine the rank of a customer in a monthly poll. This reports the rank correctly only if the date range in one month.

    select 
        t1.*, 
        @rownum := @rownum + 1 AS RANK
    from    
        (
            select 
                date_format(EVE_DATE,'%Y-%m') as MON_DATE,
                CUST,
                SUM(POLL) as SCORE
            from
                TABLE
            where 
                EVE_DATE >= '2016-01-01' and EVE_DATE <= '2016-01-31'
            group by
                MON_DATE,
                CUST
            order by 
                SCORE desc
        )t1,
(SELECT @rownum := 0) r 
order by
        RANK DESC

我遇到的问题是，如果我要将日期范围更改为跨越多个月，则显示的排名不正确.我挖得更深一点意识到，问题是由于这样一个事实，即当天数跨月时，每个客户被列出的次数与有问题的月数一样多.因此，输出中的行数是 number_of_customers * number of Months 这意味着每月的排名不再是一个有意义的值.

The problem I have is, if I were to change the date range to span over multiple months, then the rank shown isn't right. I've dug a bit deeper & realize that, the problem is due to the fact that when the number of days span across months, every customer gets listed as many times as the number of months in question. Thereby, number of rows in the output is number_of_customers * number of months which means the rank per month is no longer a meaningful value.

例如，如果有 100 个客户 &如果我要计算一个月的排名，我可以拥有的最大排名是 100，这是正确的.但是，如果我考虑 2 个月，则排名可能在 1 到 200 之间，这是不正确的.这是因为只有 100 个客户，但由于考虑了 2 个月，所以出现了两次.

For example, if there are 100 customers & if I were to calculate the rank for one month, the maximum rank I can have is 100 which is correct. However, if I considered 2 months, the rank can range from 1 to 200 which is incorrect. This is because there are only 100 customers, but, are appearing twice due to 2 months being the consideration.

如何更正以下查询以正确显示我每月的排名?

How could I correct the below query to show me rank per month correctly?

select 
    t2.*
from
(
        select 
            t1.*, 
            @rownum := @rownum + 1 AS RANK
        from    
            (
                select 
                    date_format(EVE_DATE,'%Y-%m') as MON_DATE,
                    CUST,
                    SUM(POLL) as SCORE
                from
                    TABLE
                where 
                    EVE_DATE >= (curdate() - INTERVAL 3 MONTH)
                group by
                    MON_DATE,
                    CUST
                order by 
                    SCORE desc
            )t1,
    (SELECT @rownum := 0) r 
    order by
            RANK DESC
)t2
where
    t2.CUST= 'customerA'
order by
    t2.MON_DATE desc

如果您能帮助我前进，我将不胜感激.

I'd appreciate any help here to get me going please.

推荐答案

我认为您希望内部子查询仅按客户聚合，而不是按客户和日期聚合:

I think you want the inner subquery to aggregate only by customer, not by customer and date:

select t1.*, 
       @rownum := @rownum + 1 AS RANK
from (select CUST, SUM(POLL) as SCORE
      from TABLE
      where EVE_DATE >= '2016-01-01' and EVE_DATE <= '2016-01-31'
      group by CUST
      order by SCORE desc
     ) t1 cross join
     (SELECT @rownum := 0) r 
order by RANK DESC;

这篇关于MySQL - 几个月内的每月排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！