Geek Answers Handbook

Get even / odd / all numbers between two numbers

I want to display all the numbers (even / odd / mixed) between two numbers (1-9; 2-10, 11-20) in one (or two) columns.
Example raw data:

| rang | | r1 | r2 | -------- -----|----- | 1-9 | | 1 | 9 | | 2-10 | | 2 | 10 | | 11-20 | or | 11 | 20 | CREATE TABLE initialtableone(rang TEXT); INSERT INTO initialtableone(rang) VALUES ('1-9'), ('2-10'), ('11-20'); CREATE TABLE initialtabletwo(r1 NUMERIC, r2 NUMERIC); INSERT INTO initialtabletwo(r1, r2) VALUES ('1', '9'), ('2', '10'), ('11', '20');

Result:

 | output | ---------------------------------- | 1,3,5,7,9 | | 2,4,6,8,10 | | 11,12,13,14,15,16,17,18,19,20 |
+4
source share
2 answers

Something like that:

 create table ranges (range varchar); insert into ranges values ('1-9'), ('2-10'), ('11-20'); with bounds as ( select row_number() over (order by range) as rn, range, (regexp_split_to_array(range,'-'))[1]::int as start_value, (regexp_split_to_array(range,'-'))[2]::int as end_value from ranges ) select rn, range, string_agg(i::text, ',' order by i.ordinality) from bounds b cross join lateral generate_series(b.start_value, b.end_value) with ordinality i group by rn, range

It is output:

 rn | range | string_agg ---+-------+------------------------------ 3 | 2-10 | 2,3,4,5,6,7,8,9,10 1 | 1-9 | 1,2,3,4,5,6,7,8,9 2 | 11-20 | 11,12,13,14,15,16,17,18,19,20
+4
source

Based on your first example, simplified, but with PK:

 CREATE TABLE tbl1 ( tbl1_id serial PRIMARY KEY -- optional , rang text -- can be NULL ? );

Use split_part() to extract the lower and upper split_part() . ( regexp_split_to_array() would be uselessly expensive and error prone). And generate_series() to generate numbers.

Use LATERAL join and aggregate the set immediately to simplify aggregation. the ARRAY constructor is the fastest in this case:

 SELECT t.tbl1_id, a.output -- array; added id is optional FROM ( SELECT tbl1_id , split_part(rang, '-', 1)::int AS a , split_part(rang, '-', 2)::int AS z FROM tbl1 ) t , LATERAL ( SELECT ARRAY( -- preserves rows with NULL SELECT g FROM generate_series(a, z, CASE WHEN (za)%2 = 0 THEN 2 ELSE 1 END) g ) AS output ) a;

AIUI, you want each number in the range only if the upper and lower bounds are a mixture of even and odd numbers. Else, only return every second number, which results in even / odd numbers for these cases. This expression implements interval calculation:

 CASE WHEN (za)%2 = 0 THEN 2 ELSE 1 END

Result at will:

 output ----------------------------- 1,3,5,7,9 2,4,6,8,10 11,12,13,14,15,16,17,18,19,20

In this case, you do not need WITH ORDINALITY because the order of the elements is guaranteed.

The aggregate array_agg() function makes the request a little shorter (but slower) - or use string_agg() to create a string directly, depending on the desired output format:

 SELECT a.output -- string FROM ( SELECT split_part(rang, '-', 1)::int AS a , split_part(rang, '-', 2)::int AS z FROM tbl1 ) t , LATERAL ( SELECT string_agg(g::text, ',') AS output FROM generate_series(a, z, CASE WHEN (za)%2 = 0 THEN 2 ELSE 1 END) g ) a;

Note the subtle difference when using the aggregate function or the ARRAY constructor in the LATERAL subquery: usually rows with rang IS NULL are excluded from the result because the LATERAL subquery LATERAL no row .
If you summarize the result right away, "no line" is converted to a single line with a null value, so the original line is saved. I added a demo to the violin.

SQL Fiddle

For this you do not need a CTE, which would be more expensive.

In addition: type conversion to integer automatically removes the gap at the beginning / training, so this line is also suitable for rank : ' 1 - 3' .

+2
source

More articles:


All Articles