Oracle: Identify records with unique conditions in a large dataset

Data and Databases
1

Hey folks, I’m working with a big Oracle table (over 2 million rows) and I need to pull out some specific records. There’s no constraint set up, which makes it tricky. Here’s what I’m trying to do:

account_id    total_amount
-----------------------------
A001          100
A001          100
A001          200   // This is what I'm after
B002          300

I want to find cases where an account_id has more than one unique total_amount. It’s okay if an account_id has multiple rows with the same total_amount, but if it has different total_amount values, that’s what I need to grab.

I tried this query, but it’s not quite right:

SELECT DISTINCT account_id, total_amount
FROM transactions
GROUP BY account_id, total_amount
HAVING COUNT(DISTINCT account_id) > 1

Any ideas on how to make this work? I’m stumped and could really use some help. Thanks!

2

hmm, interesting problem! have you considered using a window function? something like:

SELECT DISTINCT account_id
FROM (
  SELECT account_id, COUNT(DISTINCT total_amount) OVER (PARTITION BY account_id) as unique_amounts
  FROM transactions
)
WHERE unique_amounts > 1

this might work better for your case. what do you think? have you tried any other approaches?

3

Your approach is on the right track, but needs a slight adjustment. Consider using a self-join with a GROUP BY clause to identify accounts with multiple unique total amounts:

SELECT DISTINCT t1.account_id
FROM transactions t1
JOIN transactions t2 ON t1.account_id = t2.account_id
WHERE t1.total_amount <> t2.total_amount
GROUP BY t1.account_id

This query efficiently compares each transaction with others for the same account, filtering out those with different total amounts. It should perform well even on large datasets. Remember to test it on a subset of your data first to ensure it meets your specific requirements.

4

yo, have u tried this? might be faster:

SELECT account_id
FROM transactions
GROUP BY account_id
HAVING COUNT(DISTINCT total_amount) > 1

it groups by account_id and checks if there’s more than 1 unique total_amount. should work for ur case i think. lemme know if it helps!