Hi Team,
Data Setup:
create table test_item as
with t1 as
(
select 1 category, 'abc' Item_name,100 item_volume,12.3 item_price,to_date('04-may-2017 12:22:33 AM','dd-mon-yyyy hh:mi:ss AM') time from dual
union
select 1,'abc' ,-100 ,12.3 ,to_date('04-may-2017 12:22:34 AM','dd-mon-yyyy hh:mi:ss AM') from dual
union
select 1,'abc' ,200 ,12.3 ,to_date('04-may-2017 12:22:34 AM','dd-mon-yyyy hh:mi:ss AM') from dual
union
select 1,'abc' ,-100 ,12.3 ,to_date('04-may-2017 12:22:35 AM','dd-mon-yyyy hh:mi:ss AM') from dual
union
select 2 category, 'abcd' Item_name,100 item_volume,12.32 item_price,to_date('04-may-2017 12:22:33 AM','dd-mon-yyyy hh:mi:ss AM') time from dual
union
select 2,'abcd' ,-25 ,12.32 ,to_date('04-may-2017 12:22:34 AM','dd-mon-yyyy hh:mi:ss AM') from dual
union
select 2,'abcd' ,-75 ,12.32 ,to_date('04-may-2017 12:22:34 AM','dd-mon-yyyy hh:mi:ss AM') from dual
union
select 2,'abcd' ,100 ,12.32 ,to_date('04-may-2017 12:22:35 AM','dd-mon-yyyy hh:mi:ss AM') from dual
)
select * from t1;
--------------------------------------------------
CATEGORY ITEM ITEM_VOLUME ITEM_PRICE TO_CHAR(TIME,'DD-MON-YY
---------- ---- ----------- ---------- -----------------------
1 abc 100 12.3 04-may-2017 12:22:33 am
1 abc -100 12.3 04-may-2017 12:22:34 am
1 abc 200 12.3 04-may-2017 12:22:34 am
1 abc -100 12.3 04-may-2017 12:22:35 am
2 abcd 100 12.32 04-may-2017 12:22:33 am
2 abcd -75 12.32 04-may-2017 12:22:34 am
2 abcd -25 12.32 04-may-2017 12:22:34 am
2 abcd 100 12.32 04-may-2017 12:22:35 am
Question:Need a SQL query which will result below rows only,
Requirement:Partition by item_name,item_price and order by time and sum the item_volume, wherever the sum will become ZERO those records need to print,if we see below expected output 100,-100 resulting zero AND 100,-75,-25 resulting zero so only those records needed.
1 abc 100 12.3 04-MAY-17
1 abc -100 12.3 04-MAY-17
2 abcd 100 12.32 04-MAY-17
2 abcd -75 12.32 04-MAY-17
2 abcd -25 12.32 04-MAY-17
So you want to find the rows where the running total per category is zero. Then display that and all the rows preceding it? What happens if the running total can reach zero on two separate occasions?
Anyway, here's a basic algorithm:
1. Calculating the running total per category
2. Assign a row number by category
3. Find the highest row num for the rows with the smallest running total
4. The smallest value for the running total
Do the first two steps in the first pass:
select ti.*,
sum(item_volume) over (partition by category order by time, item_volume) tot,
row_number() over (partition by category order by time, item_volume) rn
from test_item ti;
You can find the value for step 3 with:
max(rn) keep (dense_rank first order by tot) over (partition by category)
This is saying:
Sort the rows by (running) tot. Then for those that have the lowest running total in each category, find the maximum row number.
All you need to do after this is ensure that the min running total is zero and find all the rows with a row number equal to or lower than that found in step 3:
create table test_item as
with t1 as
(
select 1 category, 'abc' Item_name,100 item_volume,12.3 item_price,to_date('04-may-2017 12:22:33 AM','dd-mon-yyyy hh:mi:ss AM') time from dual
union
select 1,'abc' ,-100 ,12.3 ,to_date('04-may-2017 12:22:34 AM','dd-mon-yyyy hh:mi:ss AM') from dual
union
select 1,'abc' ,200 ,12.3 ,to_date('04-may-2017 12:22:34 AM','dd-mon-yyyy hh:mi:ss AM') from dual
union
select 1,'abc' ,-100 ,12.3 ,to_date('04-may-2017 12:22:35 AM','dd-mon-yyyy hh:mi:ss AM') from dual
union
select 2 category, 'abcd' Item_name,100 item_volume,12.32 item_price,to_date('04-may-2017 12:22:33 AM','dd-mon-yyyy hh:mi:ss AM') time from dual
union
select 2,'abcd' ,-25 ,12.32 ,to_date('04-may-2017 12:22:34 AM','dd-mon-yyyy hh:mi:ss AM') from dual
union
select 2,'abcd' ,-75 ,12.32 ,to_date('04-may-2017 12:22:34 AM','dd-mon-yyyy hh:mi:ss AM') from dual
union
select 2,'abcd' ,100 ,12.32 ,to_date('04-may-2017 12:22:35 AM','dd-mon-yyyy hh:mi:ss AM') from dual
union
select 3,'def' ,100 ,12.32 ,to_date('04-may-2017 12:22:34 AM','dd-mon-yyyy hh:mi:ss AM') from dual
union
select 3,'def' ,50 ,12.32 ,to_date('04-may-2017 12:22:35 AM','dd-mon-yyyy hh:mi:ss AM') from dual
)
select * from t1;
with tots as (
select ti.*,
sum(item_volume) over (partition by category order by time, item_volume) tot,
row_number() over (partition by category order by time, item_volume) rn
from test_item ti
), rws as (
select t.*, max(rn) keep (dense_rank first order by tot) over (partition by category) mxrn ,
min(tot) over (partition by category) mn
from tots t
)
select * from rws
where rn <= mxrn
and mn = 0
order by 1, 5;
CATEGORY ITEM_NAME ITEM_VOLUME ITEM_PRICE TIME TOT RN MXRN MN
1 abc 100 12.3 04-MAY-2017 00:22:33 100 1 2 0
1 abc -100 12.3 04-MAY-2017 00:22:34 0 2 2 0
2 abcd 100 12.32 04-MAY-2017 00:22:33 100 1 3 0
2 abcd -75 12.32 04-MAY-2017 00:22:34 25 2 3 0
2 abcd -25 12.32 04-MAY-2017 00:22:34 0 3 3 0
Or if you want to do it 12c style, you can use match_recognize:
select * from test_item
match_recognize (
partition by category order by time, item_volume
measures classifier() c
all rows per match
pattern (running* zero)
define
zero as sum(running.item_volume) = 0
)
where c = 'RUNNING';
CATEGORY TIME ITEM_VOLUME C ITEM_NAME ITEM_PRICE
1 04-MAY-2017 00:22:33 100 RUNNING abc 12.3
1 04-MAY-2017 00:22:34 -100 RUNNING abc 12.3
2 04-MAY-2017 00:22:33 100 RUNNING abcd 12.32
2 04-MAY-2017 00:22:34 -75 RUNNING abcd 12.32
2 04-MAY-2017 00:22:34 -25 RUNNING abcd 12.32
This will only match the first time the running total hits zero though. The analytic method will match multiple:
insert into test_item
select 1,'abc' ,-100 ,12.3 ,to_date('04-may-2017 12:22:35 AM','dd-mon-yyyy hh:mi:ss AM') from dual;
select * from test_item
match_recognize (
partition by category order by time, item_volume
measures classifier() c
all rows per match
pattern (running* zero)
define
zero as sum(running.item_volume) = 0
)
where c = 'RUNNING';
CATEGORY TIME ITEM_VOLUME C ITEM_NAME ITEM_PRICE
1 04-MAY-2017 00:22:33 100 RUNNING abc 12.3
1 04-MAY-2017 00:22:34 -100 RUNNING abc 12.3
2 04-MAY-2017 00:22:33 100 RUNNING abcd 12.32
2 04-MAY-2017 00:22:34 -75 RUNNING abcd 12.32
2 04-MAY-2017 00:22:34 -25 RUNNING abcd 12.32
with tots as (
select ti.*,
sum(item_volume) over (partition by category order by time, item_volume) tot,
row_number() over (partition by category order by time, item_volume) rn
from test_item ti
), rws as (
select t.*, max(rn) keep (dense_rank first order by tot) over (partition by category) mxrn ,
min(tot) over (partition by category) mn
from tots t
)
select * from rws
where rn <= mxrn
and mn = 0
order by 1, 5;
CATEGORY ITEM_NAME ITEM_VOLUME ITEM_PRICE TIME TOT RN MXRN MN
1 abc 100 12.3 04-MAY-2017 00:22:33 100 1 5 0
1 abc -100 12.3 04-MAY-2017 00:22:34 0 2 5 0
1 abc 200 12.3 04-MAY-2017 00:22:34 200 3 5 0
1 abc -100 12.3 04-MAY-2017 00:22:35 0 4 5 0
1 abc -100 12.3 04-MAY-2017 00:22:35 0 5 5 0
2 abcd 100 12.32 04-MAY-2017 00:22:33 100 1 3 0
2 abcd -75 12.32 04-MAY-2017 00:22:34 25 2 3 0
2 abcd -25 12.32 04-MAY-2017 00:22:34 0 3 3 0