OraFAQ Forum: SQL & PL/SQL » Query help

Home » SQL & PL/SQL » SQL & PL/SQL » Query help (Oracle)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query help [message #657421]

Tue, 08 November 2016 04:50

julien1630
Messages: 5
Registered: November 2016

Junior Member

Hello,

Using the table below, I want to know the number of days since the price is unchanged for product A.

PRODUCT --- DATE --- PRICE
A --- 11/8/2016 --- 12
A --- 11/7/2016 --- 12
A --- 11/6/2016 --- 12
A --- 11/5/2016 --- 14

The function should return 3 for this example.

Constraints:
- The table is 10 millions of rows.
- The function could be called for thousands of products.
- The simpler the better! That's why I want to avoid recursive algo or loops

Thanks

Report message to a moderator

Re: Query help [message #657423 is a reply to message #657421]

Tue, 08 November 2016 05:05

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Can you post a working test case, the full oracle version and what happens if there's more than one price change. Where is the expected output to go? A column to the side? A flat function return call? Something else?

Report message to a moderator

Re: Query help [message #657424 is a reply to message #657423]

Tue, 08 November 2016 05:14

julien1630
Messages: 5
Registered: November 2016

Junior Member

Oracle version : 11.2.0.4

Expected output is a simple function return with the number of days since the price is unchanged. getNbStalledPrice(ProductA) to return 3 for example

I don't care about price changes in the past.

Case 1 :
Today price is 10, yesterday price was different => the method will return 0 (the price has changed this night)

Case 2:
Today price is 10, yesterday price is 10, Day-2 price is 11 => the method will return 1 (the price has not changed for 1 night)

Case 3:
The price is 10 for a week, it was different before => the method will return 6 (the price has not changed for 6 nights)

Our aim is to detect products for which today's price has not changed for at least one night.

Hope this will help.

[Updated on: Tue, 08 November 2016 05:15]

Report message to a moderator

Re: Query help [message #657425 is a reply to message #657424]

Tue, 08 November 2016 05:34

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

So I assume your expected output to be this where "cnt" is the value you seek:

P DT               VAL        CNT
- --------- ---------- ----------
a 03-NOV-16         11          0
a 04-NOV-16         11          1
a 05-NOV-16         10          0
a 06-NOV-16         10          1
a 07-NOV-16          9          0
a 08-NOV-16          9          1
b 05-NOV-16         10          0
b 06-NOV-16         10          1
b 07-NOV-16         10          2
b 08-NOV-16         10          3
c 06-NOV-16         10          0
c 07-NOV-16          9          0
c 08-NOV-16          9          1

If so, try count in its analytic form. That should get you the logic you're after.

Here's a test case, please give one in future Smile

Smile

with mydata as (
select 'a' prod, sysdate-5 dt, 11 val from dual
union all
select 'a', sysdate-4, 11 from dual
union all
select 'a', sysdate-3, 10 from dual
union all
select 'a', sysdate-2, 10 from dual
union all
select 'a', sysdate-1, 9 from dual
union all
select 'a', sysdate, 9 from dual
union all
select 'b', sysdate-3, 10 from dual
union all
select 'b', sysdate-2, 10 from dual
union all
select 'b', sysdate-1, 10 from dual
union all
select 'b', sysdate, 10 from dual
union all
select 'c', sysdate-2, 10 from dual
union all
select 'c', sysdate-1, 9 from dual
union all
select 'c', sysdate, 9 from dual
)
select v.*
from mydata v

Report message to a moderator

Re: Query help [message #657426 is a reply to message #657421]

Tue, 08 November 2016 05:48

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator

Re: Query help [message #657428 is a reply to message #657426]

Tue, 08 November 2016 07:29

julien1630
Messages: 5
Registered: November 2016

Junior Member

I implemented a solution based on analytic functions + loop

Thanks,
Julien

Report message to a moderator

Re: Query help [message #657429 is a reply to message #657428]

Tue, 08 November 2016 07:31

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

julien1630 wrote on Tue, 08 November 2016 05:29

I implemented a solution based on analytic functions + loop

Thanks,
Julien

so post it here to help others in the future.

Report message to a moderator

Re: Query help [message #657430 is a reply to message #657429]

Tue, 08 November 2016 08:03

julien1630
Messages: 5
Registered: November 2016

Junior Member

The solution look like this:

I loop on a tab containing T and T-1 prices on one row, I compare if the values are the same and I add 1 to my counter... If the values are different, I exit.

for myTab in (
select
product,
date,
price,
LEAD(price, 1, 0) OVER (ORDER BY date desc) AS price_prev_day,
from myTable
where product = myProduct
order by date desc
) loop
if(myTab.price = myTab.price_prev_day) then
l_return_nb_days := l_return_nb_days+1;
else
l_exit := 1;
end if;

exit when l_exit = 1;
end loop;

return l_return_nb_days;

Report message to a moderator

Re: Query help [message #657432 is a reply to message #657430]

Tue, 08 November 2016 08:15

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Please read and follow How to use [code] tags and make your code easier to read?

I take it you didn't try count like Roachcoach suggested? You wouldn't need the loop then.

Here's an example with his data:

SQL> with mydata as (
  2  select 'a' prod, sysdate-5 dt, 11 val from dual
  3  union all
  4  select 'a', sysdate-4, 11 from dual
  5  union all
  6  select 'a', sysdate-3, 10 from dual
  7  union all
  8  select 'a', sysdate-2, 10 from dual
  9  union all
 10  select 'a', sysdate-1, 9 from dual
 11  union all
 12  select 'a', sysdate, 9 from dual
 13  union all
 14  select 'b', sysdate-3, 10 from dual
 15  union all
 16  select 'b', sysdate-2, 10 from dual
 17  union all
 18  select 'b', sysdate-1, 10 from dual
 19  union all
 20  select 'b', sysdate, 10 from dual
 21  union all
 22  select 'c', sysdate-2, 10 from dual
 23  union all
 24  select 'c', sysdate-1, 9 from dual
 25  union all
 26  select 'c', sysdate, 9 from dual
 27  )
 28  select prod, dt, val, num_days
 29  from (
 30  select prod,
 31         dt,
 32         val,
 33         count(*) over (partition  by prod, val) as num_days,
 34         row_number() over (partition by prod order by dt desc) as rn
 35  from mydata v)
 36  where rn = 1
 37  order by prod;
 
PROD DT                 VAL   NUM_DAYS
---- ----------- ---------- ----------
a    11/08/2016           9          2
b    11/08/2016          10          4
c    11/08/2016           9          2
 
SQL>

I added row_number to restrict the result to one row per product.

Report message to a moderator

Re: Query help [message #657437 is a reply to message #657432]

Tue, 08 November 2016 08:40

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Data magic. Your solution assumes latest date price is unique while it isn't necessary the case:

with mydata as (
select 'a' prod, sysdate-5 dt, 9 val from dual
union all
select 'a', sysdate-4, 9 from dual
union all
select 'a', sysdate-3, 9 from dual
union all
select 'a', sysdate-2, 11 from dual
union all
select 'a', sysdate-1, 9 from dual
union all
select 'a', sysdate, 9 from dual
union all
select 'b', sysdate-3, 10 from dual
union all
select 'b', sysdate-2, 10 from dual
union all
select 'b', sysdate-1, 10 from dual
union all
select 'b', sysdate, 10 from dual
union all
select 'c', sysdate-2, 10 from dual
union all
select 'c', sysdate-1, 9 from dual
union all
select 'c', sysdate, 9 from dual
)
select prod, dt, val, num_days
from (
select prod,
       dt,
       val,
       count(*) over (partition  by prod, val) as num_days,
       row_number() over (partition by prod order by dt desc) as rn
from mydata v)
where rn = 1
order by prod
/

P DT               VAL   NUM_DAYS
- --------- ---------- ----------
a 08-NOV-16          9          5 <--- Wrong
b 08-NOV-16         10          4
c 08-NOV-16          9          2

SQL>

SY.

Report message to a moderator

Re: Query help [message #657438 is a reply to message #657437]

Tue, 08 November 2016 08:48

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Grr, yup, you're right. Any suggestions?

Report message to a moderator

Re: Query help [message #657440 is a reply to message #657438]

Tue, 08 November 2016 09:32

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Well, OP never stated version, so I'll assume 12C and wait for OP to respond Laughing

Laughing

:

with mydata as (
select 'a' prod, sysdate-5 dt, 9 val from dual
union all
select 'a', sysdate-4, 9 from dual
union all
select 'a', sysdate-3, 9 from dual
union all
select 'a', sysdate-2, 11 from dual
union all
select 'a', sysdate-1, 9 from dual
union all
select 'a', sysdate, 9 from dual
union all
select 'b', sysdate-3, 10 from dual
union all
select 'b', sysdate-2, 10 from dual
union all
select 'b', sysdate-1, 10 from dual
union all
select 'b', sysdate, 10 from dual
union all
select 'c', sysdate-2, 10 from dual
union all
select 'c', sysdate-1, 9 from dual
union all
select 'c', sysdate, 9 from dual
)
select  prod,
        dt,
        val,
        num_days
  from  mydata
  match_recognize(
                  partition by prod
                  order by dt desc
                  measures
                    dt as dt,
                    val as val,
                    final first(dt) - final last(dt) + 1 as num_days
                  pattern(same_val+)
                  define
                    same_val as same_val.val = first(same_val.val) and match_number() = 1
                 )
/

P DT               VAL   NUM_DAYS
- --------- ---------- ----------
a 07-NOV-16          9          2
b 05-NOV-16         10          4
c 07-NOV-16          9          2

SQL>

SY.

Report message to a moderator

Re: Query help [message #657441 is a reply to message #657440]

Tue, 08 November 2016 09:40

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

He said 11.2.0.4 so I did it the hobo way Wink

Wink

with 
mydata as (select 'a' prod, sysdate-5 dt, 9 val from dual
           union all
           select 'a', sysdate-4, 9 from dual
           union all
           select 'a', sysdate-3, 9 from dual
           union all
           select 'a', sysdate-2, 11 from dual
           union all
           select 'a', sysdate-1, 9 from dual
           union all
           select 'a', sysdate, 9 from dual
           union all
           select 'b', sysdate-3, 10 from dual
           union all
           select 'b', sysdate-2, 10 from dual
           union all
           select 'b', sysdate-1, 10 from dual
           union all
           select 'b', sysdate, 10 from dual
           union all
           select 'c', sysdate-3, 10 from dual
           union all
           select 'c', sysdate-2, 8 from dual
           union all
           select 'c', sysdate-1, 9 from dual
           union all
           select 'c', sysdate, 8 from dual
)
,mydata2 as (select 
                   v.* 
                   ,case when lag(val) over (partition by prod order by dt) = val then 1 else 0 end flg
                   ,count(*) over (partition by prod,val order by dt desc) cnt
                   ,row_number() over (partition by prod order by dt desc) rn
             from mydata v)
,enddata as (select v2.*
                    ,first_value(cnt) over (partition by prod order by flg, dt desc) days_since_change
             from mydata2 v2
            )
select v3.prod
       ,v3.dt
       ,v3.val current_val
       ,v3.days_since_change
from enddata v3 
where v3.rn=1



P DT        CURRENT_VAL DAYS_SINCE_CHANGE
- --------- ----------- -----------------
a 08-NOV-16           9                 2
b 08-NOV-16          10                 4
c 08-NOV-16           8                 1

It can probably be done in a neater fashion, but to be fair it's been a while since I dusted off these particular cogs.

Ed: I forgot to subtract 1 from the days since change, but hey Smile

Smile

[Updated on: Tue, 08 November 2016 09:50]

Report message to a moderator

Re: Query help [message #657442 is a reply to message #657441]

Tue, 08 November 2016 09:56

julien1630
Messages: 5
Registered: November 2016

Junior Member

Thanks guys

Report message to a moderator

Re: Query help [message #657443 is a reply to message #657441]

Tue, 08 November 2016 10:36

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Hierarchical solution would be simpler:

with mydata as (
select 'a' prod, sysdate-5 dt, 9 val from dual
union all
select 'a', sysdate-4, 9 from dual
union all
select 'a', sysdate-3, 9 from dual
union all
select 'a', sysdate-2, 11 from dual
union all
select 'a', sysdate-1, 9 from dual
union all
select 'a', sysdate, 9 from dual
union all
select 'b', sysdate-3, 10 from dual
union all
select 'b', sysdate-2, 10 from dual
union all
select 'b', sysdate-1, 10 from dual
union all
select 'b', sysdate, 10 from dual
union all
select 'c', sysdate-3, 10 from dual
union all
select 'c', sysdate-2, 8 from dual
union all
select 'c', sysdate-1, 9 from dual
union all
select 'c', sysdate, 8 from dual
),
t as (
      select  m.*,
              row_number() over (partition by prod order by dt desc) rn
        from  mydata m
     )
select  prod,
        dt,
        val,
        connect_by_root dt - dt + 1 num_days
  from  t
  where connect_by_isleaf = 1
  start with rn = 1
  connect by prod = prior prod
         and val = prior val
         and rn = prior rn + 1
/

P DT                         VAL   NUM_DAYS
- ------------------- ---------- ----------
a 11/07/2016 11:34:28          9          2
b 11/05/2016 11:34:28         10          4
c 11/08/2016 11:34:28          8          1

SQL>

SY.

Report message to a moderator

Previous Topic:	external table with single column
Next Topic:	Getting months in the current Fiscal Year in Oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 12:14:54 CDT 2024