1

Topic: Higher mathematics a pancake

Good afternoon
There are some dial-ups of identifiers (id, month), call set for every month: m1, m2, m3... m [k-1], m [k]
It is necessary to receive on an output
On a step
1: set of identifiers entering into a dial-up m1 and m2
2: set of identifiers entering into a dial-up m1 and m2 and m3
3: set of identifiers entering into a dial-up m1 and m2 and m3 and m4
...
k: set of identifiers entering into a dial-up m1 and m2 and m3 and m4... m [k]
That that at all I do not represent as to approach to the task
Any help and ideas are welcomed

2

Re: Higher mathematics a pancake

The test a dial-up:

with t as (select to_date (trunc (sysdate, ' mm ')), level
from dual
connect by level <30
union
select add_months (to_date (trunc (sysdate, ' mm ')),-1), level + 3
from dual
connect by level <20
union
select add_months (to_date (trunc (sysdate, ' mm ')),-2), level + 5
from dual
connect by level <10
union
select add_months (to_date (trunc (sysdate, ' mm ')),-3), level + 8
from dual
connect by level <5)
select * from t

3

Re: Higher mathematics a pancake

sharkoff_new;
groupnig sets

4

Re: Higher mathematics a pancake

env;
Was born  as that bit masks to mark groups, then to collect depending on .
How to make with grouping set yet did not master sad

with t as
(select to_date (trunc (sysdate, ' mm ')) d_d;
level id;
4 k;
bin_to_num (1, 1, 1, 1) bitmask
from dual
connect by level <30
union
select add_months (to_date (trunc (sysdate, ' mm ')),-1);
level + 3;
3;
bin_to_num (1, 1, 1, 0) bitmask
from dual
connect by level <20
union
select add_months (to_date (trunc (sysdate, ' mm ')),-2);
level + 5;
2;
bin_to_num (1, 1, 0, 0) bitmask
from dual
connect by level <10
union
select add_months (to_date (trunc (sysdate, ' mm ')),-3);
level + 8;
1;
bin_to_num (1, 0, 0, 0) bitmask
from dual
connect by level <5);
-- select * from t
in_grp as
(select bitmask, bitand (bitmask, bin_to_num (1, 1, 0, 0)), id, d_d
from t
where bitand (bitmask, bin_to_num (1, 1, 0, 0)) = bin_to_num (1, 1, 0, 0))
--select * from in_grp
select id from in_grp group by id having count (1) = 3;

5

Re: Higher mathematics a pancake

with t as (select to_date (trunc (sysdate, ' mm ')) dt, level id
from dual
connect by level <30
union ALL
select add_months (to_date (trunc (sysdate, ' mm ')),-1), level + 3
from dual
connect by level <20
union ALL
select add_months (to_date (trunc (sysdate, ' mm ')),-2), level + 5
from dual
connect by level <10
union ALL
select add_months (to_date (trunc (sysdate, ' mm ')),-3), level + 8
from dual
connect by level <5)
select id
from t
where dt> = add_months (to_date (trunc (sysdate, ' mm ')),-2) - m1 U m2 U m3
group by id
having count (dictinct trunc (dt, ' mm ')) = 3 - id present in all three months

6

Re: Higher mathematics a pancake

with t as (select to_date (trunc (sysdate, ' mm ')) dt, level lvl
from dual
connect by level <30
union all
select add_months (to_date (trunc (sysdate, ' mm ')),-1), level + 3
from dual
connect by level <20
union all
select add_months (to_date (trunc (sysdate, ' mm ')),-2), level + 5
from dual
connect by level <10
union all
select add_months (to_date (trunc (sysdate, ' mm ')),-3), level + 8
from dual
connect by level <5)
select dt, sum (count (*)) over (order by dt) sm
from t
group by dt;
DT SM
------------------- ----------
9/1/2016 OF 0:00:00 AM 4
01.10.2016 00:00:00 13
01.11.2016 00:00:00 32
01.12.2016 00:00:00 61

7

Re: Higher mathematics a pancake

sharkoff_new;

SELECT T.RNK
,T.CNT
,T.MONTH
FROM (SELECT MONTH
,COUNT (*) OVER (ORDER BY MONTH) AS CNT
,ROW_NUMBER () OVER (ORDER BY MONTH) - 1 RNK
FROM TABLE
) T
WHERE T.RNK> 0

8

Re: Higher mathematics a pancake

sharkoff_new, hurried a little. Here the correct decision:

WITH CTE (ID, MONTH) AS
(SELECT 1, 1 FROM DUAL
UNION ALL
SELECT 2, 1 FROM DUAL
UNION ALL
SELECT 1, 2 FROM DUAL
UNION ALL
SELECT 5, 2 FROM DUAL
UNION ALL
SELECT 3, 3 FROM DUAL
UNION ALL
SELECT 4, 4 FROM DUAL
)
SELECT DISTINCT T.RNK
,T.CNT
FROM (SELECT MONTH
,COUNT (*) OVER (ORDER BY MONTH) AS CNT
,DENSE_RANK () OVER (ORDER BY MONTH) - 1 RNK
FROM CTE
) T
WHERE T.RNK> 0

9

Re: Higher mathematics a pancake

select id
from tbl
where month in (m1, m2, m3... m [k-1], m [k])
group by id
having count (month) = k - count (distinct month) = k if the pair id, month is not unique
/

SY.