postgresql - 在postgres中查找系列的长度

Question

postgres 的棘手查询。想象一下，我有一组行，其中有一个名为(例如)成功的 bool 列。像这样:

id | success  9  | false  8  | false  7  | true  6  | true  5  | true  4  | false  3  | false  2  | true  1  | false  

And I need to calculate a length of the latest (not) successful series. E. g. in this case it would be "3" for successful and "2" for not successful. Or using window functions, then something like:

id | success | length  9  | false   | 2  8  | false   | 2  7  | true    | 3  6  | true    | 3  5  | true    | 3  4  | false   | 1  3  | true    | 2  2  | true    | 2  1  | false   | 1  

(note that I generally need a length of only the latest series, not all of those)

The closest answer I've found so far was this article:https://jaxenter.com/10-sql-tricks-that-you-didnt-think-were-possible-125934.html(See #5)

However, postgres doesn't support "IGNORE NULLS" option so the query doesn't work. Without "IGNORE NULLS" it simply returns me nulls in length column.

Here is the closest I was able to get:

WITH
  trx1(id, success, rn) AS (
    SELECT id, success, row_number() OVER (ORDER BY id desc) 
    FROM results
  ),
  trx2(id, success, rn, lo, hi) AS (
    SELECT trx1.*,
    CASE WHEN coalesce(lag(success) OVER (ORDER BY id DESC), FALSE) != success THEN rn END,
    CASE WHEN coalesce(lead(success) OVER (ORDER BY id DESC), FALSE) != success THEN rn END
    FROM trx1
  )
SELECT trx2.*, 1
- last_value (lo) IGNORE nulls OVER (ORDER BY id DESC ROWS BETWEEN
UNBOUNDED PRECEDING AND CURRENT ROW)
+ first_value(hi) OVER (ORDER BY id DESC ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING)
AS length FROM trx2;

你对这样的查询有什么想法吗？

Answer 1

可以使用窗口函数row_number()来指定系列:

select max(id) as max_id, success, count(*) as length
from (
    select *, row_number() over wa - row_number() over wp as grp
    from my_table
    window
        wp as (partition by success order by id desc),
        wa as (order by id desc)
    ) s
group by success, grp
order by 1 desc

 max_id | success | length 
--------+---------+--------
      9 | f       |      2
      7 | t       |      3
      4 | f       |      2
      2 | t       |      1
      1 | f       |      1
(5 rows)

DbFiddle.