Search Postgresql Archives

Re: Strategy for preparing a query containg dynamic case / when

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi subin,

can you clarify please?

On Thu, Aug 11, 2022 at 3:42 PM subin <subtitle.indo@xxxxxxxxx> wrote:
Please let me know if that is okay.

On Thu, Aug 11, 2022 at 11:11 AM Theofilos Theofovos <fithis2001@xxxxxxxxx> wrote:
Hello,

just for the sake of the example  lets have this kind of table

CREATE TABLE experimentals (
  indy integer not null primary KEY,
  XXX1 integer NOT NULL,
  XXX2 integer NOT NULL,
  json_data jsonb
);

It emulates a case where an item has integer labels XXX1, and XXX2
and characteristics described as json Z1, Z2 (can be any number of them, e.g. Z3, Z4 ...)
each having a value in [0,1).

A synthetic population of the table can take the form

insert into experimentals  
select indy, max(XXX1), max(XXX2), json_object_agg(zval, tval) as json_data
from (
    select
     (RANDOM() * 3)::INT as XXX1,
     (RANDOM() * 5)::INT XXX2,
     unnest(ARRAY['Z1', 'Z2']) as zval,
     unnest(ARRAY[RANDOM(), RANDOM()]) as tval,
     unnest(ARRAY[seq, seq]) as indy
   FROM GENERATE_SERIES(1, 1000) seq
) exploded_jsons
group by indy;


Now, for each pair of labels we define a partitioning of the characteristics, two possible appear here

-- XXX1 = 1, XXX2 = 2 partition (Z1, Z2) => (0..1 , 0..0.5) -> F1 (0..1, 0.5..1) -> F2

-- XXX1 = 3, XXX2 = 1 partition (Z1, Z2) => (0..0.3 , 0..0.5) -> G1 (0..0.6, 0.5..1) -> G2, ELSE  G3


In concrete SQL they take the form:


-- Partitioning 1

select  XXX1, XXX2, indy, json_data,
case
  when
     (json_data->>'Z2')::float >= 0 and (json_data->>'Z2')::float < 0.5
  then
    'F1'
  else
    'F2'
end as some_label
from experimentals
where XXX1 = 1 and XXX2 = 2 ;

-- Partitioning 2
select  XXX1, XXX2, indy, json_data,
case
   when
     (json_data->>'Z1')::float >= 0 and (json_data->>'Z2')::float < 0.3 and
     (json_data->>'Z2')::float >= 0 and (json_data->>'Z2')::float < 0.5
   then
     'G1'
   when
     (json_data->>'Z1')::float >= 0 and (json_data->>'Z2')::float < 0.6 and
     (json_data->>'Z2')::float >= 0.5 and (json_data->>'Z2')::float < 1
   then
     'G2'
   else
     'G3'
  end as some_label
from experimentals
where XXX1 = 3 and XXX2 = 1 ;


the partitioning is an immutable function of a column and is given to me at runtime in some format

Now, to the real question for preparing it.

I would expect something (pseudo sql) for partitioning1

PREPARE fooplan (int, int, immutable (jsonb -> varchar) ) AS
    select XXX1, XXX2, indy, json_data, $x3(json_data) as some_label
from experimentals
where XXX1 = $1 and XXX2 = $2 ; EXECUTE fooplan(1, 2, 't', json_data ->
case
  when
     (json_data->>'Z2')::float >= 0 and (json_data->>'Z2')::float < 0.5
  then
    'F1'
  else
    'F2'
end
);

Is this something possible, is there any recommended strategy for these cases? 

PS The real queries are containing joins and other filters, I just include a simplified case.

Vasilis



--
Dr. Vasileios Anagnostopoulos (MSc,PhD)
Researcher/Developer


--
Dr. Vasileios Anagnostopoulos (MSc,PhD)
Researcher/Developer

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux