时间戳

首页 > 解决方案 > 数量基于条件

sql - 数量基于条件

问题描述

我正在尝试根据条件生成一个数字。当按 Start_Date 排序的客户端分区中的“停止”列中为“是”时，密集排名必须重新开始。所以我尝试了几件事，但这仍然不是我想要的。我的表有当前号码和预期号码

+-----------+------------+------+------------+-------------+
| Client_No | Start_Date | Stop | Current_No | Expected_No |
+-----------+------------+------+------------+-------------+
|     1     |  1-1-2018  |  No  |      1     |      1      |
+-----------+------------+------+------------+-------------+
|     1     |  1-2-2018  |  No  |      2     |      2      |
+-----------+------------+------+------------+-------------+
|     1     |  1-3-2018  |  No  |      3     |      3      |
+-----------+------------+------+------------+-------------+
|     1     |  1-4-2018  |  Yes |      1     |      1      |
+-----------+------------+------+------------+-------------+
|     1     |  1-5-2018  |  No  |      4     |      2      |
+-----------+------------+------+------------+-------------+
|     1     |  1-6-2018  |  No  |      5     |      3      |
+-----------+------------+------+------------+-------------+
|     2     |  1-2-2018  |  No  |      1     |      1      |
+-----------+------------+------+------------+-------------+
|     2     |  1-3-2018  |  No  |      2     |      2      |
+-----------+------------+------+------------+-------------+
|     2     |  1-4-2018  |  Yes |      1     |      1      |
+-----------+------------+------+------------+-------------+
|     2     |  1-5-2018  |  No  |      3     |      2      |
+-----------+------------+------+------------+-------------+
|     2     |  1-6-2018  |  Yes |      2     |      1      |
+-----------+------------+------+------------+-------------+

到目前为止我使用的查询：

DENSE_RANK() OVER(PARTITION BY Client_No, Stop ORDER BY Start_Date ASC)

这似乎不是解决方案，因为它从值“否”开始计算，但我不知道如何以另一种方式处理这个问题。

标签： sqlsql-servertsqlgaps-and-islands

解决方案

解决这种“差距与岛屿”难题的一种方法是首先计算一个以“是”止损开头的排名。

然后在该排名上计算 row_number 或 dense_rank。

例如：

create table test 
(
  Id int identity(1,1) primary key,
  Client_No int,
  Start_Date date,
  Stop varchar(3)
)

insert into test 
(Client_No, Start_Date, Stop) values
  (1,'2018-01-01','No')
 ,(1,'2018-02-01','No')
 ,(1,'2018-03-01','No')
 ,(1,'2018-04-01','Yes')
 ,(1,'2018-05-01','No')
 ,(1,'2018-06-01','No')

 ,(2,'2018-02-01','No')
 ,(2,'2018-03-01','No')
 ,(2,'2018-04-01','Yes')
 ,(2,'2018-05-01','No')
 ,(2,'2018-06-01','Yes')

select *
, row_number() over (partition by Client_no, Rnk order by start_date) as rn
from
(
  select *
  , sum(case when Stop = 'Yes' then 1 else 0 end) over (partition by Client_No order by start_date) rnk
  from test
) q
order by Client_No, start_date
GO

身份证 | 客户_否 | 开始日期 | 停止 | rnk | rn
-: | --------: | :----------------- | :--- | --: | :-
 1 | 1 | 01/01/2018 00:00:00 | 没有 | 0 | 1
 2 | 1 | 01/02/2018 00:00:00 | 没有 | 0 | 2
 3 | 1 | 2018 年 1 月 3 日 00:00:00 | 没有 | 0 | 3
 4 | 1 | 01/04/2018 00:00:00 | 是 | 1 | 1
 5 | 1 | 01/05/2018 00:00:00 | 没有 | 1 | 2
 6 | 1 | 2018 年 1 月 6 日 00:00:00 | 没有 | 1 | 3
 7 | 2 | 01/02/2018 00:00:00 | 没有 | 0 | 1
 8 | 2 | 2018 年 1 月 3 日 00:00:00 | 没有 | 0 | 2
 9 | 2 | 01/04/2018 00:00:00 | 是 | 1 | 1
10 | 2 | 01/05/2018 00:00:00 | 没有 | 1 | 2
11 | 2 | 2018 年 1 月 6 日 00:00:00 | 是 | 2 | 1

db<>在这里摆弄

使用这个的区别：

row_number() over (partition by Client_no, Rnk order by start_date)

与此相比：

dense_rank() over (partition by Client_no, Rnk order by start_date)

是dense_rank 会为每个Client_no 和Rnk 计算相同的start_date 数字。

推荐阅读