Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Oracle : Analytical function question

Options
  • 12-09-2005 1:18pm
    #1
    bonkey
    Registered Users 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