当前位置：文江博客话题详情

选择 MySQL 中数值的前 X（或后）百分比

发布于 2024-10-12 20:53:04 字数 103 浏览 1 评论 0原文

我想知道 MySQL 中是否可以使用任何函数从包含数值的列中选择前 X（或后）百分比。

基本上，我有一个包含价格列表的列，我只想返回价格前十个百分位中的那些字段。有什么建议吗？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

花心好男孩 2024-10-19 20:53:04

编辑 - 新答案

在将 SQL Server 查询转换为 MySQL 中回答

Select *
from
(
    SELECT tbl.*, @counter := @counter +1 counter
    FROM (select @counter:=0) initvar, tbl
    ORDER BY ordcolumn
) X
where counter <= (50/100 * @counter);
ORDER BY ordcolumn

旧答案

对于MySQL，您可以计算所需的批量大小，然后限制为该记录数

SELECT @rows := ROUND(COUNT(*) * 10/100) FROM table;
PREPARE STMT FROM ‘SELECT * FROM tbl ORDER BY price LIMIT ?’;
EXECUTE STMT USING @rows;

对于底部百分比，只需反向排序

SELECT @rows := ROUND(COUNT(*) * 10/100) FROM table;
PREPARE STMT FROM ‘SELECT * FROM tbl ORDER BY price DESC LIMIT ?’;
EXECUTE STMT USING @rows;

哎呀，也许DESC属于第一个查询，但您明白了含义。

注意
对于 SQL Server，TOP N PERCENT 子句肯定有帮助

select top 10 PERCENT *
FROM TBL
ORDER BY price

EDIT - new answer

Answered in Convert SQL Server query to MySQL

Select *
from
(
    SELECT tbl.*, @counter := @counter +1 counter
    FROM (select @counter:=0) initvar, tbl
    ORDER BY ordcolumn
) X
where counter <= (50/100 * @counter);
ORDER BY ordcolumn

OLD ANSWER

For MySQL, you could calculate the batch size required and then LIMIT to that number of records

SELECT @rows := ROUND(COUNT(*) * 10/100) FROM table;
PREPARE STMT FROM ‘SELECT * FROM tbl ORDER BY price LIMIT ?’;
EXECUTE STMT USING @rows;

For a bottom percent, just order in reverse

SELECT @rows := ROUND(COUNT(*) * 10/100) FROM table;
PREPARE STMT FROM ‘SELECT * FROM tbl ORDER BY price DESC LIMIT ?’;
EXECUTE STMT USING @rows;

Oops, maybe the DESC belongs in the first query, but you get the meaning.

Note
For SQL Server, the TOP N PERCENT clause certainly helps

select top 10 PERCENT *
FROM TBL
ORDER BY price

回复收藏 0 原文

无戏配角 2024-10-19 20:53:04

仅供参考（我知道这个问题已经有几年了），这也可以通过其他更干净的方式来完成。

SELECT * FROM product_table WHERE price >= (SELECT price FROM product_table 
ORDER BY price DESC LIMIT 1 OFFSET (SELECT 0.1 * COUNT(*) FROM product_table));

我在一个非常大的数据库上运行了类似的查询，并且运行得非常快。

just as an FYI (i know this question is a few years old), this can be done other, cleaner ways as well.

SELECT * FROM product_table WHERE price >= (SELECT price FROM product_table 
ORDER BY price DESC LIMIT 1 OFFSET (SELECT 0.1 * COUNT(*) FROM product_table));

i ran a similar query over a very large database, and it ran very quickly.

回复收藏 0 原文

横笛休吹塞上声 2024-10-19 20:53:04

更新：来自更了解的人对这个主题的更深思熟虑的解释这里。尽管如此，MySQL 中似乎仍然没有嵌入式函数来计算百分位数。

尝试：

~~SELECT * FROMpricesWHEREprice>= (SELECT 0.9 * max(price)FROMprices)~~

SELECT price FROM prices p1 WHERE
(SELECT count(*) FROM prices p2 WHERE p2.price >= p1.price) <=
     (SELECT 0.1 * count(*) FROM prices)
);

这将为中的记录数给出价格P1具有 price >= P1 的 Price 表将是 Price 表中记录总数的十分之一。
之后：

SELECT * FROM prices WHERE price >= (SELECT price FROM prices p1 WHERE
(SELECT count(*) FROM prices p2 WHERE p2.price >= p1.price) <=
     (SELECT 0.1 * count(*) FROM prices)
);

将返回所有所需的记录。

注意：我没有检查这个查询的性能，我认为使用临时表/变量的解决方案一定更有效。

UPDATE: Much more thought-out explanation of the subject from much more knowing person here. Nonetheless, it still seems there's no embedded function in MySQL to calculate percentiles.

Try:

~~SELECT * FROM prices WHERE price >= (SELECT 0.9 * max(price) FROM prices)~~

SELECT price FROM prices p1 WHERE
(SELECT count(*) FROM prices p2 WHERE p2.price >= p1.price) <=
     (SELECT 0.1 * count(*) FROM prices)
);

This will give price P1 for which number of records in Price table having price >= P1 will be one tenth of total number of records in Price table.
After that:

SELECT * FROM prices WHERE price >= (SELECT price FROM prices p1 WHERE
(SELECT count(*) FROM prices p2 WHERE p2.price >= p1.price) <=
     (SELECT 0.1 * count(*) FROM prices)
);

will return all desired records.

Note: I didn't examine performance of this query, I think solution with temporary table/variable must be more effective.

回复收藏 0 原文

~没有更多了~