I'm using the following SQL query twice with different values of
limit. In my case, 40 and 100, though it doesn't really matter. My question is - can I combine these two queries into one query? It would probably be faster.
sample is a PG array.
I don't know how much more information is needed, but if you need more details, please ask. I'm reluctant to add details about the schema unless necessary, since it will make the question much longer.
SELECT SUM(motif) FROM (SELECT ROW_NUMBER() OVER (PARTITION BY crossvalnum ORDER BY crossvalnum, pvalue DESC, margstat) AS r, (seqindex IS NOT NULL)::INTEGER AS motif, crossval.crossvalnum, data.margstat, data.pvalue FROM data INNER JOIN datasubgroup ON data.datasubgroup_id=datasubgroup.id INNER JOIN crossval ON datasubgroup.crossval_id=crossval.id WHERE data.seqindex =ANY(crossval.sample) OR data.seqindex IS NULL ) AS q WHERE q.r <= limit GROUP BY crossvalnum;
The value for 40 is
sum ----- 25 22 19 16 24 (5 rows)
The value for 100 is
sum ----- 32 28 24 23 31 (5 rows)
motif is defined an an indicator variable,
(seqindex IS NOT NULL)::INTEGER. The
SUM corresponds with the
GROUP BY crossvalnum as an aggregate query. So the sum happens across each crossvalnum group. So there is a window function inside an aggregate query. I want the output to be two columns.