sql - 如何填充SQLite查询结果

Question

我有一个具有以下架构的每日价格历史记录的非常简单的SQLite数据库：

CREATE TABLE OHLCV_Components (symbol TEXT, tradeDate TEXT, openPrice REAL, highPrice REAL, lowPrice REAL, closePrice REAL, volume INTEGER)

价格仅在数据库中存在该符号/日期组合数据的日期的价格。
我通过Matlab构造了一个查询，以返回带有给定日期范围内价格数据的所有交易品种的表格：

SELECT symbol, 
       tradeDate, 
       closePrice 
FROM OHLCV_Components 
WHERE tradeDate BETWEEN DATE('2000-01-01') AND DATE('2000-01-05') 
ORDER BY symbol ASC, tradeDate ASC

-------------------------------------
symbol       tradeDate     closePrice
-------------------------------------
A            2000-01-01       10.1 
A            2000-01-02       10.3 
A            2000-01-03       10.3 

B            2000-01-01        5.1 
B            2000-01-02        5.5 

C            2000-01-01       20.9 
C            2000-01-02       20.8 
C            2000-01-05       20.7 

D            2000-01-01       25.9 
D            2000-01-02       25.8 
D            2000-01-03       25.8 
D            2000-01-05       25.7 

希望表中的每个符号的长度都等于请求的日期范围内的最长连续日期记录（在这种情况下为符号D）；也就是说，在给定日期内缺少价格数据的交易品种分配为NULL：

-------------------------------------
symbol      tradeDate      closePrice
-------------------------------------
A           2000-01-01        10.1 
A           2000-01-02        10.3 
A           2000-01-03        10.3
A           2000-01-05        NULL

B           2000-01-01        5.1 
B           2000-01-02        5.5 
B           2000-01-03        NULL
B           2000-01-05        NULL

C           2000-01-01        20.9 
C           2000-01-02        20.8 
C           2000-01-03        NULL
C           2000-01-05        20.7 

D           2000-01-01        25.9 
D           2000-01-02        25.8 
D           2000-01-03        25.8 
D           2000-01-05        25.7 

是否可以将该任务分流到高效的SQLite查询中，而不是通过编程方式解决，这对于大型查询而言可能会非常缓慢。

Answer 1

在子查询中，您可以选择范围内不同的交易日期和交易品种。将它们交叉加入以获得该范围内的所有交易品种组合。然后离开表，以获取收盘价（如果有）。

SELECT y.symbol,
       x.tradedate,
       o.closeprice
       FROM (SELECT DISTINCT
                    tradedate
                    FROM ohlcv_components
                    WHERE tradedate BETWEEN date('2000-01-01')
                                            AND date('2000-01-05')) x
            CROSS JOIN (SELECT DISTINCT
                               symbol
                               FROM ohlcv_components
                               WHERE tradedate BETWEEN date('2000-01-01')
                                                       AND date('2000-01-05')) y
            LEFT JOIN ohlcv_components o
                      ON o.tradedate = x.tradedate
                         AND o.symbol = y.symbol
       ORDER BY y.symbol ASC,
                x.tradedate ASC;

为了提高性能，在 ohlcv_components(tradedate, symbol)上创建索引可能会很好。