SQL (ORACLE): inner-query with 2 or more related/paired container-query "pointers"

Question

FOR ALL THOSE "OMG! HE IS USING IMAGES :O :O :O" PEOPLE (ORIGINAL POST UNDER THIS):

I've a table:

date1 | id1 | value
date1 | id2 | value
date1 | id3 | value
date1 | id4 | value
-------------------
date2 | id1 | value
date2 | id2 | value
date2 | id5 | value
date2 | id7 | value
-------------------
date3 | id4 | value
date3 | id5 | value
date3 | id10 | value
date3 | id11 | value
-------------------
...
-------------------
dateN | id1 | value
dateN | id2 | value
dateN | id3 | value
dateN | id4 | value

Having an input like:

date: X - ids: 2, 3
date: Y - ids: 4
date: Z - ids: 2, 4, 5
...

Need a single query (non PL, non conditionals, variables or cursors...) that brings for every date and every single ID of it's own, a row for the date and the ID with the VALUE specific for it. If date do not has a value for its specific ID, has to find the most closest date that has it.

ie:

date: 06/03/2016 - ids: 5, 6

result:

06/03/2016|5|value
04/03/2016|6|value

Second result row has different date because hasn't found a record for date 06/03/2016 and ID=6. That's why brings the closest date which matches. Oh!, and by closest I mean, BEFORE. Not after the date. So, same specified date day or closest before day if not matched on date.

I could create a query for every line of the input, and finally make a UNION from all of'em. But this is not an elegant way to solve it. And throwing the query on every loop turn on PHP is not an option too.

ORIGINAL THREAD:

Good morning, first of all, excuse my english please, as I'm from Spain.

Said that, I'm going to try to be concise with this question at the end, but first of all, let me expose the environtment:

I've a tipical date/data-type/value table.

I've divided visually by red lines de date-change blocks. And blue arrows mark the "data-type" ID (from another table) column and the other blue one, the date asociated for those values. So you have date blocks with different type-ids which for every one of'em you got a change value (CAMBIO): the green arrow.

Now, ¿what I'm able to do? A query that gives me the CAMBIO values for some specific date:

NOTE: GRUPO is another column that exists on the table, and I always need the values from this grupo-type, 1. It's imperative.

Next step is, could happen that for an specified DATE do not exists values on table records. So I've to find the closest one.

I could do a query like this:

You could see on first image that there are no records for March 6th or March 5th, so through this query, I can go to the most closest value existing, in this case, same March 4th.

The PROBLEM with those queries is, that I need to specify the DIVISA_C column to specific values, let's say 5 and 10. Then I colud go through this:

Which works nice for the existing DIVISA_C ids on the result, but if I need an ID which is not included in the "date-block" (ie: 2016-03-04), let's say the 6, then I get no result for this DIVISA_C id, and I NEED to have a result for it, concretly, the most closest date for it as I've showed before.

If I do:

nothing comes for this 6 ID. Same result as last query. In this case, I know how to solve the thing too:

You see, I've added a "pointer" on the WHERE from the inner query to the self DIVISA_C id, that basically points to the DIVISA_C from the ourtsider/container query. This causes that for the specified DATE on the inner query, for every single ID in the container-query "IN list", brings the most possible associated row as expected. So if inner query could resolve specified date for the specified DIVISA_C id, it brings the record. Else, brings the most closest date for the DIVISA_C id value. Which is exaclty what I needed.

NOTE: I've added some extra-filter in the inner query to be coherent with the outside/container query. Not a problem or a change to take in account at all.

Now, explained that, here comes the REAL QUESTION:

- Now I've a list of dates, and for every one of those dates, a list of DIVISA_C ids. And I need to make a single query, with no PL-SQL (so no FORs, no IFs, no CURSORS, etc...), just a SELECT combination, that brings me the result as expected, wich is basically this very last result I've showed to you, but for a lot of dates with their associated DIVISA_C ids list.

To make and example, the last query I've showed could be explained like:

date: 06/03/2016 - divisa_c ids: 5, 6, 10

and this brings me 3 rows with aproximated dates for every ID in this case, as March 6th do not have record for any of the DIVISA_C ids.

Now I've a full input list that have to be solved with just one single query, ie:

date: 06/03/2016 - divisa_c ids: 5, 6, 10
date: 05/02/2016 - divisa_c ids: 5
date: 03/02/2016 - divisa_c ids: 5, 6
date: 01/01/2016 - divisa_c ids: 6, 10
date: 31/12/2015 - divisa_c ids: 4, 6, 10, 12
date: 24/10/2015 - divisa_c ids: 3, 4, 5, 11
...

Of course, I can shoot a single query on every PHP loop turn for as many as rows the input list have.

Or I can create every single query with the input rows on the list and make a UNION of all of'em, to create a masive long string query wich will give me precisely the results I need, but this is NOT an option. I need to solve it with a more much elegant way, and here I'm stuck... :(

I've though with something like this:

You see, some kind of "linked" packets on the outter WHERE, that causes the inner query to resolve all lines on the DIVISA_C IN lists for every single associated date and in the inner query, the DATE is a pointer too... but as you can imagine this doesn't work, as the inner query is linked in the where through the FECHA = (subquery...), and doesn't make sense the packets I've wrote after this...

I've thought to create an INNER JOIN on the outter query to the very same table, so table against itself to have 2 pointers somehow and combine them for the situation... but I don't know how to do it properly...

Could you bring me light on this please?

Thanks to all, and sorry for the long post! But I think it's much more confortable to explain it through images.

Greetings,

Mark.

http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557 — , Mar 08 '16 at 12:14
@a_horse_with_no_name come on man. In this particular case images are just to show. Not useful for anything else. Question can be thrown directly without showing any example at all. Is the last piece of bold text on the post. Those're here to exemplificate visually what I'm asking. — Lightworker, Mar 08 '16 at 12:20
@Lightworker not all of us are able to see images (for example, they're blocked by a firewall), so by using them where you could provide the same information in formatted text you prevent some people from being able to help you. — Boneist, Mar 08 '16 at 12:40

score 2 · Accepted Answer · answered Mar 08 '16 at 14:21

Use row_number() to find values from closest days. In with clause define input parameters, in my test data I used ('2016-03-04', (5)), ('2016-03-06', (5)), ('2016-03-07', (5, 6, 10)). Id is not needed, I added it for clarity:

with t as ( 
  select 1 id, date '2016-03-04' fecha, 5 divisa from dual union all 
  select 2 id, date '2016-03-06' fecha, 5 divisa from dual union all 
  select 3 id, date '2016-03-07' fecha, 5 divisa from dual union all 
  select 3 id, date '2016-03-07' fecha, 6 divisa from dual union all 
  select 3 id, date '2016-03-07' fecha,10 divisa from dual )
select * from (
  select cd.*, t.fecha input_fecha, t.divisa input_divisa,
         row_number() over (partition by t.fecha, t.divisa order by t.fecha-cd.fecha) rn
    from cotizaciones_div cd 
    join t on cd.divisa_c = t.divisa and cd.grupo = 1 and cd.fecha<=t.fecha)
  where rn=1 order by input_fecha, input_divisa

To define input parameters you can also use type sys.odcinumberlist, if it is more comfortable for you (it can shorten syntax when divisa lists are long), like here:

  select 1 id, date '2016-03-04' fecha, column_value as divisa 
    from table(sys.odcinumberlist(5)) union all 
  select 2 id, date '2016-03-06' fecha, column_value as divisa 
    from table(sys.odcinumberlist(5)) union all 
  select 3 id, date '2016-03-07' fecha, column_value as divisa 
    from table(sys.odcinumberlist(5, 6, 10))

Test data and output:

create table cotizaciones_div (codigo number(8), divisa_o number(3), divisa_c number(3), 
  fecha date, cambio number(12, 4), grupo number(3));

insert into cotizaciones_div values ( 1000,  4, 11, date '2016-01-01', 0.5123, 8);
insert into cotizaciones_div values ( 2273, 15,  6, date '1998-12-31', 0,      1);
insert into cotizaciones_div values (63289,  4,  5, date '2016-03-04', 1.0998, 1);
insert into cotizaciones_div values (63297,  4, 10, date '2016-03-04', 7.4622, 1);
insert into cotizaciones_div values (63290,  4, 11, date '2016-03-04', 0.7738, 1);
insert into cotizaciones_div values (63309,  4,  5, date '2016-03-07', 1.1016, 1);
insert into cotizaciones_div values (63317,  4, 10, date '2016-03-07', 7.4619, 1);
insert into cotizaciones_div values (63310,  4, 11, date '2016-03-07', 0.7724, 1);

   CODIGO DIVISA_O DIVISA_C FECHA               CAMBIO GRUPO INPUT_FECHA INPUT_DIVISA
--------- -------- -------- ----------- -------------- ----- ----------- ------------
    63289        4        5 2016-03-04          1,0998     1 2016-03-04             5
    63289        4        5 2016-03-04          1,0998     1 2016-03-06             5
    63309        4        5 2016-03-07          1,1016     1 2016-03-07             5
     2273       15        6 1998-12-31          0,0000     1 2016-03-07             6
    63317        4       10 2016-03-07          7,4619     1 2016-03-07            10

Man... you're awesome! You really have a great Oracle level as you really have shown here. Would you please expend a bit of your time to explain a bit how and why it works? Maybe editing the post and adding some extra info/tips? I would love to really understand which is the magic beyond it... :) Maybe the row_number() part, how you order it and what is doing exactly. And how you do the INNER JOIN here too. Thanks again!! — Lightworker, Mar 08 '16 at 16:03
For each tuple (input_date, divisa) I am checking difference of dates (input_date and fecha). Row_number() "organizes" these differences with `partition by` clause and "sorts" these rows with `order by` clause, so dates closest to input_date have least values. We are interested only in rows with RN = 1. Link to [rownumber()](https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm) documentation, also please look at other analytical functions and examples. — Ponder Stibbons, Mar 08 '16 at 16:27

SQL (ORACLE): inner-query with 2 or more related/paired container-query "pointers"

1 Answers1