Home » SQL & PL/SQL » SQL & PL/SQL » Query help (Oracle)
Query help [message #657421] Tue, 08 November 2016 04:50 Go to next message
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

Re: Query help [message #657423 is a reply to message #657421] Tue, 08 November 2016 05:05 Go to previous messageGo to next message
Roachcoach
Messages: 1571
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?
Re: Query help [message #657424 is a reply to message #657423] Tue, 08 November 2016 05:14 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Roachcoach
Messages: 1571
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

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
Re: Query help [message #657426 is a reply to message #657421] Tue, 08 November 2016 05:48 Go to previous messageGo to next message
Michel Cadot
Messages: 65835
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.

Re: Query help [message #657428 is a reply to message #657426] Tue, 08 November 2016 07:29 Go to previous messageGo to next message
julien1630
Messages: 5
Registered: November 2016
Junior Member
I implemented a solution based on analytic functions + loop

Thanks,
Julien
Re: Query help [message #657429 is a reply to message #657428] Tue, 08 November 2016 07:31 Go to previous messageGo to next message
BlackSwan
Messages: 26183
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.
Re: Query help [message #657430 is a reply to message #657429] Tue, 08 November 2016 08:03 Go to previous messageGo to next message
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;
Re: Query help [message #657432 is a reply to message #657430] Tue, 08 November 2016 08:15 Go to previous messageGo to next message
cookiemonster
Messages: 13265
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.
Re: Query help [message #657437 is a reply to message #657432] Tue, 08 November 2016 08:40 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
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.
Re: Query help [message #657438 is a reply to message #657437] Tue, 08 November 2016 08:48 Go to previous messageGo to next message
cookiemonster
Messages: 13265
Registered: September 2008
Location: Rainy Manchester
Senior Member
Grr, yup, you're right. Any suggestions?
Re: Query help [message #657440 is a reply to message #657438] Tue, 08 November 2016 09:32 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
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 :

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.
Re: Query help [message #657441 is a reply to message #657440] Tue, 08 November 2016 09:40 Go to previous messageGo to next message
Roachcoach
Messages: 1571
Registered: May 2010
Location: UK
Senior Member
He said 11.2.0.4 so I did it the hobo way 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

[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 Go to previous messageGo to next message
julien1630
Messages: 5
Registered: November 2016
Junior Member
Thanks guys
Re: Query help [message #657443 is a reply to message #657441] Tue, 08 November 2016 10:36 Go to previous message
Solomon Yakobson
Messages: 2806
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.
Previous Topic: external table with single column
Next Topic: Getting months in the current Fiscal Year in Oracle
Goto Forum:
  


Current Time: Tue Oct 16 05:36:34 CDT 2018