CODEWITHSUNDEEP
sqlpostgresql
nachocab
nachocab

Reputation: 14364

How to detect if a column is increasing continuously in postgres?

I have a table sorted by 'category' and 'number'. I'm trying to detect the rows that don't have an increasing number. I've tried this approach:

WITH test AS (
    SELECT * FROM (
        VALUES
            ('A', 1),
            ('B', 2),
            ('C', 4),
            ('D', 3),
            ('F', 5),
            ('F', 6),
            ('G', 7),
            ('H', 9),
            ('H', 8)
    ) AS t(category, number)
),
test_with_previous as(
select t.*, 
  lag(number) over (order by category, number) as prev_number
  from test t
)
select *
from test_with_previous
where prev_number > number;

Which gives:

category number prev_number
D 3 4

But the first unordered row should be "C". How can I fix it?

Upvotes: 1

Views: 178

Answers (2)

MtwStark
MtwStark

Reputation: 4048

You can add a ROW_NUMBER() to data to get sequence number and then JOIN with next record and filter all with increment not equal to 1, or you can simply adjust the filter to get whatever you want

;WITH 
test AS (
    SELECT * FROM (
        VALUES
            ('A', 1),
            ('B', 2),
            ('C', 4),
            ('D', 3),
            ('F', 5),
            ('F', 6),
            ('G', 7),
            ('H', 9),
            ('H', 8)
    ) AS t(category, number)
),
test_idx as (
    select *, ROW_NUMBER() over (order by category, number) id
    from test
)
select t1.id, t2.id, t1.category c1, t2.category c2, t1.number n1, t2.number n2, t2.number - t1.number diff
from test_idx t1
left join test_idx t2 on t1.id = t2.id-1
where t1.number <> t2.number -1;

Outputs

id  id  c1  c2  n1  n2  diff
2   3   B   C   2   4   2
3   4   C   D   4   3   -1
4   5   D   F   3   5   2

Upvotes: 1

Jim Jones
Jim Jones

Reputation: 19613

Use lead() instead of lag():

WITH test (category, number) AS (
  VALUES
   ('A', 1),
   ('B', 2),
   ('C', 4),
   ('D', 3),
   ('F', 5),
   ('F', 6),
   ('G', 7),
   ('H', 9),
   ('H', 8)), 
 test_with_next AS(
SELECT t.*, lead(number) OVER (ORDER BY category, number) AS next_number
FROM test t)
SELECT *
FROM test_with_next
WHERE next_number < number;

 category | number | next_number 
----------+--------+-------------
 C        |      4 |           3

Upvotes: 1

Related Questions