1

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.

MT0
  • 143,790
  • 11
  • 59
  • 117
Nicolás Rivera
  • 70
  • 8

1 Answers1

1

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.)

Tomáš Záluský
  • 10,735
  • 2
  • 36
  • 64
  • I provided some data, but maybe it's not properly formatted and thus you don't see it, can you? Also I understand the logic of your query but it doesn't work, I think the problem is in `shuffled s`. I think you meant just `s`. – Nicolás Rivera Dec 19 '22 at 10:18
  • 1
    @NicolásRivera Corrected, thanks. "To provide data" means to create reproducible example on sites like dbfiddle.uk or similar. It helps to concentrate on answer instead of text formatting. – Tomáš Záluský Dec 19 '22 at 10:29
  • I know the data I provided was rather sacarse. Nevertheless, your answer worked. I just wanted to comment that instead of `JOIN` I used implicit inner join: ```SELECT * FROM s, b WHERE s.client_id = b.client_id...``` – Nicolás Rivera Dec 19 '22 at 11:00
  • @NicolásRivera yes, it will certainly work as well with old-style joins. I am just from the camp of those who find ansi joins more readable (good question on this topic [here](https://stackoverflow.com/q/18891148/653539)). Glad to hear it helped you. – Tomáš Záluský Dec 19 '22 at 12:08