在KDB中为表列添加过去n个元素的方差计算列

Got it, let's figure out how to add that rolling variance column to your kdb+ table. You're right that mavg works smoothly for moving averages, but kdb+ doesn't have a built-in mvar function—so we'll build it using sliding window operations instead.

First, let's recap your existing table setup for context:

t:([] td:2001.01.01 2001.01.02 2001.01.03 2001.01.04 2001.01.05 2001.01.06; px:121 125 127 126 129 130)
t:update retLogPcnt:100*log px%prev px from t
t:update mvAvgRet:2 mavg retLogPcnt from t

Option 1: Use slide with var #

The slide function creates sliding windows of your specified size, and we can apply the var function to each window using each:

t:update varRetns:{[windowSize; col] var each windowSize slide col}[3; retLogPcnt] from t

• 3 slide retLogPcnt generates consecutive 3-element windows from your retLogPcnt column
• var each ... calculates the population variance for every window
• Early rows with fewer than 3 valid values will show 0N (null) for varRetns, which is exactly what we want since variance can't be computed for incomplete windows.

Option 2: Use the built-in wf window operator #

Kdb+ has a concise window operator wf that applies a function to the last n elements up to the current row. This does the same job with less code:

t:update varRetns:3 wf var retLogPcnt from t

3 wf var automatically handles the rolling window logic—for each row, it grabs the last 3 elements (including the current one) and computes their variance.

Need sample variance instead of population variance? #

If you want sample variance (dividing by n-1 instead of n), just replace var with kdb+'s svar function in either solution:

t:update varRetns:3 wf svar retLogPcnt from t

Verify the result #

For the 2001.01.04 row you mentioned, let's confirm the calculation:

q)var 3.252319 1.587335 -0.790518
2.75235

In your updated table, the varRetns value for this row will match exactly, as expected.

Your final table will look like this:

q)t
td          px  retLogPcnt mvAvgRet varRetns
--------------------------------------------
2001.01.01  121 0N         0N       0N
2001.01.02  125 3.252319   0N       0N
2001.01.03  127 1.587335   2.419827 0N
2001.01.04  126 -0.790518  0.398408 2.75235
2001.01.05  129 2.302585   0.756033 2.12283
2001.01.06  130 0.772589   1.537587 2.14475

内容的提问来源于stack exchange，提问作者Sven F.