Elenca prestazioni intersezioniEcco un
domanda per tutti voi maghi JSL.
ho del testo
dati che devo raggruppare in modi diversi.
stile="">
La mia tabella è così:
style="font-weight: bold; text-decoration: sottolineato; font-family: monospace;">My
Categoria di valore 1
Categoria 2 Categoria 3
style="font-family: monospace;">
aa
1
1
0
style="font-family: monospace;">
BB
0
1
0
style="font-family: monospace;">
CC
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;">
aa
1
1
0
style="font-family: monospace;">
BB
0
1
0
style="font-family: monospace;">
GG
0
0
0
style="font-family: monospace;">
HH
0
0
1
style="font-family: monospace;">
MM
0
1
1
La categoria
le colonne mi consentono di raggruppare i dati in modi diversi.
stile=""> Il
le categorie per questo esempio provengono da
seguenti elenchi:
style="font-size: 10pt; font-family: "Courier New"; color: black;">Categoria
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: viola;">"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: viola;">"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: viola;">"EE"
style="font-size: 10pt; font-family: "Courier New"; color: nero;">}
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;">Categoria
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: viola;">"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: viola;">"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: viola;">"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: viola;">"MM"
style="font-size: 10pt; font-family: "Courier New"; color: nero;">}
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;">Category
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: viola;">"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: viola;">"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;">;
Se tu sei
chiedendomi perché non aggiungo solo una colonna di categoria,
è perché ci può essere
si sovrappongono negli elenchi delle categorie.
così io
bisogno di una colonna separata per ogni categoria.
Se un valore
nel MyValue
la colonna corrisponde a a
valore in un elenco di categorie, quindi la colonna della categoria è 1, altrimenti
è 0.
Questo mi permette di eseguire rapidamente analisi separate dei dati
corrispondente a ciascuna categoria.
Il mio problema
è una delle prestazioni durante il calcolo delle colonne di categoria.
stile=""> È
non male per tavoli piccoli (10.000 righe, 3
categorie, elenchi di piccole categorie).
stile="">
Tuttavia si impantana
per set di dati più grandi (300.000 righe, 200
categorie, elenchi di grandi categorie).
stile=""> Alcuni
degli elenchi di categorie può essere piuttosto grande (2000 valori).
Fondamentalmente io
bisogno di un modo veloce per fare una sorta di intersezione di elenchi tra i dati
nel MyValue
colonna e i dati in ciascuna
elenco di categorie.
Questo è molto semplice
esempio, ma illustra il concetto.
ecco il
logica che sto usando per una categoria.
stile=""> Il
capacità di trovare corrispondenze tra un elenco e una colonna utilizzando
style="">SELEZIONA DOVE
è molto potente.
Se può essere accelerato in qualche modo
mi piacerebbe sapere come fare!
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: viola;">"Category
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: viola;">"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: viola;">"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: viola;">"EE"
style="font-size: 10pt; font-family: "Courier New"; color: nero;">}
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: nero;">(
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: nero;">(
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: teal;">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: teal;">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;">//
Aggiungi la colonna della categoria
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
colonna
style="font-size: 10pt; font-family: "Courier New"; color: nero;">(
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;">
numerico )
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);">column
style="font-size: 10pt; font-family: "Courier New"; color: nero;">(
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;">
una_categoria )
style="font-size: 10pt; font-family: "Courier New"; color: navy;"><<
style="font-size: 10pt; font-family: "Courier New"; color: black;">
nascondere (
style="font-size: 10pt; font-family: "Courier New"; color: teal;">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;">//
Imposta l'intera colonna su essere
zero inizialmente
style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">column
style="font-size: 10pt; font-family: "Courier New"; color: nero;">(
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;">
una_categoria )
style="font-size: 10pt; font-family: "Courier New"; color: navy;"><<
style="font-size: 10pt; font-family: "Courier New"; color: black;">
i valori ( col_zeroes [
style="font-size: 10pt; font-family: "Courier New"; color: teal;">0
style="font-size: 10pt; font-family: "Courier New"; color: navy;">,
style="font-size: 10pt; font-family: "Courier New"; color: teal;">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;">//
Seleziona tutte le righe con valori
corrispondente a questa categoria
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;">
seleziona dove (
style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">contiene
style="font-size: 10pt; font-family: "Courier New"; color: nero;">(
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;">My
Valore ))
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;">//
Ottieni un elenco dei selezionati
righe
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;">
fatti selezionare
righe ()
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;">//
Imposta tutte le righe corrispondenti su 1 per
questa categoria
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: nero;">(
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: teal;">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;">
stile="">
style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">column
style="font-size: 10pt; font-family: "Courier New"; color: nero;">(
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;">
una_categoria )[ 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: teal;">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;">;
