SQL Problem - Date / Txn Sequences

[p175]

People,

I'm trying to figure if SQL is able find sequences of transactions that
occur over say a four day period.

I have a table that contains a txn id [integer] and a date. Each
transaction might occur say 30 times within the first quarter of a
year, so there would be 30 records in the table [ID, DATE] for each
txn. There may be say 50k transactions. What I am trying to do is find
subsequent transactions that appear the day after the initial occurence
of a specific transaction up to say a limit of 4 times in a row.

ie: If txn 1 occures 30 times on various dates, then which transactions
are found to be ocurring on the subsequent date and so on. The
following transactions do not necessarily have to occur on all 30
subsequent dates, so they can be variances. What I would like to find
is a say a set of x many sequences that always occur say 6 times out of
the 30 possible dates. Meaning that this sequence of four dates,
started on these x dates and happened say 6 times.

So txn 1 was followed by txn 497, which was followed by txn 9673, which
was followed by txn 25675. The sequence happened 6 times and started on
these x,x,x,x,x,x dates.

Is this even possible in SQL ? I've tried converting the dates to
integers and using a date_id +/- 1 calculation to link what happens
next or previous, but I can't get it to work on a sequence of say four
in a row.

Anyone got any bright ideas, I'd greatly appreciate the help.

Many thanks. Tim

[db2newbie]

I am not sure whether the frequent pattern search algorithm or the
apriori algorithm for association rule mining would help.

[p175]

db2newbie wrote:

I am not sure whether the frequent pattern search algorithm or the
apriori algorithm for association rule mining would help.

I could use a mining algorithm and have in the past, but for this piece
I was hoping SQL could answer the query in a more timely fashion.

[p175]

I guess this is not possible then huh ?

p175 wrote:

People,

I'm trying to figure if SQL is able find sequences of transactions that
occur over say a four day period.

I have a table that contains a txn id [integer] and a date. Each
transaction might occur say 30 times within the first quarter of a
year, so there would be 30 records in the table [ID, DATE] for each
txn. There may be say 50k transactions. What I am trying to do is find
subsequent transactions that appear the day after the initial occurence
of a specific transaction up to say a limit of 4 times in a row.

ie: If txn 1 occures 30 times on various dates, then which transactions
are found to be ocurring on the subsequent date and so on. The
following transactions do not necessarily have to occur on all 30
subsequent dates, so they can be variances. What I would like to find
is a say a set of x many sequences that always occur say 6 times out of
the 30 possible dates. Meaning that this sequence of four dates,
started on these x dates and happened say 6 times.

So txn 1 was followed by txn 497, which was followed by txn 9673, which
was followed by txn 25675. The sequence happened 6 times and started on
these x,x,x,x,x,x dates.

Is this even possible in SQL ? I've tried converting the dates to
integers and using a date_id +/- 1 calculation to link what happens
next or previous, but I can't get it to work on a sequence of say four
in a row.

Anyone got any bright ideas, I'd greatly appreciate the help.

Many thanks. Tim

[Serge Rielau]

p175 wrote:

I guess this is not possible then huh ?

p175 wrote:

People,

I'm trying to figure if SQL is able find sequences of transactions that
occur over say a four day period.

I have a table that contains a txn id [integer] and a date. Each
transaction might occur say 30 times within the first quarter of a
year, so there would be 30 records in the table [ID, DATE] for each
txn. There may be say 50k transactions. What I am trying to do is find
subsequent transactions that appear the day after the initial occurence
of a specific transaction up to say a limit of 4 times in a row.

ie: If txn 1 occures 30 times on various dates, then which transactions
are found to be ocurring on the subsequent date and so on. The
following transactions do not necessarily have to occur on all 30
subsequent dates, so they can be variances. What I would like to find
is a say a set of x many sequences that always occur say 6 times out of
the 30 possible dates. Meaning that this sequence of four dates,
started on these x dates and happened say 6 times.

So txn 1 was followed by txn 497, which was followed by txn 9673, which
was followed by txn 25675. The sequence happened 6 times and started on
these x,x,x,x,x,x dates.

Is this even possible in SQL ? I've tried converting the dates to
integers and using a date_id +/- 1 calculation to link what happens
next or previous, but I can't get it to work on a sequence of say four
in a row.

Anyone got any bright ideas, I'd greatly appreciate the help.

Many thanks. Tim

It is possible. But it woudl be better if you gave sampelinput and
sampel output.
You may want to take a look at OLAP expressions. In particluar teh
windowing capabilities.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab