How to check if a column data is an arithematic progression in PostgreSQL

Question

Suppose i have a column C in a table T, which is as follow:

sr	c
1	34444444444440
2	34444444444442
3	34444444444444
4	34444444444446
5	34444444444448
6	34444444444450

How can i verify or check if the values in Column C are arithmetic progression?

This is no arithm. progression since the last value has a digit too much. — S-Man, Jan 13 '21 at 08:47
@Popeye yes, difference could be any but constant, Answer by S-Man looks promising but i need to evaluate it in my actual use case first — Abdul Wakeel, Jan 13 '21 at 14:36

score 2 · Accepted Answer · answered Jan 13 '21 at 12:45

An arithmetic progression means that the differences are all constants. Assuming that the values are not floating point, then you can directly compare them:

select (min(c - prev_c) = max(c - prev_c)) as is_arithmetic_progression
from (select t.*,
             lag(c) over (order by sr) as prev_c
      from t
     ) t

If these are floating point values, you probably want some sort of tolerance, such as:

select abs(min(c - prev_c), max(c - prev_c)) < 0.001 as is_arithmetic_progression

this one performs better than @S-Man 's query with me – Abdul Wakeel Jan 14 '21 at 05:53 — Abdul Wakeel, Jan 14 '21 at 05:53

S-Man · Answer 2 · 2021-01-13T09:01:19.067

step-by-step demo:db<>fiddle

SELECT
    COUNT(*) = 1 as is_arithmetic_progression             -- 4
FROM (
    SELECT
        difference
    FROM (
        SELECT
            *,
            lead(c) OVER (ORDER BY sr) - c as difference  -- 1
        FROM
            mytable
    ) s
    WHERE difference IS NOT NULL                          -- 2
    GROUP BY difference                                   -- 3
) s

Arithmetical progression: The difference between each element is constant.

lead() window function shifts the next value into the current row. Generating the difference to the current value shows the difference
lead() creates a NULL value in the last column, because it has no "next" value. So, this will be filtered
Grouping the difference values.
If you only have one difference value, this would return in only one single group. Only one difference value means: You have a constant difference between the elements. That is exactly what arithmetical progression means. So if the number of groups is exactly 1, you have arithmetical progression.

have you seen the last value - `344444444444410` which is more higher than other values. Your query will give result in false for given sample data — Popeye, Jan 13 '21 at 09:27
@Popeye Yes, as wrote in the comments above, the example is NO arithmetic progression because of this value. I guess, it's a mistake of the TO. The value must be 34444444444450 to be an arith. progression — S-Man, Jan 13 '21 at 09:40

score -1 · Answer 3 · answered Jan 13 '21 at 08:59

-1

You can use exists as follows:

Select case when count(*) > 0 then 'no progression' else 'progression' end as res_
  From your_table t
 Where exists
      (select 1 from your_table tt
        Where tt.str > t.str
          And tt.c < t.c)

answered Jan 13 '21 at 08:59

Popeye

35,427
4
10
31

https://en.wikipedia.org/wiki/Arithmetic_progression Arithmetical progression means that the difference between all elements is constant. You check for increasing values, but not if the values are increasing constantly – S-Man Jan 13 '21 at 09:04

How to check if a column data is an arithematic progression in PostgreSQL

3 Answers3