sql - 甲骨文分组

Question

我想要做的就是总结 ID 在一组中的每个“情节”的时间，然后获得 NC 到 C 和 C 到 NC 的第一集时间以及最后一集时间NC 到 C 和 C 到 NC 在下表中，我手动添加了 GRP_Time 列。我还添加了最终结果表

这是我试图系统生成的指标。

ID    ASSign_ID  GRP      Time            GRP_Time   
11    1788       NC       6             
11    1802       NC       1               7
11    2995       C        7               7
11    5496       NC       11              11
11    6077       C        2 
11    6216       C        2
11    6226       C        4               8  
11    6790       NC       5               5
12    1234       C        6               6
12    2345       NC       1               
12    3456       NC       8               9
12    4567       C       11              11
14    6789       C        2 
14    7890       C        2
14    8900       C        4               8  
14    8904       NC       5               5

结果表

ID   First_ET_NC_C    First_ET_C_NC   LAST_ET_NC_C   LAST_ET_C_NC
11   7                7               11             8
12   9                6                9             6 
14   -                8                -             8

Answer 1

试试这个:

with seq as 
(    
  select tbl.*, 
      row_number() over(order by assign_id) rn  -- naturalized the order
  from tbl
),
grp as 
(
  select cr.*,          
     sum(case when cr.grp = pr.grp or pr.grp is null then 0 else 1 end)
     over(order by cr.rn) gn      
  from seq cr -- current row
  left join seq pr -- previous row
  on pr.rn = cr.rn - 1
)
,run as
(
  select grp.*,
      sum(time) over(partition by gn order by rn) as run_tot
  from grp
)
select 
   id, assign_id, grp, time,
   case when max(rn) over(partition by gn) <> rn then 
      null
   else
      run_tot
   end as run_total
from run r;

输出:

ID        ASSIGN_ID GRP       TIME      RUN_TOTAL
11        1788      NC        6         (null)
11        1802      NC        1         7
11        2995      C         7         7
11        5496      NC        11        11
11        6077      C         2         (null)
11        6216      C         2         (null)
11        6226      C         4         8
11        6790      NC        5         5

现场测试:http://www.sqlfiddle.com/#!4/faacc/1

<小时/>

它是如何工作的:

ID        ASSIGN_ID GRP       TIME      RN        GN
11        1788      NC        6         1         0
11        1802      NC        1         2         0
11        2995      C         7         3         1
11        5496      NC        11        4         2
11        6077      C         2         5         3
11        6216      C         2         6         3
11        6226      C         4         7         3
11        6790      NC        5         8         4

我们基本上需要为连续的 grp 分配一个组号(GN 列)。然后我们可以根据 GN 进行分区的运行总计

您可以在此处查看查询的进度:http://www.sqlfiddle.com/#!4/faacc/1

每个步骤都建立在上一步的基础上。只需向下滚动即可查看解决方案的进展情况

<小时/>

编辑

查询可以缩短，因为您的报告不显示每行总计的运行情况，并且仅显示最后一行，而不是 sum(time) over(partition by gn order by rn)作为 run_tot，我们可以做 sum(time) over(partition by gn) as run_tot，即我们删除 order by rn；然后检测该行是否是最后一行，如果是则进行求和，否则显示 null。

最终查询:

with seq as 
(

  select
  
     tbl.*, 
     row_number() over(order by assign_id) rn  -- naturalized the order
  from tbl
),
grp as 
(
  select 
   
     cr.*,
      
     sum(case when cr.grp = pr.grp or pr.grp is null then 0 else 1 end)
     over(order by cr.rn) gn
  
  from seq cr -- current row
  left join seq pr -- previous row
  on pr.rn = cr.rn - 1
)
select
     grp.*,

     case when max(rn) over(partition by gn) <> rn then -- if not last row
        null
     else -- if last row
        sum(time) over(partition by gn) 
     end as running_total
from grp;

现场测试:http://www.sqlfiddle.com/#!4/faacc/7

<小时/><小时/>

编辑

关于多个ID，例如6790:

ID        ASSIGN_ID GRP       TIME
11        1788      NC        6
11        1802      NC        1
11        2995      C         7
11        5496      NC        11
11        6077      C         2
11        6216      C         2
11        6226      C         4
11        6790      NC        5
12        6790      NC        1
12        6791      NC        3
12        6792      NC        1
12        6793      NC        4
12        6794      C         1
12        6795      C         6
12        6797      C         8
13        6793      C         1
13        6794      C         4
13        6795      C         3

有两个相似的 ASSIGN_ID，例如6790，但它属于一个更大的组(在 ID 上，对于 11 和 12)，因此为了隔离这两个组，我们必须按 ID 对其进行分区。

这是最终的查询，请注意评论中添加了此内容:http://www.sqlfiddle.com/#!4/83789/2

with seq as 
(    
  select tbl.*, 

     -- added this: partition by id
     -- naturalized the order: rn       
     row_number() over(partition by id order by assign_id) rn  
  from tbl
)
,grp as 
(
  select cr.*,        

     -- added this: partition by cr.id
     sum(case when cr.grp = pr.grp then 0 else 1 end)
     over(partition by cr.id order by cr.rn) gn      
  from seq cr -- current row
  left join seq pr -- previous row
  on 
    pr.id = cr.id -- added this
    and pr.rn = cr.rn - 1
)
select id, assign_id, grp, time, 

     -- added this: partition by id
     case when max(rn) over(partition by id,gn) <> rn then 
        null
     else
        -- added this: partition by id
        sum(time) over(partition by id,gn) 
     end as running_total
from grp
order by id, rn;

输出:

ID        ASSIGN_ID GRP       TIME      RUNNING_TOTAL
11        1788      NC        6         (null)
11        1802      NC        1         7
11        2995      C         7         7
11        5496      NC        11        11
11        6077      C         2         (null)
11        6216      C         2         (null)
11        6226      C         4         8
11        6790      NC        5         5
12        6790      NC        1         (null)
12        6791      NC        3         (null)
12        6792      NC        1         (null)
12        6793      NC        4         9
12        6794      C         1         (null)
12        6795      C         6         (null)
12        6797      C         8         15
13        6793      C         1         (null)
13        6794      C         4         (null)
13        6795      C         3         8

这是如何工作的，请记下ID 和 GN:

ID        ASSIGN_ID GRP       TIME      RN        GN        RUNNING_TOTAL
11        1788      NC        6         1         1         (null)
11        1802      NC        1         2         1         7
11        2995      C         7         3         2         7
11        5496      NC        11        4         3         11
11        6077      C         2         5         4         (null)
11        6216      C         2         6         4         (null)
11        6226      C         4         7         4         8
11        6790      NC        5         8         5         5
12        6790      NC        1         1         1         (null)
12        6791      NC        3         2         1         (null)
12        6792      NC        1         3         1         (null)
12        6793      NC        4         4         1         9
12        6794      C         1         5         2         (null)
12        6795      C         6         6         2         (null)
12        6797      C         8         7         2         15
13        6793      C         1         1         1         (null)
13        6794      C         4         2         1         (null)
13        6795      C         3         3         1         8

在此处查看查询进度:http://www.sqlfiddle.com/#!4/83789/2

<小时/>

更新尝试使用这个，它更简洁易读:https://stackoverflow.com/a/10629498