Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Get a value from an array depending on the number of occurrence

user13117585Jan 21 2021 — edited Jan 21 2021

Hello again,
As suggested in one of my previous messages, I'd better create a new question.
So, I was wondering if anyone can help me with this? I tried so many things found online...
I'm trying to randomly select a value depending on the values in the table. Anyone could help here?
Imagine I have a table of element like this one. A key may appear multiple times. In reality this table contains millions of keys and I will only keep the first 1000 or so.

CREATE TABLE elements
(
 key varchar2(10),
 value number
);

insert into elements(key, value) VALUES('a', 1);
insert into elements(key, value) VALUES('a', 2);
insert into elements(key, value) VALUES('a', 3);

insert into elements(key, value) VALUES('b', 1);
insert into elements(key, value) VALUES('b', 1);

insert into elements(key, value) VALUES('c', 1);

insert into elements(key, value) VALUES('d', 1);
insert into elements(key, value) VALUES('d', 1);
insert into elements(key, value) VALUES('d', 1);

insert into elements(key, value) VALUES('e', 1);
COMMIT;

DECLARE
 TYPE string_t IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
 elements_l string_t;
  
 rnd VARCHAR2(10) ;
BEGIN

  SELECT key
   BULK COLLECT INTO elements_l   
   FROM elements;
  
 rnd := -- get random value depending on the number of occurences that appear in my table.
  
 DBMS_OUTPUT.put_line(rnd);
  
END;
/

What I would to do is to get the keys and the number of times it appears in my set (in this elements table). Then, depending on the number of times it is there, I want to randomly give it more weight.
Somehow, it's true that if I load everything, it will work. But, I wanted to avoid that... And I'm getting stuck here...
the value that should be returned should take into account the number of values in the table. For example a appears 3 times. So if I execute my function 100 times I should get a about 30 times in the result. I know how to use dbms_random to get a value between two numbers. But I have no idea how to "weight" that random with the number of occurrences.
Any help? Any suggestions?
Any advise would be more than welcome :)
Regards,
This post has been answered by Frank Kulash on Jan 22 2021
Jump to Answer

Comments

L. Fernigrini

Please post a simpler example, including some table creation and sample data, and the select sentence in text format (not as a picture)

Frank Kulash

Hi, @edisson-gabriel-lopez
In addition to what Fernigrini said, explain (in general terms, with specific examples) how you get the desired results from the given sample data. Since the results depend on what today is, say what that is in the example. (In my time zone, it's currently day 278 of year 2012; it may be a day off in your time zone, but I still don't see why 121209 meets the condition, but 121211 does not. For that matter, if you're only interested in a single day (e.g. "yesterday"), I don't see why 121207, 121208 and 121209 all meet the condition.)
Always post your complete Oracle version (e.g. 18.4.0.0.0).
Information about dates belongs in DATE columns, not in a NUMBER column such as periodo. This problem, and lots of others, would be simpler and more efficient if you used a DATE column. The data can be collected and entered in a coded form (like periodo) if you really want that, but when it is stored in the database it should be converted to a DATE.
When using TO_DATE (or any function) make sure the arguments have the correct data types. The first argument to TO_DATE should be a string, not a DATE or a NUMBER. So
to_date(to_date(1900000 + ...
has two errors:
The first argument to the outer TO_DATE call is a DATE. (TO_DATE, as the name suggests, always returns a DATE. TO_DATE (TO_DATE ... is always wrong.)
The first argument to the inner TO_DATE call is a NUMBER.

Frank Kulash

Hi, [Edisson Gabriel López](/ords/forums/user/Edisson Gabriel López)
Never write, let alone post, unformatted code. Which is easier to read and understand: what you posted:
 where to_date(to_date(1900000 + COLGATE_INVENTRY.periodo, 'yyyyddd'),'DD/MM/YYYY')-1 = to_date(to_date(1900000 + IVTRDJ, 'yyyyddd'),'DD/MM/YYYY')-1
or this:

	 where 	to_date ( to_date ( 1900000 + COLGATE_INVENTRY.periodo
	 		 	 , 'yyyyddd'
				 )
			, 'DD/MM/YYYY'
			) - 1
	      = to_date ( to_date ( 1900000 + IVTRDJ
	   		 	  , 'yyyyddd'
				  )
			, 'DD/MM/YYYY'
			) - 1

Which do you think will be easier to you to debug and maintain?
What is the purpose of the condition above? It looks like you want to see if the day before periodo is the same as day before ivtrdj, which is just a complicated way of checking if periodo is the same day as ivtrdj. A clearer way to code that is:

where COLGATE_INVENTRY.periodo =  IVTRDJ
Gaz in Oz

flashback query, flashback database, flashback table, go back in time...

Edisson Gabriel López

[Frank Kulash](/ords/forums/user/Frank Kulash) [L. Fernigrini](/ords/forums/user/L. Fernigrini) [Gaz in Oz](/ords/forums/user/Gaz in Oz)
Hi guys, thanks for your comments and sorry for my lack of explanation(I couldn't send you test data because I was using a WITH closure which was a very large query to retrieve the information and also does an INSERT of the information it retrieves from the WITH).

The solution was to make another WITH that brings the same information and in the query I made the SELECT with the following operation: 

NVL((SELECT COLG.INV_FINAL FROM COLGATE_INITIAL COLG WHERE TO_DATE(TO_DATE(1900000 + COLGATEINV.PERIODO, 'YYYYDDD'),'DD/MM/YYYY')-2 = TO_DATE(TO_DATE(1900000 + COLG.PERIODO_YESTERDAY, 'YYYYDDD'),'DD/MM/YYYY')-1 AND COLGATEINV.DISTRIBUIDORA = COLG.DISTRIBUIDORA_ONE AND COLGATEINV.CODIGO_BODEGA = COLG.CODIGO_BODEGA),0) INV_INICIAL,
NVL((SELECT COLG.VALOR_INV_FINAL FROM COLGATE_INITIAL COLG WHERE TO_DATE(TO_DATE(1900000 + COLGATEINV.PERIODO, 'YYYYDDD'),'DD/MM/YYYY')-2 = TO_DATE(TO_DATE(1900000 + COLG.PERIODO_YESTERDAY, 'YYYYDDD'),'DD/MM/YYYY')-1 AND COLGATEINV.DISTRIBUIDORA = COLG.DISTRIBUIDORA_ONE AND COLGATEINV.CODIGO_BODEGA = COLG.CODIGO_BODEGA),0) VALOR_INV_INICIAL,

 As we can see the IVTRDJ field (121207 to 121208) retrieves the values (IVFUSN6 and IVAG6) from yesterday and places them in the fields (IVMATH01 and IVMATH02):
image.pngBut now I am asked that these values must continue to be filled in (i.e. what is in the row "IVTRDJ = 121208" the values of IVMATH01 and IVMATH02 must be the same "IVTRDJ 121208 to 121210") until the values (IVFUSN6 and IVAG6) change.

IVTRDJ  IVCITM              IVMATH01  IVMATH02 IVFUSN6  IVAG6
121207	CO01606A         	0	0	7200000	-5327280
121208	CO01606A         	7200000	-5327280	0	0
121210	CO01606A         	7200000	-5327280	0	0
121211	CO01606A         	7200000	-5327280	0	0

I hope you can help me.

Warren Tolentino

maybe you are looking for lag analytic function. this examples might help.

SQL> with t as
  2   (select 1000 product_id, to_date('2007/09/25','yyyy/mm/dd') order_date, 20 quantity from dual union all
  3    select 2000 product_id, to_date('2007/09/26','yyyy/mm/dd') order_date, 30 quantity from dual union all
  4    select 1000 product_id, to_date('2007/09/27','yyyy/mm/dd') order_date, 10 quantity from dual union all
  5    select 2000 product_id, to_date('2007/09/28','yyyy/mm/dd') order_date, 25 quantity from dual union all
  6    select 2000 product_id, to_date('2007/09/29','yyyy/mm/dd') order_date, 35 quantity from dual union all
  7    select 1000 product_id, to_date('2007/09/30','yyyy/mm/dd') order_date, 05 quantity from dual)
  8  select * from t;


PRODUCT_ID ORDER_DAT   QUANTITY
---------- --------- ----------
      1000 25-SEP-07         20
      2000 26-SEP-07         30
      1000 27-SEP-07         10
      2000 28-SEP-07         25
      2000 29-SEP-07         35
      1000 30-SEP-07          5


6 rows selected.


SQL>
SQL> with t as
  2   (select 1000 product_id, to_date('2007/09/25','yyyy/mm/dd') order_date, 20 quantity from dual union all
  3    select 2000 product_id, to_date('2007/09/26','yyyy/mm/dd') order_date, 30 quantity from dual union all
  4    select 1000 product_id, to_date('2007/09/27','yyyy/mm/dd') order_date, 10 quantity from dual union all
  5    select 2000 product_id, to_date('2007/09/28','yyyy/mm/dd') order_date, 25 quantity from dual union all
  6    select 2000 product_id, to_date('2007/09/29','yyyy/mm/dd') order_date, 35 quantity from dual union all
  7    select 1000 product_id, to_date('2007/09/30','yyyy/mm/dd') order_date, 05 quantity from dual)
  8  select vt.product_id, quantity, new_order_date
  9    from (select product_id, order_date, quantity,
 10                 lag(order_date,1) over (partition by product_id order by product_id, order_date) as new_order_date
 11            from t) vt
 12    where vt.new_order_date is not null;


PRODUCT_ID   QUANTITY NEW_ORDER
---------- ---------- ---------
      1000         10 25-SEP-07
      1000          5 27-SEP-07
      2000         25 26-SEP-07
      2000         35 28-SEP-07


SQL>
Frank Kulash

Hi, @edisson-gabriel-lopez
It's still not clear what you want. Again, post a little sample data and the exact results you want from that data
I couldn't send you test data because I was using a WITH closure which was a very large query to retrieve the information and also does an INSERT of the information it retrieves from the WITH
Are you saying that you have a complicated query that produces results that look like what you posted in your original message, and that you want to INSERT some new rows into some table based on that complicated query's result set? If so, post two sets of CREATE TABLE and INSERT statements: one that creates a table that looks like the result set of your complicated query, and the other to create and populate the table into which you will INSERT new rows as it exists before the INSERT in question begins. The desired results will be the contents of that second table after the INSERT is finished.
Once again, post your complete Oracle version number.

Edisson Gabriel López

Hi, @frank-kulash
Researching, I found the LAG() function that retrieves the previous record, but I have no idea how I can tell it with a WHERE sort what information I want it to fetch(example: it has to be the same ID (1600000545) of the previous record and the next one):
Result I want to achieve:
image.pngimage.pngNOTE: Oracle version Database 19c

I remain attentive, thank you.

Frank Kulash

Hi, @edisson-gabriel-lopez
Thanks for posting the desired results and Oracle version. Don't forget to post CREATE TABLE and INSERT statements for the sample data that produces those results; then we can begin.

1 - 9

Post Details

Added on Jan 21 2021
#12c
13 comments
2,583 views
1 mention