是否有一个窗口函数或窗口函数的组合，我可以通过tsql来获得这个期望的结果

ojsjcaue 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(350)

以下表为例：

ORDER_KEY  |   STATUS_DATE   | TASK_CREATED_DATE |  TASK_KEY  | TASK_ROW_ID | TASK_REVISION_ID 
-----------|-----------------|-------------------|------------|-------------|------------------ 
1274796898 | 10/4/2019 18:19 | 10/4/2019 18:12   | 5277852673 |           1 |                  
1274796898 | 10/4/2019 18:19 | 10/4/2019 18:12   | 5277852674 |           2 |                  
1274796898 | 10/4/2019 18:19 | 4/7/2020 14:22    | 5277853673 |           3 |                1 
1274796898 | 10/4/2019 18:19 | 4/7/2020 14:22    | 5277853674 |           4 |                1 
1274796898 | 10/4/2019 18:19 | 4/7/2020 14:22    | 5277853675 |           5 |                1 
1274737653 | 10/4/2019 15:32 | 10/4/2019 15:31   | 5277852586 |           1 |                  
1274737653 | 10/4/2019 15:32 | 10/4/2019 15:31   | 5277852587 |           2 |                  
1274737653 | 10/4/2019 15:32 | 10/4/2019 15:31   | 5277852588 |           3 |                  
1274737653 | 10/4/2019 15:32 | 10/4/2019 15:32   | 5277852589 |           4 |                  
1274737647 | 10/4/2019 11:49 | 10/4/2019 11:42   | 5277855586 |           1 |                  
1274737647 | 10/4/2019 11:49 | 10/4/2019 11:42   | 5277855587 |           2 |                  
1274737647 | 10/4/2019 11:49 | 10/4/2019 11:42   | 5277855588 |           3 |                  
1274737647 | 10/4/2019 11:49 | 10/4/2019 11:42   | 5277855589 |           4 |                  
1274737647 | 10/4/2019 11:49 | 10/4/2019 11:42   | 5277855590 |           5 |                  
1274737647 | 10/4/2019 11:49 | 10/4/2019 11:49   | 5277855587 |           6 |                  
1274737647 | 10/4/2019 11:49 | 10/4/2019 11:49   | 5277856270 |           7 |                  
1274737647 | 10/4/2019 11:49 | 4/7/2020 14:10    | 5281403575 |           8 |                1 
1274737647 | 10/4/2019 11:49 | 4/7/2020 14:10    | 5281403576 |           9 |                1 
1274737647 | 10/4/2019 11:49 | 4/7/2020 14:12    | 5281403595 |          10 |                1 
1274737647 | 10/4/2019 11:49 | 4/14/2020 09:32   | 5281403599 |          11 |                2 
1274737647 | 10/4/2019 11:49 | 4/26/2020 13:10   | 5281403600 |          12 |                3

可通过以下查询复制：

with data as (
    select *
    from (
      values
      (1274796898, '10/4/2019 18:19', '10/4/2019 18:12', 5277852673,   1), 
      (1274796898, '10/4/2019 18:19', '10/4/2019 18:12', 5277852674,   2), 
      (1274796898, '10/4/2019 18:19', '4/7/2020 14:22', 5277853673,   3), 
      (1274796898, '10/4/2019 18:19', '4/7/2020 14:22', 5277853674,   4), 
      (1274796898, '10/4/2019 18:19', '4/7/2020 14:22', 5277853675,   5), 
      (1274737653, '10/4/2019 15:32', '10/4/2019 15:31', 5277852586,   1), 
      (1274737653, '10/4/2019 15:32', '10/4/2019 15:31', 5277852587,   2), 
      (1274737653, '10/4/2019 15:32', '10/4/2019 15:31', 5277852588,   3), 
      (1274737653, '10/4/2019 15:32', '10/4/2019 15:32', 5277852589,   4), 
      (1274737647, '10/4/2019 11:49', '10/4/2019 11:42', 5277855586,   1), 
      (1274737647, '10/4/2019 11:49', '10/4/2019 11:42', 5277855587,   2), 
      (1274737647, '10/4/2019 11:49', '10/4/2019 11:42', 5277855588,   3), 
      (1274737647, '10/4/2019 11:49', '10/4/2019 11:42', 5277855589,   4), 
      (1274737647, '10/4/2019 11:49', '10/4/2019 11:42', 5277855590,   5), 
      (1274737647, '10/4/2019 11:49', '10/4/2019 11:49', 5277855587,   6), 
      (1274737647, '10/4/2019 11:49', '10/4/2019 11:49', 5277856270,   7), 
      (1274737647, '10/4/2019 11:49', '4/7/2020 14:10', 5281403575,   8), 
      (1274737647, '10/4/2019 11:49', '4/7/2020 14:10', 5281403576,   9), 
      (1274737647, '10/4/2019 11:49', '4/7/2020 14:12', 5281403595,  10), 
      (1274737647, '10/4/2019 11:49', '4/14/2020 09:32', 5281403599,  11), 
      (1274737647, '10/4/2019 11:49', '4/26/2020 13:10', 5281403600,  12)
    ) v (ORDER_KEY, STATUS_DATE, TASK_CREATED_DATE, TASK_KEY, TASK_ROW_ID)
)
select * from data;

我最终想要实现的行为是 TASK_REVISION_ID 列输出。
在上面的数据中，它是手动输入的，以便于说明。

TASK_ROW_ID = ROW_NUMBER() OVER(PARTITION BY ORDER_KEY ORDER BY TASK_CREATED_DATE, TASK_KEY)

在 ORDER_KEY 级别，我想增加 TASK_REVISION_ID 以以下方式：
套 TASK_REVISION_ID 到1时 TASK_CREATED_DATE > STATUS_DATE 增量 TASK_REVISION_ID 当当前行之间以分钟为单位的差值 TASK_CREATED_DATE 和上一行 TASK_CREATED_DATE >= 5

sql window-functions sql-server tsql

来源：https://stackoverflow.com/questions/62009435/is-there-a-window-function-or-combination-of-window-functions-that-i-can-use-to

2条答案

按热度按时间

e3bfsja21#

你只需要稍微修改一下原来的答案就可以看出正确的想法：

select
  d.ORDER_KEY, d.STATUS_DATE, d.TASK_CREATED_DATE, d.TASK_KEY, d.TASK_ROW_ID, 
  task_revision_id = (
    case when grp = 1
    then sum(case when datediff(mi, prev_tcd, TASK_CREATED_DATE) > 5 or grp_first = 1 then 1 else 0 end) over
                      (partition by d.order_key, d.grp order by task_row_id)
    end
  )
from
  (select d.*,
          lag(TASK_CREATED_DATE) over (partition by order_key order by task_row_id) as prev_tcd,
          case when task_created_date > status_date then 1 else 0 end as grp,
          case when lag(case when task_created_date > status_date then 1 else 0 end, 1, 0) over (partition by order_key order by task_row_id) = 0 then 1 else 0 end as grp_first
   from
      data d
  ) d 
order by
  order_key,
  task_row_id

赞(0）回复(0）举报 2021-07-26

nszi6y052#

这回答了问题的原始版本。
您所描述的内容由以下查询捕获：

select (case when grp = 1
             then sum(case when prev_tcd > dateadd(minute, -5, TASK_CREATED_DATE) then 0 else 1 end) over
                      (partition by d.order_key, v.grp order by task_created_date)
        end),
       d.*
from (select d.*,
             lag(TASK_CREATED_DATE) over (partition by order_key order by TASK_CREATED_DATE) as prev_tcd
      from data d
     ) d cross apply
     (values ((case when task_created_date > status_date then 1 else 0 end))) v(grp)
order by 1, 2, 3;

这是一把小提琴。

赞(0）回复(0）举报 2021-07-26

我来回答

是否有一个窗口函数或窗口函数的组合，我可以通过tsql来获得这个期望的结果

2条答案

相关问题

热门标签

最新问答