如何在PostgreSQL中创建WINDOW，直到再次出现相同的值？ [英] How create a WINDOW in PostgreSQL until the same value appears again?

问题描述

我想在PostgreSQL中创建一个 WINDOW 并从一个数字中获得 row_number（）直到它再次出现。例如，假设我要创建一个从数字79开始直到再次出现79的窗口并重置计数，它必须是这样的：

I want to create a WINDOW in PostgreSQL and get the row_number() from a number until it appears again. As example, supposing I want to create a window from number 79 until 79 appears again and reset the counting, it must be like this:

number        must be        row_number number
  50                             ?        50
  79                             1        79
  85                             2        85 
  74                             3        74 
  14                             4        14
  79                             1        79
  46                             2        46
  85                             3        85   
  79                             1        79
  45                             2        45  

我该怎么做？

推荐答案

请考虑以下内容：

-- temporary test table
CREATE TEMP TABLE tbl (id serial, nr int);
INSERT INTO tbl(nr) VALUES
 (50),(79),(85),(74),(14)
,(79),(46),(85),(79),(45);

SELECT id, nr
       ,CASE WHEN grp > 0 THEN
          row_number() OVER (PARTITION BY grp ORDER BY id)::text
        ELSE '?' END AS rn      
FROM  (
    SELECT id, nr
          ,sum(CASE WHEN nr = 79 THEN 1 ELSE 0 END) OVER (ORDER BY id) AS grp
    FROM   tbl) x
-- WHERE grp > 0  -- to exclude numbers before the first 79

之前的数字。

这篇关于如何在PostgreSQL中创建WINDOW，直到再次出现相同的值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！