-
1. Re: Sql
Hans Steijntjes Feb 23, 2018 7:25 AM (in response to Oracle_Monkey)select t1.*
from test_table t1
where not exists ( select t2.t_id
from test_table t2
where t2.cust_id = t1.cust_id
and t2.t_id != t1.t_id
and extract(month from t2.trans_dt) = 10
)
and extract(month from t1.trans_dt) = 9
-
2. Re: Sql
Manik Feb 23, 2018 8:06 AM (in response to Oracle_Monkey)Try this (11.2.0.3) with extended test cases.. Just used your dataset.
select cust_id from test_table having count ( case when to_char ( trans_dt, 'MM' ) = 9 then 1 end ) = 1 and count ( case when to_char ( trans_dt, 'MM' ) = 10 then 1 end ) = 0 group by cust_id;
-
3. Re: Sql
BrunoVroman Feb 23, 2018 8:01 AM (in response to Oracle_Monkey)Hello Oracle_Monkey,
an "easy to understand" way: a) list September customers b) list October customers c) result is "a minus b"
WITH sep AS
( SELECT t1.cust_id
FROM test_table t1
WHERE EXISTS( SELECT NULL
FROM test_table t2
WHERE t2.trans_dt >= DATE '2017-09-01'
AND t2.trans_dt < DATE '2017-10-01'
AND t2.cust_id = t1.cust_id
)
), oct AS
( SELECT t1.cust_id
FROM test_table t1
WHERE EXISTS( SELECT NULL
FROM test_table t2
WHERE t2.trans_dt >= DATE '2017-10-01'
AND t2.trans_dt < DATE '2017-11-01'
AND t2.cust_id = t1.cust_id
)
)
SELECT sep.cust_id
FROM sep
MINUS
SELECT oct.cust_id
FROM oct
;
Best regards,
Bruno Vroman.
-
4. Re: Sql
mNem Feb 23, 2018 8:10 AM (in response to Oracle_Monkey)SQL> select * from test_table
match_recognize
(
partition by cust_id
order by trans_dt
pattern( x+ y* $)
define
x as extract(month from trans_dt) = 9,
y as extract(month from trans_dt) != 10
);
CUST_ID
----------
200
600
-
5. Re: Sql
3367698 Feb 23, 2018 10:20 AM (in response to Oracle_Monkey)Hi ,
select distinct cust_id from test_table where extract(month from trans_dt) = 9
and cust_id not in (select cust_id from test_table where extract(month from trans_dt) = 10 );
CUST_ID 200 600 -
6. Re: Sql
Frank Kulash Feb 23, 2018 10:50 AM (in response to Oracle_Monkey)Hi,
Oracle_Monkey wrote:
Hello ,
I need the customer ids who have done Transaction in September but not in October
...
You didn't say anything about the year. Do you mean the customer must have at least 1 transaction in September of some year, and must not have any transactions in October of any year? If so:
SELECT cust_id
FROM test_table
WHERE TO_CHAR (trans_dt, 'MM') IN ('09', '10')
GROUP BY cust_id
HAVING MAX (TO_CHAR (trans_dt, 'MM')) = '09'
ORDER BY cust_id;If you want customers that had a transaction in September of 2017, but not in October of 2017, then:
SELECT cust_id
FROM test_table
WHERE trans_dt >= TO_DATE ('01-SEP-2017', 'DD-MON-YYYY')
AND trans_dt < TO_DATE ('01-NOV-2017', 'DD-MON-YYYY')
GROUP BY cust_id
HAVING MAX (trans_dt) < TO_DATE ('01-OCT-2017', 'DD-MON-YYYY')
ORDER BY cust_id;Always use 4 digits for the year.
-
7. Re: Sql
mNem Feb 23, 2018 11:14 AM (in response to Oracle_Monkey)If you need to find for any given year, some rows for SEPT and no rows for OCT, then partitioning by year will get you there.
SQL> with t (id, cust_id, trans_dt) as
(
select 1,100,to_date('12-SEP-17','DD-MON-RR') from dual union all
select 2,100,to_date('12-OCT-17','DD-MON-RR') from dual union all
select 3,200,to_date('12-SEP-17','DD-MON-RR') from dual union all
select 10,200,to_date('12-OCT-18','DD-MON-RR') from dual union all
select 11,200,to_date('12-SEP-18','DD-MON-RR') from dual union all
select 12,200,to_date('12-MAY-16','DD-MON-RR') from dual union all
select 13,200,to_date('12-SEP-16','DD-MON-RR') from dual union all
select 4,300,to_date('12-OCT-17','DD-MON-RR') from dual union all
select 5,400,to_date('12-JAN-17','DD-MON-RR') from dual union all
select 6,500,to_date('12-OCT-17','DD-MON-RR') from dual union all
select 7,500,to_date('12-MAR-17','DD-MON-RR') from dual union all
select 8,600,to_date('12-SEP-17','DD-MON-RR') from dual union all
select 9,600,to_date('12-JUL-17','DD-MON-RR') from dual
)
select * from
(select t.*, extract(year from trans_dt) yr from t)
match_recognize
(
partition by cust_id, yr
order by trans_dt
pattern( x+ y* $)
define
x as extract(month from trans_dt) = 9,
y as extract(month from trans_dt) != 10
);
CUST_ID YR
---------- ----------
200 2016
200 2017
600 2017
if you need just the customer ids:
select distinct cust_id from
(select t.*, extract(year from trans_dt) yr from t)
match_recognize
(
partition by cust_id, yr
order by trans_dt
pattern( x+ y* $)
define
x as extract(month from trans_dt) = 9,
y as extract(month from trans_dt) != 10
);
CUST_ID
----------
200
600
-
8. Re: Sql
Jonathan Lewis Feb 23, 2018 12:07 PM (in response to BrunoVroman)Bruno,
Nice to see the MINUS, but the following seems likely to be more efficient:
SQL> l
1 select cust_id from test_table where trunc(trans_dt,'MM') = to_date('01-Sep-2017')
2 minus
3 select cust_id from test_table where trunc(trans_dt,'MM') = to_date('01-Oct-2017')
4*
SQL> /
CUST_ID
----------
200
600
2 rows selected.
Regards
Jonathan Lewis
(Assuming the OP is thinking only of the most recent Sep/Oct pair of consecutive months - but it seems a little unlikely that the target is customer who have bought something in at least one september in the past but have never bought anything in an october).
-
9. Re: Sql
BrunoVroman Feb 23, 2018 12:17 PM (in response to Jonathan Lewis)Thank you Jonathan.
I'm aware that my way is not the more efficient (by far) but sometimes (often) I prefer a solution that is not the best but is easy to understand (and adapted to the level of the original question, after all the actual "customer").
For example the answer "4." of mNem with MATCH_RECOGNIZE is most probably more efficient, but I will spend more time trying to figure out the meaning of <<pattern( x+ y* $)>> than the overhead of CPU or IO that my version will put on database ;-)
Best regards,
Bruno
-
10. Re: Sql
Jonathan Lewis Feb 23, 2018 12:41 PM (in response to BrunoVroman)Bruno.Vroman wrote:
I'm aware that my way is not the more efficient (by far) but sometimes (often) I prefer a solution that is not the best but is easy to understand
For example the answer "4." of mNem with MATCH_RECOGNIZE is most probably more efficient, but I will spend more time trying to figure out the meaning of <<pattern( x+ y* $)>> than the overhead of CPU or IO that my version will put on database ;-)
Totally agree with the principle - human efficiency should always be part of the whole efficiency equation. Easy to understand, easy for the next person to modify (without introducing errors) is often worth a little extra CPU or I/O time.
I'm thinking about #4 as well - going to have to check the manuals for the interpretation rather than just making the "obvious" guess. It's clearly a winner from being a one-pass read of the table rather than two-pass, but it's not what Oracle would label as "DETERMINISTIC FINITE AUTO" so there may be excessive CPU costs to worry about depending on the data volume and pattern. It's possible that a variation on Frank Kulash simple aggregation trick at #6 would be the most CPU efficient.
Regards
Jonathan Lewis
-
11. Re: Sql
mNem Feb 23, 2018 1:13 PM (in response to BrunoVroman)Hi Bruno,
Here is what I could come up with to explain the reply #4.
partition by cust_id order by trans_id:
for each logical group of cust_ids, ordered by trans_dt in ascending order,
x+
find one or more rows that satisfies the rule DEFINEd for x (month of trans_dt is 9)
immediately followed by
y*
find zero or more rows that satisfies the rule DEFINEd for y (month of trans_dt is NOT 10)
immediately followed by
$
anchor for end of rows for the partition
Please note that any row that may occur prior to matching the starting pattern variable x was ignored since our pattern is anchored to the end of rows (shown below for cust_id 700).
Here is a more verbose version that shows the matches relative to the rest of the pattern matches (including unmatched rows).
CLASSIFIER() is a built in function to show the matched pattern variable.
SQL> with t (id, cust_id, trans_dt) as
(
select 1,100,to_date('12-SEP-17','DD-MON-RR') from dual union all
select 2,100,to_date('12-OCT-17','DD-MON-RR') from dual union all
select 3,200,to_date('12-SEP-17','DD-MON-RR') from dual union all
select 4,300,to_date('12-OCT-17','DD-MON-RR') from dual union all
select 5,400,to_date('12-JAN-17','DD-MON-RR') from dual union all
select 6,500,to_date('12-OCT-17','DD-MON-RR') from dual union all
select 7,500,to_date('12-MAR-17','DD-MON-RR') from dual union all
select 8,600,to_date('12-SEP-17','DD-MON-RR') from dual union all
select 9,600,to_date('12-JUL-17','DD-MON-RR') from dual union all
select 14,700,to_date('12-JUN-17','DD-MON-RR') from dual union all
select 15,700,to_date('12-MAY-17','DD-MON-RR') from dual union all
select 10,700,to_date('12-SEP-17','DD-MON-RR') from dual union all
select 11,700,to_date('12-NOV-17','DD-MON-RR') from dual union all
select 12,700,to_date('10-DEC-17','DD-MON-RR') from dual union all
select 13,700,to_date('12-DEC-17','DD-MON-RR') from dual
)
select * from t
match_recognize
(
partition by cust_id
order by trans_dt
measures classifier() cl
all rows per match with unmatched rows
pattern( x+ y* $)
define
x as extract(month from trans_dt) = 9,
y as extract(month from trans_dt) != 10
);
CUST_ID TRANS_DT CL ID
---------- --------------- ---------- ----------
100 12-Sep-2017 1
100 12-Oct-2017 2
200 12-Sep-2017 X 3
300 12-Oct-2017 4
400 12-Jan-2017 5
500 12-Mar-2017 7
500 12-Oct-2017 6
600 12-Jul-2017 9
600 12-Sep-2017 X 8
700 12-May-2017 15
700 12-Jun-2017 14
700 12-Sep-2017 X 10
700 12-Nov-2017 Y 11
700 10-Dec-2017 Y 12
700 12-Dec-2017 Y 13
Hope it explains.
-
12. Re: Sql
BrunoVroman Feb 23, 2018 2:28 PM (in response to mNem)Dear mNem,
I hope you didn't feel offended by my remark, it was not at all "against you". Thank you for the explanations. My point was that all components of SQL are not equally simple... And when we read a question (this one or others), it can appear that the OP has not yet much experience, and if he/she is +/- a beginner, providing a complex answer will simply "fly over his/her head" even if it is "correct". For example if an OP shows that he has difficulties in understanding a simple JOIN between "emp" and "dept", the answer to the question should not use analytic functions.
Each of us has his/her own learning curve, and it is OK when we receive something that will push us up a little bit (like your explanations to me, thanks again), but the point is missed if we receive something that is too complex for us.
Hence when possible a simple answer can be good even if "gurus" can play together and propose many other "better" answers (sometimes we see this in the forums: a beginner asking a beginner's question followed by some "Ping-Pong" of gurus showing high level skills)
Have a nice day, as well as other readers (especially Oracle_Monkey, forgive us of having a bit "hijacked" your thread)
Bruno
-
13. Re: Sql
Rajeshwaran, Jeyabal Feb 28, 2018 11:36 AM (in response to Oracle_Monkey)Just aggregate them and apply filters on top of the aggregated values.
demo@ORA12C> select * from t;
T_ID CUST_ID TRANS_DT
---------- ---------- -----------
1 100 12-SEP-2017
2 100 12-OCT-2017
3 200 12-SEP-2017
4 300 12-OCT-2017
5 400 12-JAN-2017
6 500 12-OCT-2017
7 500 12-MAR-2017
8 600 12-SEP-2017
9 600 12-JUL-2017
9 rows selected.
demo@ORA12C> select cust_id
2 from t
3 group by cust_id
4 having count( case when trans_dt >= to_date('01-sep-2017')
5 and trans_dt < to_date('01-oct-2017')
6 then 1 end ) >0
7 and count( case when trans_dt >= to_date('01-oct-2017')
8 and trans_dt < to_date('01-nov-2017')
9 then 1 end ) = 0
10 /
CUST_ID
----------
600
200
demo@ORA12C>
-
14. Re: Sql
John_K Feb 28, 2018 11:53 AM (in response to Rajeshwaran, Jeyabal)So pretty much exactly what was suggested in reply 2 of this thread then?