Categorizing Data based on Partition-CodePudding

I have some data that looks like this:

fac_id     unit_id     created_at           rentable     r_id     row_num
1          1           2021-01-04 13:22:00  TRUE         1        1
1          1           2021-01-05 13:22:00  TRUE         2        2
1          1           2021-01-06 13:22:00  FALSE        1        3
1          1           2021-01-07 13:22:00  FALSE        2        4
1          1           2021-01-08 13:22:00  TRUE         1        5
1          1           2021-01-09 13:22:00  TRUE         2        6
1          1           2021-01-10 13:22:00  TRUE         3        7

What I want to do is partition the data by fac_id, unit_id, and rentable. Every time that the row switches from rentable = TRUE to rentable = FALSE, for that same fac_id and unit_id value, I want the classify_num value to increment by 1 so that the data looks like this.

Desired Output:

fac_id     unit_id     created_at           rentable     r_id     row_num   classify_num
1          1           2021-01-04 13:22:00  TRUE         1        1         1            
1          1           2021-01-05 13:22:00  TRUE         2        2         1
1          1           2021-01-06 13:22:00  FALSE        1        3         2
1          1           2021-01-07 13:22:00  FALSE        2        4         2
1          1           2021-01-08 13:22:00  TRUE         1        5         3
1          1           2021-01-09 13:22:00  TRUE         2        6         3
1          1           2021-01-10 13:22:00  TRUE         3        7         3

CodePudding user response：

You can use LAG() and SUM() window functions such as

WITH t2 AS
(
 SELECT *,
        LAG(rentable) OVER (PARTITION BY fac_id, unit_id ORDER BY created_at) AS lg
   FROM t -- your original table
)   
SELECT *,
       SUM(CASE WHEN rentable=lg THEN 0 ELSE 1 END) OVER (ORDER BY created_at) AS classify_num
  FROM t2
 ORDER BY created_at

Demo

CodePudding user response：

You can use Window Functions to reference a value expression from another rows field. However window functions cannot be nested. So you can use CTE. Here are sum and lag functions used.

WITH my_values AS (
  SELECT
      fac_id,
      unit_id,
      created_at,
      rentable,
      r_id,
      row_num,
      --returns 1 if there is a change 
      CASE WHEN (lag(rentable, 1, rentable) OVER (ORDER BY r_id)) = rentable 
        THEN 0
        ELSE 1
      END AS change
    FROM my_table
)
SELECT
    fac_id,
    unit_id,
    created_at,
    rentable,
    r_id,
    row_num,
    -- sums change values for each row till current row.
    sum(change) OVER (ORDER BY r_id) classify_num
  FROM my_values

db<>fiddle here