I have a task where I need to create a 1000 sample row limit query for each attribute selected.
So for example if I have:
SELECT * FROM data_table WHERE atribut in ('atrib1', 'atrbi2','atrib3');
I would need to turn this in to:
(SELECT * FROM data_table WHERE atribut = 'atrib1' ORDER BY random() LIMIT 1000) UNION (SELECT * FROM data_table WHERE atribut = 'atrib2' ORDER BY random() LIMIT 1000) UNION (SELECT * FROM data_table WHERE atribut = 'atrib3' ORDER BY random() LIMIT 1000);
Problem is that I have a lot of these attributes and that I might be adding more attributes later. Is there a better way of writing this? Like maybe with a FOR loop that iterates through an array of strings to get everything at once? I tried the following but I don’t really know how to do it properly:
DO $do$ DECLARE atribut_array text[] := array['atrib1', 'atrib2', 'atrib3']; atr text; BEGIN FOREACH atr IN ARRAY atribut_array LOOP SELECT * FROM data_table WHERE atribut LIKE atr ORDER BY random() LIMIT 1000 END LOOP; END $do$
Advertisement
Answer
You can use row_number()
:
SELECT t.* FROM (SELECT t.*, ROW_NUMBER() OVER (PARTITION BY attribute ORDER BY random()) as seqnum FROM data_table t WHERE attribute in ('atrib1', 'atrbi2', 'atrib3') ) t WHERE seqnum <= 1000;
You could also use a lateral join:
SELECT t1000.* FROM (VALUES ('atrib1'), ('atrbi2'), ('atrib3') ) v(attribute) CROSS JOIN LATERAL (SELECT t.* FROM data_table t WHERE t.attribute = v.attribute ORDER BY random() LIMIT 1000 ) t1000