2 column values going inverse and how to display corresponding keys [message #660726] |
Sat, 25 February 2017 20:05 |
|
manikandan23
Messages: 34 Registered: February 2017
|
Member |
|
|
I have following columns in my table with rows as shown below:
cust_id order_date prod_id units
C1 01-JAN-00 P1 10
C2 01-JAN-02 P2 15
C2 01-FEB-03 P3 16
C3 01-APR-02 P3 17
C4 01-APR-03 P1 20
C4 01-JAN-06 P2 2
C4 05-AUG-07 P5 1
C1 01-MAY-06 P5 7
C1 01-MAY-07 P3 3
Here, how do I write a SQL statement which can generate the list of customers whose total ordered units is consistently lesser than the previous order. As in Total Qty purchased in the nth order is less than Total Qty purchased in n-1 th order, and the next previous order is also less. Another way to say it, list the customers buy fewer units as time goes forward.
This has to be an SQL statement and not a procedure.
Please help. Thanks,
[mod-edit: code tags added by bb; next time please add them yourself]
[Updated on: Sat, 25 February 2017 21:54] by Moderator Report message to a moderator
|
|
|
|
|
Re: 2 column values going inverse and how to display corresponding keys [message #660744 is a reply to message #660726] |
Sun, 26 February 2017 02:03 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
[Edit: changing the example to take Solomon's TBL table below instead of EMP table to get a constant example along the topic.]
SQL> select * from tbl order by order_date ;
CUST_ID ORDER_DATE PROD_ID UNITS
---------- ------------------- --------- ----------
10 17/11/1980 00:00:00 PRESIDENT 5000
20 17/12/1980 00:00:00 CLERK 800
30 20/02/1981 00:00:00 SALESMAN 1600
30 22/02/1981 00:00:00 SALESMAN 1250
20 02/04/1981 00:00:00 MANAGER 2975
30 01/05/1981 00:00:00 MANAGER 2850
10 09/06/1981 00:00:00 MANAGER 2450
30 08/09/1981 00:00:00 SALESMAN 1500
30 28/09/1981 00:00:00 SALESMAN 1250
30 03/12/1981 00:00:00 CLERK 950
20 03/12/1981 00:00:00 ANALYST 3000
10 23/01/1982 00:00:00 CLERK 1300
20 19/04/1987 00:00:00 ANALYST 3000
20 23/05/1987 00:00:00 CLERK 1100
First you generate 2 columns: the rank is in a CUST_ID and the number of orders in this one:
SQL> break on cust_id skip 1 dup
SQL> select cust_id, order_date, prod_id, units,
2 row_number() over (partition by cust_id order by order_date) seq,
3 count(*) over (partition by cust_id) cnt
4 from tbl
5 order by cust_id, order_date;
CUST_ID ORDER_DATE PROD_ID UNITS SEQ CNT
---------- ------------------- --------- ---------- ---------- ----------
10 17/11/1980 00:00:00 PRESIDENT 5000 1 3
10 09/06/1981 00:00:00 MANAGER 2450 2 3
10 23/01/1982 00:00:00 CLERK 1300 3 3
20 17/12/1980 00:00:00 CLERK 800 1 5
20 02/04/1981 00:00:00 MANAGER 2975 2 5
20 03/12/1981 00:00:00 ANALYST 3000 3 5
20 19/04/1987 00:00:00 ANALYST 3000 4 5
20 23/05/1987 00:00:00 CLERK 1100 5 5
30 20/02/1981 00:00:00 SALESMAN 1600 1 6
30 22/02/1981 00:00:00 SALESMAN 1250 2 6
30 01/05/1981 00:00:00 MANAGER 2850 3 6
30 08/09/1981 00:00:00 SALESMAN 1500 4 6
30 28/09/1981 00:00:00 SALESMAN 1250 5 6
30 03/12/1981 00:00:00 CLERK 950 6 6
now you have to follow the hierarchy for each CUST_ID stopping when the previous UNITS is lower than the current one and checking if when you stop you are at the last order of the customer:
SQL> with
2 data as (
3 select cust_id, order_date, prod_id, units,
4 row_number() over (partition by cust_id order by order_date) seq,
5 count(*) over (partition by cust_id) cnt
6 from tbl
7 )
8 select cust_id
9 from data
10 where seq = cnt
11 connect by prior cust_id = cust_id
12 and prior seq = seq - 1
13 and prior units > units
14 start with seq = 1
15 order by cust_id
16 /
CUST_ID
----------
10
This is only one way, there are many others.
[Updated on: Mon, 27 February 2017 07:29] Report message to a moderator
|
|
|
Re: 2 column values going inverse and how to display corresponding keys [message #660749 is a reply to message #660744] |
Sun, 26 February 2017 04:03 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
[Edit: changing the example to take Solomon's TBL table below instead of EMP table to get a constant example along the topic.]
Here's another way to do it using recursive query instead of hierarchical query:
SQL> with
2 data as (
3 select cust_id, order_date, prod_id, units,
4 row_number() over (partition by cust_id order by order_date) seq,
5 count(*) over (partition by cust_id) cnt
6 from tbl
7 ),
8 run (cust_id, units, seq, cnt) as (
9 select cust_id, units, seq, cnt
10 from data
11 where seq = 1
12 union all
13 select d.cust_id, d.units, d.seq, d.cnt
14 from run r, data d
15 where d.cust_id = r.cust_id
16 and d.seq = r.seq + 1
17 and d.units < r.units
18 )
19 select cust_id
20 from run
21 where seq = cnt
22 /
CUST_ID
----------
10
[Updated on: Mon, 27 February 2017 07:31] Report message to a moderator
|
|
|
Re: 2 column values going inverse and how to display corresponding keys [message #660760 is a reply to message #660726] |
Sun, 26 February 2017 06:38 |
Solomon Yakobson
Messages: 3285 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Oracle 12C match recognize solution:
drop table tbl purge
/
create table tbl
as
select deptno cust_id,
hiredate order_date,
job prod_id,
sal units
from emp
/
update tbl
set order_date = date '1980-11-17'
where prod_id = 'PRESIDENT'
/
commit
/
select *
from tbl
order by cust_id,
order_date
/
CUST_ID ORDER_DAT PROD_ID UNITS
---------- --------- --------- ----------
10 17-NOV-80 PRESIDENT 5000
10 09-JUN-81 MANAGER 2450
10 23-JAN-82 CLERK 1300
20 17-DEC-80 CLERK 800
20 02-APR-81 MANAGER 2975
20 03-DEC-81 ANALYST 3000
20 19-APR-87 ANALYST 3000
20 23-MAY-87 CLERK 1100
30 20-FEB-81 SALESMAN 1600
30 22-FEB-81 SALESMAN 1250
30 01-MAY-81 MANAGER 2850
CUST_ID ORDER_DAT PROD_ID UNITS
---------- --------- --------- ----------
30 08-SEP-81 SALESMAN 1500
30 28-SEP-81 SALESMAN 1250
30 03-DEC-81 CLERK 950
14 rows selected.
select cust_id
from tbl
match_recognize(
partition by cust_id
order by order_date
measures
up_or_flat.units as up_or_flat_units
pattern(
up_or_flat* down+ up_or_flat*
)
define
down as units > next(units) and match_number() = 1,
up_or_flat as units <= next(units)
)
where up_or_flat_units is null
/
CUST_ID
----------
10
SQL>
SY.
|
|
|
Re: 2 column values going inverse and how to display corresponding keys [message #660764 is a reply to message #660760] |
Sun, 26 February 2017 17:08 |
|
manikandan23
Messages: 34 Registered: February 2017
|
Member |
|
|
Thanks guys!
I guess I did it just with LAG function. See below:
SELECT DISTINCT CUSTOMER_ID
FROM
(SELECT
CUSTOMER_ID,
ORDER_ID,
QUANTITY,
ORDER_DATE,
LAG(ORDER_DATE,1) OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_ID, ORDER_DATE) AS PREV_ORDER_DATE,
LAG(QUANTITY,1) OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_ID, QUANTITY) AS PREV_QTY
FROM
ORDERS
) A
WHERE
A.ORDER_DATE > NVL(A.PREV_ORDER_DATE, '01-JAN-1950')
AND A.QUANTITY < NVL(A.PREV_QTY,0)
*BlackSwan added {code} tags. Please do so yourself in the future.
How to use {code} tags and make your code easier to read
[Updated on: Sun, 26 February 2017 19:13] by Moderator Report message to a moderator
|
|
|
|
|
Re: 2 column values going inverse and how to display corresponding keys [message #660791 is a reply to message #660788] |
Mon, 27 February 2017 05:51 |
Solomon Yakobson
Messages: 3285 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Works for me:
SQL> select *
2 from tbl
3 order by cust_id,
4 order_date
5 /
CUST_ID ORDER_DAT PROD_ID UNITS
---------- --------- --------- ----------
10 17-NOV-80 PRESIDENT 5000
10 09-JUN-81 MANAGER 2450
10 23-JAN-82 CLERK 1300
20 17-DEC-80 CLERK 800
20 02-APR-81 MANAGER 2975
20 03-DEC-81 ANALYST 3000
20 09-DEC-82 ANALYST 3000
20 12-JAN-83 CLERK 1100
30 20-FEB-81 SALESMAN 1600
30 22-FEB-81 SALESMAN 1250
30 01-MAY-81 MANAGER 2850
30 08-SEP-81 SALESMAN 1500
30 28-SEP-81 SALESMAN 1250
30 03-DEC-81 CLERK 950
14 rows selected.
SQL> select cust_id from (
2 select cust_id,
3 case when lag(units,1,units+1) over(partition by cust_id order by order_date) > units
4 then 1
5 end is_descending
6 from tbl
7 )
8 group by cust_id
9 having count(is_descending) = count(*) and count(*) > 1;
CUST_ID
----------
10
SQL>
SY.
|
|
|
|
|
|
|
Re: 2 column values going inverse and how to display corresponding keys [message #660865 is a reply to message #660819] |
Mon, 27 February 2017 16:13 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl_12.1.0.2.0> SELECT * FROM test_tab
2 /
CUST_ID ORDER_DATE PROD_ID UNITS
------- --------------- ------- ----------
C1 Sat 01-Jan-2000 P1 10
C2 Tue 01-Jan-2002 P2 15
C2 Sat 01-Feb-2003 P2 16
C3 Mon 01-Apr-2002 P3 17
C4 Tue 01-Apr-2003 P1 20
C4 Sun 01-Jan-2006 P2 2
C4 Sun 05-Aug-2007 P5 1
C1 Mon 01-May-2006 P1 7
C1 Tue 01-May-2007 P1 3
C5 Wed 01-Aug-2007 P4 7
C5 Fri 02-May-2008 P1 5
11 rows selected.
SCOTT@orcl_12.1.0.2.0> SELECT cust_id
2 FROM test_tab
3 GROUP BY cust_id
4 HAVING COUNT (DISTINCT prod_id) = 1
5 ORDER BY cust_id
6 /
CUST_ID
-------
C1
C2
C3
3 rows selected.
|
|
|