I'm calculating a conditional cumulative sum in DolphinDB SQL that resets the accumulator when the value changes sign (from 1 to -1 or -1 to 1). Here's a complete, reproducible scenario and my specific question:
1. Environment
- DolphinDB Version: 2.00.17
- Module:
mytt(for MACD calculation)
2. Minimal Reproducible Data
First, create a sample table t with Close prices (to generate MACD) and a timestamp column (for ordered calculation):
t = table(
2025.11.01T09:30:00 + 60 * (0..4) as datetime,
[28.5, 28.7, 28.3, 28.1, 28.6] as Close //Sample Close prices
)
3. Step 1: Generate MACD_sign column
I use the mytt::MACD UDF to get the MACD line, then convert positive values to 1 and negative values to -1:
t1 = select
datetime,
Close,
mytt::MACD(Close, SHORT_=12, LONG_=26, M=9)[2] as MACD,
iif(MACD > 0, 1, -1) as MACD_sign //Convert to 1/-1
from t
// Intermediate output of t1 (verify sign conversion):
datetime Close MACD MACD_sign
2025.11.01T09:30:00 28.5 0.023 1
2025.11.01T09:31:00 28.7 0.031 1
2025.11.01T09:32:00 28.3 -0.018 -1
2025.11.01T09:33:00 28.1 -0.025 -1
2025.11.01T09:34:00 28.6 0.015 1
4. Goal: Conditional Cumulative Sum with Reset on Sign Change
I need to calculate CumSum that resets to 0 exactly when MACD_sign changes (1↔-1), with the following desired output:
datetime Close MACD_sign Desired_CumSum
2025.11.01T09:30:00 28.5 1 1
2025.11.01T09:31:00 28.7 1 2
2025.11.01T09:32:00 28.3 -1 -1 -- Reset (1→-1)
2025.11.01T09:33:00 28.1 -1 -2
2025.11.01T09:34:00 28.6 1 1 -- Reset (-1→1)
5. My Failed Attempts
I tried using cumsum() with if-else and window functions, but couldn’t dynamically reset the accumulator. For example:
// Failed attempt: Can't track sign changes to reset cumsum
select
datetime,
MACD_sign,
cumsum(MACD_sign) as Wrong_CumSum // This gives 1,2,1,0,1 (no reset)
from t1
I attempted using cumsum with if-else, but couldn’t find a way to dynamically reset the accumulator in SQL.DolphinDB’s window functions don’t directly support resetting based on conditions.
6. Question
How to implement this conditional cumulative sum in DolphinDB SQL (preferably without UDFs, or with minimal custom logic)? The solution must:
- Order the data by datetime (critical for time-series calculation)
- Reset CumSum to 0 only when MACD_sign changes between 1 and -1
- Work with large time-series datasets (avoid performance bottlenecks)
