查找列值连续增加的行

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列是主键。

在表中，股票编号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是有帮助的（实际值在这里并不是真的有帮助）。 捕获 的天数（在此表中）是最简单的-
如果您想要其他内容（例如仅工作日，忽略周末/节假日等），则它会涉及更多；您可能需要一个日历html" target="_blank">文件。如果您还没有索引，您将要在[
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](http://sqlfiddle.com/#!6/b1e78/4/0)
（提琴还有一个多次运行的示例）

该分析将忽略所有差距，正确匹配所有运行（下次开始运行正运行时）。

那么这是怎么回事？

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()，您需要使用类似CASEafter
after的东西来实现此目的）。您得到一个看起来像这样的临时集：

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是最低值，它会导致所有其他行的行索引都保留下来，直到发生另一个不匹配（给出新值）为止。至此，我们基本上有了间隔和孤岛查询的中间结果，可以进行最终汇总了。

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子句用于消除“过短”的运行。