Re: Sort Order in Nested query

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Fri, 01 Feb 2013 17:30:13 +0100
Message-ID: <510BED95.4010901_at_roughsea.com>

On 02/01/2013 12:43 PM, Fergal Taheny wrote:
> SQL*Plus <sys_at_ACBSOLT8> select * from transaction_stage
> 2 ;
>
> CUSTOMER_ID VALUE
> ----------- ----------
> 1 5
> 1 6
> 2 2
> 2 6
> 2 3

Fergal,

    I all depends on how you want to identify your batches. Personally, I think I would opt for a composite id such as (timestamp, customer_id, batch_id), plus possibly a sys_guid if you can have concurrent processes, and wouldn't use a sequence; and under this hypothesis, you can assign (not optimally) your values to one batch with something relatively simple, e.g.

select customer_id,

        value,
        ceil(sum(value)
                 over (partition by customer_id
                       order by value desc)/10) batch_id

from transaction_stage
/

Naturally, several runs will return the same batch_id values for the same customer_id, hence my composite key with a timestamp. On the positive side, the composite key would allow you some flexibility for partitioning if this is ever needed.

-- 
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>


--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 01 2013 - 17:30:13 CET