Does dolphindb support list query?

Question

I would like to apply the following group of SQL statements at once and union the result to get the most recent record behind mt=52355979 of various stock(idetified by 'symbol') of different trade places and market types(identified by 'c1','c2','c3','c4').

select * from t where symbol=`A,c1=25,c2=814,c3=11,c4=2, date=2020.02.05, mt<52355979 order by mt desc limit 1
select * from t where symbol=`B,c1=25,c2=814,c3=12,c4=2, date=2020.02.05, mt<52355979 order by mt desc limit 1
select * from t where symbol=`C,c1=25,c2=814,c3=12,c4=2, date=2020.02.05, mt<52354979 order by mt desc limit 1
select * from t where symbol=`A,c1=1180,c2=333,c3=3,c4=116, date=2020.02.05, mt<52355979 order by mt desc limit 1

The filter columns in where condition will not change, while the filter values may change each time. Does DolphindB offer querying methods which allow to run list query with varying input parameters?

user13644519 user13644519 · Accepted Answer · 2020-07-16T22:26:02

You can define a function as follows

def bundleQuery(tbl, dt, dtColName, mt, mtColName, filterColValues, filterColNames){
    cnt = filterColValues[0].size()
    filterColCnt =filterColValues.size()
    orderByCol = sqlCol(mtColName)
    selCol = sqlCol("*")
    filters = array(ANY, filterColCnt + 2)
    filters[filterColCnt] = expr(sqlCol(dtColName), ==, dt)
    filters[filterColCnt+1] = expr(sqlCol(mtColName), <, mt)
    
    queries = array(ANY, cnt)
    for(i in 0:cnt) {
        for(j in 0:filterColCnt){
            filters[j] = expr(sqlCol(filterColNames[j]), ==, filterColValues[j][i])
        }
        queries.append!(sql(select=selCol, from=tbl, where=filters, orderBy=orderByCol, ascOrder=false, limit=1))
    }
    return loop(eval, queries).unionAll(false)
}

and then use the following script

dt = 2020.02.05
dtColName = "dsl"
mt = 52355979
mtColName = "mt"
colNames = `symbol`c1`c2`c3`c4
colValues = [50982208 50982208 51180116 41774759, 25 25 25 1180, 814 814 814 333, 11 12 12 3, 2 2 2 116]

bundleQuery(t, dt, dtColName, mt, mtColName, colValues, colNames)

Does dolphindb support list query?

1 Answers