时间戳

首页 > 解决方案 > SQL Server - 组和数字匹配连续值

sql - SQL Server - 组和数字匹配连续值

问题描述

我有一份股票交易清单，我正在使用 Over(Partition By) 按证券计算运行总数（头寸）。随着时间的推移，特定证券的持有可能是多头、空头或持平。我试图找到一种有效的方法来仅提取与每种证券的当前头寸相关的交易。

我创建了一个简化的sqlfiddle来展示我到目前为止所拥有的。cte 查询生成每个证券 (code_id) 的运行总计，并确定持股何时为多头 (L)、空头 (s) 或持平 (f)。我需要的是为每个 code_id 分组和编号匹配 L、S 或 F 的连续值。

我到目前为止是这样的：

; WITH RunningTotals as
(
SELECT 
    *,
    RunningTotal = sum(qty) OVER (Partition By code_id Order By id) 
FROM
    TradeData
), LongShortFlat as
(
    SELECT 
        *,
        LSF = CASE 
                WHEN RunningTotal > 0 THEN 'L'
                WHEN RunningTotal < 0 THEN 'S'
                ELSE 'F'
            END
    FROM
        RunningTotals
)
SELECT 
    *
FROM 
    LongShortFlat r

我认为我需要做的是通过为每个 code_id 中的每组 L、S 和 F 应用 row_number 创建一个 GroupNum 列，因此结果如下所示：

id code_id     qty  RunningTotal     LSF    GroupNum
  1       1       5             5      L            1
  2       1       2             7      L            1
  3       1       7            14      L            1
  4       1      -3            11      L            1
  5       1      -5             6      L            1
  6       1      -6             0      F            2
  7       1       5             5      L            3
  8       1       5            10      L            3
  9       1      -2             8      L            3
 10       1      -4             4      L            3
 11       2       5             5      L            1
 12       2       3             8      L            1
 13       2      -4             4      L            1
 14       2      -2             2      L            1
 15       2      -2             0      F            2
 16       2       6             6      L            3
 17       2      -5             1      L            3
 18       2      -5            -4      S            4
 19       2       2            -2      S            4
 20       2       4             2      L            5
 21       2      -5            -3      S            6
 22       2      -2            -5      S            6
 23       3       5             5      L            1
 24       3       2             7      L            1
 25       3       1             8      L            1

我正在努力生成 GroupNum 列。

在此先感谢您的帮助。

标签： sqlsql-server-2012

解决方案

[修改]

对不起，我读你的问题太快了。我想出了一个使用递归公用表表达式（如下）的解决方案，然后看到您已经使用LAG. 无论如何，我都会发布我修改后的查询，以供后代使用。无论哪种方式，生成的查询都是（恕我直言）非常难看。

;WITH cteBaseAgg
 as (
     --  Build the "sum increases over time" data
     SELECT
        row_number() over (partition by td.code_id order by td.code_id, td.Id)  RecurseKey
       ,td.code_id
       ,td.id
       ,td.qty
       ,sum(tdPrior.qty)  RunningTotal
       ,case
          when sum(tdPrior.qty) > 0 then 'L'
          when sum(tdPrior.qty) < 0 then 'S'
          else 'F'
        end  LSF
      from dbo.TradeData  td
       inner join dbo.TradeData  tdPrior
        on tdPrior.code_id = td.code_id  --  All for this code_id
         and tdPrior.id <= td.Id         --  For this and any prior Ids
      group by
        td.code_id
       ,td.id
       ,td.qty
    )
,cteRecurse
  as (
      --  "Set" the first row for each code_id
      SELECT
        RecurseKey
       ,code_id
       ,id
       ,qty
       ,RunningTotal
       ,LSF
       ,1  GroupNum

       from cteBaseAgg
       where RecurseKey = 1
      --  For each succesive row in each set, check if need to increment GroupNum
      UNION ALL SELECT
        agg.RecurseKey
       ,agg.code_id
       ,agg.id
       ,agg.qty
       ,agg.RunningTotal
       ,agg.LSF
       ,rec.GroupNum + case when rec.LSF = agg.LSF then 0 else 1 end
      from cteBaseAgg  agg
       inner join cteRecurse  rec
        on rec.code_id = agg.code_id
         and agg.RecurseKey - 1 = rec.RecurseKey
     )
--  Show results
SELECT
   id
  ,code_id
  ,qty
  ,RunningTotal
  ,LSF
  ,GroupNum
 from cteRecurse
 order by
   code_id
  ,id

推荐阅读