order by 理解请大虾们分别对以下没戏每项结果集进行分析

2009-09-17 09:40:08 226 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

银行客户投诉预测模型优化在实际操作中，银行可以使用这个优化后的模型来识别那些可能引起客户不满的因素，并采取预防措施。例如，如果模型表明退款金额是一个很强的预测因子，银行可能会审查其退款政策，以确保它对客户是公平和透明的。在银行业务中，客户投诉是一个重要的指标，它直接关系到客户满意度和银行服务质量。某一个指标，比如：退款金额，本文假设退款金额对于预测投诉的权重应该高于赔偿金额。在每次迭代中，本文使用当前的权重组合来训练模型，并计算在测试集上的准确率。的可能值来搜索最佳的权重组合，并输出最佳准确率及对应的权重。来寻找最佳的权重组合。阅读详情

关于user表

mysql> select * from user;

+--------+--------------------+

| userid | username           |

+--------+--------------------+

|      1 | aaaaaaaaaaaaaaaaaa |

|      2 | 螟ｧ譏ｯ荳ｪ             |

|      3 | 譏ｯ蠕ｷ蝗ｽ             |

|      4 |                    |

|      5 | 莠懶ｽ難ｽ・            |

|      6 | 螢ｫ螟ｧ螟ｫ             |

|      7 | d                  |

|      8 | 螳俶婿               |

|      9 | ?                  |

+--------+--------------------+

9 rows in set (0.00 sec)

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------

mysql> select * from user order by rand()*1000000000;

+--------+--------------------+

| userid | username           |

+--------+--------------------+

|      3 | 譏ｯ蠕ｷ蝗ｽ             |

|      6 | 螢ｫ螟ｧ螟ｫ             |

|      8 | 螳俶婿               |

|      5 | 莠懶ｽ難ｽ・            |

|      2 | 螟ｧ譏ｯ荳ｪ             |

|      9 | ?                  |

|      1 | aaaaaaaaaaaaaaaaaa |

|      4 |                    |

|      7 | d                  |

+--------+--------------------+

9 rows in set (0.00 sec)



mysql> select * from user order by rand()*1000000000;

+--------+--------------------+

| userid | username           |

+--------+--------------------+

|      4 |                    |

|      1 | aaaaaaaaaaaaaaaaaa |

|      7 | d                  |

|      9 | ?                  |

|      6 | 螢ｫ螟ｧ螟ｫ             |

|      3 | 譏ｯ蠕ｷ蝗ｽ             |

|      5 | 莠懶ｽ難ｽ・            |

|      8 | 螳俶婿               |

|      2 | 螟ｧ譏ｯ荳ｪ             |

+--------+--------------------+

9 rows in set (0.02 sec)



mysql> select * from user order by rand()*1000000000;

+--------+--------------------+

| userid | username           |

+--------+--------------------+

|      2 | 螟ｧ譏ｯ荳ｪ             |

|      6 | 螢ｫ螟ｧ螟ｫ             |

|      8 | 螳俶婿               |

|      1 | aaaaaaaaaaaaaaaaaa |

|      9 | ?                  |

|      5 | 莠懶ｽ難ｽ・            |

|      3 | 譏ｯ蠕ｷ蝗ｽ             |

|      7 | d                  |

|      4 |                    |

+--------+--------------------+

9 rows in set (0.00 sec)



mysql> select * from user order by rand()*1000000000;

+--------+--------------------+

| userid | username           |

+--------+--------------------+

|      9 | ?                  |

|      4 |                    |

|      8 | 螳俶婿               |

|      5 | 莠懶ｽ難ｽ・            |

|      6 | 螢ｫ螟ｧ螟ｫ             |

|      1 | aaaaaaaaaaaaaaaaaa |

|      2 | 螟ｧ譏ｯ荳ｪ             |

|      7 | d                  |

|      3 | 譏ｯ蠕ｷ蝗ｽ             |

+--------+--------------------+

9 rows in set (0.00 sec)



mysql> select * from user order by rand();

+--------+--------------------+

| userid | username           |

+--------+--------------------+

|      7 | d                  |

|      2 | 螟ｧ譏ｯ荳ｪ             |

|      9 | ?                  |

|      3 | 譏ｯ蠕ｷ蝗ｽ             |

|      5 | 莠懶ｽ難ｽ・            |

|      6 | 螢ｫ螟ｧ螟ｫ             |

|      4 |                    |

|      1 | aaaaaaaaaaaaaaaaaa |

|      8 | 螳俶婿               |

+--------+--------------------+

9 rows in set (0.00 sec)



mysql> select * from user order by 2;

+--------+--------------------+

| userid | username           |

+--------+--------------------+

|      4 |                    |

|      9 | ?                  |

|      1 | aaaaaaaaaaaaaaaaaa |

|      7 | d                  |

|      5 | 莠懶ｽ難ｽ・            |

|      6 | 螢ｫ螟ｧ螟ｫ             |

|      2 | 螟ｧ譏ｯ荳ｪ             |

|      8 | 螳俶婿               |

|      3 | 譏ｯ蠕ｷ蝗ｽ             |

+--------+--------------------+

9 rows in set (0.00 sec)



mysql> select * from user order by 132456.12345;

+--------+--------------------+

| userid | username           |

+--------+--------------------+

|      1 | aaaaaaaaaaaaaaaaaa |

|      2 | 螟ｧ譏ｯ荳ｪ             |

|      3 | 譏ｯ蠕ｷ蝗ｽ             |

|      4 |                    |

|      5 | 莠懶ｽ難ｽ・            |

|      6 | 螢ｫ螟ｧ螟ｫ             |

|      7 | d                  |

|      8 | 螳俶婿               |

|      9 | ?                  |

+--------+--------------------+

9 rows in set (0.00 sec)



mysql> select * from user order by 132456;

ERROR 1054 (42S22): Unknown column '132456' in 'order clause'

...全文

Halcon异常检测训练避坑指南：如何避免GPU内存溢出和样本不足问题本文针对Halcon深度学习异常检测训练中常见的GPU内存溢出和样本不足两大难题，提供了从原理分析到实战解决方案的深度指南。通过调整批次大小、图像尺寸、使用梯度累积等技巧有效管理显存，并利用针对性数据增强与人工合成策略破解小样本困境，帮助开发者构建稳定高效的训练流程。阅读详情

nianzhang747

MySQL 57,062 成员 56,759 内容

+ 关注 + 加入社区

切换为时间正序

百年树人 2009-09-17

打赏
举报

底层的东西不了解，但是可以按照任何类型的数据来排序这个不难理解

就像一群人要排队，
排队的先后顺序我们可以按照他们的性别(bit 0 or 1)、名字(varchar)、身高(numeric)等等进行排序

nianzhang747 2009-09-17

打赏
举报

但是他order by 任何类型的数据都可以

它底层是怎么实现的呢

百年树人 2009-09-17

打赏
举报

谢谢ACMAIN_CHM的分享！

不知道用视图查询会这么样，试试这个

select id,crand
from(
select id,rand() as crand from t2) t
order by crand;

ACMAIN_CHM 2009-09-17

打赏
举报

mysql> select id,rand() as crand from t2 order by crand;

+--------+--------------------+

| id     | crand              |

+--------+--------------------+

|  50000 |  0.059513117242151 |

|  80000 | 0.0837476030772064 |

|  40000 |  0.099545445385897 |

|  30000 |  0.146071380140326 |

|  20000 |  0.210270495349793 |

|      1 |  0.543954915873664 |

| 100000 |  0.600841207989344 |

|  70000 |  0.816107082940738 |

|  10000 |  0.968427042447428 |

|  90000 |  0.970416797297464 |

|  60000 |  0.998929280786709 |

+--------+--------------------+

11 rows in set (0.00 sec)



mysql>

百年树人 2009-09-17

打赏
举报

但如果是一个固定的数，order by 132456 就是按照第132456列进行排序，显然你的列数没有这么多

百年树人 2009-09-17

打赏
举报

个人意见:

SELECT * FROM tbl_name ORDER BY RAND();

应该等价于

SELECT *,RAND() as px FROM tbl_name ORDER BY px;

ACMAIN_CHM 2009-09-17

打赏
举报

[Quote]如果是这样的话order by的列就是个定值而不是随机制[/Quote]

是随机

RAND 函数是每行计算，与其它函数不同。

nianzhang747 2009-09-17

打赏
举报

原因是 ORDER BY 会计算列的多重时间。
--------------------------------
不是很理解
查询应该是先查询结果集然后在order by 排序
如果是这样的话order by的列就是个定值而不是随机制

如果select * from user where id=ceil(rand()*10);
那么它是一行一行匹配如果有索引也是匹配多行

ACMAIN_CHM 2009-09-17

打赏
举报

order by rand()

这种是以表达式结果来排序。

在ORDER BY语句中，不能使用一个带有RAND()值的列，原因是 ORDER BY 会计算列的多重时间。然而，可按照如下的随机顺序检索数据行：

mysql> SELECT * FROM tbl_name ORDER BY RAND();

ORDER BY RAND()同 LIMIT 的结合从一组列中选择随机样本很有用：

nianzhang747 2009-09-17

打赏
举报

那版主解释下

我上面的order by rand()
运行原理吧

mysql> select rand();

+-------------------+

| rand()            |

+-------------------+

| 0.832840707928725 |

+-------------------+

1 row in set (0.00 sec)

ACMAIN_CHM 2009-09-17

打赏
举报

[ORDER BY {col_name | expr | position}

被选择的用于输出的列可以使用列名称、列别名或列位置被引用到ORDER BY和GROUP BY子句中。列位置为整数，从1开始：

ACMAIN_CHM 2009-09-17

打赏
举报

[Quote]mysql> select * from user order by 132456;
ERROR 1054 (42S22): Unknown column '132456' in 'order clause'
[/Quote]
你的表中没有这么多列啊。

LeoBai 2009-09-17

打赏
举报

[Quote=引用 19 楼 nianzhang747 的回复:]
明白了
rand()相当于生成一个伪列然后按照伪劣进行排序
[/Quote]

that's right

LeoBai 2009-09-17

打赏
举报

sqlcli> select * from t1 order by 1;
+------+
| a |
+------+
| 1 |
| 10 |
+------+
2 rows in set (0.00 sec)

sqlcli> select * from t1 order by 2;
ERROR 1054 (42S22): Unknown column '2' in 'order clause'
sqlcli> select * from t1 order by '2';
+------+
| a |
+------+
| 1 |
| 10 |
+------+
2 rows in set (0.01 sec)

order by 2 = order by 第二个字段

nianzhang747 2009-09-17

打赏
举报

明白了
rand()相当于生成一个伪列然后按照伪劣进行排序

mysql> select username ,userid as Vatural from user order by Vatural;

+-----------+---------+

| username  | Vatural |

+-----------+---------+

| 螟ｧ譏ｯ荳ｪ    |       2 |

| 譏ｯ蠕ｷ蝗ｽ    |       3 |

|           |       4 |

| 莠懶ｽ難ｽ・   |       5 |

| 螢ｫ螟ｧ螟ｫ    |       6 |

| d         |       7 |

| 螳俶婿      |       8 |

| ?         |       9 |

+-----------+---------+

8 rows in set (0.00 sec)



mysql> select username ,rand() as Vatural from user order by Vatural;

+-----------+--------------------+

| username  | Vatural            |

+-----------+--------------------+

| 螟ｧ譏ｯ荳ｪ    | 0.0555073358635486 |

| 莠懶ｽ難ｽ・   |  0.134616873352432 |

| ?         |  0.185337211177998 |

| 螳俶婿      |  0.270653398028252 |

|           |  0.371484097439315 |

| d         |  0.389309936565636 |

| 螢ｫ螟ｧ螟ｫ    |  0.558632105177857 |

| 譏ｯ蠕ｷ蝗ｽ    |  0.907601214859263 |

+-----------+--------------------+

8 rows in set (0.00 sec)

netxuning 2009-09-17

打赏
举报

版主的意思可能是，select *, rand() from tbl; 的执行过程中，每取出一条结果的同时计算一个随机数！
而max，min等函数则是列结果出来之后才计算！

这个可以楼主按照《understanding mysql internals》中对mysql的调试方法来看看到底那个值是怎么出来的！

ACMAIN_CHM 2009-09-17

打赏
举报

对比一下下面的结果，就比较容易理解了。

mysql> explain select * from t2 order by rand();

+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+

| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                                        |

+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+

|  1 | SIMPLE      | t2    | index | NULL          | PRIMARY | 4       | NULL |   11 | Using index; Using temporary; Using filesort |

+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+

1 row in set (0.00 sec)



mysql> explain select * from t2 order by id;

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

|  1 | SIMPLE      | t2    | index | NULL          | PRIMARY | 4       | NULL |   11 | Using index |

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

1 row in set (0.00 sec)



mysql> explain select id,rand() from t2 order by id;

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

|  1 | SIMPLE      | t2    | index | NULL          | PRIMARY | 4       | NULL |   11 | Using index |

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

1 row in set (0.00 sec)



mysql>

ACMAIN_CHM 2009-09-17

打赏
举报

mysql> explain select * from t2 order by rand();

+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+

| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                                        |

+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+

|  1 | SIMPLE      | t2    | index | NULL          | PRIMARY | 4       | NULL |   11 | Using index; Using temporary; Using filesort |

+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+

1 row in set (0.00 sec)

ACMAIN_CHM 2009-09-17

打赏
举报

读取所有的行，对每行生成这个RAND，然后再对这个临时结果集进行排序。

netxuning 2009-09-17

打赏
举报

学习！

加载更多回复（1）

order by 理解 请大虾们分别对以下没戏每项结果集进行分析

相关推荐

order by 理解请大虾们分别对以下没戏每项结果集进行分析