关于窗口函数报错，row_number() 的使用：

[Tongzhenguo]

hive row_number用了也不少，感觉用法应该没问题

参考mysql文档:https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number

MySQL [data_pre]> select gid
    ->     ,feed_id
    ->     ,row_number() over() rank
    -> from (
    ->     select min(cast(
    ->         SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(scm,".",-1),"|",3),"|",-1) as float)) pre_score
    ->         ,gid
    ->         ,feed_id
    ->     from data_pre.processed_hot_impression_sdk
    ->     where create_time >= '2020-07-14 14:20:00'
    ->         and create_time < '2020-07-14 14:30:00'
    ->         and scm like "%.%.%.%|%|%"
    ->     group by gid,feed_id
    -> ) a
    -> limit 10;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 3 column 30 near "rank

MySQL [data_pre]> select gid
    ->     ,feed_id
    ->     ,row_number() over(order by pre_score) rank
    -> from (
    ->     select min(cast(
    ->         SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(scm,".",-1),"|",3),"|",-1) as float)) pre_score
    ->         ,gid
    ->         ,feed_id
    ->     from data_pre.processed_hot_impression_sdk
    ->     where create_time >= '2020-07-14 14:20:00'
    ->         and create_time < '2020-07-14 14:30:00'
    ->         and scm like "%.%.%.%|%|%"
    ->     group by gid,feed_id
    -> ) a
    -> limit 10;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 3 column 48 near "rank

[来了老弟]

8.0 的窗口函数目前支持的还不是很完全，兼容行方面目前可以在 5.7 上做下。

[Tongzhenguo]

MySQL [data_pre]> select @@version;
+--------------------+
| @@version          |
+--------------------+
| 5.7.25-TiDB-v4.0.0 |
+--------------------+

8.0是值的啥？要升级？

[来了老弟]

tidb 对 mysql 8.0的窗口函数还没有完全支持的意思

[Tongzhenguo]

@Tongzhenguo

5.7.25

row_number应该早就有了吧？

[yilong (yi888long)]

请参考下这个文档来写，多谢。 TiDB 3.0：窗口函数初体验

[Tongzhenguo]

好的，我试下

[来了老弟]

@rongyilong-PingCAP 感谢。

@Tongzhenguo有问题可以随时反馈

[Tongzhenguo]

好的，刚有时间；我测下

[Tongzhenguo]

啊！sorry；貌似方向错了；
报错是因为rank是关键字，改成rn就可以了；

[来了老弟]