sql - 查找列值连续增加的行

Question

我有一个存储股票每日价格的 SQL 表。每天收市后都会插入新记录。我想找到价格连续上涨的股票。

该表有很多列，但这是相关的子集:

quoteid     stockid      closeprice     createdate
--------------------------------------------------
    1           1               1       01/01/2012
    2           2              10       01/01/2012
    3           3              15       01/01/2012

    4           1               2       01/02/2012
    5           2              11       01/02/2012
    6           3              13       01/02/2012

    7           1               5       01/03/2012
    8           2              13       01/03/2012
    9           3              17       01/03/2012

   10           1               7       01/04/2012
   11           2              14       01/04/2012
   12           3              18       01/04/2012

   13           1               9       01/05/2012
   14           2              11       01/05/2012
   15           3              10       01/05/2012

quoteid 列是主键。

在表中，股票 ID 1 的收盘价每天都在上涨。股票id 3波动较大，股票id 2最后一天价格下跌。

我正在寻找这样的结果:

stockid     Consecutive Count (CC)
----------------------------------
    1                5
    2                4

如果您可以获得连续条纹的日期输出，那就更好了:

stockid     Consecutive Count (CC)      StartDate      EndDate
---------------------------------------------------------------
    1                5                 01/01/2012    01/05/2012
    2                4                 01/01/2012    01/04/2012

StartDate 是价格开始上涨的时间，EndDate 是牛市实际结束的时间。

我发现这不是一个简单的问题。我在这里查看了其他帖子，它们也处理这个连续的场景，但它们不符合我的需要。如果您知道任何与我类似的帖子，请告诉我。

Answer 1

无论如何，以增加每只股票的行数来表示是有帮助的(实际的 quoteid 值在这里并没有多大帮助)。捕获的天数(在此表中)是最简单的 - 如果您想要其他内容(例如仅工作日，忽略周末/节假日或其他)，它会变得更加复杂；您可能需要一个日历文件。如果您还没有 [stockid, createdate] 索引，您将需要一个索引。

WITH StockRow AS (SELECT stockId, closePrice, createdDate,
                         ROW_NUMBER() OVER(PARTITION BY stockId 
                                           ORDER BY createdDate) rn
                  FROM Quote),

     RunGroup AS (SELECT Base.stockId, Base.createdDate,
                         MAX(Restart.rn) OVER(PARTITION BY Base.stockId
                                              ORDER BY Base.createdDate) groupingId
                  FROM StockRow Base
                  LEFT JOIN StockRow Restart
                         ON Restart.stockId = Base.stockId
                            AND Restart.rn = Base.rn - 1
                            AND Restart.closePrice > Base.closePrice)

SELECT stockId, 
       COUNT(*) AS consecutiveCount, 
       MIN(createdDate) AS startDate, MAX(createdDate) AS endDate
FROM RunGroup
GROUP BY stockId, groupingId
HAVING COUNT(*) >= 3
ORDER BY stockId, startDate

根据提供的数据产生以下结果:

Increasing_Run
stockId   consecutiveCount  startDate    endDate
===================================================
1         5                 2012-01-01   2012-01-05
2         4                 2012-01-01   2012-01-04
3         3                 2012-01-02   2012-01-04

SQL Fiddle Example
(Fiddle 还有一个多次运行的示例)

此分析将忽略所有差距，正确匹配所有运行(下次正运行开始时)。

<小时/>

那么这是怎么回事？

StockRow AS (SELECT stockId, closePrice, createdDate,
                    ROW_NUMBER() OVER(PARTITION BY stockId 
                                      ORDER BY createdDate) rn
             FROM Quote)

此 CTE 用于一个目的:我们需要一种方法来查找下一行/上一行，因此首先我们按(日期)顺序对每一行进行编号...

RunGroup AS (SELECT Base.stockId, Base.createdDate,
                    MAX(Restart.rn) OVER(PARTITION BY Base.stockId
                                         ORDER BY Base.createdDate) groupingId
             FROM StockRow Base
             LEFT JOIN StockRow Restart
                    ON Restart.stockId = Base.stockId
                       AND Restart.rn = Base.rn - 1
                           AND Restart.closePrice > Base.closePrice)

...然后根据索引将它们连接起来。如果您最终得到了具有 LAG()/LEAD() 的东西，那么使用它们几乎肯定是更好的选择。不过，这里有一个关键的事情 - 仅当行无序(小于前一行)时才匹配。否则，该值最终会为 null (使用 LAG()，您之后需要使用类似 CASE 的内容来实现此目的) 。您会得到一个看起来像这样的临时集:

B.rn   B.closePrice   B.createdDate  R.rn   R.closePrice   R.createdDate  groupingId
1      15             2012-01-01     -      -              -              -
2      13             2012-01-02     1      15             2012-01-01     1
3      17             2012-01-03     -      -              -              1
4      18             2012-01-04     -      -              -              1
5      10             2012-01-05     4      18             2012-01-04     4

...因此，仅当前一行大于“当前”行时，才会有Restart 的值。在窗口函数中使用 MAX() 是迄今为止看到的最大值...因为 null 是最低的，导致行索引为保留所有其他行，直到发生另一次不匹配(给出新值)。此时，我们基本上得到了 gaps-and-islands 的中间结果。查询，为最终聚合做好准备。

SELECT stockId, 
       COUNT(*) AS consecutiveCount, 
       MIN(createdDate) AS startDate, MAX(createdDate) AS endDate
FROM RunGroup
GROUP BY stockId, groupingId
HAVING COUNT(*) >= 3
ORDER BY stockId, startDate

查询的最后部分是获取运行的开始日期和结束日期，并计算这些日期之间的条目数。如果日期计算有更复杂的事情，可能需要在此时进行。 GROUP BY 显示 不在 SELECT 子句中包含列的少数合法实例之一。 HAVING 子句用于消除“太短”的运行。