Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

Oracle : Analytical function question

  • 12-09-2005 01:18PM
    #1
    bonkey
    Registered Users, Registered Users 2 Posts: 15,443 ✭✭✭✭


    Hi guys...

    Here's an outline of a problem I'm trying to efficiently resolve at the mo:

    TableX contains a large number of rows, which equate to transactions from customers. What I want to find & retrieve is the transactions in a given month for the N customers with the highest transaction totals for that month.

    What I'm thinking at present is that I can use analytical functions in two steps. First, I need to get a total per month/customer against each record:

    [php]

    SELECT dateField
    , CustomerId
    , transactionValue
    , SUM(transactionValue) OVER (PARTITION BY dateField,
    CustomerId) AS MonthlyCustomerTransactionTotal
    FROM <source tables and join information to go here>
    [/php]
    OK...so then, I can wrap this to densely rank the totals:

    [php]
    SELECT dateField
    , CustomerId
    , transactionValue
    , MonthlyCustomerTransactionTotal
    , DENSE_RANK() OVER (PARTITION BY dateField,
    CustomerId
    ORDER BY dateField,
    CustomerId,
    MonthlyCustomerTransactionTotal) as MonthlyCustomerRank
    FROM (
    <SQL from above goes here as a nested-select pseudo-table>
    )
    [/php]
    I can now use this code as the basis for a View, which can be queried for any given month....and the user can specify how many ranked positions he wants.

    What I'm wondering is whether or not I can ditch using nested queries. Can anyone think of / see / tell me of a way to do this?


Advertisement