SQL: number of continuous periods with or without orders

From: Dino Hsu <nospam.dino1_at_ms1.hinet.net>
Date: Sun, 06 Jan 2002 17:53:02 +0800
Message-ID: <vb7g3u842f125d277784j4sadt8lgp97qp@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 Sun Jan 06 2002 - 03:53:02 CST