Home » SQL & PL/SQL » SQL & PL/SQL » Ranking account and customer balances (19c)
Ranking account and customer balances [message #688047] |
Tue, 15 August 2023 06:20 |
|
Unclefool
Messages: 85 Registered: August 2021
|
Member |
|
|
I have the following setup, which is working well. As you can see I'm calling functions to get account and customer balances.
I'm getting syntax errors when I try to wrap this in a CTE where I want to return the first two (DENSE_RANK want to include ties) highest balances.
Any help would be greatly appreciated. Thanks to all who answer.
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
CREATE TABLE CUSTOMERS (
CUSTOMER_ID, FIRST_NAME, LAST_NAME,IS_ACTIVE) AS SELECT 'L382059', 'Leo', 'Langford','Y' FROM DUAL UNION ALL
SELECT 'P382319', 'Tom', 'Micelli','Y' FROM DUAL UNION ALL
SELECT 'E379466', 'Bonnie', 'Winterbottom','Y' FROM DUAL UNION ALL
SELECT 'X060162', 'Lisa','Saladino','Y' FROM DUAL UNION ALL
SELECT 'Y331964', 'Sandy', 'Herring','Y' FROM DUAL UNION ALL
SELECT 'Z888555', 'Barbara', 'Broadwater','Y' FROM DUAL;
ALTER TABLE customers
ADD CONSTRAINT customers_pk PRIMARY KEY (customer_id);
CREATE TABLE vendors AS
SELECT level AS vendor_id,
'Vendor ' || level AS vendor_name
FROM dual
CONNECT BY level <= 3;
ALTER TABLE vendors
ADD ( CONSTRAINT vendors_pk
PRIMARY KEY (vendor_id));
CREATE TABLE CUSTOMER_ACCOUNTS (
ACCOUNT_NUMBER,
CUSTOMER_ID, VENDOR_ID,
IS_ACTIVE) AS
SELECT 'Z17ARWYYZRCU2Q2', 'P382319', 1, 'Y' FROM DUAL
UNION ALL
SELECT '0T81Z07CS6LXQ7Z', 'P382319', 3, 'Y' FROM DUAL
UNION ALL
SELECT 'YWYXC3Q5N9XZ7S', 'L382059', 1, 'Y' FROM DUAL UNION ALL
SELECT '612ZKAQ66VA3W3', 'Y331964', 3, 'Y' FROM DUAL UNION ALL
SELECT 'BCHD9TW78W67S1D', 'Z888555', 3, 'Y' FROM DUAL UNION ALL
SELECT '0HLS87LDR1TE8WB',
'X060162', 3, 'Y' FROM DUAL UNION ALL
SELECT 'Z69AG7DKS37UYU',
'X060162', 3, 'Y' FROM DUAL UNION ALL
SELECT 'B17ARWYYZRCU2Q2',
'X060162', 3, 'Y' FROM DUAL UNION ALL
SELECT 'THVQD6M9LR7AVK', 'E379466', 1, 'Y' FROM DUAL UNION ALL
SELECT '0Z76WT5NTLRZPTW',
'E379466', 1, 'Y' FROM DUAL;
ALTER TABLE customer_accounts
ADD CONSTRAINT customer_accounts_pk PRIMARY KEY (ACCOUNT_NUMBER);
ALTER TABLE CUSTOMER_ACCOUNTS ADD CONSTRAINT ca_customer_fk FOREIGN KEY (CUSTOMER_ID) REFERENCES customers(customer_id);
ALTER TABLE CUSTOMER_ACCOUNTS ADD CONSTRAINT ca_vendor_fk FOREIGN KEY (VENDOR_ID) REFERENCES vendors(vendor_id);
create table transactions (
transaction_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
account_number VARCHAR2(15),
transaction_type varchar2(1) DEFAULT 'C',
transaction_amount NUMBER(10,2),
transaction_date DATE DEFAULT SYSDATE
);
ALTER TABLE TRANSACTIONS ADD CONSTRAINT transactions_account_number_fk FOREIGN KEY (ACCOUNT_NUMBER) REFERENCES customer_accounts
(account_number);
insert into transactions(
account_number, transaction_type, transaction_amount, transaction_date)
SELECT 'Z17ARWYYZRCU2Q2', 'D', (LEVEL * 1250.50), date '2023-05-14' + level * interval '5 15:13' day to minute from dual
connect by level <= 7
union all
SELECT 'Z17ARWYYZRCU2Q2', 'C', (LEVEL * 1175.75), date '2023-07-04' + level * interval '1 21:23' day to minute from dual
connect by level <= 5
union all
SELECT '0T81Z07CS6LXQ7Z', 'D', (LEVEL * 1250.50), date '2023-02-14' + level * interval '3 15:13' day to minute from dual
connect by level <= 17
union all
SELECT '0T81Z07CS6LXQ7Z', 'C', (LEVEL * 75.75), date '2023-02-04' + level * interval '2 21:23' day to minute from dual
connect by level <= 11
union all
select '612ZKAQ66VA3W3', 'D', 555.25 * LEVEL, (DATE '2023-07-13' + 13/24) + (level * 2) from dual
connect by level <= 25
UNION ALL
select '612ZKAQ66VA3W3', 'C', 555.25 * LEVEL, (DATE '2023-07-23' + 13/24) + (level * 2) from dual
connect by level <= 20
UNION ALL
select 'BCHD9TW78W67S1D', 'D', 1125.25 * LEVEL, date '2023-01-23' + level * interval '1 1' day to hour from dual
connect by level <= 11
union all
select 'BCHD9TW78W67S1D', 'C', 925.00 * LEVEL, date '2023-01-25' + level * interval '1 1' day to hour from dual
connect by level <= 7
union all
SELECT '0HLS87LDR1TE8WB', 'D', (LEVEL * 1250.50), date '2023-05-04' + level * interval '1 15:13' day to minute from dual
connect by level <= 7
union all
SELECT '0HLS87LDR1TE8WB', 'C', (LEVEL * 1175.75), date '2023-05-04' + level * interval '1 15:13' day to minute from dual
connect by level <= 5
union all
SELECT 'Z69AG7DKS37UYU', 'D', ((LEVEL * 5) * 1750), date '2023-06-01' + level * interval '1 18:43:35' day to second from dual
connect by level <= 15
union all
SELECT 'Z69AG7DKS37UYU', 'C', ((LEVEL * 5) * 1750), date '2023-06-11' + level * interval '1 15:23:49' day to second from dual
connect by level <= 13
union all
SELECT '0Z76WT5NTLRZPTW', 'D', (LEVEL * 100.57), date '2023-04-02' + level * interval '1 4' day to hour from dual
connect by level <= 5
union all
SELECT '0Z76WT5NTLRZPTW', 'C', (LEVEL * 25.26), date '2023-04-04' + level * interval '1 4' day to hour from dual
connect by level <= 5
union all
SELECT 'THVQD6M9LR7AVK', 'D', (LEVEL * 250.10), date '2023-05-10' + level * interval '1 7' day to hour from dual
connect by level <= 13
union all
SELECT 'THVQD6M9LR7AVK', 'C', (LEVEL * 133.11), (SYSDATE - LEVEL) from dual
connect by level <= 9;
CREATE OR REPLACE FUNCTION get_customer_balance
( i_customer_id IN customers.customer_id%TYPE
)
RETURN transactions.transaction_amount%TYPE
IS
v_balance transactions.transaction_amount%TYPE;
BEGIN
SELECT SUM (
CASE t.transaction_type
WHEN 'C'
THEN -t.transaction_amount
ELSE t.transaction_amount
END
)
INTO v_balance
FROM customer_accounts ca
JOIN transactions t ON t.account_number = ca.account_number
WHERE ca.customer_id = i_customer_id -- one customer
OR ca.customer_id IS NULL; -- all customers
RETURN v_balance;
END get_customer_balance;
/
CREATE OR REPLACE FUNCTION get_account_balance(
i_account_number IN TRANSACTIONS.ACCOUNT_NUMBER%TYPE
) RETURN TRANSACTIONS.TRANSACTION_AMOUNT%TYPE
IS
v_balance TRANSACTIONS.TRANSACTION_AMOUNT%TYPE;
BEGIN
SELECT SUM(
CASE transaction_type WHEN 'C' THEN -1 ELSE 1 END
* transaction_amount
)
INTO v_balance
FROM transactions
WHERE account_number = i_account_number -- one account
OR i_account_number IS NULL; -- all accounts
RETURN v_balance;
END;
/
-- account balance works
SELECT
CA.ACCOUNT_NUMBER,
C.CUSTOMER_ID,
C.FIRST_NAME,
C.LAST_NAME,
get_account_balance(ca.account_number) AS balance
FROM CUSTOMER_ACCOUNTS CA
INNER JOIN customers c ON ca.customer_id = c.customer_id;
-- customer balance works
SELECT C.CUSTOMER_ID,
C.FIRST_NAME,
C.LAST_NAME,
get_customer_balance(C.CUSTOMER_ID) AS balance
FROM customers c;
|
|
|
Re: Ranking account and customer balances [message #688054 is a reply to message #688047] |
Tue, 15 August 2023 13:35 |
|
mathguy
Messages: 108 Registered: January 2023
|
Senior Member |
|
|
I don't understand.
You posted all the code that works. Where is the code that doesn't work? What are the syntax errors you are getting?
What is the exact desired output? What top two highest balances are you looking for - account balances? Customer balances? Top two accounts for each customer? Something else?
Also, you said you are using DENSE_RANK to get the ties. That is very rarely the correct function to use. If there are three "records" tied for highest balance, and then three more records tied for next highest balance, then DENSE_RANK will assign 1 to the first three "records" (tied for highest balance) and 2 to the next three records (tied for next highest balance). If you then select where DENSE_RANK <= 2, you will get all six records; you are getting the top two highest balances (including ties), not the top two accounts with highest balances (including ties), which should be just the three accounts tied for highest balance.
If you are looking for the top two accounts (including ties), you should use RANK, not DENSE_RANK. In my example, the first three "records" (tied for highest balance) will have RANK = 1; the next three will have RANK = 4.If you want top five accounts (including ties) you would use RANK <= 5 in the WHERE clause - that will return the top six "records".
|
|
|
|
Re: Ranking account and customer balances [message #688062 is a reply to message #688054] |
Tue, 15 August 2023 15:46 |
|
mathguy
Messages: 108 Registered: January 2023
|
Senior Member |
|
|
OK, so you want two different queries - one for second highest account balance, and one for second highest customer balance. (For "account balance" I understand over all the data, not second highest separately for each customer - but please confirm.) I won't discuss more about RANK vs. DENSE_RANK; queries should look the same, the two functions have different meanings, you will use whichever you find solves your problem.
I am still not clear about the "wrapping" thing, which I thought I would understand if you showed us your attempt (even with syntax errors). What do you mean by "wrapping within CTE" - do you mean that, instead of defining the functions separately, you want to define them in a CTE instead? That seems trivial, see example below, where I essentially copied and pasted from your code, with very few changes.
Note that - at least in SQL*Plus - you must end the query with a forward slash, not semi-colon. And some interfaces may not yet understand functions in the WITH clause; I believe Live SQL is (or was) one of them.
with
function get_account_balance(
i_account_number IN TRANSACTIONS.ACCOUNT_NUMBER%TYPE
) RETURN TRANSACTIONS.TRANSACTION_AMOUNT%TYPE
IS
v_balance TRANSACTIONS.TRANSACTION_AMOUNT%TYPE;
BEGIN
SELECT SUM(
CASE transaction_type WHEN 'C' THEN -1 ELSE 1 END
* transaction_amount
)
INTO v_balance
FROM transactions
WHERE account_number = i_account_number -- one account
OR i_account_number IS NULL; -- all accounts
RETURN v_balance;
END;
prep as (
SELECT C.CUSTOMER_ID,
C.FIRST_NAME,
C.LAST_NAME,
get_customer_balance(C.CUSTOMER_ID) AS balance,
dense_rank() over (order by get_customer_balance(c.customer_id) desc) as drn
FROM customers c)
select customer_id, first_name, last_name, balance
from prep
where drn = 2
/
CUSTOMER_ID FIRST_NAME LAST_NAME BALANCE
----------- ---------- --------- ---------
X060162 Lisa Saladino 271127.75
|
|
|
|
|
Re: Ranking account and customer balances [message #688072 is a reply to message #688071] |
Wed, 16 August 2023 07:54 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Here are two options for rectifying it.
-- option 1: add NULLS LAST (see line 23)
C##SCOTT@XE_21.3.0.0.0> with
2 function get_account_balance(
3 i_account_number IN TRANSACTIONS.ACCOUNT_NUMBER%TYPE
4 ) RETURN TRANSACTIONS.TRANSACTION_AMOUNT%TYPE
5 IS
6 v_balance TRANSACTIONS.TRANSACTION_AMOUNT%TYPE;
7 BEGIN
8 SELECT SUM(
9 CASE transaction_type WHEN 'C' THEN -1 ELSE 1 END
10 * transaction_amount
11 )
12 INTO v_balance
13 FROM transactions
14 WHERE account_number = i_account_number -- one account
15 OR i_account_number IS NULL; -- all accounts
16 RETURN v_balance;
17 END;
18 prep as (
19 SELECT C.CUSTOMER_ID,
20 C.FIRST_NAME,
21 C.LAST_NAME,
22 get_customer_balance(C.CUSTOMER_ID) AS balance,
23 dense_rank() over (order by get_customer_balance(c.customer_id) desc NULLS LAST) as drn
24 FROM customers c)
25 select customer_id, first_name, last_name, balance
26 from prep
27 where drn = 2
28 /
CUSTOME FIRST_N LAST_NAME BALANCE
------- ------- ------------ ----------
P382319 Tom Micelli 203704.75
1 row selected.
-- option 2: use the NVL function (see line 23)
C##SCOTT@XE_21.3.0.0.0> with
2 function get_account_balance(
3 i_account_number IN TRANSACTIONS.ACCOUNT_NUMBER%TYPE
4 ) RETURN TRANSACTIONS.TRANSACTION_AMOUNT%TYPE
5 IS
6 v_balance TRANSACTIONS.TRANSACTION_AMOUNT%TYPE;
7 BEGIN
8 SELECT SUM(
9 CASE transaction_type WHEN 'C' THEN -1 ELSE 1 END
10 * transaction_amount
11 )
12 INTO v_balance
13 FROM transactions
14 WHERE account_number = i_account_number -- one account
15 OR i_account_number IS NULL; -- all accounts
16 RETURN v_balance;
17 END;
18 prep as (
19 SELECT C.CUSTOMER_ID,
20 C.FIRST_NAME,
21 C.LAST_NAME,
22 get_customer_balance(C.CUSTOMER_ID) AS balance,
23 dense_rank() over (order by NVL (get_customer_balance(c.customer_id), 0) desc) as drn
24 FROM customers c)
25 select customer_id, first_name, last_name, balance
26 from prep
27 where drn = 2
28 /
CUSTOME FIRST_N LAST_NAME BALANCE
------- ------- ------------ ----------
P382319 Tom Micelli 203704.75
1 row selected.
|
|
|
|
Re: Ranking account and customer balances [message #688088 is a reply to message #688077] |
Wed, 16 August 2023 13:52 |
|
Unclefool
Messages: 85 Registered: August 2021
|
Member |
|
|
@mathguy
@Barbara Boehmer
Thanks I was googling and found the NULLS LAST OPTION.
I been reading up on SQL_MACRO and wanted to get your opinions. Are these functions good candidates to be converted? I know they have been available for a while but never used them. Would the get_customer_balance function be difficult to convert?
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:16:06 CDT 2024
|