返回最新数据的行 [英] Return rows of the latest 'streak' of data
本文介绍了返回最新数据的行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
给出一个包含以下数据的简单表:
Given a simple table with the following data:
id | result | played
----+--------+------------
7 | L | 2012-01-07
6 | L | 2012-01-06
5 | L | 2012-01-05
4 | W | 2012-01-04
3 | W | 2012-01-03
2 | L | 2012-01-02
1 | W | 2012-01-01
我该如何编写查询以返回最后丢失或赢取的连续记录使用PostgreSQL?在这种情况下,我正在寻找以下结果:
How would I write a query to return the lastest losing or winning streak of rows using PostgreSQL? In this case, I'm looking for a result of:
id | result | played
----+--------+------------
7 | L | 2012-01-07
6 | L | 2012-01-06
5 | L | 2012-01-05
我猜答案是使用 lag ()
和分区语法,但我似乎无法将其确定下来。
I'm guessing the answer is to use lag()
and partition syntax, but I can't seem to nail it down.
推荐答案
假设(您不知道)
-
结果$ c $确实有两个不同的值c>:
(W,L)
。 -
id
在感觉最新条目的id
最高。
- there are exactly two distinct values for
result
:(W, L)
. id
is sequential in the sense that the latest entry has the highestid
.
作业:
SELECT *
FROM tbl
WHERE id > (
SELECT max(id)
FROM tbl
GROUP BY result
ORDER BY max(id)
LIMIT 1
);
这将获取 W
的最新ID,并 L
,二者取其先。因此, LIMIT 1
获得相反结果的最后一个条目。 id
高的行形成最新的条纹。 Voilá。
This gets the latest id for W
and L
, the earlier of the two first. So a LIMIT 1
gets the last entry of the opposite outcome. Rows with an id
higher than that form the latest streak. Voilá.
这篇关于返回最新数据的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文