Re: SQL: number of continuous periods with or without orders

You probably havent received anything because the question is something out of University Challenge x 10! ;-)

Good luck anyway

M

ps Sounds like you may want to use PL/SQL instead

Dino Hsu <nospam.dino1_at_ms1.hinet.net> wrote in message news:<vb7g3u842f125d277784j4sadt8lgp97qp_at_4ax.com>...
> Dear all,
>
> I posted this problem to comp.databases.oracle.server on Dec. 6th,
> 2001, but there have been no follow-ups. I guess
> comp.databases.oracle.misc is more appropriate for SQL discussion, I
> hope someone would be interested:
>
> A direct-selling company has the following two core tables:
> 1.tb_orders: order_no, period, dealer_no, item_no, units
> 1-1.PK=order_no+item_no, FK=period+dealer_no
> 1-2.sales transactions, denormalized to improve performance
> 2.tb_dealers: period, dealer_no, dealer_type
> 2-1.PK=period+dealer_no
> 2-2.periods are a custom time system, assume them numeric for easy
> calculation
> 2-3.number of dealers and dealer types can change over periods, but
> the same dealer always holds the same dealer_no
> 2-4.a dealer joins the company by placing orders, and will be removed
> from the dealer list after a certain periods of inactivity
>
> They want to find out the factors related to the drop in dealer
> activity, two terms are defined:
> 1.y-count: number of continuous periods with orders
> 2.n-count: number of continuous periods without orders
> 3.At the beginning of each period, each dealer has a yn-count status
> according to activities of direct previous periods backwards.
> 4.yn-count is the combination of positive y-count and negative n-count
> because they are mutually exclusive.
>
> They want to get a report like this:
>
> period yn_count t_dealer_count a_dealer_count order_count units
> ------ -------- -------------- -------------- ----------- -----
> 1 -3
> 1 -2
> 1 -1
> 1 1
> 1 2
> 1 3
> 2 -3
> 2 -2
> 2 -1
> 2 1
> 2 2
> 2 3
> 3 -3
> 3 -2
> 3 -1
> 3 1
> 3 2
> 3 3
>
> t_dealer_count: total dealer count
> a_dealer_count: active dealer count (those who place ordres)
> (for simplicity, the number of periods to 'look back' is only 3, the
> measures are obvious)
>
> If we have the yn_count for each dealer_no+period group, we can easily
> make the above report by grouping period+yn_count, but that is the
> difficult part. The nature of the yn_count measure is a
> state-transition machine over the period dimension, which seems to go
> beyond the ability of 'windowing functions' or 'analytical functions'.
>
> I have a partial solution to this problem, which requires a limit on
> the number of periods to 'look back', say 3 periods, I can use the lag
> function to get the units of previous 3 periods and then get the
> yn-count with a simple user-defined function with 3 parameters. This
> can be good enough for some cases, but not in general cases.
>
> Anyone can suggest a complete solution, i.e., one without a limit on
> the number of periods to 'look back'? Thanks in advance.
>
> Dino
> OCP, Taipei
Received on Mon Jan 07 2002 - 07:01:06 CST