On Tue, 23 Jun 2009 04:41:44 +1000, Mike Toews <mwtoews@xxxxxx> wrote:
Window functions appear to be the best solution for this style of
problem, and I'm looking forward to their applications. However, I'm
sticking with 8.3 for at least a year, so I'm not able to explore this
solution yet. For now, I can only post-process the output in a non-SQL
environment. I also need to do other fun stuff, like cumulative sums,
which is also challenging with SQL, but much easier and intuitive with R.
As a largely procedural programmer, the PL/SQL solution is quite appealing
to me, and would be similarly simple to calculate cumulative sums. The
integration of SELECT statements within PL/SQL also seems much tighter
than with other PL languages. Unfortunately, one can't send a cursor or a
set of results directly as a PL argument.
I'm having a skim through Celko's chapter 24, but it doesn't seem to be
close to my needs either.
On Tue, 23 Jun 2009 08:05:14 +1000, Mike Toews <mwtoews@xxxxxx> wrote:
...
# Determine where the rows are different; 1=different rows, 0=same rows
dat$breaks <- ifelse(dat$bin != c(TRUE,
as.character(dat$bin[-nrow(dat)])), 1, 0)
# Determine where the continuous parts are:
dat$part <- factor(cumsum(dat$breaks))
Yes, as far as I can tell, this is almost identical to my WINDOW-based
solution in finding when there is a change, marking it with 0 or 1 and the
using cumulative sum to number the partitions. This could be similarly
done in PL/SQL but it seemed more sensible to just do the whole thing
rather than using GROUP BY after enumeration.
- Joel
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
