sqlite - 月度价格数据:开盘价，最高价，最低价和收盘价

Question

我有一个表，如下所示：

dpTicker    dpDate      dpOpen      dpHigh      dpLow       dpClose     dpVolume    dpAdjClose  dpCreated            dpModified
GLE.PA      2016-02-01  35.39       35.455      34.375      34.785      2951300     34.785      2016-02-06 13:33:40  2016-02-06 13:33:40
GLE.PA      2016-02-02  34.515      34.565      32.165      32.575      7353600     32.575      2016-02-06 13:33:40  2016-02-06 13:33:40
GLE.PA      2016-02-03  32.4        32.495      30.885      31.6        7007000     31.6        2016-02-06 13:33:40  2016-02-06 13:33:40
GLE.PA      2016-02-04  32.075      32.38       30.67       31.98       8181000     31.98       2016-02-06 13:33:40  2016-02-06 13:33:40
GLE.PA      2016-02-05  32.55       33.0        31.86       32.11       7056700     32.11       2016-02-06 13:33:40  2016-02-06 13:33:40

该数据是每日股票价格信息，并且该表包含数百个报价器（例如GLE.PA）。每个股票代码（例如GLE.PA）都有每个“工作日”的条目。

我的目标是从此“每日价格”数据表中查询“每月价格摘要”。每月数据的结构如下：


月开放时间：dpOpen在该月的第一个工作日；
月最高：该月的max（dpHigh）；
月最低：每月的最低（dpLow）；
月关闭：dp月的最后一个营业日关闭。


我设法通过在SQLite3中使用以下查询来查询特定月份的数据：

SELECT
    strftime ('%Y-%m', dpDate) AS month,    
    (SELECT dpOpen
        FROM DailyPrices
        WHERE dpTicker = 'GLE.PA'
        AND dpDate = 
        (SELECT min(dpDate)
        FROM DailyPrices
        WHERE strftime('%Y%m', dpDate) = '201509'   
        )
    ) AS Open,
    max(dpHigh) AS High,
    min (dpLow) AS Low,
    (SELECT dpClose
        FROM DailyPrices
        WHERE dpTicker = 'GLE.PA'
        AND dpDate = 
        (SELECT max(dpDate)
        FROM DailyPrices
        WHERE strftime('%Y%m', dpDate) = '201509'   
        )
    ) AS Close
FROM DailyPrices
WHERE dpTicker ='GLE.PA'
AND strftime('%Y%m', dpDate) = '201509';

查询的输出如下：

bash-3.2$ sqlite3 myShares < month.sql
month       Open        High        Low         Close     
----------  ----------  ----------  ----------  ----------
2015-09     42.72       44.07       37.25       39.85     
bash-3.2$ 

通过以下查询，我设法生成每月最高和最低概览：

SELECT  
    strftime('%Y-%m', dpDate) AS Month,
    max(dpHigh) AS High,
    min(dpLow) AS Low
FROM DailyPrices
WHERE dpTicker ='GLE.PA'
GROUP BY strftime('%Y%m', update);

输出的快照如下所示：

bash-3.2$ sqlite3 myShares < monthly.sql
Month       High        Low       
----------  ----------  ----------
2000-01     219.32      184.346   
2000-02     206.43      181.977   
2000-03     210.411     181.503   
2000-04     221.405     197.805   
2000-05     226.239     55.9199
...

通过以下查询，我设法提取正确的打开数据，并通过类推正确的关闭数据：

SELECT 
    strftime('%Y-%m', dpDate) AS Month, 
    dpOpen AS Open
FROM DailyPrices
WHERE dpTicker = 'GLE.PA' 
AND dpDate IN
    (SELECT min(dpDate)
        FROM DailyPrices
        WHERE dpTicker = 'GLE.PA'
        GROUP BY strftime('%Y%m', dpDate)
    );

输出快照如下：

bash-3.2$ sqlite3 myShares < Open.sql
Month       Open      
----------  ----------
2000-01     218.846   
2000-02     200.269   
2000-03     206.525   
2000-04     201.312   
2000-05     215.908 
...

我正在努力将查询month.sql和open.sql组合到一个查询中，以获取以下输出：

Month    Open    High    Low    Close
-------  -----   -----   -----  -----
2015-01  42.79   42.79   33.69  35.18
2015-02  35.39   35.46   26.61  32.42
2015-03  32.32   37.65   31.93  32.48
...

任何解决这个问题的帮助将不胜感激。
最好的祝福

甘

Answer 1

第三个查询指的是在三个位置搜索的特定月份。让我们删除子查询中的两个事件；这需要使用别名，以便我们可以通过名称引用同一表的其他实例：

SELECT
    strftime ('%Y-%m', dpDate) AS month,    
    (SELECT dpOpen
        FROM DailyPrices
        WHERE dpTicker = 'GLE.PA'
        AND dpDate = 
        (SELECT min(dpDate)
        FROM DailyPrices AS DP2
        WHERE strftime('%Y%m', DP2.dpDate) = strftime('%Y%m', DP1.dpDate)
        )
    ) AS Open,
    max(dpHigh) AS High,
    min (dpLow) AS Low,
    (SELECT dpClose
        FROM DailyPrices
        WHERE dpTicker = 'GLE.PA'
        AND dpDate = 
        (SELECT max(dpDate)
        FROM DailyPrices AS DP2
        WHERE strftime('%Y%m', DP2.dpDate) = strftime('%Y%m', DP1.dpDate)
        )
    ) AS Close
FROM DailyPrices AS DP1
WHERE dpTicker ='GLE.PA'
AND strftime('%Y%m', dpDate) = '201509';

现在，只有最外面的查询需要知道月份，我们可以简单地将过滤器替换为GROUP BY：

SELECT
    strftime ('%Y-%m', dpDate) AS month,    
    (...) AS Open,
    max(dpHigh) AS High,
    min (dpLow) AS Low,
    (...) AS Close
FROM DailyPrices
WHERE dpTicker ='GLE.PA'
GROUP BY strftime('%Y%m', dpDate);

请注意，可以使用ORDER BY / LIMIT简化打开/关闭子查询：

(SELECT dpOpen
 FROM DailyPrices
 WHERE dpTicker = 'GLE.PA'
   AND ... dpDate ...
 ORDER BY dpDate ASC
 LIMIT 1) AS Open