How to get rolling week count [message #656254] |
Fri, 30 September 2016 08:38 |
|
jaggy
Messages: 15 Registered: November 2014 Location: India
|
Junior Member |
|
|
Hi,
Advance thanks for the solution.I have a table which has 3 columns as store,week and product. I want to retrieve the number of distinct products available in a store for last 12 weeks.
In other words for current week,I should go back to previous 11 weeks in the store and get the distinct products available. For next week, I should go back 11 weeks from next week and get no of distinct products available in that store. Basically it is getting rolling week count.
I tried with multiple options using "ROWS BETWEEN 11 PRECEDING AND current row",order by analytical functions. But something is going wrong and I am not getting desired out put. Can some one please help me here?
SourceTable structure:create table store_detail
(store integer,
AB_WEEK_ID integer,
IRI_PDCN_SKU_ID integer );
Data:Insert statement .sql added as attachment.
Desired Output:
store week distinct_product_cnt
199792954 201634 12
199792954 201633 12
199792954 201632 12
199792954 201631 12
199792954 201630 12
199792954 201629 12
199792954 201628 12
| | |
| | |
| | |
5607442 201634 9
5607442 201633 9
5607442 201632 9
5607442 201631 9
I tried like below..SELECT store, week,
count(product) OVER (PARTITION BY store
ORDER BY week ROWS BETWEEN 11 PRECEDING AND current row) as prod_cnt
FROM table s
ORDER BY store
|
|
|
Re: How to get rolling week count [message #656257 is a reply to message #656254] |
Fri, 30 September 2016 11:35 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Please post a VALID test case:
SQL> Insert into store_detail (STORE,WEEK,PRODUCT) values (199792954,201629,12892);
Insert into store_detail (STORE,WEEK,PRODUCT) values (199792954,201629,12892)
*
ERROR at line 1:
ORA-00904: "PRODUCT": invalid identifier
Good old way:
SQL> break on store dup skip 1
SQL> select s1.store, s1.week, count(distinct s2.product) cnt
2 from store_detail s1, store_detail s2
3 where s2.store = s1.store
4 and s2.week between s1.week-11 and s1.week
5 group by s1.store, s1.week
6 order by s1.store, s1.week
7 /
STORE WEEK CNT
---------- ---------- ----------
5607442 201610 8
5607442 201611 8
5607442 201612 8
5607442 201613 9
5607442 201614 9
5607442 201615 9
5607442 201616 9
5607442 201617 9
5607442 201618 9
5607442 201619 9
5607442 201620 9
5607442 201621 9
5607442 201622 9
5607442 201623 9
5607442 201624 9
5607442 201625 9
5607442 201626 9
5607442 201628 9
5607442 201629 9
5607442 201630 9
5607442 201631 9
5607442 201632 9
5607442 201633 9
5607442 201634 9
199792954 201609 8
199792954 201610 8
199792954 201611 9
199792954 201612 10
199792954 201613 10
199792954 201614 10
199792954 201615 11
199792954 201616 11
199792954 201617 11
199792954 201618 12
199792954 201619 12
199792954 201620 12
199792954 201621 12
199792954 201622 12
199792954 201623 12
199792954 201624 12
199792954 201625 12
199792954 201626 12
199792954 201627 12
199792954 201628 12
199792954 201629 12
199792954 201630 12
199792954 201631 12
199792954 201632 12
199792954 201633 12
199792954 201634 12
Given the way you code your weeks you have to take care of the cases overlapping 2 years.
[Updated on: Fri, 30 September 2016 11:37] Report message to a moderator
|
|
|
Re: How to get rolling week count [message #656265 is a reply to message #656254] |
Fri, 30 September 2016 14:31 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Well, you were on the right track:
select store,
week,
product,
count(product) over(
partition by store
order by week
range between 11 preceding and current row
) as prod_cnt
from store_detail
order by store,
week
/
The only step left is count distinct. Unfortunately DISTINCT + ORDER BY isn't supported in analytic functions:
select store,
week,
product,
count(distinct product) over(
partition by store
order by week
range between 11 preceding and current row
) as prod_cnt
from store_detail
order by store,
week
/
order by week
*
ERROR at line 6:
ORA-30487: ORDER BY not allowed here
SQL>
So you have to do a self-join as Michel did. But you still might have an issue. Condition s2.week between s1.week-11 and s1.week will not work as soon as 12 week window spans year. For example s1 = 201603. Then s2.week between 201582 and 201603 will miss 2015 weeks. How to fix it depends if your data has contiguous weeks or there can be gaps.
SY.
|
|
|
|
|
Re: How to get rolling week count [message #656270 is a reply to message #656268] |
Fri, 30 September 2016 15:58 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Michel Cadot wrote on Fri, 30 September 2016 15:49
I think "I want to retrieve the number of distinct products available in a store for last 12 weeks." means for the last 12 calendar weeks not for the last 12 weeks present in the table and gaps then does not matter.
Well, it is not so straight-forward to convert 201643 to a date (unless OP is using week 1 starts January 1). That's why analytic ROW_NUMBER might be easier if there are no gaps in weeks.
SY.
[Updated on: Fri, 30 September 2016 16:07] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
|
|