1

Topic: on unique entrance

There is a table

Date Platform ID
2016-01 Test1 1
2016-01 Test1 2
2016-01 Test2 1
2016-01 Test2 2
2016-01 Test3 2
2016-01 Test3 2
2016-01 Test3 1
2016-01 Test3 3
2016-01 Test4 1
2016-01 Test4 2
2016-01 Test4 3
2016-01 Test4 4
2016-01 Test4 5
2016-01 Test4 6

I should deduce an amount unique ID which enter in groups Test4 and nearby an amount of unique values which enter only in Test4 (and a case in point)
That is on an output should be

Test4 |6 | 3

Well it is possible also other data from groupings to deduce
To me at the main thing to look as to receive which enter only in Test4

2

Re: on unique entrance

-=Koba =-;

with t (dt_chr, platform, id) as
(
select ' 2016-01 ', ' Test1 ', 1 from dual union all
select ' 2016-01 ', ' Test1 ', 2 from dual union all
select ' 2016-01 ', ' Test2 ', 1 from dual union all
select ' 2016-01 ', ' Test2 ', 2 from dual union all
select ' 2016-01 ', ' Test3 ', 2 from dual union all
select ' 2016-01 ', ' Test3 ', 2 from dual union all
select ' 2016-01 ', ' Test3 ', 1 from dual union all
select ' 2016-01 ', ' Test3 ', 3 from dual union all
select ' 2016-01 ', ' Test4 ', 1 from dual union all
select ' 2016-01 ', ' Test4 ', 2 from dual union all
select ' 2016-01 ', ' Test4 ', 3 from dual union all
select ' 2016-01 ', ' Test4 ', 4 from dual union all
select ' 2016-01 ', ' Test4 ', 5 from dual union all
select ' 2016-01 ', ' Test4 ', 6 from dual
);
t2 (id) as
(
select id from t where platform <> ' Test4'
)
select t.platform, count (distinct t.id), count (distinct t2.id)
from t
left join t2 on t.id = t2.id
where platform = ' Test4'
group by platform;

3

Re: on unique entrance

I thank for the help in a direction

4

Re: on unique entrance

Down ;

wrote:

To me at the main thing to look as to receive which enter only in Test4

.....
stax

5

Re: on unique entrance

Stax?

6

Re: on unique entrance

Whether and it is possible to superimpose the given decision here?

/* Formatted on 10/12/2017 OF 3:01:06 PM (QP5 v5.313) */
WITH
t (operation_time, platform, USER_ID) AS
(SELECT ' 2016-01 ', ' Test1 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test1 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test2 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test2 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test3 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test3 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-03 ', ' Test3 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-04 ', ' Test3 ', 3 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test4 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test4 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-03 ', ' Test4 ', 3 FROM DUAL
UNION ALL
SELECT ' 2016-04 ', ' Test4 ', 4 FROM DUAL
UNION ALL
SELECT ' 2016-05 ', ' Test4 ', 5 FROM DUAL
UNION ALL
SELECT ' 2016-06 ', ' Test4 ', 6 FROM DUAL)
SELECT operation;
SUM (a);
SUM (b);
SUM (c);
SUM (d)
FROM (SELECT *
FROM ( SELECT operation_time AS operation, platform, USER_ID
FROM t
WHERE platform IN (' Test1 ';
' Test2 ';
' Test3 ';
' Test4 ')
GROUP BY GROUPING SETS ((operation_time, PLATFORM, USER_ID), (PLATFORM, USER_ID)))
PIVOT
(COUNT (*) FOR platform IN (' Test1 ' AS A, ' Test2 ' AS B, ' Test3 ' AS A C, ' Test4 ' AS D)))
GROUP BY operation
ORDER BY operation
2016-01 1 1 1 1
2016-02 1 1 1 1
2016-03 0 0 1 1
2016-04 0 0 1 1
2016-05 0 0 0 1
2016-06 0 0 0 1
2 2 3 6

Here count an amount of unique entrances in a platform for the period
5 column for test4 is necessary to me

2016-01 1 1 1 1 0
2016-02 1 1 1 1 0
2016-03 0 0 1 1 0
2016-04 0 0 1 1 1
2016-05 0 0 0 1 1
2016-06 0 0 0 1 1
2 2 3 6 3

At test4 during 2016-01, 2016-02, 2016-03 enter into other groups, are not considered,
And 2016-04 2016-05, 2016-06 already approach

7

Re: on unique entrance

-=Koba =-;
Task setting is formulated.... Not precisely enough
Something is necessary such?

WITH
t (operation_time, platform, USER_ID) AS
(SELECT ' 2016-01 ', ' Test1 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test1 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test2 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test2 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test3 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test3 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-03 ', ' Test3 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-04 ', ' Test3 ', 3 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test4 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test4 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-03 ', ' Test4 ', 3 FROM DUAL
UNION ALL
SELECT ' 2016-04 ', ' Test4 ', 4 FROM DUAL
UNION ALL
SELECT ' 2016-05 ', ' Test4 ', 5 FROM DUAL
UNION ALL
SELECT ' 2016-06 ', ' Test4 ', 6 FROM DUAL)
SELECT platform, count (distinct USER_ID) cnt_for_all;
count (distinct
case when platform! = ' Test4 ' or operation_time in (' 2016-01 ', ' 2016-02 ', ' 2016-03 ') then null
else USER_ID end
) cnt_for_4
FROM t
WHERE platform IN (' Test1 ';
' Test2 ';
' Test3 ';
' Test4 ')
group by platform

8

Re: on unique entrance

MaximaXXL wrote:

- =Koba =-;
Task setting is formulated.... Not precisely enough
Something is necessary such?

WITH
t (operation_time, platform, USER_ID) AS
(SELECT ' 2016-01 ', ' Test1 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test1 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test2 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test2 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test3 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test3 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-03 ', ' Test3 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-04 ', ' Test3 ', 3 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test4 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test4 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-03 ', ' Test4 ', 3 FROM DUAL
UNION ALL
SELECT ' 2016-04 ', ' Test4 ', 4 FROM DUAL
UNION ALL
SELECT ' 2016-05 ', ' Test4 ', 5 FROM DUAL
UNION ALL
SELECT ' 2016-06 ', ' Test4 ', 6 FROM DUAL)
SELECT platform, count (distinct USER_ID) cnt_for_all;
count (distinct
case when platform! = ' Test4 ' or operation_time in (' 2016-01 ', ' 2016-02 ', ' 2016-03 ') then null
else USER_ID end
) cnt_for_4
FROM t
WHERE platform IN (' Test1 ';
' Test2 ';
' Test3 ';
' Test4 ')
group by platform

Yes but at you a binding
or operation_time in (' 2016-01 ', ' 2016-02 ', ' 2016-03 ')

9

Re: on unique entrance

t (operation_time, platform, USER_ID) AS
(SELECT ' 2016-01 ', ' Test1 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test1 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test2 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test2 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test3 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test3 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-03 ', ' Test3 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-04 ', ' Test3 ', 3 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test4 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test4 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-03 ', ' Test4 ', 3 FROM DUAL
UNION ALL
SELECT ' 2016-04 ', ' Test4 ', 4 FROM DUAL
UNION ALL
SELECT ' 2016-05 ', ' Test4 ', 5 FROM DUAL
UNION ALL
SELECT ' 2016-06 ', ' Test4 ', 6 FROM DUAL)

I should count number of unique entrances in platform on the periods

2016-01 1 1 1 1
2016-02 1 1 1 1
2016-03 0 0 1 1
2016-04 0 0 1 1
2016-05 0 0 0 1
2016-06 0 0 0 1
2 2 3 6

I made it works
One more column for test4 too the most unique value in platform on the periods, but plus still a condition that USER_ID that  for this period did not appear anywhere is necessary to me
Satisfies

SELECT ' 2016-04 ', ' Test4 ', 4 FROM DUAL
UNION ALL
SELECT ' 2016-05 ', ' Test4 ', 5 FROM DUAL
UNION ALL
SELECT ' 2016-06 ', ' Test4 ', 6 FROM DUAL

10

Re: on unique entrance

All subject is filled up  from . Where that unique full and consistent post from which it is possible to understand what is required to the author?

11

Re: on unique entrance

AmKad wrote:

All subject is filled up  from . Where that unique full and consistent post from which it is possible to understand what is required to the author?

Last before you

12

Re: on unique entrance

  wrote:

Stax?

with t (dt_chr, platform, id) as
(
select ' 2016-01 ', ' Test1 ', 1 from dual union all
select ' 2016-01 ', ' Test1 ', 2 from dual union all
select ' 2016-01 ', ' Test2 ', 1 from dual union all
select ' 2016-01 ', ' Test2 ', 2 from dual union all
select ' 2016-01 ', ' Test3 ', 2 from dual union all
select ' 2016-01 ', ' Test3 ', 2 from dual union all
select ' 2016-01 ', ' Test3 ', 1 from dual union all
select ' 2016-01 ', ' Test3 ', 3 from dual union all
select ' 2016-01 ', ' Test4 ', 1 from dual union all
select ' 2016-01 ', ' Test4 ', 2 from dual union all
select ' 2016-01 ', ' Test4 ', 3 from dual union all
select ' 2016-01 ', ' Test4 ', 4 from dual union all
select ' 2016-01 ', ' Test4 ', 5 from dual union all
select ' 2016-01 ', ' Test4 ', 7 from dual union all
select ' 2016-01 ', ' Test4 ', 7 from dual union all
select ' 2016-01 ', ' Test4 ', 8 from dual union all
select ' 2016-01 ', ' Test4 ', 6 from dual
);
t2 (id) as
(
select id from t where platform <> ' Test4'
)
select t.platform, count (distinct t.id), count (distinct t2.id)
from t
left join t2 on t.id = t2.id
where platform = ' Test4'
group by platform
/
PLATF COUNT (DISTINCTT.ID) COUNT (DISTINCTT2.ID)
----- ------------------- --------------------
Test4 8 3

I so understood that is necessary 7 instead of 5
.....
stax

13

Re: on unique entrance

-=Koba =-;
Count separately and  to
ps
On  the index is?
......
stax

14

Re: on unique entrance

Yes so it is exact

/* Formatted on 10/12/2017 OF 4:14:30 PM (QP5 v5.313) */
WITH
t (id, operation_time, platform, USER_ID) AS
(SELECT 1, ' 2016-01 ', ' Test1 ', 1 FROM DUAL
UNION ALL
SELECT 2, ' 2016-02 ', ' Test1 ', 2 FROM DUAL
UNION ALL
SELECT 3, ' 2016-01 ', ' Test2 ', 1 FROM DUAL
UNION ALL
SELECT 4, ' 2016-02 ', ' Test2 ', 2 FROM DUAL
UNION ALL
SELECT 5, ' 2016-01 ', ' Test3 ', 2 FROM DUAL
UNION ALL
SELECT 6, ' 2016-02 ', ' Test3 ', 2 FROM DUAL
UNION ALL
SELECT 7, ' 2016-03 ', ' Test3 ', 1 FROM DUAL
UNION ALL
SELECT 8, ' 2016-04 ', ' Test3 ', 3 FROM DUAL
UNION ALL
SELECT 9, ' 2016-01 ', ' Test4 ', 1 FROM DUAL
UNION ALL
SELECT 10, ' 2016-02 ', ' Test4 ', 2 FROM DUAL
UNION ALL
SELECT 11, ' 2016-03 ', ' Test4 ', 3 FROM DUAL
UNION ALL
SELECT 12, ' 2016-04 ', ' Test4 ', 4 FROM DUAL
UNION ALL
SELECT 13, ' 2016-05 ', ' Test4 ', 5 FROM DUAL
UNION ALL
SELECT 14, ' 2016-06 ', ' Test4 ', 6 FROM DUAL)
SELECT *
FROM t

15

Re: on unique entrance

Stax wrote:

- =Koba =-;
Count separately and  to
ps
On  the index is?
......
stax

So it and the question how to check up that for the given period user_id except test4 is not present at other platforms, on join I will already connect them

16

Re: on unique entrance

-=Koba =-;
,
N passes on , not optimally

WITH
t (operation_time, platform, USER_ID) AS
(SELECT ' 2016-01 ', ' Test1 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test1 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test2 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test2 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test3 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test3 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-03 ', ' Test3 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-04 ', ' Test3 ', 3 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test4 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test4 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-03 ', ' Test4 ', 3 FROM DUAL
UNION ALL
SELECT ' 2016-04 ', ' Test4 ', 4 FROM DUAL
UNION ALL
SELECT ' 2016-05 ', ' Test4 ', 5 FROM DUAL
UNION ALL
SELECT ' 2016-05 ', ' Test4 ', 7 FROM DUAL
UNION ALL
SELECT ' 2016-05 ', ' Test4 ', 7 FROM DUAL
UNION ALL
SELECT ' 2016-05 ', ' Test4 ', 8 FROM DUAL
UNION ALL
SELECT ' 2016-06 ', ' Test4 ', 6 FROM DUAL)
,t4 as
(SELECT count (distinct user_id) c4 FROM t t1
WHERE platform IN (' Test4 ')
and not exists (select 1 from t t2 where t2.user_id=t1.user_id
and t2.platform IN (' Test1 ', ' Test2 ', ' Test3 '))
)
,p as (
SELECT operation;
SUM (a);
SUM (b);
SUM (c);
SUM (d)
FROM (SELECT *
FROM ( SELECT operation_time AS operation, platform, USER_ID
FROM t
WHERE platform IN (' Test1 ';
' Test2 ';
' Test3 ';
' Test4 ')
GROUP BY GROUPING SETS ((operation_time, PLATFORM, USER_ID), (PLATFORM, USER_ID)))
PIVOT
(COUNT (*) FOR platform IN (' Test1 ' AS A, ' Test2 ' AS B, ' Test3 ' AS A C, ' Test4 ' AS D)))
GROUP BY operation
)
select p. *, decode (operation, null, c4) c4 from p, t4
ORDER BY operation
/
SQL> /
OPERATI SUM (A) SUM (B) SUM (C) SUM (D) C4
------- ---------- ---------- ---------- ---------- ----------
2016-01 1 1 1 1
2016-02 1 1 1 1
2016-03 0 0 1 1
2016-04 0 0 1 1
2016-05 0 0 0 3
2016-06 0 0 0 1
2 2 3 8 5
7 rows selected.

.....
stax

17

Re: on unique entrance

[quote = - = Koba =-] So it and the question how to check up that for the given period user_id except test4 is not present at other platforms, on join I will already connect them

select platform, count (*) from (
select user_id, min (platform) platform
from t
group by user_id
having count (distinct platform) = 1
) group by platform
;