Home » SQL & PL/SQL » SQL & PL/SQL » 2 column values going inverse and how to display corresponding keys (11g)
2 column values going inverse and how to display corresponding keys [message #660726] Sat, 25 February 2017 20:05 Go to next message
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 #660727 is a reply to message #660726] Sat, 25 February 2017 20:40 Go to previous messageGo to next message
BlackSwan
Messages: 26201
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: 2 column values going inverse and how to display corresponding keys [message #660740 is a reply to message #660726] Sun, 26 February 2017 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

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 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
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 Go to previous messageGo to next message
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 #660771 is a reply to message #660764] Mon, 27 February 2017 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

But the query does not work.

Re: 2 column values going inverse and how to display corresponding keys [message #660788 is a reply to message #660760] Mon, 27 February 2017 05:34 Go to previous messageGo to next message
Stew Ashton
Messages: 4
Registered: February 2017
Junior Member
Based on Solomon's sample data:

With version 12c and MATCH_RECOGNIZE, a more concise solution. ^ means start of the partition, $ means end of the partition. I assume a customer with only one order does not count.

select * from tbl;
match_recognize(
  partition by cust_id order by order_date
  pattern(^a b+$)
  define b as units < prev(units)
);

Using LAG:
select cust_id from (
  select cust_id,
  case when lag(units,1,units+1) over(partition by cust_id order by order_date) > units
    then 1
  end is_descending
  from tbl
)
group by cust_id
having count(is_descending) = count(*) and count(*) > 1;
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
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 #660808 is a reply to message #660791] Mon, 27 February 2017 08:28 Go to previous messageGo to next message
manikandan23
Messages: 34
Registered: February 2017
Member
Thank you all. I am using the LAG version and my DB is on 11g.
Re: 2 column values going inverse and how to display corresponding keys [message #660819 is a reply to message #660808] Mon, 27 February 2017 09:01 Go to previous messageGo to next message
manikandan23
Messages: 34
Registered: February 2017
Member
I have one more question.

I want the faster method of returning the customers who purchased one and only one product id.

cust_id order_date prod_id units
C1 01-JAN-00 P1 10
C2 01-JAN-02 P2 15
C2 01-FEB-03 P2 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 P1 7
C1 01-MAY-07 P1 3
C5 01-AUG-07 P4 7
C5 02-AUG-08 P1 5

In the above example, my results should be

C1
C2
C3

Thanks,
Mani A




Re: 2 column values going inverse and how to display corresponding keys [message #660827 is a reply to message #660819] Mon, 27 February 2017 09:29 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sun, 26 February 2017 08:37

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: 2 column values going inverse and how to display corresponding keys [message #660829 is a reply to message #660819] Mon, 27 February 2017 09:30 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I want the faster method
Make your tests.

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 Go to previous message
Barbara Boehmer
Messages: 8890
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.
Previous Topic: Avoiding 2 Group By clauses
Next Topic: Execute Immediate with Dynamic From table
Goto Forum:
  


Current Time: Sat Oct 20 01:22:29 CDT 2018