最大连胜内的行数？

Question

给定行，

symbol_id profit date
1         100    2009-08-18 01:01:00
1         100    2009-08-18 01:01:01
1         156    2009-08-18 01:01:04
1         -56    2009-08-18 01:01:06
1         18     2009-08-18 01:01:07

我如何最有效地选择涉及最大连续（利润）的行。

最大的连胜是前 3 行，我想要这些行。我想出的查询只是一堆嵌套查询和派生表。我正在寻找一种使用公用表表达式或更高级的方法来执行此操作的有效方法。

Answer 1

您尚未定义如何处理 0 利润，或者如果最长连胜平局会发生什么。但类似...

;WITH T1 AS
(
SELECT *,
       ROW_NUMBER() OVER (PARTITION BY symbol_id ORDER BY date) - 
       ROW_NUMBER() OVER (PARTITION BY symbol_id, SIGN(profit) 
                              ORDER BY date) AS Grp 
FROM  Data      
), T2 AS  
(
SELECT *,
       COUNT(*) OVER (PARTITION BY symbol_id,Grp) AS StreakLen
FROM T1       
)
SELECT TOP 1 WITH TIES *
FROM T2 
ORDER BY  StreakLen DESC

或者 - 如果您正在寻找最有利的连续走势

;WITH T1 AS
(
SELECT *,
       ROW_NUMBER() OVER (PARTITION BY symbol_id ORDER BY date) - 
       ROW_NUMBER() OVER (PARTITION BY symbol_id, CASE WHEN profit >= 0 THEN 1 END
                              ORDER BY date) AS Grp 
FROM  Data      
), T2 AS  
(
SELECT *,
       SUM(profit) OVER (PARTITION BY symbol_id,Grp) AS StreakProfit
FROM T1       
)
SELECT TOP 1 WITH TIES *
FROM T2 
ORDER BY  StreakProfit DESC

Answer 2

declare @T table
(
  symbol_id int,
  profit int,
  [date] datetime
)

insert into @T values
(1,         100,    '2009-08-18 01:01:00'),
(1,         100,    '2009-08-18 01:01:01'),
(1,         156,    '2009-08-18 01:01:04'),
(1,         -56,    '2009-08-18 01:01:06'),
(1,         18 ,    '2009-08-18 01:01:07')

;with C1 as
(
  select *,
         row_number() over(order by [date]) as rn
  from @T
),
C2 as
(
  select *,
         rn - row_number() over(order by rn) as grp
  from C1
  where profit >= 0
)
select top 1 with ties *
from C2
order by sum(profit) over(partition by grp) desc

结果：

symbol_id   profit      date                    rn                   grp
----------- ----------- ----------------------- -------------------- --------------------
1           100         2009-08-18 01:01:00.000 1                    0
1           100         2009-08-18 01:01:01.000 2                    0
1           156         2009-08-18 01:01:04.000 3                    0

Answer 3

如果那是 MSSQL 服务器，那么您需要考虑在 select 子句中使用 TOP 3
并按利润描述排序。
如果 mysql/postgres 您可能需要考虑在 select 子句中使用 limit
也同样的顺序。

希望这有帮助。