Home » SQL & PL/SQL » SQL & PL/SQL » Query help (Oracle)
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
|
|
|
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?
|
|
|
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
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 #657432 is a reply to message #657430] |
Tue, 08 November 2016 08:15 |
cookiemonster
Messages: 13925 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 |
Solomon Yakobson
Messages: 3275 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 #657440 is a reply to message #657438] |
Tue, 08 November 2016 09:32 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Well, OP never stated version, so I'll assume 12C and wait for OP to respond :
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 |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
He said 11.2.0.4 so I did it the hobo way
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
[Updated on: Tue, 08 November 2016 09:50] 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: 3275 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.
|
|
|
Goto Forum:
Current Time: Fri Jun 28 10:23:22 CDT 2024
|