根据标志对计数递增的记录进行分组 [英] Group records with incrementing count based on flag

问题描述

拿两个...

实际数据:

division    ID          date            flag
ABC123      ZZZ123      1/17/2013       Y
ABC123      ZZZ123      1/25/2013       N
ABC123      ZZZ123      2/22/2013       Y
ABC123      ZZZ123      2/26/2013       N
ABC123      YYY222      3/20/2013       Y
ABC123      YYY222      5/17/2013       N
XYZ456      ZZZ999      1/15/2012       N
XYZ456      ZZZ999      1/30/2012       N
XYZ456      ZZZ123      2/09/2012       N
XYZ456      ZZZ123      4/13/2012       Y
XYZ456      ZZZ123      6/23/2012       N
XYZ456      ZZZ123      10/5/2012       Y
XYZ456      ZZZ123      11/18/2012      N

我需要构建一个新列 ORDER_group，它将根据以下规则进行填充:

I need to build a new column, ORDER_group, that will populate based on the following rules:

• 每个部门和 ID 组合都被视为一个组"，按日期排序，并应为其分配一个 ORDER_group(从 1 开始).
• 每次组"遇到标志Y"时，它应该将 ORDER_group 增加 1.
• 如果组"以标志 =N"开始(具有最早日期的第一条记录)，它仍应以 ORDER_group = 1 开始.
• 如果组"以标志 =Y"开始(具有最早日期的第一条记录)，它仍应以 ORDER_group = 1 开始.
• 每个后续记录都应该是相同的 ORDER_group 编号，除非遇到新的组"(部门/ID)，此时，它应该重置回 1，或者遇到下一个标志 =Y".

预期结果:

division    ID          date            flag    ORDER_group
ABC123      ZZZ123      1/17/2013       Y       1
ABC123      ZZZ123      1/25/2013       N       1
ABC123      ZZZ123      2/22/2013       Y       2
ABC123      ZZZ123      2/26/2013       N       2
ABC123      YYY222      3/20/2013       Y       1
ABC123      YYY222      5/17/2013       N       1
XYZ456      ZZZ999      1/15/2012       N       1
XYZ456      ZZZ999      1/30/2012       N       1
XYZ456      ZZZ123      2/09/2012       N       1
XYZ456      ZZZ123      4/13/2012       Y       2
XYZ456      ZZZ123      6/23/2012       N       2
XYZ456      ZZZ123      10/5/2012       Y       3
XYZ456      ZZZ123      11/18/2012      N       3

理想情况下，这应该在没有循环/游标的情况下完成，除非有 CTE/临时表的性能原因.填充此新列的最佳方法是什么?

Ideally this should be accomplished without a loop/cursor, unless there are performance reasons with CTE/temp tables. What is the best way to populate this new column?

任何帮助将不胜感激.

实际数据的 SQL Fiddler:http://sqlfiddle.com/#!3/5cca0/2

SQL Fiddler for Actual data: http://sqlfiddle.com/#!3/5cca0/2

推荐答案

所以这里有一个方法.它基于 How do I在不使用游标的情况下在 SQL 中计算运行总数? 这确实有一些缺陷.我在建议中使用索引，它可以使排序解决尽管更新顺序无法保证的事实.

So here a way to do it. It based on How do I calculate a running total in SQL without using a cursor? which does have some flaws. I'm using an index on the advice that it makes the ordering work out DESPITE the fact that order on the update is not guaranteed.

另外值得指出的是计算运行总额/运行余额 用于 Aaron Bertrand 治疗.

And it also worth pointing you to Calculate running total / running balance for Aaron Bertrand treatment.

这里可能很聪明的一点是将 Y/N 转换为 1/0 以用于计算.

The possibly clever bit here is the conversion of Y/N to 1/0 for use in calculating.

CREATE TABLE Orders (division CHAR(6),ID CHAR(6),dat DATETIME, flag CHAR(1))
INSERT INTO Orders VALUES

('ABC123','ZZZ123','01/17/2013','Y')
,('ABC123','ZZZ123','01/25/2013','N')
,('ABC123','ZZZ123','01/25/2013','N')
,('ABC123','ZZZ123','01/25/2013','N')
,('ABC123','ZZZ123','01/25/2013','N')
,('ABC123','ZZZ123','02/22/2013','Y')
,('ABC123','ZZZ123','02/26/2013','N')
,('ABC123','YYY222','03/20/2013','Y')
,('ABC123','YYY222','05/17/2013','N')
,('XYZ456','ZZZ999','01/15/2012','N')
,('XYZ456','ZZZ999','01/30/2012','N')
,('XYZ456','ZZZ123','02/09/2012','N')
,('XYZ456','ZZZ123','04/13/2012','Y')
,('XYZ456','ZZZ123','06/23/2012','N')
,('XYZ456','ZZZ123','010/5/2012','Y')
,('XYZ456','ZZZ123','11/18/2012','N')


CREATE TABLE #Orders (division CHAR(6),    ID CHAR(6),   dat DATETIME, flag CHAR(1),flag_int INTEGER, rn BIGINT, OrderGroup INT)

CREATE CLUSTERED INDEX IDX_C_Temp_Order ON #Orders(division, id,rn)

INSERT INTO #Orders (division, id,dat,flag,flag_int,rn,OrderGroup)
SELECT division
      ,ID
      ,dat
      ,flag
      ,CASE flag WHEN 'y' THEN 1 ELSE 0 END flag_int
      ,ROW_NUMBER() OVER (PARTITION BY division, id ORDER BY dat) rn
      ,0 OrderGroup
  FROM Orders

DECLARE @OrderGroup INT = 0
UPDATE #Orders
   SET @OrderGroup = OrderGroup  = CASE WHEN rn = 1 THEN 1 ELSE @OrderGroup + flag_int END
  FROM #Orders

SELECT * 
  FROM #Orders
 ORDER BY division
         ,ID
         ,rn

DROP TABLE #Orders

这篇关于根据标志对计数递增的记录进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！