How to count number of records for each week, from last month activity on a table?-CodePudding

I'm working with Oracle and I have a table with a column of type TIMESTAMP. I was wondering how can I extract the records from last 4 weeks of activity on the database, partitioned by week.

Following rows are inserted on week 1

kc 2 04-10-2021
vc 3 06-10-2021
vk 4 07-10-2021

Following rows are inserted on week2

cv 1 12-10-2021
ck 5 14-10-2021

Following rows are inserted on week3

vv 7 19-10-2021

Following rows are inserted on week4

vx 7 29-10-2021

Table now has

SQL>select * from tab;
NAME VALUE TIMESTAMP
-------------------- ----------
kc 2 04-10-2021
vc 3 06-10-2021
vk 4 07-10-2021
cv 1 12-10-2021
ck 5 14-10-2021
vv 7 19-10-2021
vx 7 29-10-2021

I would like a query which would give me the number of rows added each week, in the last 4 weeks.

This is what I would like to see

numofrows week
--------- -----
3 1
2 2
1 3
1 4

CodePudding user response：

One option is to use to_char function and its iw parameter:

SQL> with  test (name, datum) as
  2    (select 'kc', date '2021-10-04' from dual union all
  3     select 'vc', date '2021-10-06' from dual union all
  4     select 'vk', date '2021-10-07' from dual union all
  5     select 'cv', date '2021-10-12' from dual union all
  6     select 'ck', date '2021-10-14' from dual union all
  7     select 'vv', date '2021-10-19' from dual union all
  8     select 'vx', DATE '2021-10-29' from dual
  9    )
 10  select to_char(datum, 'iw') week,
 11    count(*)
 12  from test
 13  where datum >= add_months(sysdate, -1) --> the last month
 14  group by to_char(datum, 'iw');

WE   COUNT(*)
-- ----------
42          1
43          1
40          3
41          2

SQL>

Line #13: I intentionally used "one month" instead of "4 weeks" as I thought (maybe wrongly) that you, actually, want that (you know, "a month has 4 weeks" - not exactly, but close, sometimes not close enough).

If you want 4 weeks, what is that, then? Sysdate minus 28 days (as every week has 7 days)? Then you'd modify line #13 to

where datum >= trunc(sysdate - 4*7)

Or, maybe it is really the last 4 weeks:

SQL> with  test (name, datum) as
  2    (select 'kc', date '2021-10-04' from dual union all
  3     select 'vc', date '2021-10-06' from dual union all
  4     select 'vk', date '2021-10-07' from dual union all
  5     select 'cv', date '2021-10-12' from dual union all
  6     select 'ck', date '2021-10-14' from dual union all
  7     select 'vv', date '2021-10-19' from dual union all
  8     select 'vx', DATE '2021-10-29' from dual
  9    ),
 10  temp as
 11    (select to_char(datum, 'iw') week,
 12        count(*) cnt,
 13        row_number() over (order by to_char(datum, 'iw') desc) rn
 14      from test
 15      group by to_char(datum, 'iw')
 16     )
 17  select week, cnt
 18  from temp
 19  where rn <= 4
 20  order by week;

WE        CNT
-- ----------
40          3
41          2
42          1
43          1

SQL>

Now you have several options, see which one fits the best (if any).

I "simulated" missing data (see TEST CTE), created a calendar (calend) and ... did the job. Read comments within code:

SQL> with test (name, datum) as
  2    -- sample data
  3    (select 'vv', date '2021-10-19' from dual union all
  4     select 'vx', DATE '2021-10-29' from dual
  5    ),
  6  calend as
  7    -- the last 31 days; 4 weeks are included, obviously
  8    (select max_datum - level   1 datum
  9     from (select max(a.datum) max_datum from test a)
 10     connect by level <= 31
 11    ),
 12  joined as
 13    -- joined TEST and CALEND data
 14    (select to_char(c.datum, 'iw') week,
 15            t.name
 16     from calend c left join test t on t.datum = c.datum
 17    ),
 18  last4 as
 19    -- last 4 weeks
 20    (select week, count(name) cnt,
 21        row_number() over (order by week desc) rn
 22      from joined
 23      group by week
 24     )
 25  select week, cnt
 26  from last4
 27  where rn <= 4
 28  order by week;

WE        CNT
-- ----------
40          0
41          0
42          1
43          1

SQL>