在Postgres中求数列的长度 [英] Finding the length of a series in postgres

问题描述

Postgres的棘手查询。想象一下，我有一组行，其中包含一个名为(例如)Success的布尔列。如下所示：

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

我需要计算最新的(不成功的)系列的长度。例如，在这种情况下，它将是"3"表示成功，而"2"表示不成功。或使用窗口函数，然后类似于：

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  

(请注意，我通常只需要最新系列的长度，而不是所有系列)

到目前为止，我找到的最接近的答案是这篇文章： https://jaxenter.com/10-sql-tricks-that-you-didnt-think-were-possible-125934.html (见#5)

但是，Postgres不支持"Ignore Null"选项，因此查询无法工作。如果没有"忽略空值"，它只会在"长度"列中返回空值。

这是我能得到的最接近的：

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;

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

推荐答案

可以使用窗口函数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.

这篇关于在Postgres中求数列的长度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！