如何在KDB+的fby中实现反向rank？按日期筛选col2最大两值

需求说明 #

按date字段分组，筛选出每个分组中col2值最大的前2条记录，即实现**反向rank（降序排名）**后取前2名。

测试数据 #

dt: 2024.05.13 2024.05.17 2024.05.16 2024.05.17 2024.05.14 2024.05.13 2024.05.16 2024.05.16 2024.05.15 2024.05.16 2024.05.16 2024.05.14 2024.05.13 2024.05.15 2024.05.16 2024.05.14 2024.05.13 2024.05.14

col1: 0 13 45 19 30 72 54 4 55 40 46 62 5 18 88 92 20 27

col2: 9107 9325 3813 9198 9227 8418 7868 3945 9840 1227 5613 5591 8140 9703 1225 8801 5940 9433

t: ([] date: dt; col1:col1; col2:col2)

表t的内容：

q)t
date       col1 col2
--------------------
2024.05.13 0    9107
2024.05.17 13   9325
2024.05.16 45   3813
2024.05.17 19   9198
2024.05.14 30   9227
2024.05.13 72   8418
2024.05.16 54   7868
2024.05.16 4    3945
2024.05.15 55   9840
2024.05.16 40   1227
2024.05.16 46   5613
2024.05.14 62   5591
2024.05.13 5    8140
2024.05.15 18   9703
2024.05.16 88   1225
2024.05.14 92   8801
2024.05.13 20   5940
2024.05.14 27   9433

错误尝试及结果 #

尝试以下语句后，部分日期的筛选结果不符合预期：

gt: select from t where 2>(idesc;col2) fby date

执行后按date和col2降序查看结果：

q)`date`col2 xdesc gt
date       col1 col2
--------------------
2024.05.17 13   9325
2024.05.17 19   9198
2024.05.16 45   3813
2024.05.16 40   1227
2024.05.15 55   9840
2024.05.15 18   9703
2024.05.14 27   9433
2024.05.14 62   5591
2024.05.13 0    9107
2024.05.13 72   8418

对比原表按date和col2降序的正确排序：

q)`date`col2 xdesc t
date       col1 col2
--------------------
2024.05.17 13   9325
2024.05.17 19   9198
2024.05.16 54   7868
2024.05.16 46   5613
2024.05.16 4    3945
2024.05.16 45   3813
2024.05.16 40   1227
2024.05.16 88   1225
2024.05.15 55   9840
2024.05.15 18   9703
2024.05.14 27   9433
2024.05.14 30   9227
2024.05.14 92   8801
2024.05.14 62   5591
2024.05.13 0    9107
2024.05.13 72   8418
2024.05.13 5    8140
2024.05.13 20   5940

解答 #

(1) 原fby语句为何失效？ #

原语句存在两处关键错误：

1. 语法错误：(idesc;col2)是元组而非合法的函数调用，正确的函数调用形式应为idesc col2或idesc[col2]。
2. 逻辑错误：idesc是排序函数，它返回分组内col2降序排序后的数值数组，而非每个值在分组内的排名位置。原语句2>(idesc;col2) fby date本质是在比较排序后的数值是否小于2，这和“取降序排名前2”的需求完全无关，因此得到错误结果。

(2) 正确实现反向rank以达成需求的方法 #

以下三种方法均可实现需求：

方法1：利用rank计算降序排名

对col2取负数后计算升序排名，等价于对原col2计算降序排名（排名从0开始），筛选排名小于2的行即可：

gt: select from t where 2>rank[-col2] fby date

验证结果（按date和col2降序排序）：

q)`date`col2 xdesc gt
date       col1 col2
--------------------
2024.05.17 13   9325
2024.05.17 19   9198
2024.05.16 54   7868
2024.05.16 46   5613
2024.05.15 55   9840
2024.05.15 18   9703
2024.05.14 27   9433
2024.05.14 30   9227
2024.05.13 0    9107
2024.05.13 72   8418

方法2：用rowidx定位降序前2位置

rowidx返回元素在排序后数组中的索引，对-col2取rowidx可得到降序排列的索引，筛选索引为0和1的行：

gt: select from t where rowidx[-col2] fby date in 0 1

方法3：分组后直接取前2行

先按date分组，对每个分组内的行按col2降序排序后取前2行，再合并为表：

// 分组处理并取前2行
gt: raze each {2#idesc x} group t by date
// 转换为规范表并排序
gt: `date`col2 xdesc ([] date:exec date from gt; col1:exec col1 from gt; col2:exec col2 from gt)

内容的提问来源于stack exchange，提问作者tommylicious