Home » SQL & PL/SQL » SQL & PL/SQL » Data history (11.2.0.3)
Data history [message #685192] Thu, 04 November 2021 03:18 Go to next message
Amine
Messages: 371
Registered: March 2010
Senior Member

Hi Gurus,

drop table id_jobs;
create table id_jobs
(
	id 		int	,
	job		int	,
	dat_job		date
);
alter table id_jobs add constraint PK_id_hist primary key (id, job);

insert into id_jobs(id, job, dat_job) values (1, 10, '01/07/2021');
insert into id_jobs(id, job, dat_job) values (2, 20, '30/04/2020');
insert into id_jobs(id, job, dat_job) values (3, 30, '01/08/2020');
insert into id_jobs(id, job, dat_job) values (4, 40, '01/08/2020');
-- ==================================
drop table job_val;
create table job_val
(
	job		int		,
	val		int not null    ,
	dat_val		date
);
alter table job_val add constraint PK_job_val primary key (job, dat_val);

insert into job_val(job, val, dat_val) values (10, 100, '01/06/2020');
insert into job_val(job, val, dat_val) values (10, 200, '28/07/2021');

insert into job_val(job, val, dat_val) values (20, 100, '01/08/2021');

insert into job_val(job, val, dat_val) values (30, 100, '01/07/2020');
Here is the problem. We need to get the job value of each id according to dat_job.

For instance, id 1 get job 10 on 01/07/2021. At that time, the value of job 10 was 100.
On 28/07/2021 it has changed to 200. So before 01/07/2021 we have 100 and after that we have 200.

Same thing for id 2. He get the job on 30/04/2020. At that time, the were no value for job 20.
But now the value is 100.

for id 3. He get the job on 01/08/2020. At that time, the value was 100 and it has not changed.

for id 4. He get the job on 01/08/2020. And the job was not valued till now.

The question is how to get this information in one query ?

Here is my start (And I am sure, Gurus will optimize it) :

select 
id
, id_jobs.job
, dat_job
--, old_val.val old_val
, new_val.val new_val
from id_jobs
--, job_val old_val
, job_val new_val
where 1 = 1
-- and id_jobs.job = old_val.job(+)
and id_jobs.job = new_val.job(+)
and (new_val.dat_val in (select max(dat_val) from job_val where job = id_jobs.job)
or new_val.dat_val is null)
order by 1
;
So I want to uncomment the comments to get this result :

       ID       JOB DAT_JOB       OLD_VAL    NEW_VAL
--------- --------- ---------- ---------- ----------
        1        10 01/07/2021        100        200
        2        20 30/04/2020       null        100
        3        30 01/08/2020        100        100
        4        40 01/08/2020       null	null
Thanks in advance,

Amine
Re: Data history [message #685193 is a reply to message #685192] Thu, 04 November 2021 03:55 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Your code is buggy:
orclz> insert into id_jobs(id, job, dat_job) values (1, 10, '01/07/2021');

1 row created.

orclz> select * from id_jobs;

             ID             JOB DAT_JOB
--------------- --------------- -------------------
              1              10 0001-07-20:21:00:00

orclz> select sysdate from dual;

SYSDATE
-------------------
2021-11-04:08:54:28

orclz>
Re: Data history [message #685194 is a reply to message #685193] Thu, 04 November 2021 04:00 Go to previous messageGo to next message
Amine
Messages: 371
Registered: March 2010
Senior Member

drop table id_jobs;
create table id_jobs
(
	id 			int	,
	job			int	,
	dat_job		date
);
alter table id_jobs add constraint PK_id_hist primary key (id, job);

insert into id_jobs(id, job, dat_job) values (1, 10, to_date('01/07/2021', 'dd/mm/yyyy'));
insert into id_jobs(id, job, dat_job) values (2, 20, to_date('30/04/2020', 'dd/mm/yyyy'));
insert into id_jobs(id, job, dat_job) values (3, 30, to_date('01/08/2020', 'dd/mm/yyyy'));
insert into id_jobs(id, job, dat_job) values (4, 40, to_date('01/08/2020', 'dd/mm/yyyy'));
-- ==================================
drop table job_val;
create table job_val
(
	job			int					,
	val			int		not null	,
	dat_val		date
);
alter table job_val add constraint PK_job_val primary key (job, dat_val);

insert into job_val(job, val, dat_val) values (10, 100, to_date('01/06/2020', 'dd/mm/yyyy'));
insert into job_val(job, val, dat_val) values (10, 200, to_date('28/07/2021', 'dd/mm/yyyy'));

insert into job_val(job, val, dat_val) values (20, 100, to_date('01/08/2021', 'dd/mm/yyyy'));

insert into job_val(job, val, dat_val) values (30, 100, to_date('01/07/2020', 'dd/mm/yyyy'));
Re: Data history [message #685195 is a reply to message #685194] Thu, 04 November 2021 04:20 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
This is not solution that covers all possibilities by any means, and it is certainly not very efficient, but it might give you a start on one approach:
orclz> select id_jobs.*,
  2  (select val from job_val where job=id_jobs.job and dat_val < id_jobs.dat_job) old_val,
  3  (select val from job_val where job=id_jobs.job and dat_val > id_jobs.dat_job) new_val
  4  from id_jobs;

             ID             JOB DAT_JOB                     OLD_VAL         NEW_VAL
--------------- --------------- ------------------- --------------- ---------------
              1              10 2021-07-01:00:00:00             100             200
              2              20 2020-04-30:00:00:00                             100
              3              30 2020-08-01:00:00:00             100
              4              40 2020-08-01:00:00:00

orclz>
--update: a (slightly) more robust solution:
select id_jobs.*,
(select max(val) from job_val where job=id_jobs.job and dat_val < id_jobs.dat_job) old_val,
(select min(val) from job_val where job=id_jobs.job and dat_val > id_jobs.dat_job) new_val
from id_jobs
/

[Updated on: Thu, 04 November 2021 04:22]

Report message to a moderator

Re: Data history [message #685196 is a reply to message #685195] Thu, 04 November 2021 07:21 Go to previous messageGo to next message
Amine
Messages: 371
Registered: March 2010
Senior Member

Thanks John for the reply.

I was expecting a better solution and a beautiful query as I always have in this site.

The second solution is not right due to business rules. The value of a job doesn't increase necessary.
Re: Data history [message #685198 is a reply to message #685196] Thu, 04 November 2021 10:21 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
What an extraordinarily rude reply. Goodbye.
Re: Data history [message #685202 is a reply to message #685192] Thu, 04 November 2021 11:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If we add the following rows, what should be the result?
insert into id_jobs(id, job, dat_job) values (1, 20, to_date('15/07/2021', 'dd/mm/yyyy'));
insert into job_val(job, val, dat_val) values (20, 200, to_date('01/09/2021', 'dd/mm/yyyy'));
Or, in words, what is the semantics of OLD_VAL and NEW_VAL?
OK, for OLD_VAL it is clear: it is the value of the job when "id" joins it.
But for NEW_VAL, it is less clear.

Re: Data history [message #685210 is a reply to message #685202] Sat, 06 November 2021 12:38 Go to previous messageGo to next message
Amine
Messages: 371
Registered: March 2010
Senior Member

What's the problem John ?
What's the problem when being demanding with myself, looking for the best, the most readable and the most beautiful SQL query ?
I did thank your proposition isn't it ? So why is your answer disproportional ?
And I did thank all the community of Gurus that helped me, through this post.

When I started coming here, I've understood that mastering SQL in the key point.
And This video demonstrates it again.

I hope you will come to your senses John.

Back to business.
Responding to Michel Cadot :
About he semantics of OLD_VAL and NAW_VAL :

OLD_VAL is the last value of the job when id joins it.
and dat_val in (select max(dat_val) from job_val where job = id_jobs.job
and dat_val <= dat_job)
NEW_VAL is the actual value of the job (doesn't depend on when id get the job).
and dat_val in (select max(dat_val) from job_val where job = id_jobs.job)
So according to the rows added, the result should be like this :

id 1 joined job 20 on 15/07/2021. At that time, this job was not valued (actually it started to be
valued on 01/08/2021). So the OLD_VAL is null.

Now the NEW_VAL is the latest value of the job. So it is 200.

Thanks Gurus ! Even John Watson Wink

[Updated on: Sat, 06 November 2021 12:44]

Report message to a moderator

Re: Data history [message #685211 is a reply to message #685210] Sat, 06 November 2021 14:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

"doesn't depend on when id get the job" and I assume it also does not depend when id left it: this is the absolute current value of the job.

I also added the following row:
insert into job_val(job, val, dat_val) values (10, 250, to_date('10/05/2020', 'dd/mm/yyyy'));

It comes:
SQL> select * from id_jobs order by id, job;
        ID        JOB DAT_JOB
---------- ---------- -----------
         1         10 01-JUL-2021
         1         20 15-JUL-2021
         2         20 30-APR-2020
         3         30 01-AUG-2020
         4         40 01-AUG-2020

5 rows selected.

SQL> select * from job_val order by job, dat_val;
       JOB        VAL DAT_VAL
---------- ---------- -----------
        10        250 10-MAY-2020
        10        100 01-JUN-2020
        10        200 28-JUL-2021
        20        100 01-AUG-2021
        20        200 01-SEP-2021
        30        100 01-JUL-2020

5 rows selected.

SQL> with
  2    jobval as (
  3      select job, val, dat_val,
  4             row_number() over (partition by job order by dat_val desc) rn
  5      from job_val
  6    ),
  7    idjobs as (
  8      select i.id, i.job, v.val,
  9             row_number() over (partition by i.id, i.job order by v.dat_val desc) rn
 10      from id_jobs i left outer join jobval v on v.job = i.job and v.dat_val <= i.dat_job
 11    )
 12  select i.id, i.job, i.val old_val, c.val new_val
 13  from idjobs i left outer join jobval c on c.job = i.job and c.rn = 1
 14  where i.rn = 1
 15  order by i.id, i.job
 16  /
        ID        JOB    OLD_VAL    NEW_VAL
---------- ---------- ---------- ----------
         1         10        100        200
         1         20                   200
         2         20                   200
         3         30        100        100
         4         40

[Updated on: Sun, 07 November 2021 15:11]

Report message to a moderator

Re: Data history [message #685212 is a reply to message #685211] Sun, 07 November 2021 09:17 Go to previous messageGo to next message
Amine
Messages: 371
Registered: March 2010
Senior Member

Perfect Michel !
Thank you so much !

Amine
Re: Data history [message #685213 is a reply to message #685212] Sun, 07 November 2021 11:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just for fun I customized it as:
SQL> col old_val format a21
SQL> col new_val format a21
SQL> with
  2    jobval as (
  3      select job, val, dat_val,
  4             row_number() over (partition by job order by dat_val desc) rn
  5      from job_val
  6    ),
  7    idjobs as (
  8      select i.id, i.job, i.dat_job, v.val, v.dat_val,
  9             row_number() over (partition by i.id, i.job order by v.dat_val desc) rn
 10      from id_jobs i left outer join jobval v on v.job = i.job and v.dat_val <= i.dat_job
 11    )
 12  select i.id, i.job, i.dat_job,
 13         i.val||decode(i.val, null,'', ' since '||to_char(i.dat_val)) old_val,
 14         c.val||decode(c.val, null,'', ' since '||to_char(c.dat_val)) new_val
 15  from idjobs i left outer join jobval c on c.job = i.job and c.rn = 1
 16  where i.rn = 1
 17  order by i.id, i.job
 18  /
        ID        JOB DAT_JOB     OLD_VAL               NEW_VAL
---------- ---------- ----------- --------------------- ---------------------
         1         10 01-JUL-2021 100 since 01-JUN-2020 200 since 28-JUL-2021
         1         20 15-JUL-2021                       200 since 01-SEP-2021
         2         20 30-APR-2020                       200 since 01-SEP-2021
         3         30 01-AUG-2020 100 since 01-JUL-2020 100 since 01-JUL-2020
         4         40 01-AUG-2020
Smile

[Updated on: Sun, 07 November 2021 15:11]

Report message to a moderator

Re: Data history [message #685217 is a reply to message #685213] Mon, 08 November 2021 03:51 Go to previous messageGo to next message
Amine
Messages: 371
Registered: March 2010
Senior Member

Thank you Michel for giving this tremendous tricks !
Re: Data history [message #685219 is a reply to message #685217] Mon, 08 November 2021 05:59 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
select  i.*,
        (
         select  max(val) keep(dense_rank last order by v.dat_val)
           from  job_val v
           where v.job = i.job
             and v.dat_val <= i.dat_job
        ) old_val,
        (
         select  max(val)
           from  job_val v
           where v.job = i.job
        ) new_val
  from  id_jobs i
  order by i.id
/

           ID           JOB DAT_JOB          OLD_VAL       NEW_VAL
------------- ------------- ---------- ------------- -------------
            1            10 01/07/2021           100           200
            2            20 30/04/2020                         100
            3            30 01/08/2020           100           100
            4            40 01/08/2020

SQL>
SY.
Re: Data history [message #685220 is a reply to message #685196] Mon, 08 November 2021 07:13 Go to previous messageGo to next message
Amine
Messages: 371
Registered: March 2010
Senior Member

Amine wrote on Thu, 04 November 2021 13:21
..... The value of a job doesn't increase necessary.
Thanks SY for the reply but as I mentioned it earlier, The value of a job doesn't increase necessary.
Re: Data history [message #685224 is a reply to message #685220] Mon, 08 November 2021 11:13 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Not sure I follow. max(val) keep(dense_rank last order by v.dat_val)

SY.
Re: Data history [message #685226 is a reply to message #685224] Tue, 09 November 2021 03:11 Go to previous messageGo to next message
Amine
Messages: 371
Registered: March 2010
Senior Member

Thanks SY.

I was talking about the new_val. new_val is not necessary the max(val) of the job. It's actually the latest value of the job.
Re: Data history [message #685227 is a reply to message #685226] Tue, 09 November 2021 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I think you misunderstand the expression, "max" here is not "max of all val" but, in case there are several "val" for "dat_val"="last order by v.dat_val" then take "max(val)". If there can't be several "val" for this last date then max(val)=val, of course.

Re: Data history [message #685228 is a reply to message #685227] Tue, 09 November 2021 06:53 Go to previous messageGo to next message
Amine
Messages: 371
Registered: March 2010
Senior Member

if we add this row :
insert into job_val values (10,50, to_date('01/08/2021', 'dd/mm/yyyy'));
SY solution gives us this result :

SQL> select i.*
  2  ,
  3  (
  4   select max(val) keep(dense_rank last order by v.dat_val)
  5   from job_val v
  6   where v.job = i.job
  7   and v.dat_val <= i.dat_job
  8  ) old_val,
  9  (
 10   select max(val)
 11   from job_val v
 12   where v.job = i.job
 13  ) new_val
 14  from id_jobs i
 15  order by i.id;

       ID       JOB DAT_JOB      OLD_VAL   NEW_VAL
--------- --------- ---------- --------- ---------
        1        10 01/07/2021       100       200
        2        20 30/04/2020                 100
        3        30 01/08/2020       100       100
        4        40 01/08/2020

SQL> 
And Michel Cadot solution gives this result :

SQL> select *
  2  from
  3  (
  4   with
  5   jobval as (
  6    select job, val, dat_val,
  7    row_number() over (partition by job order by dat_val desc) rn
  8    from job_val
  9   ),
 10   idjobs as (
 11    select i.id, i.job, v.val,
 12    row_number() over (partition by i.id, i.job order by v.dat_val desc) rn
 13    from id_jobs i left outer join jobval v on v.job = i.job and v.dat_val <= i.dat_job
 14   )
 15   select i.id, i.job, i.val old_val, c.val new_val
 16   from idjobs i left outer join jobval c on c.job = i.job and c.rn = 1
 17   where i.rn = 1
 18   order by i.id, i.job
 19  )
 20  /

       ID       JOB   OLD_VAL   NEW_VAL
--------- --------- --------- ---------
        1        10       100        50
        2        20                 100
        3        30       100       100
        4        40

SQL> 
Michel Cadot solution's is the right one.
Re: Data history [message #685229 is a reply to message #685228] Tue, 09 November 2021 07:04 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Ah, cut & paste error - second subquery is missing keep clause:

select  i.*,
        (
         select  max(val) keep(dense_rank last order by v.dat_val)
           from  job_val v
           where v.job = i.job
             and v.dat_val <= i.dat_job
        ) old_val,
        (
         select  max(val) keep(dense_rank last order by v.dat_val)
           from  job_val v
           where v.job = i.job
        ) new_val
  from  id_jobs i
  order by i.id
/

        ID        JOB DAT_JOB      OLD_VAL    NEW_VAL
---------- ---------- --------- ---------- ----------
         1         10 01-JUL-21        100         50
         2         20 30-APR-20                   100
         3         30 01-AUG-20        100        100
         4         40 01-AUG-20

SQL>
SY.
Re: Data history [message #685230 is a reply to message #685228] Tue, 09 November 2021 14:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can combine Solomon's and my queries to:
SQL> with
  2    jobval as (
  3      select job,
  4             max(val) keep (dense_rank last order by dat_val) val
  5      from job_val
  6      group by job
  7    ),
  8    idjobs as (
  9      select i.id, i.job,
 10             max(v.val) keep (dense_rank last order by v.dat_val) val
 11      from id_jobs i left outer join job_val v on v.job = i.job and v.dat_val <= i.dat_job
 12      group by i.id, i.job
 13    )
 14  select i.id, i.job, i.val old_val, c.val new_val
 15  from idjobs i left outer join jobval c on c.job = i.job
 16  order by i.id, i.job
 17  /
        ID        JOB    OLD_VAL    NEW_VAL
---------- ---------- ---------- ----------
         1         10        100        200
         1         20                   200
         2         20                   200
         3         30        100        100
         4         40

5 rows selected.

The fastest one will depend on your needs and data.
Solomon's subqueries in SELECT clause will be executed for each row, mine only once.
If you want or have a small number of ids or jobs and have good indexes, Solomon's query will be faster.
If you want a report for overall data, mine will be faster.

Re: Data history [message #685231 is a reply to message #685230] Tue, 09 November 2021 18:14 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
create or replace
  type val_obj
    is object(
              old_val number,
              new_val number
             )
/
Now:

set autotrace on
with t as (
           select  i.*,
                   (
                    select  val_obj(
                                    max(case when v.dat_val <= i.dat_job then val end) keep(dense_rank last order by case when v.dat_val <= i.dat_job then v.dat_val end nulls first),
                                    max(val) keep(dense_rank last order by v.dat_val)
                                   )
                      from  job_val v
                      where v.job = i.job
                   ) vals
             from  id_jobs i
          )
select  id,
        job,
        dat_job,
        treat(vals as val_obj).old_val old_val,
        treat(vals as val_obj).new_val new_val
  from  t
  order by id
/

           ID           JOB DAT_JOB      OLD_VAL       NEW_VAL
------------- ------------- --------- ---------- -------------
            1            10 01-JUL-21        100            50
            2            20 30-APR-20                      100
            3            30 01-AUG-20        100           100
            4            40 01-AUG-20


Execution Plan
----------------------------------------------------------
Plan hash value: 314537274

---------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |            |     4 |    56 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                      |            |     1 |    14 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| JOB_VAL    |     1 |    14 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | PK_JOB_VAL |     1 |       |     1   (0)| 00:00:01 |
|   4 |  TABLE ACCESS BY INDEX ROWID         | ID_JOBS    |     4 |    56 |     2   (0)| 00:00:01 |
|   5 |   INDEX FULL SCAN                    | PK_ID_HIST |     4 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("V"."JOB"=:B1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
        929  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

SQL>
SY.
Previous Topic: Dates integrity
Next Topic: Oracle regex_substr parsing a clob
Goto Forum:
  


Current Time: Fri Mar 29 08:18:03 CDT 2024