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  |
 |
Unclefool
Messages: 65
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 #686882 is a reply to message #686881] |
Sun, 29 January 2023 12:38  |
 |
Barbara Boehmer
Messages: 8985
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 #686884 is a reply to message #686883] |
Sun, 29 January 2023 13:37 |
|
Barbara Boehmer
Messages: 8985
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 |
Solomon Yakobson
Messages: 3211
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>
|
|
|
|
| Re: Get customer with GREATEST amount of purchases [message #686886 is a reply to message #686885] |
Sun, 29 January 2023 13:59 |
Solomon Yakobson
Messages: 3211
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>
|
|
|
|
|
|
| Re: Get customer with GREATEST amount of purchases [message #686888 is a reply to message #686887] |
Sun, 29 January 2023 14:43 |
Solomon Yakobson
Messages: 3211
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
/
|
|
|
|
|
|
| Re: Get customer with GREATEST amount of purchases [message #686897 is a reply to message #686889] |
Mon, 30 January 2023 05:02 |
Solomon Yakobson
Messages: 3211
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>
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>
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 |
---------------------------------------------------------------------------------------------------
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 |
--------------------------------------------------------------------------------------------------
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 |
-------------------------------------------------------------------------------------------------
|
|
|
|
| Re: Get customer with GREATEST amount of purchases [message #686898 is a reply to message #686897] |
Mon, 30 January 2023 05:13 |
Solomon Yakobson
Messages: 3211
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 |
-------------------------------------------------------------------------------------------------
|
|
|
|
| Re: Get customer with GREATEST amount of purchases [message #686904 is a reply to message #686897] |
Mon, 30 January 2023 09:55 |
 |
mathguy
Messages: 37
Registered: January 2023
|
Member |
|
|
Solomon Yakobson wrote on Mon, 30 January 2023 05:02mathguy 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.
|
|
|
|
Goto Forum:
Current Time: Tue Mar 21 21:45:57 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
