按 ID 排名前 20% - MySQL [英] Top 20 percent by id - MySQL

问题描述

我正在使用类似于此处另一个问题的查询的修改版本:将 SQL Server 查询转换为 MySQL

I am using a modified version of a query similiar to another question here:Convert SQL Server query to MySQL

Select *
from
(
SELECT tbl.*, @counter := @counter +1 counter
FROM (select @counter:=0) initvar, tbl
Where client_id = 55
ORDER BY ordcolumn
) X
where counter >= (80/100 * @counter);
ORDER BY ordcolumn

tbl.* 包含字段client_id"，我试图在单个语句中获取每个 client_id 的前 20% 的记录.现在，如果我在 where 语句中为它提供单个 client_id，它会给我正确的结果，但是如果我为它提供多个 client_id，它只会取组合记录集的前 20%，而不是单独处理每个 client_id.

tbl.* contains the field 'client_id' and I am attempting to get the top 20% of the records for each client_id in a single statement. Right now if I feed it a single client_id in the where statement it gives me the correct results, however if I feed it multiple client_id's it simply takes the top 20% of the combined recordset instead of doing each client_id individually.

我知道如何在大多数数据库中执行此操作，但我无法理解 MySQL 中的逻辑.我觉得这涉及到一些排名和分区.

I'm aware of how to do this in most databases, but the logic in MySQL is eluding me. I get the feeling it involves some ranking and partitioning.

示例数据非常简单.

Client_id  rate    
1          1
1          2
1          3
(etc to rate = 100)
2          1
2          2
2          3
(etc to rate = 100)

实际值不是那么干净，但它有效.

Actual values aren't that clean, but it works.

作为一个额外的好处...还有一个与这些记录相关联的日期字段，并且此客户存在多个日期的 1 到 100.我需要为每个 client_id、year(date)、month(date) 获取前 20% 的记录

As an added bonus...there is also a date field associated to these records and 1 to 100 exists for this client for multiple dates. I need to grab the top 20% of records for each client_id, year(date),month(date)

推荐答案

需要对每个客户端进行枚举:

You need to do the enumeration for each client:

SELECT *
FROM (SELECT tbl.*, @counter := @counter +1 counter
             (@rn := if(@c = client_id, @rn + 1,
                        if(@c := client_id, 1, 1)
                       )
             )
      FROM (select @c := -1, @rn := 0) initvar CROSS JOIN tbl
      ORDER BY client_id, ordcolumn
     ) t cross join
     (SELECT client_id, COUNT(*) as cnt
      FROM tbl
      GROUP BY client_id
     ) tt
where rn >= (80/100 * tt.cnt);
ORDER BY ordcolumn;

这篇关于按 ID 排名前 20% - MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！