列出交叉口性能这是一个
所有 JSL 向导的问题。
我有文字
我需要以不同方式分组的数据。
样式="">
我的桌子看起来像这样：
style="font-weight: bold; text-decoration: underline; font-family: monospace;">我的
价值类别 1
类别 2 类别 3
style="font-family: monospace;">
机管局
1
1
0
style="font-family: monospace;">
BB
0
1
0
style="font-family: monospace;">
抄送
1
0
0
style="font-family: monospace;">
DD
0
1
0
style="font-family: monospace;">
EE
1
0
0
style="font-family: monospace;">
FF
0
0
0
style="font-family: monospace;">
机管局
1
1
0
style="font-family: monospace;">
BB
0
1
0
style="font-family: monospace;">
GG
0
0
0
style="font-family: monospace;">
卫生局
0
0
1
style="font-family: monospace;">
毫米
0
1
1
类别
列允许我以不同的方式对数据进行分组。
样式="">这
此示例的类别来自
以下列表：
style="font-size: 10pt; font-family: "Courier New"; color: black;">类别
1
style="font-size: 10pt; font-family: "Courier New"; color: navy;">=
style="font-size: 10pt; font-family: "Courier New"; color: black;">
{
style="font-size: 10pt; font-family: "Courier New"; color: Purple;">"AA"
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: Purple;">"CC"
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: Purple;">"EE"
style="font-size: 10pt; font-family: "Courier New"; color: black;">}
style="font-size: 10pt; font-family: "Courier New"; color: navy;">;
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: black;">类别
2
style="font-size: 10pt; font-family: "Courier New"; color: navy;">=
style="font-size: 10pt; font-family: "Courier New"; color: black;">
{
style="font-size: 10pt; font-family: "Courier New"; color: Purple;">"AA"
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: Purple;">"BB"
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: Purple;">"DD"
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: Purple;">"MM"
style="font-size: 10pt; font-family: "Courier New"; color: black;">}
style="font-size: 10pt; font-family: "Courier New"; color: navy;">;
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: black;">类别
3
style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: navy;">=
style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: black;">
{
style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: Purple;">"KK"
style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: black;">
style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: Purple;">"MM"
style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: black;">}
style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: navy;">;
如果你是
想知道为什么我不只添加一个类别列，
这是因为可以有
在类别列表中重叠。
因此我
每个类别需要一个单独的列。
如果一个值
在里面我的价值
列匹配一个
类别列表中的值，则类别列为 1，否则为
是 0。
这使我能够快速对数据进行单独分析
匹配每个类别。
我的问题
是计算类别列时的性能之一。
样式="">它是
对于小表来说还不错（10,000 行，3
类别，小类别列表）。
样式="">
然而它陷入困境
对于更大的数据集（300,000 行，200
类别，大类别列表）。
样式="">一些
类别列表的数量可能非常大（2000 个值）。
基本上我
需要一种快速的方法来在数据之间进行某种列表交集
在里面我的价值
列和每个数据
类别列表。
这是一个非常简单的
示例，但它说明了这个概念。
这是
我用于一个类别的逻辑。
样式="">这
使用查找列表和列之间的匹配项的能力
style="">选择哪里
非常强大。
如果它可以以某种方式加速
我很想知道怎么做！
style="font-size: 10pt; font-family: "Courier New"; color: black;">one_category
style="font-size: 10pt; font-family: "Courier New"; color: navy;">=
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: Purple;">"类别
1"
style="font-size: 10pt; font-family: "Courier New"; color: navy;">;
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: black;">category_list
style="font-size: 10pt; font-family: "Courier New"; color: navy;">=
style="font-size: 10pt; font-family: "Courier New"; color: black;">
{
style="font-size: 10pt; font-family: "Courier New"; color: Purple;">"AA"
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: Purple;">"CC"
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: Purple;">"EE"
style="font-size: 10pt; font-family: "Courier New"; color: black;">}
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: black;">nr
style="font-size: 10pt; font-family: "Courier New"; color: navy;">=
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">nrows
style="font-size: 10pt; font-family: "Courier New"; color: black;">(
style="font-size: 10pt; font-family: "Courier New"; color: black;">dt )
style="font-size: 10pt; font-family: "Courier New"; color: navy;">;
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: black;">col_zeroes
style="font-size: 10pt; font-family: "Courier New"; color: navy;">=
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">j
style="font-size: 10pt; font-family: "Courier New"; color: black;">(
style="font-size: 10pt; font-family: "Courier New"; color: black;">nr
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: blue;">1
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: 青色;">0
style="font-size: 10pt; font-family: "Courier New"; color: black;">)
style="font-size: 10pt; font-family: "Courier New"; color: navy;">;
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: green;">//
添加类别列
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: black;">dt
style="font-size: 10pt; font-family: "Courier New"; color: navy;"><<
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">new
柱子
style="font-size: 10pt; font-family: "Courier New"; color: black;">(
style="font-size: 10pt; font-family: "Courier New"; color: black;">one_category
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: black;">
数字)
style="font-size: 10pt; font-family: "Courier New"; color: navy;">;
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">列
style="font-size: 10pt; font-family: "Courier New"; color: black;">(
style="font-size: 10pt; font-family: "Courier New"; color: black;">dt
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: black;">
one_category )
style="font-size: 10pt; font-family: "Courier New"; color: navy;"><<
style="font-size: 10pt; font-family: "Courier New"; color: black;">
隐藏(
style="font-size: 10pt; font-family: "Courier New"; color: blue;">1
style="font-size: 10pt; font-family: "Courier New"; color: black;">)
style="font-size: 10pt; font-family: "Courier New"; color: navy;">;
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: green;">//
将整列设置为
最初为零
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">列
style="font-size: 10pt; font-family: "Courier New"; color: black;">(
style="font-size: 10pt; font-family: "Courier New"; color: black;">dt
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: black;">
one_category )
style="font-size: 10pt; font-family: "Courier New"; color: navy;"><<
style="font-size: 10pt; font-family: "Courier New"; color: black;">
价值观( col_zeroes [
style="font-size: 10pt; font-family: "Courier New"; color: 青色;">0
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: blue;">1
style="font-size: 10pt; font-family: "Courier New"; color: black;">])
style="font-size: 10pt; font-family: "Courier New"; color: navy;">;
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: green;">//
选择所有具有值的行
匹配这个类别
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: black;">dt
style="font-size: 10pt; font-family: "Courier New"; color: navy;"><<
style="font-size: 10pt; font-family: "Courier New"; color: black;">
选择哪里(
style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">包含
style="font-size: 10pt; font-family: "Courier New"; color: black;">(
style="font-size: 10pt; font-family: "Courier New"; color: black;">category_list
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: navy;">:
style="font-size: 10pt; font-family: "Courier New"; color: black;">我的
价值))
style="font-size: 10pt; font-family: "Courier New"; color: navy;">;
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: green;">//
获取选中的列表
行
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: black;">one_cat_rows
style="font-size: 10pt; font-family: "Courier New"; color: navy;">=
style="font-size: 10pt; font-family: "Courier New"; color: black;">
dt
style="font-size: 10pt; font-family: "Courier New"; color: navy;"><<
style="font-size: 10pt; font-family: "Courier New"; color: black;">
被选中
行()
style="font-size: 10pt; font-family: "Courier New"; color: navy;">;
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: green;">//
将所有匹配的行设置为 1
这个类别
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">if
style="font-size: 10pt; font-family: "Courier New"; color: black;">
(
style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">nrows
style="font-size: 10pt; font-family: "Courier New"; color: black;">(
style="font-size: 10pt; font-family: "Courier New"; color: black;">one_cat_rows )
style="font-size: 10pt; font-family: "Courier New"; color: navy;">>
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: 青色;">0
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: black;">
样式="">
style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">列
style="font-size: 10pt; font-family: "Courier New"; color: black;">(
style="font-size: 10pt; font-family: "Courier New"; color: black;">dt
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: black;">
one_category )[ one_cat_rows ]
style="font-size: 10pt; font-family: "Courier New"; color: navy;">=
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: blue;">1
style="font-size: 10pt; font-family: "Courier New"; color: navy;">;
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: black;">)
style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: navy;">;
