mariadb - SQL, operation between paired rows

Question

I have a table t1

a   b
1   4
2   2
3   9
2   1
1   6
3   7

I need the output

a   result
1   4*6
2   2*1
3   9*7

I have been able to do it in a far from efficient way and would like a more satisfactory way. Thank you.

question from:https://stackoverflow.com/questions/66067168/sql-operation-between-paired-rows

Answer 1

If you don't want a floating point result, and you can have more than 2 b values for each a value, you can use a recursive CTE to generate the results:

WITH RECURSIVE t1r AS (
  SELECT *, 
         ROW_NUMBER() OVER (PARTITION BY a) AS rn
  FROM t1
),
maxrows AS (
  SELECT a, MAX(rn) AS maxrow
  FROM t1r
  GROUP BY a
),
mul AS (
  SELECT a, b AS result, rn, rn AS maxrow
  FROM t1r
  WHERE rn = 1
  UNION ALL
  SELECT mul.a, result * b, t1r.rn, t1r.rn
  FROM mul
  JOIN t1r ON t1r.a = mul.a AND t1r.rn = mul.rn + 1
)
SELECT mul.a, mul.result
FROM mul
JOIN maxrows mr ON mr.a = mul.a AND mr.maxrow = mul.rn
ORDER BY mul.a

Output:

a   result
1   24
2   2
3   63

Demo on dbfiddle

Note that this could be simplified if there was a column by which you could order the rows, that would remove the need to have the maxrows CTE.