Home » SQL & PL/SQL » SQL & PL/SQL » Get customer with GREATEST amount of purchases
Get customer with GREATEST amount of purchases [message #686880] Sun, 29 January 2023 11:50 Go to next message
Unclefool
Messages: 82
Registered: August 2021
Member

I'm trying to use rank() to find the customer with the greatest amount of purchases.

As you can see I have a query below, which gets all the customers. How can I fix my attempt to get only one row where customer_id =3?


ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY  HH24:MI:SS.FF';

 ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

CREATE TABLE customers 
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'Faith', 'Mazzarone' FROM DUAL UNION ALL
SELECT 2, 'Lisa', 'Saladino' FROM DUAL UNION ALL
SELECT 3, 'Micheal', 'Palmice' FROM DUAL UNION ALL
SELECT 4, 'Jerry', 'Torchiano' FROM DUAL;

CREATE TABLE items 
(PRODUCT_ID, PRODUCT_NAME, PRICE) AS
SELECT 100, 'Black Shoes', 79.99 FROM DUAL UNION ALL
SELECT 101, 'Brown Pants', 111.99 FROM DUAL UNION ALL
SELECT 102, 'White Shirt', 10.99 FROM DUAL;

CREATE TABLE purchases(
    PURCHASE_ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
    CUSTOMER_ID NUMBER, 
    PRODUCT_ID NUMBER, 
    QUANTITY NUMBER, 
   PURCHASE_DATE TIMESTAMP
);

INSERT  INTO purchases
(CUSTOMER_ID, PRODUCT_ID, QUANTITY, PURCHASE_DATE) 
SELECT 1, 101, 3, TIMESTAMP'2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 1, 100, 1, TIMESTAMP '2022-10-12 19:04:18' FROM DUAL UNION ALL
SELECT 2, 101,1, TIMESTAMP '2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 2, 101, 3, TIMESTAMP '2022-10-17 19:34:58' FROM DUAL UNION ALL
SELECT 2, 102, 3,TIMESTAMP '2022-12-06 11:41:25' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM  dual CONNECT BY  LEVEL <= 6 UNION ALL
SELECT 2, 102, 3,TIMESTAMP '2022-12-26 11:41:25' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM  dual CONNECT BY  LEVEL <= 6 UNION ALL
SELECT 3, 101,1, TIMESTAMP '2022-12-21 09:54:48' FROM DUAL UNION ALL
SELECT 3, 102,1, TIMESTAMP '2022-12-27 19:04:18' FROM DUAL UNION ALL
SELECT 3, 102, 4,TIMESTAMP '2022-12-22 21:44:35' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM    dual
CONNECT BY  LEVEL <= 15 UNION ALL 
SELECT 3, 101,1, TIMESTAMP '2022-12-11 09:54:48' FROM DUAL UNION ALL
SELECT 3, 102,1, TIMESTAMP '2022-12-17 19:04:18' FROM DUAL UNION ALL
SELECT 3, 102, 4,TIMESTAMP '2022-12-12 21:44:35' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM    dual
CONNECT BY  LEVEL <= 5;

ALTER TABLE customers 
ADD CONSTRAINT customers_pk PRIMARY KEY (customer_id);

ALTER TABLE items 
ADD CONSTRAINT items_pk PRIMARY KEY (product_id);

ALTER TABLE purchases 
ADD CONSTRAINT purchases_pk PRIMARY KEY (purchase_id);

ALTER TABLE purchases ADD CONSTRAINT customers_fk FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

ALTER TABLE purchases ADD CONSTRAINT items_fk FOREIGN KEY (PRODUCT_ID) REFERENCES items(product_id);


/* total purchases for each customer */

SELECT
     c.customer_id,
     c.first_name,
     c.last_name, 
SUM (p.quantity * i.price)		AS total_amt
FROM 	  purchases  p
JOIN 	  customers  c  ON  p.customer_id = c.customer_id
JOIN 	  items      i 	ON  p.product_id  = i.product_id
 GROUP BY
     c.customer_id,
     c.first_name,
     c.last_name

CUSTOMER_ID	FIRST_NAME	LAST_NAME	TOTAL_AMT
1	Faith	Mazzarone	415.96
2	Lisa	Saladino	843.6
3	Micheal	Palmice	1125.16

/* not working */
with cte as         
(SELECT
     c.customer_id,
     c.first_name,
     c.last_name, 
rank() over
              (partition by p.customer_id
               order by sum(p.quantity * i.price) desc) rnk,
SUM (p.quantity * i.price)		AS total_amt
FROM 	  purchases  p
JOIN 	  customers  c  ON  p.customer_id = c.customer_id
JOIN 	  items      i 	ON  p.product_id  = i.product_id
 GROUP BY
     c.customer_id,
     c.first_name,
     c.last_name
)
SELECT
     customer_id,
     first_name,
     last_name, 
    total_amt
FROM cte
where rnk = 1;

Re: Get customer with GREATEST amount of purchases [message #686881 is a reply to message #686880] Sun, 29 January 2023 12:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I'd of course could give a solution to your problem but as you NEVER gives feedback nor thank who spend time to help you I won't do it.

Re: Get customer with GREATEST amount of purchases [message #686882 is a reply to message #686881] Sun, 29 January 2023 12:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_12.1.0.2.0> with cte as
  2    (SELECT
  3  	    c.customer_id,
  4  	    c.first_name,
  5  	    c.last_name,
  6    SUM (p.quantity * i.price)	     AS total_amt
  7    FROM    purchases  p
  8    JOIN    customers  c  ON  p.customer_id = c.customer_id
  9    JOIN    items	  i  ON  p.product_id  = i.product_id
 10    GROUP BY
 11  	    c.customer_id,
 12  	    c.first_name,
 13  	    c.last_name),
 14  cte2 as
 15    (SELECT customer_id,
 16  	       first_name,
 17  	       last_name,
 18  	       total_amt,
 19  	       RANK() OVER (ORDER BY total_amt DESC) rnk
 20  	FROM   cte)
 21  SELECT *
 22  FROM   cte2
 23  WHERE  rnk = 1
 24  /

CUSTOMER_ID FIRST_N LAST_NAME  TOTAL_AMT        RNK
----------- ------- --------- ---------- ----------
          3 Micheal Palmice      1125.16          1

1 row selected.
Re: Get customer with GREATEST amount of purchases [message #686883 is a reply to message #686882] Sun, 29 January 2023 13:07 Go to previous messageGo to next message
Unclefool
Messages: 82
Registered: August 2021
Member
Thanks. So the trick was using 2 cte
Re: Get customer with GREATEST amount of purchases [message #686884 is a reply to message #686883] Sun, 29 January 2023 13:37 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Actually, you could do it with one cte. You just need to remove the partition clause from the rank function, as shown below.

COTT@orcl_12.1.0.2.0> with cte as
  2  (SELECT
  3  	  c.customer_id,
  4  	  c.first_name,
  5  	  c.last_name,
  6  rank() over
  7  		   (order by sum(p.quantity * i.price) desc) rnk,
  8  SUM (p.quantity * i.price) 	     AS total_amt
  9  FROM      purchases  p
 10  JOIN      customers  c  ON  p.customer_id = c.customer_id
 11  JOIN      items	  i  ON  p.product_id  = i.product_id
 12   GROUP BY
 13  	  c.customer_id,
 14  	  c.first_name,
 15  	  c.last_name
 16  )
 17  SELECT
 18  	  customer_id,
 19  	  first_name,
 20  	  last_name,
 21  	 total_amt
 22  FROM cte
 23  where rnk = 1
 24  /

CUSTOMER_ID FIRST_N LAST_NAME  TOTAL_AMT
----------- ------- --------- ----------
          3 Micheal Palmice      1125.16

1 row selected.
Re: Get customer with GREATEST amount of purchases [message #686885 is a reply to message #686884] Sun, 29 January 2023 13:55 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
SELECT  C.*,
        SUM(I.PRICE * P.QUANTITY) TOTAL_AMT
  FROM  CUSTOMERS C,
        ITEMS I,
        PURCHASES P
  WHERE C.CUSTOMER_ID = P.CUSTOMER_ID
    AND I.PRODUCT_ID = P.PRODUCT_ID
  GROUP BY C.CUSTOMER_ID,
           C.FIRST_NAME,
           C.LAST_NAME
  ORDER BY RANK() OVER(ORDER BY TOTAL_AMT DESC)
  FETCH FIRST 1 ROW WITH TIES
/

CUSTOMER_ID FIRST_N LAST_NAME  TOTAL_AMT
----------- ------- --------- ----------
          3 Micheal Palmice      1125.16

SQL>
SY.
Re: Get customer with GREATEST amount of purchases [message #686886 is a reply to message #686885] Sun, 29 January 2023 13:59 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
And if you want just any one top customer even if there are several customers with same highest amount:

SELECT  C.*,
        SUM(I.PRICE * P.QUANTITY) TOTAL_AMT
  FROM  CUSTOMERS C,
        ITEMS I,
        PURCHASES P
  WHERE C.CUSTOMER_ID = P.CUSTOMER_ID
    AND I.PRODUCT_ID = P.PRODUCT_ID
  GROUP BY C.CUSTOMER_ID,
           C.FIRST_NAME,
           C.LAST_NAME
  ORDER BY TOTAL_AMT DESC
  FETCH FIRST 1 ROW ONLY
/

CUSTOMER_ID FIRST_N LAST_NAME  TOTAL_AMT
----------- ------- --------- ----------
          3 Micheal Palmice      1125.16

SQL>
SY.
Re: Get customer with GREATEST amount of purchases [message #686887 is a reply to message #686885] Sun, 29 January 2023 14:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Solomon,

Very nice! That's much simpler.
Re: Get customer with GREATEST amount of purchases [message #686888 is a reply to message #686887] Sun, 29 January 2023 14:43 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Actually, I used RANK since OP was trying to use it. There is no need for RANK, just:

SELECT  C.*,
        SUM(I.PRICE * P.QUANTITY) TOTAL_AMT
  FROM  CUSTOMERS C,
        ITEMS I,
        PURCHASES P
  WHERE C.CUSTOMER_ID = P.CUSTOMER_ID
    AND I.PRODUCT_ID = P.PRODUCT_ID
  GROUP BY C.CUSTOMER_ID,
           C.FIRST_NAME,
           C.LAST_NAME
  ORDER BY TOTAL_AMT DESC
  FETCH FIRST 1 ROW WITH TIES
/
SY.
Re: Get customer with GREATEST amount of purchases [message #686889 is a reply to message #686888] Sun, 29 January 2023 17:09 Go to previous messageGo to next message
mathguy
Messages: 106
Registered: January 2023
Senior Member
"There is no need for RANK" is only an illusion. FETCH FIRST ... is just syntactic sugar, it is implemented with RANK() or ROW_NUMBER() depending on the "WITH TIES" vs. "ONLY" option.
Re: Get customer with GREATEST amount of purchases [message #686897 is a reply to message #686889] Mon, 30 January 2023 05:02 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
mathguy wrote on Sun, 29 January 2023 18:09
"There is no need for RANK" is only an illusion. FETCH FIRST ... is just syntactic sugar, it is implemented with RANK() or ROW_NUMBER() depending on the "WITH TIES" vs. "ONLY" option.
Yes in terms of syntactic sugar but no in terms of efficiency:

variable c clob
begin
    dbms_utility.expand_sql_text('
SELECT  C.*,
        SUM(I.PRICE * P.QUANTITY) TOTAL_AMT
  FROM  CUSTOMERS C,
        ITEMS I,
        PURCHASES P
  WHERE C.CUSTOMER_ID = P.CUSTOMER_ID
    AND I.PRODUCT_ID = P.PRODUCT_ID
  GROUP BY C.CUSTOMER_ID,
           C.FIRST_NAME,
           C.LAST_NAME
  ORDER BY RANK() OVER(ORDER BY TOTAL_AMT DESC)
  FETCH FIRST 1 ROW WITH TIES',:c);
end;
/

PL/SQL procedure successfully completed.

SQL> print c

C
--------------------------------------------------------------------------------
SELECT  "A1"."CUSTOMER_ID" "CUSTOMER_ID",
        "A1"."FIRST_NAME" "FIRST_NAME",
        "A1"."LAST_NAME" "LAST_NAME",
        "A1"."TOTAL_AMT" "TOTAL_AMT"
  FROM  (
         SELECT  "A2"."CUSTOMER_ID" "CUSTOMER_ID",
                 "A2"."FIRST_NAME" "FIRST_NAME",
                 "A2"."LAST_NAME" "LAST_NAME",
                 "A2"."TOTAL_AMT" "TOTAL_AMT",
                 "A2"."rowlimit_$_0" "rowlimit_$_0",
                 RANK() OVER (ORDER BY "A2"."rowlimit_$_0") "rowlimit_$$_rank"
           FROM  (
                  SELECT  "A5"."CUSTOMER_ID" "CUSTOMER_ID",
                          "A5"."FIRST_NAME" "FIRST_NAME",
                          "A5"."LAST_NAME" "LAST_NAME",
                          SUM("A4"."PRICE"*"A3"."QUANTITY") "TOTAL_AMT",
                          RANK() OVER ( ORDER BY SUM("A4"."PRICE"*"A3"."QUANTITY") DESC ) "rowlimit_$_0"
                    FROM  "SCOTT"."CUSTOMERS" "A5",
                          "SCOTT"."ITEMS" "A4",
                          "SCOTT"."PURCHASES" "A3"
                    WHERE "A5"."CUSTOMER_ID"="A3"."CUSTOMER_ID"
                      AND "A4"."PRODUCT_ID"="A3"."PRODUCT_ID"
                    GROUP BY "A5"."CUSTOMER_ID",
                             "A5"."FIRST_NAME",
                             "A5"."LAST_NAME"
                 ) "A2"
        ) "A1"
  WHERE "A1"."rowlimit_$$_rank"<=1
  ORDER BY "A1"."rowlimit_$_0"

SQL>
And:

begin
    dbms_utility.expand_sql_text('
SELECT  C.*,
        SUM(I.PRICE * P.QUANTITY) TOTAL_AMT
  FROM  CUSTOMERS C,
        ITEMS I,
        PURCHASES P
  WHERE C.CUSTOMER_ID = P.CUSTOMER_ID
    AND I.PRODUCT_ID = P.PRODUCT_ID
  GROUP BY C.CUSTOMER_ID,
           C.FIRST_NAME,
           C.LAST_NAME
  ORDER BY TOTAL_AMT DESC
  FETCH FIRST 1 ROW WITH TIES',:c);
end;
/
SQL> /

PL/SQL procedure successfully completed.

SQL> print c

C
--------------------------------------------------------------------------------
SELECT  "A1"."CUSTOMER_ID" "CUSTOMER_ID",
        "A1"."FIRST_NAME" "FIRST_NAME",
        "A1"."LAST_NAME" "LAST_NAME",
        "A1"."TOTAL_AMT" "TOTAL_AMT"
  FROM  (
         SELECT  "A4"."CUSTOMER_ID" "CUSTOMER_ID",
                 "A4"."FIRST_NAME" "FIRST_NAME",
                 "A4"."LAST_NAME" "LAST_NAME",
                 SUM("A3"."PRICE"*"A2"."QUANTITY") "TOTAL_AMT",
                 SUM("A3"."PRICE"*"A2"."QUANTITY") "rowlimit_$_0",
                 RANK() OVER ( ORDER BY SUM("A3"."PRICE"*"A2"."QUANTITY") DESC ) "rowlimit_$$_rank"
           FROM  "SCOTT"."CUSTOMERS" "A4",
                 "SCOTT"."ITEMS" "A3",
                 "SCOTT"."PURCHASES" "A2"
           WHERE "A4"."CUSTOMER_ID"="A2"."CUSTOMER_ID"
            AND "A3"."PRODUCT_ID"="A2"."PRODUCT_ID"
            GROUP BY "A4"."CUSTOMER_ID",
                     "A4"."FIRST_NAME",
                     "A4"."LAST_NAME"
        ) "A1"
  WHERE "A1"."rowlimit_$$_rank"<=1
  ORDER BY "A1"."rowlimit_$_0" DESC

SQL>
As you can see rank (a.k.a. syntactic sugar) is calculated againts order by expression so Oracle ends up calculating rank of total amount and then rank of the rank while with orader by total amount it calculates rank of total amount only. Compare execution plans:

SELECT  C.*,
        SUM(I.PRICE * P.QUANTITY) TOTAL_AMT
  FROM  CUSTOMERS C,
        ITEMS I,
        PURCHASES P
  WHERE C.CUSTOMER_ID = P.CUSTOMER_ID
    AND I.PRODUCT_ID = P.PRODUCT_ID
  GROUP BY C.CUSTOMER_ID,
           C.FIRST_NAME,
           C.LAST_NAME
  ORDER BY RANK() OVER(ORDER BY TOTAL_AMT DESC)
  FETCH FIRST 1 ROW WITH TIES
/

CUSTOMER_ID FIRST_N LAST_NAME  TOTAL_AMT
----------- ------- --------- ----------
          3 Micheal Palmice      1125.16


Execution Plan
----------------------------------------------------------
Plan hash value: 1425103093

---------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |              |    24 |  1512 |    12  (34)| 00:00:01 |
|*  1 |  VIEW                              |              |    24 |  1512 |    12  (34)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK          |              |    24 |  1200 |    12  (34)| 00:00:01 |
|   3 |    VIEW                            |              |    24 |  1200 |    11  (28)| 00:00:01 |
|   4 |     WINDOW SORT                    |              |    24 |   888 |    11  (28)| 00:00:01 |
|   5 |      HASH GROUP BY                 |              |    24 |   888 |    11  (28)| 00:00:01 |
|*  6 |       HASH JOIN                    |              |    40 |  1480 |     9  (12)| 00:00:01 |
|   7 |        MERGE JOIN                  |              |    40 |  1120 |     6  (17)| 00:00:01 |
|   8 |         TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |     4 |    72 |     2   (0)| 00:00:01 |
|   9 |          INDEX FULL SCAN           | CUSTOMERS_PK |     4 |       |     1   (0)| 00:00:01 |
|* 10 |         SORT JOIN                  |              |    40 |   400 |     4  (25)| 00:00:01 |
|  11 |          TABLE ACCESS FULL         | PURCHASES    |    40 |   400 |     3   (0)| 00:00:01 |
|  12 |        TABLE ACCESS FULL           | ITEMS        |     3 |    27 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
And:

SELECT  C.*,
        SUM(I.PRICE * P.QUANTITY) TOTAL_AMT
  FROM  CUSTOMERS C,
        ITEMS I,
        PURCHASES P
  WHERE C.CUSTOMER_ID = P.CUSTOMER_ID
    AND I.PRODUCT_ID = P.PRODUCT_ID
  GROUP BY C.CUSTOMER_ID,
           C.FIRST_NAME,
           C.LAST_NAME
  ORDER BY TOTAL_AMT DESC
  FETCH FIRST 1 ROW WITH TIES
/

CUSTOMER_ID FIRST_N LAST_NAME  TOTAL_AMT
----------- ------- --------- ----------
          3 Micheal Palmice      1125.16


Execution Plan
----------------------------------------------------------
Plan hash value: 219398172

--------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |              |    24 |  1512 |    12  (34)| 00:00:01 |
|   1 |  SORT ORDER BY                    |              |    24 |  1512 |    12  (34)| 00:00:01 |
|*  2 |   VIEW                            |              |    24 |  1512 |    11  (28)| 00:00:01 |
|*  3 |    WINDOW SORT PUSHED RANK        |              |    24 |   888 |    11  (28)| 00:00:01 |
|   4 |     HASH GROUP BY                 |              |    24 |   888 |    11  (28)| 00:00:01 |
|*  5 |      HASH JOIN                    |              |    40 |  1480 |     9  (12)| 00:00:01 |
|   6 |       MERGE JOIN                  |              |    40 |  1120 |     6  (17)| 00:00:01 |
|   7 |        TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |     4 |    72 |     2   (0)| 00:00:01 |
|   8 |         INDEX FULL SCAN           | CUSTOMERS_PK |     4 |       |     1   (0)| 00:00:01 |
|*  9 |        SORT JOIN                  |              |    40 |   400 |     4  (25)| 00:00:01 |
|  10 |         TABLE ACCESS FULL         | PURCHASES    |    40 |   400 |     3   (0)| 00:00:01 |
|  11 |       TABLE ACCESS FULL           | ITEMS        |     3 |    27 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
As you can see orader by rank solution has extra sort (rank of the rank sort). It is always more efficient to use CTE and then where rank = 1 (or other rnak-based expression) comparing to using order by rank fetch. So Barbara's rank solution with cte is more writing but is more efficient comparing to order by rank fetch:

WITH CTE AS (
             SELECT  C.*,
                     SUM(I.PRICE * P.QUANTITY) TOTAL_AMT,
                     RANK() OVER(ORDER BY SUM(I.PRICE * P.QUANTITY) DESC) RNK
               FROM  CUSTOMERS C,
                     ITEMS I,
                     PURCHASES P
               WHERE C.CUSTOMER_ID = P.CUSTOMER_ID
                 AND I.PRODUCT_ID = P.PRODUCT_ID
               GROUP BY C.CUSTOMER_ID,
                        C.FIRST_NAME,
                        C.LAST_NAME
             )
SELECT  CUSTOMER_ID,
        FIRST_NAME,
        LAST_NAME,
        TOTAL_AMT
  FROM  CTE
  WHERE RNK = 1
/

CUSTOMER_ID FIRST_N LAST_NAME  TOTAL_AMT
----------- ------- --------- ----------
          3 Micheal Palmice      1125.16


Execution Plan
----------------------------------------------------------
Plan hash value: 3647462318

-------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |              |    24 |  1200 |    11  (28)| 00:00:01 |
|*  1 |  VIEW                            |              |    24 |  1200 |    11  (28)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK        |              |    24 |   888 |    11  (28)| 00:00:01 |
|   3 |    HASH GROUP BY                 |              |    24 |   888 |    11  (28)| 00:00:01 |
|*  4 |     HASH JOIN                    |              |    40 |  1480 |     9  (12)| 00:00:01 |
|   5 |      MERGE JOIN                  |              |    40 |  1120 |     6  (17)| 00:00:01 |
|   6 |       TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |     4 |    72 |     2   (0)| 00:00:01 |
|   7 |        INDEX FULL SCAN           | CUSTOMERS_PK |     4 |       |     1   (0)| 00:00:01 |
|*  8 |       SORT JOIN                  |              |    40 |   400 |     4  (25)| 00:00:01 |
|   9 |        TABLE ACCESS FULL         | PURCHASES    |    40 |   400 |     3   (0)| 00:00:01 |
|  10 |      TABLE ACCESS FULL           | ITEMS        |     3 |    27 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

SY.
Re: Get customer with GREATEST amount of purchases [message #686898 is a reply to message #686897] Mon, 30 January 2023 05:13 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Just to clarify "It is always more efficient to use CTE and then where rank = 1 (or other rnak-based expression) comparing to using order by rank fetch". I meant when fetching desired rows can't be accomplished without analytic rank/dense_rank/row_number/ntile, etc. Otherwise order by non-analytic expression fetch is less typing. For example, if we want customer with the greatest amount of purchases wor each year:

WITH CTE AS (
             SELECT  TO_CHAR(P.PURCHASE_DATE,'YYYY') YEAR,
                     C.*,
                     SUM(I.PRICE * P.QUANTITY) TOTAL_AMT,
                     RANK() OVER(PARTITION BY TO_CHAR(P.PURCHASE_DATE,'YYYY') ORDER BY SUM(I.PRICE * P.QUANTITY) DESC) RNK
               FROM  CUSTOMERS C,
                     ITEMS I,
                     PURCHASES P
               WHERE C.CUSTOMER_ID = P.CUSTOMER_ID
                 AND I.PRODUCT_ID = P.PRODUCT_ID
               GROUP BY TO_CHAR(P.PURCHASE_DATE,'YYYY'),
                        C.CUSTOMER_ID,
                        C.FIRST_NAME,
                        C.LAST_NAME
             )
SELECT  YEAR,
        CUSTOMER_ID,
        FIRST_NAME,
        LAST_NAME,
        TOTAL_AMT
  FROM  CTE
  WHERE RNK = 1
/

YEAR CUSTOMER_ID FIRST_N LAST_NAME  TOTAL_AMT
---- ----------- ------- --------- ----------
2022           2 Lisa    Saladino      711.72
2023           3 Micheal Palmice       483.56


Execution Plan
----------------------------------------------------------
Plan hash value: 3647462318

-------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |              |    40 |  2160 |    11  (28)| 00:00:01 |
|*  1 |  VIEW                            |              |    40 |  2160 |    11  (28)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK        |              |    40 |  1920 |    11  (28)| 00:00:01 |
|   3 |    HASH GROUP BY                 |              |    40 |  1920 |    11  (28)| 00:00:01 |
|*  4 |     HASH JOIN                    |              |    40 |  1920 |     9  (12)| 00:00:01 |
|   5 |      MERGE JOIN                  |              |    40 |  1560 |     6  (17)| 00:00:01 |
|   6 |       TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |     4 |    72 |     2   (0)| 00:00:01 |
|   7 |        INDEX FULL SCAN           | CUSTOMERS_PK |     4 |       |     1   (0)| 00:00:01 |
|*  8 |       SORT JOIN                  |              |    40 |   840 |     4  (25)| 00:00:01 |
|   9 |        TABLE ACCESS FULL         | PURCHASES    |    40 |   840 |     3   (0)| 00:00:01 |
|  10 |      TABLE ACCESS FULL           | ITEMS        |     3 |    27 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
SY.
Re: Get customer with GREATEST amount of purchases [message #686904 is a reply to message #686897] Mon, 30 January 2023 09:55 Go to previous message
mathguy
Messages: 106
Registered: January 2023
Senior Member
Solomon Yakobson wrote on Mon, 30 January 2023 05:02
mathguy wrote on Sun, 29 January 2023 18:09
"There is no need for RANK" is only an illusion. FETCH FIRST ... is just syntactic sugar, it is implemented with RANK() or ROW_NUMBER() depending on the "WITH TIES" vs. "ONLY" option.
Yes in terms of syntactic sugar but no in terms of efficiency:
[ ........ ]
"Syntactic sugar" means syntax that replaces something to make the code easier to read, while it does not change the semantics. The comparison of FETCH FIRST ... against another FETCH FIRST ... solution that also contains an explicit ROW_NUMBER() bit is OK, but it has nothing to do with what I said.

What I meant is just that FETCH FIRST ... also does one pass of RANK() or ROW_NUMBER() (not two passes!) under the hood, so using FETCH FIRST ... makes the code easier to write and to read, but it does not avoid the use of one analytic function. There is need for RANK() or ROW_NUMBER() to implement FETCH FIRST ... - they just don't need to appear explicitly in the code.
Previous Topic: REST API with DDOS protection
Next Topic: xml auto generate atribute
Goto Forum:
  


Current Time: Thu Mar 28 08:19:34 CDT 2024