Hi subin, can you clarify please?
On Thu, Aug 11, 2022 at 3:42 PM subin <subtitle.i...@gmail.com> wrote: > Please let me know if that is okay. > > On Thu, Aug 11, 2022 at 11:11 AM Theofilos Theofovos <fithis2...@gmail.com> > 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
