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:
x
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