How to do arithmetic operation on last n rows from different tables in postgresql without join-CodePudding

I have a two tables (for examle table a with columns c, d and table b with column e). I'm trying to do math operation like this: (c d) * e for every row in last n rows from this tables (ORDER BY id desc limit n) keeping the order of the lines.

I already tried this query:

SELECT f1.r1 * f2.r2 
FROM (
  select c   d as r1 
  from a 
  order by id desc 
  limit n
) as f1,
  (select e as r2 
   from b 
   order by id desc 
   limit n) as f2

but it returns n^2 columns (i just need n).

P.S: i can't use join beacuse id of rows in this tables are not correlate

CodePudding user response：

You can use join not on id but on row_number():

with u as 
(select c, d, row_number() over(order by id desc) as rownum_a from a),
v as (select e, row_number() over(order by id desc) as rownum_b from b)
(select c, d, e, (c   d)*e as "(c d)*e" from u inner join v on rownum_a = 
rownum_b limit n)

Fiddle