Let's say I have a table B:
| client_id | n_periods | qtty |
|---|---|---|
| 1 | 9 | 200 |
| 2 | 3 | 100 |
| 3 | 8 | 150 |
And then I have a (large) table foo:
| client_id | date | period | amount |
|---|---|---|---|
| 1 | 2022-01-01 | 1 | 3000 |
| 1 | 2022-01-01 | 2 | 2800 |
| 1 | 2022-01-01 | 24 | 2800 |
| 2 | 2022-01-05 | 1 | 3500 |
| 2 | 2022-01-05 | 2 | 3500 |
| 2 | 2022-01-05 | 24 | 3500 |
I want to randomly SELECT the top B.n_periods FROM FOO WHERE B.client_id = foo.client_id. I other words, I want to randomly select n-rows for each client_id in foo, where the n-rows is given in table B also for each client_id. I also want to perform a simple substraction foo.amount minus B.qtty.
I have tried:
SELECT
TOP B.n_periods
foo.client_id,
foo.date,
foo.period,
foo.amount - B.qtty
FROM B, foo
But I get the error:
[42000][923] ORA-00923: FROM keyword not found where expected
I am a bit lost, I have tried using CONNECT BY but maybe I don't understand well how to use it.
CodePudding user response：
Shuffle rows per partition, then choose the first n rows for particular client:
select s.*
from (
select f.*
, row_number() over (partition by f.client_id order by dbms_random.random()) as rn
from foo f
) s
join b on s.client_id = b.client_id and s.rn <= b.n_periods
(I didn't tested it. Providing data sample could increase quality of my answer.)