Home » SQL & PL/SQL » SQL & PL/SQL » How to get rolling week count
How to get rolling week count [message #656254] Fri, 30 September 2016 08:38 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2699
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 #656267 is a reply to message #656265] Fri, 30 September 2016 14:46 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
condition s2.week between s1.week-11 and s1.week will not work as soon as 12 week window spans year.
This is what I said, the simplest way is to convert the current week number to a real number of weeks since a starting date and this depend on what the current week number is: calendar week or ISO week.

Re: How to get rolling week count [message #656268 is a reply to message #656267] Fri, 30 September 2016 14:49 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
How to fix it depends if your data has contiguous weeks or there can be gaps.
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.

Re: How to get rolling week count [message #656270 is a reply to message #656268] Fri, 30 September 2016 15:58 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2699
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

Re: How to get rolling week count [message #656274 is a reply to message #656270] Fri, 30 September 2016 21:52 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Not straightforward but already solved by many. Smile

ISO year/week:
SELECT TRUNC(TO_DATE(:iso_year || '-01-04', 'YYYY-MM-DD'), 'IW') + 7 * (:iso_week - 1) FROM dual;

Calendar year/week (week 1 starts January 1):
SELECT TRUNC(TO_DATE(:cal_year, 'YYYY'), 'YYYY') + 7 * (:cal_week - 1) FROM dual;

Re: How to get rolling week count [message #656297 is a reply to message #656274] Sun, 02 October 2016 06:47 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2699
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel Cadot wrote on Fri, 30 September 2016 22:52

Not straightforward but already solved by many. Smile
Yeap, and you can find many of my posts on this forum on IW. Issue is a lot of people are lost trying to understand ISO year/eek where ISO year can start previous calendar year or not January 1 of current calendar year. So yes, it is already solved but maintaining it can cause human-related issues (issues between chair & keaboard Smile ). For example, just recently I had to change code with IW because it was causing a lot of confusion during code review.

SY.
Re: How to get rolling week count [message #656307 is a reply to message #656257] Sun, 02 October 2016 12:42 Go to previous messageGo to next message
jaggy
Messages: 15
Registered: November 2014
Location: India
Junior Member
Thanks a lot..This works after little modification.
Re: How to get rolling week count [message #656308 is a reply to message #656307] Sun, 02 October 2016 13:06 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Can you share which one and why.

Re: How to get rolling week count [message #656316 is a reply to message #656308] Mon, 03 October 2016 02:47 Go to previous messageGo to next message
jaggy
Messages: 15
Registered: November 2014
Location: India
Junior Member
The weeks are in iso format like 201552 and then 201601.Year to year it will change
E.g.
For year 2015 ,weeks will be 201501-201552
For year 2014,weekd will be 201401-201452 and so on..

In this case week-11 logic will not give proper result.So I created a separate table with continuous number starting from 1 and increase it by 1 for each week for all the weeks till next 20 years.
Re: How to get rolling week count [message #656320 is a reply to message #656316] Mon, 03 October 2016 03:06 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
The weeks are in iso format like 201552 and then 201601.
There is no ISO format for weeks, there are ISO values for year and week.
See Calculating Week Numbers
An ISO year has 52 or 53 weeks.

Quote:
For year 2015 ,weeks will be 201501-201552
For year 2014,weekd will be 201401-201452 and so on..
There are more than 52 weeks per year (52*7 = 364), so what do you do with the last days?
Do you include them in week 52?

Re: How to get rolling week count [message #656327 is a reply to message #656320] Mon, 03 October 2016 05:25 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2699
Registered: January 2010
Location: Connecticut, USA
Senior Member
You see, it already started. I don't know why so many people have troubles understanding ISO year & week.

SY.
Re: How to get rolling week count [message #656328 is a reply to message #656320] Mon, 03 October 2016 05:31 Go to previous messageGo to next message
jaggy
Messages: 15
Registered: November 2014
Location: India
Junior Member
Yes. There will be 1(365 days) or 2(366 days) days added extra in last week and that does not matter to me.
Re: How to get rolling week count [message #656330 is a reply to message #656328] Mon, 03 October 2016 06:34 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, so you don't need another table just modify the second query I gave.

Re: How to get rolling week count [message #656331 is a reply to message #656327] Mon, 03 October 2016 06:35 Go to previous message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Solomon Yakobson wrote on Mon, 03 October 2016 12:25
You see, it already started. I don't know why so many people have troubles understanding ISO year & week.

SY.
Sad


Previous Topic: HTTPS Webservices Issue though PL/SQL
Next Topic: EGO_ITEM_USER_ATTRS_CP_PUB.Process_Item_User_Attrs_Data API Error
Goto Forum:
  


Current Time: Sat Jan 20 19:58:43 CST 2018

Total time taken to generate the page: 0.17796 seconds