我有下一种表：
|client| card| date | balance|
--------------------------------------------
| 1 | 123 | 10-01-2018 10:04:36| 1000 |
| 1 | 321 | 10-01-2018 10:07:28| 2980 |
| 1 | 321 | 10-01-2018 11:23:34| -100 |
| 1 | 123 | 10-01-2018 12:32:33| -200 |
| 1 | 123 | 10-01-2018 12:44:43| 100 |
| 1 | 321 | 10-01-2018 14:00:28| 2000 |
| 1 | 321 | 10-01-2018 14:00:28| -2100 |
在该表中，我们可以看到一个带有两个银行卡的客户。余额是当前时间的卡平衡。我想在当前的时间时刻获得积极平衡的卡数。我想在结果中看到的
|client| card| date | balance| bal_pos|
-----------------------------------------------------
| 1 | 123 | 10-01-2018 10:04:36| 1000 | 1 |
| 1 | 321 | 10-01-2018 10:07:28| 2980 | 2 |
| 1 | 321 | 10-01-2018 11:23:34| -100 | 1 |
| 1 | 123 | 10-01-2018 12:32:33| -200 | 0 |
| 1 | 123 | 10-01-2018 12:44:43| 100 | 1 |
| 1 | 321 | 10-01-2018 14:00:28| 2000 | 2 |
| 1 | 321 | 10-01-2018 14:00:28| -2100 | 1 |
如何计算具有正余额的卡数？（属性bal_pos）
p.s我真的不知道如何计算两张卡的日期相同的日期（表中的最后两行）时如何计算牌数。P.P.S也应该适用于下一个示例：
|client| card| date | balance|
--------------------------------------------
| 1 | 123 | 10-01-2018 10:04:36| 1000 |
| 1 | 321 | 10-01-2018 10:07:28| 2980 |
| 1 | 321 | 10-01-2018 11:23:34| 100 |
| 1 | 123 | 10-01-2018 12:32:33| 200 |
| 1 | 123 | 10-01-2018 12:44:43| 100 |
| 1 | 321 | 10-01-2018 14:00:28| 2000 |
| 1 | 321 | 10-01-2018 14:00:28| 2100 |
对于这种情况，我期望下一个结果（因为客户在该表中只有2张卡，而具有正余额的卡数不能超过2）：
|client| card| date | balance| bal_pos|
-----------------------------------------------------
| 1 | 123 | 10-01-2018 10:04:36| 1000 | 1 |
| 1 | 321 | 10-01-2018 10:07:28| 2980 | 2 |
| 1 | 321 | 10-01-2018 11:23:34| 100 | 2 |
| 1 | 123 | 10-01-2018 12:32:33| 200 | 2 |
| 1 | 123 | 10-01-2018 12:44:43| 100 | 2 |
| 1 | 321 | 10-01-2018 14:00:28| 2000 | 2 |
| 1 | 321 | 10-01-2018 14:00:28| 2100 | 2 |
您可以尝试以下查询 -
由于即使是时间戳也可以相同，您可以在分析功能上定义窗口子句（我已将int col for Order by by Arde而不是时间戳）
）with t1 as (select 1 as client, 123 as card, 1 as orderBy, 1000 as bal
union
select 1 as client, 321 as card, 2 as orderBy, 2980 as bal
union
select 1 as client, 321 as card, 3 as orderBy, -100 as bal
union
select 1 as client, 123 as card, 4 as orderBy, -200 as bal
union
select 1 as client, 123 as card, 5 as orderBy, 100 as bal
union
select 1 as client, 321 as card, 6 as orderBy, 2000 as bal
union
select 1 as client, 321 as card, 6 as orderBy, -2100 as bal)
,res1 as (select client, card, orderBy, bal, case when bal>0 then 1 else -1 end as bal_type from t1)
select client, card, orderBy, bal, sum(bal_type)
over (order by OrderBy asc, bal desc -- this to get output same as yours, but you will never be sure which bal you have to consider if time is same
rows between unbounded preceding and current row) as bal_pos from res1;
结果 -
1 123 1 1000 1
1 321 2 2980 2
1 321 3 -100 1
1 123 4 -200 0
1 123 5 100 1
1 321 6 2000 2
1 321 6 -2100 1
如果您不指定窗口子句而不是计算每一行的总和，则将计算范围，请检查以下查询的结果。
查询 -
with t1 as (select 1 as client, 123 as card, 1 as orderBy, 1000 as bal
union
select 1 as client, 321 as card, 2 as orderBy, 2980 as bal
union
select 1 as client, 321 as card, 3 as orderBy, -100 as bal
union
select 1 as client, 123 as card, 4 as orderBy, -200 as bal
union
select 1 as client, 123 as card, 5 as orderBy, 100 as bal
union
select 1 as client, 321 as card, 6 as orderBy, 2000 as bal
union
select 1 as client, 321 as card, 6 as orderBy, -2100 as bal)
,res1 as (select client, card, orderBy, bal, case when bal>0 then 1 else -1 end as bal_type from t1)
select client, card, orderBy, bal, sum(bal_type) over (order by OrderBy
-- range between unbounded preceding and current row -- check the results with range clause
) as bal_pos from res1;
1 123 1 1000 1
1 321 2 2980 2
1 321 3 -100 1
1 123 4 -200 0
1 123 5 100 1
1 321 6 -2100 1 -- sum of first row till current row but based on value of orderBy column (6)
1 321 6 2000 1
希望这有帮助