Multiply numbers with using Recursive function in SQL

Question

For the tables below, I need to write a query that will generate the desired output. How can i solve this question using with recursive function.

Note: I solved with EXP(SUM(LOG(Value))) AS ValueMultiplication but i need to solve with recursive.

Logic :

30.0 x 3.0 x 0.5 -> 45

5.0 x 7.0 ->35

1.0 x 2.0 x 3.0 x 5.0 ->30

2.0 x 0.1 x 3.0 ->0.6

0.1 ->0.1

Table Values :

WITH
EXPERIMENT(ExperimentId,Code) AS(
    SELECT
        *
    FROM(VALUES(1,'A10'),
               (2,'A20'),
               (3,'C40'),
               (4,'B00'),
               (5,'B99')
    )EXPERIMENT (ExperimentId,Code)),

MEASUREMENT(ExperimentId,Value) AS
(
    SELECT
        *
    FROM(VALUES(1,30.0),
               (1,3.0),
               (1,0.5),
               (2,5.0),
               (2,7.0),
               (3,0.1),
               (4,1.0),
               (4,2.0),
               (4,3.0),
               (4,5.0),
               (5,2.0),
               (5,0.1),
               (5,3.0)
        )MEASUREMENT(ExperimentId,Value)),

Mr. Linoff sir please help me (i'm sure you will look this post :D) — user13471674
– user13471674, Commented Jan 22, 2021 at 0:32

Nick · Accepted Answer · 2021-01-22 01:37:14Z

1

Here's one possible solution, using your table value constructors for the source data (omitted for clarity from the query). It first computes row numbers, ordering by Value ascending and descending, and then uses the ascending row numbers to order the input values to recursively multiply them (and count the number of them) and the descending row number to select the final product from that CTE:

ROWS AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY ExperimentID ORDER BY Value) AS rn,
           ROW_NUMBER() OVER (PARTITION BY ExperimentID ORDER BY Value DESC) AS rnd
    FROM MEASUREMENT
),
MULTIPLES AS (
    SELECT ExperimentId, CAST(Value AS FLOAT) AS Value, rn, rnd
    FROM ROWS
    WHERE rn = 1
    UNION ALL
    SELECT r.ExperimentId, r.Value * m.Value, r.rn, r.rnd
    FROM ROWS r
    JOIN MULTIPLES m ON r.ExperimentId = m.ExperimentId AND r.rn = m.rn + 1
)
SELECT e.Code AS ExperimentCode,
       m.rn AS ValueCount,
       m.Value AS ValueMultiplication
FROM EXPERIMENT e
JOIN MULTIPLES m ON m.ExperimentID = e.ExperimentID
WHERE m.rnd = 1
ORDER BY m.Value DESC

Output:

ExperimentCode  ValueCount  ValueMultiplication
A10             3           45
A20             2           35
B00             4           30
B99             3           0.6
C40             1           0.1

Demo on dbfiddle

edited Jan 22, 2021 at 1:37

answered Jan 22, 2021 at 1:23

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

brunoff Over a year ago

i liked your r.rn = m.rn + 1 trick. i was struggling without it. thanks for teaching this.

Nick Over a year ago

@brunoff no worries - I'm glad you found this useful.

Collectives™ on Stack Overflow

Multiply numbers with using Recursive function in SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related