postgres：SQL和条纹？

  • Z时代
  • 2024-01-10
  • 分类：问答

试图找出填充此表最后一列的最快方法，streak列存储运行结果值（win或loss）。postgres：SQL和条纹？

game_date | team | result | streak 
------------+---------+--------+-------- 
2017-10-05 | ABC  | win | 1 
2017-10-07 | ABC  | win | 2 
2017-10-11 | ABC  | loss | 1 
2017-10-14 | ABC  | win | 1 
2017-10-15 | ABC  | win | 2 
2017-10-18 | ABC  | win | 3 
2017-10-21 | ABC  | win | 4 
2017-10-23 | ABC  | loss | 1 
2017-10-24 | ABC  | win | 1 
2017-10-26 | ABC  | win | 2 
2017-10-28 | ABC  | win | 3 
2017-10-30 | ABC  | loss | 1 
2017-11-02 | ABC  | win | 1 
2017-11-04 | ABC  | loss | 1 
2017-11-07 | ABC  | win | 1 
2017-11-09 | ABC  | loss | 1 
2017-11-12 | ABC  | loss | 1 
2017-11-14 | ABC  | loss | 2 
2017-11-16 | ABC  | loss | 3 
2017-11-18 | ABC  | win | 1 
2017-11-19 | ABC  | loss | 1 
2017-11-22 | ABC  | loss | 2 
2017-11-24 | ABC  | loss | 3 
2017-11-25 | ABC  | win | 1 
2017-11-28 | ABC  | win | 2 
2017-11-30 | ABC  | win | 3 
2017-12-01 | ABC  | win | 4 
(27 rows)

正如你看到的，streak应该重置为1时，先结果为空，或者不同的，否则一个递增。

回答：

您可以通过使用row_numbers()的区别来识别结果值的“孤岛”，从而识别获胜和丢失的序列。其余仅仅是最终的应用row_number()：

select g.*, 
     row_number() over (partition by seqnum - seqnum_r, result order by game_date) as streak 
from (select g.*, 
      row_number() over (order by game_date) as seqnum, 
      row_number() over (partition by result order by game_date) as seqnum_r 
     from games g 
    ) g

以上是 postgres：SQL和条纹？ 的全部内容， 来源链接： utcz.com/qa/258910.html

回到顶部