OraFAQ Forum: SQL & PL/SQL » How to get rolling week count

Home » SQL & PL/SQL » SQL & PL/SQL » How to get rolling week count

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Attachment: Insert statement.sql.txt
(Size: 23.80KB, Downloaded 1502 times)

Report message to a moderator

Re: How to get rolling week count [message #656257 is a reply to message #656254]

Fri, 30 September 2016 11:35

Michel Cadot
Messages: 68644
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: 3273
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.

Report message to a moderator

Re: How to get rolling week count [message #656267 is a reply to message #656265]

Fri, 30 September 2016 14:46

Michel Cadot
Messages: 68644
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.

Report message to a moderator

Re: How to get rolling week count [message #656268 is a reply to message #656267]

Fri, 30 September 2016 14:49

Michel Cadot
Messages: 68644
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.

Report message to a moderator

Re: How to get rolling week count [message #656270 is a reply to message #656268]

Fri, 30 September 2016 15:58

Solomon Yakobson
Messages: 3273
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

Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Not straightforward but already solved by many. Smile

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;

Report message to a moderator

Re: How to get rolling week count [message #656297 is a reply to message #656274]

Sun, 02 October 2016 06:47

Solomon Yakobson
Messages: 3273
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.

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

Smile

). For example, just recently I had to change code with IW because it was causing a lot of confusion during code review.

SY.

Report message to a moderator

Re: How to get rolling week count [message #656307 is a reply to message #656257]

Sun, 02 October 2016 12:42

jaggy
Messages: 15
Registered: November 2014
Location: India

Junior Member

Thanks a lot..This works after little modification.

Report message to a moderator

Re: How to get rolling week count [message #656308 is a reply to message #656307]

Sun, 02 October 2016 13:06

Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Can you share which one and why.

Report message to a moderator

Re: How to get rolling week count [message #656316 is a reply to message #656308]

Mon, 03 October 2016 02:47

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.

Report message to a moderator

Re: How to get rolling week count [message #656320 is a reply to message #656316]

Mon, 03 October 2016 03:06

Michel Cadot
Messages: 68644
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?

Report message to a moderator

Re: How to get rolling week count [message #656327 is a reply to message #656320]

Mon, 03 October 2016 05:25

Solomon Yakobson
Messages: 3273
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.

Report message to a moderator

Re: How to get rolling week count [message #656328 is a reply to message #656320]

Mon, 03 October 2016 05:31

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.

Report message to a moderator

Re: How to get rolling week count [message #656330 is a reply to message #656328]

Mon, 03 October 2016 06:34

Michel Cadot
Messages: 68644
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.

Report message to a moderator

Re: How to get rolling week count [message #656331 is a reply to message #656327]

Mon, 03 October 2016 06:35

Michel Cadot
Messages: 68644
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

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 06:34:00 CDT 2024