Execute query for each element in array-CodePudding

Let's say I have the following tables:

create table user (    
  id int     
);    
    
create table transaction (    
  user_id int,    
  date timestamp,    
  amount int    
);

I have an array of 100 users (result from another query). I'd like to get the 100 last transactions of each user from my input array.

I've tried a simple query wrapped in a for loop in a script, but I'm thinking there must be a way to do this with a where in in pure SQL.

CodePudding user response：

SELECT u.user, t.date, t.amount
  FROM unnest(user_array :: integer[]) AS u(user)
 CROSS JOIN LATERAL
     ( SELECT date, amount
         FROM transaction AS t
        WHERE t.user_id = u.user
        ORDER BY date DESC
        LIMIT 100
     ) AS t
 ORDER BY u.user, t.date DESC

CodePudding user response：

You can use a window function:

select ut.user_id, ut."date", ut.amount
from (
  select t.user_id. t."date", t.amount, 
         row_number() over (partition by t.user_id order by t."date" desc) as rn
  from "transaction" t
  where t.user_id = any(<your array here>)
) ut
where ut.rn <= 100
order by ut.user_id, ut."date" desc

But if that array is the result of another query, then there is no need for the array to begin with.

  where t.user_id in (... your other query here ...)