261 questions
-1
votes
0
answers
48
views
Chronological Order Lost After Two-Level LeftSemiJoinEngine [closed]
I'm using a two-level LeftSemiJoinEngine in DolphinDB to join streaming tables, but the final output data cannot be correctly sorted by the specified column.
Scenario Description:
I need to process ...
- 29
0
votes
0
answers
71
views
Time Series Aggregator output has chronological order issue
I created a time series aggregator using createTimeSeriesAggregator, but the chronological order of the output results is problematic.
Scenario Description:
The data in the source table MD_PTB is ...
- 11
0
votes
1
answer
44
views
batchSize understanding in subscribetable function
Refer to this example attached as follows. It consists 2 methods of streaming, one by defined function, another by class append method. The 2 methods produce exactly same results no matter how I tune ...
-1
votes
1
answer
45
views
How to correctly use merge asof with different timestamp precisions? [closed]
I’m trying to perform an as-of join between two in-memory tables. However, my results are unexpected — some rows that I believe should match are not matched.
Here is a simplified reproducible example:
...
- 29
0
votes
2
answers
121
views
Filter Rows Within Each Group
I have a table t containing minute-level data with fields such as datetime (timestamp), stock_id (stock code), and ret (return). My goal is to:
First, group by date(datetime) and stock_id, and ...
- 11
0
votes
0
answers
40
views
How to merge rows with the same ID into one row by taking non-null values
I am working with a table in DolphinDB where multiple rows share the same id, and each row contains non-null values in different columns. My goal is to consolidate these rows into a single row for ...
1
vote
0
answers
83
views
How to calculate rolling regression residuals with missing values?
I'm trying to perform rolling regression on a matrix with missing values and calculate residuals for each row. Specifically, I want to use a rolling window of length 3 where for each window, the ...
- 29
0
votes
1
answer
81
views
Performing product operation on consecutive true segments in a vector
I have two vectors of equal length, one integer vector and one boolean vector. I want to calculate the product of elements in the integer vector where the corresponding boolean vector has consecutive ...
- 11
0
votes
1
answer
114
views
How to remove duplicate rows based on array column subset relationship?
I have a DolphinDB table with an array vector column. I need to remove duplicate rows based on subset relationships within that column.
Sample Input:
sym
prices
a
[3,4,5,6]
a
[3,4,5]
a
[2,4,5,6]
a
[5,...
- 29
-1
votes
0
answers
57
views
Real-time ranking and percentile calculation on streaming data in DolphinDB
I'm using DolphinDB's stream processing engine to handle a real-time data feed of financial metrics. My requirement is to maintain the latest f1 value for each unique symbol (sym). Every time a new ...
- 1
-3
votes
0
answers
157
views
How to reset cumulative sum when a value changes sign
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 ...
- 1
0
votes
1
answer
67
views
How to configure roll function for sliding window on irregular stock data?
I am working with irregular minute-level stock data in DolphinDB and need to implement a specific rolling calculation using the roll function which is new in version 3.00.4.
I have a custom ...
- 19
0
votes
1
answer
48
views
Why does the use of "enlist" in the cross-sectional engine sometimes result in errors and sometimes work normally?
I am using the cross-sectional engine and need to store multiple rows of fields as a single array vector. However, I have encountered several issues during the process:
The scenarios I attempted are ...
- 21
0
votes
1
answer
44
views
Upsert! Operation Throws "A table can't contain duplicate column names" Error
I have a base table A and a result table B in DolphinDB. Table B was initially empty and is used to store calculated results based on table A. When trying to insert the calculated results into table B,...
- 1
0
votes
1
answer
97
views
"top" function unrecognized in grouped quantile calculation + efficient grouped top 5% statistics
I need to process a large L2 market data table (≈30 million rows) in DolphinDB and calculate statistics for the top 5% of records per group. However, I’m stuck on a syntax error with the top function ...
2
votes
1
answer
67
views
How to join a pivoted table with other tables
I’m working with the DolphinDB Python API to analyze financial data, but I can’t join a table transformed by pivotby() with another table directly. The error says TablePivotBy has no merge attribute. ...
- 21
0
votes
1
answer
47
views
How to reshape vector by duplicating each element twice?
I'm working with DolphinDB V3.00.3 and need to transform a vector by duplicating each element consecutively, while keeping the original order and final vector length the same as the input.
Example
...
1
vote
1
answer
63
views
How to reverse a DolphinDB table aggregated by group by + toArray back to its original form?
I have an in - memory DolphinDB table created as follows:
ticker = `AAPL`IBM`IBM`AAPL`AMZN`AAPL`AMZN`IBM`AMZN
volume = 106 115 121 90 130 150 145 123 155;
t = table(ticker, volume);
t;
The output of ...
0
votes
1
answer
85
views
How to perform asof join (aj) with strict inequality (<) instead of default (≤)
I am using DolphinDB version 3.00.0.6 and have two in-memory tables A and B.
I need the equivalent of a Non-Equi Join with strict inequality (A.date > B.date) to find the latest record in B that is ...
- 13
0
votes
0
answers
109
views
How to calculate 1-minute forward moving average
I'm working with stock market snapshot data (3-second intervals) in DolphinDB and need to calculate the 1-minute forward moving average for each data point.
For backward moving average (past 1 minute),...
- 21
0
votes
1
answer
57
views
Line-by-Line Formula Parsing and Exploration of Metaprogramming
I want to parse and calculate formulas line by line. The formulas depend on the given variables x1 and x2. The number of formulas per line is not fixed, and there are dependencies between the results ...
- 19
0
votes
0
answers
20
views
How to resolve metaprogramming function when variable is indirectly assigned a function name?
I'm working with metaprogramming in Python and need to dynamically resolve functions. When a variable is directly assigned a function name, everything works. However, when assigned indirectly via ...
- 11
2
votes
1
answer
107
views
Why UDF returns NULL for multi-row query but not for single row?
I ran the following query to retrieve data for a single investment ID:
select *, getProductClass(InstrumentID, etf_option_mapper) as ProductClass
from investor_pos
where InvestorID = '7399000270'
...
- 19
1
vote
0
answers
57
views
Why does SQL meta-programming using macro variables fail
I have an in-memory table defined as follows in DolphinDB:
t = table(2025.01.01 as date, 1 as M01, 2 as M02, 3 as M03, 4 as M04, 5 as M05, 6 as M06, 1 as M07, 2 as M08, 3 as M09, 4 as M10, 5 as M11, 6 ...
- 11
0
votes
0
answers
23
views
Why does the object ‘date’ trigger ‘neither XDB connection nor function’ error in my function?
I’m encountering an error when defining a query function in DolphinDB. Here’s my scenario:
Table Definition:
dt = 2025.01.01T01:01:51.100 2025.01.02T01:01:51.000 2025.01.03T04:01:51.900
sym = ["...
- 11
-1
votes
1
answer
55
views
Column-wise aggregation of array vectors :calculating mean per “level” for bid/ask data
I am currently working on a data analysis task in DolphinDB where I need to perform column-wise aggregation on array vectors that store level - 10 bid/ask data. Specifically, I have data for bid ...
- 35
0
votes
1
answer
78
views
How to concatenate two tuples?
I want to concatenate two tuples in DolphinDB in the way similar to concatenating vectors.
For example, I have two tuples:
a=(1,2,3)
and
b=(4,5,6)
and I attempted to use
join
to obtain
re=(1,2,3,4,...
0
votes
1
answer
64
views
How to achieve Python-like tuple unpacking in DolphinDB SQL to dynamically select columns?
In Python, I can unpack a list into individual variables:
>>> name,age,date = ['Bob',20,'2025-1-1']
>>> name
'Bob'
>>> age
20
>>> date
'2025-1-1'
In DolphinDB, I ...
- 1
0
votes
1
answer
48
views
How to perform pairwise weighted least squares regression on multiple columns?
I'm working with DolphinDB and trying to compute weighted least squares (WLS) regression between multiple pairs of columns in a table. The mslr function (moving least squares regression) works ...
- 27
0
votes
1
answer
35
views
How to group by a column and calculate correlation coefficients between multiple columns?
I'm encountering some issues when trying to perform grouped correlation calculations in DolphinDB. Here's my scenario:
I'm using DolphinDB to calculate correlations between multiple columns in a table....
- 1
2
votes
0
answers
25
views
Stream computing error: A table cannot contain duplicate column names
When creating a sessionWindowEngine, the server reported an error: "A table can't contain duplicate column names." However, both my input and output tables are properly defined and do not ...
- 35
0
votes
0
answers
33
views
How to implement FIFO queue operations in DolphinDB similar to Python’s deque?
In Python, we can use collections.deque to implement FIFO queue operations. For example:
from collections import deque
queue = deque([1, 2, 3])
print("Initial queue:", queue) # ...
0
votes
1
answer
43
views
Why does metaprogramming fail to generate valid join SQL while direct query works?
I've been learning about metaprogramming in DolphinDB recently, and I want to use metaprogramming to generate asof join statement.
But I couldn't find any examples of using metaprogramming to generate ...
- 27
0
votes
1
answer
39
views
How can I parse array-style string data that isn't in standard JSON format?
I have data in an array-style string format that I need to parse and insert into a DolphinDB table, with each nested array element becoming a separate record. Here's an example of the data:
'[["...
- 27
0
votes
1
answer
85
views
How to prevent createDailyTimeSeriesEngine from generating fill values for non-trading days (weekends)?
I'm using createDailyTimeSeriesEngine to aggregate 1-minute k-lines with fill values for empty time periods. My trading sessions are configured as:
sessionBegin: [21:00, 09:00, 13:00]
sessionEnd: [02:...
- 27
-3
votes
1
answer
74
views
How to convert start/end dates to daily sequence per symbol in table?
I want to expand my table based on the two fields, entry_date and remove_date, with the main purpose of facilitating subsequent table joins for date matching.
Below is the expected outcome I hope to ...
- 319
0
votes
0
answers
32
views
How to deal with the 'weigted avg' with weight might be 0?
How to implement the following complex logic using SQL:
Assume my original table contains two fields, id and x, where id is an incrementing sequence and x is a sequence of floating-point numbers:
id
x
...
- 319
0
votes
1
answer
42
views
Calculate cumulative minimum by group based on value changes
Suppose I have a table with simulated data as follows:
time = [09:00:00, 09:00:01, 09:00:02, 09:00:03, 09:00:04, 09:00:05, 09:00:06, 09:00:07, 09:00:08]
b = [101, 103, 105, 110, 129, 134, 123, 111, ...
- 27
0
votes
3
answers
98
views
How to calculate the total volume over a time range when the meter resets to zero?
How to calculate the total volume over a time period, where the field 'vol' records the cumulative meter reading, and the meter might reset to zero during this period.
If there were no resets, I could ...
- 27
-3
votes
1
answer
66
views
How to write recursive query for path generation and cycle detection?
I'm working with hierarchical tree data in DolphinDB and need to solve two specific programming challenges:
Generate full paths (concatenated IDs and names) for all nodes, including infinite-level ...
- 319
1
vote
1
answer
57
views
Using the example, when I substitute table t for an OLAP partitioned table, at first I get an error and later the results are unexpected
example from DolphinDB team
defg myIndicator(high, low){
prevHigh = first(high)
prevLow = first(low)
curHigh = last(high)
curLow = last(low)
if(curHigh <=...
- 11
1
vote
1
answer
67
views
How to calculate the maximum drawdown of a stock over a rolling time window?
In quantitative finance, maximum drawdown is a key risk metric that measures the largest decline from a peak to a trough over a period.
I want to calculate the maximum drawdown over the past 10 ...
0
votes
1
answer
30
views
How to convert multiple columns into dummy variable?
I’d like to perform one-hot encoding on the industryCode column and preserve all other columns in the following table.
date
windCode
industryCode
2022.08.01
000001.SZ
1101
2022.08.02
000002.SZ
1102
...
0
votes
1
answer
21
views
How to compute Inner product between block weights and feature table?
I have two tables:
Table 1 (tb1): Shows which symbols belong to which blocks (1 = belongs, NULL = doesn't belong)
SYMBOL | block1 | block2 | block3 | block4
-------|--------|--------|--------|-------
...
- 319
1
vote
1
answer
61
views
Deduplicating array vector rows while maintaining original order
I'm using DolphinDB's array vector as a table column, and I need to deduplicate the elements in each row of this column.
I tried using the distinct statement for deduplication, but according to the ...
- 319
1
vote
1
answer
27
views
The way to compute an aggregate index over a time window [closed]
I want to create a new column in the OHLC table based on the following rule:
If the current 5-minute high is less than or equal to the previous 5-minute high and the current low is greater than or ...
1
vote
1
answer
30
views
How to Compute Pairwise Correlations for Thousands of Stocks in Batch
I would like to compute the pairwise correlation of stock return data using DolphinDB. There are thousands of stocks. How can I achieve this?
I have already implemented a version in Python:
import ...
0
votes
1
answer
30
views
Batch processing futures data to exclude non-trading hours
I'm using DolphinDB to process futures data and need to filter out non-trading hours based on different trading time ranges for each futures symbol. Since each futures product has different trading ...
- 319
-1
votes
1
answer
98
views
How to compare two tables and output mismatched fields with values?
I need to compare data between two tables (e.g., Table A and Table A1) to check if their field values match.
The solution should:
Allow specifying which fields to compare (or compare all fields by ...
- 319
0
votes
1
answer
53
views
How to calculate position state in SQL where each row depends on previous calculation?
Sharing a common DolphinDB use case and solution for data processing.
I have a table of stock observation data recording minute-by-minute indicator states, with two key indicators:
ov95: ...
- 319