2010-04-16

Packing rows

So worth tasting. Having a Keisari Strong Munchener 5,7% brewed by Nokian panimo. Figuring out a thing done today.

The case was that we have a relation that has the only one over time feature present. There is the possibility that the same attribute is the same in several continuous rows. And we want to put those rows together in a query result. So the question here was how to implement pack operator presented in C.J. Date book Temporal data and the relational model. Some good alternatives are presented in Joe Celko's thinking in sets: auxiliary, temporal, and virtual tables in SQL. He presents a OLAP Function Solution. Here we have the not overlapping present so the question is a bit simpler. And to a example...


CREATE TABLE Z(Z NUMBER(16) NOT NULL
, VALIDFROM NUMBER(4) NOT NULL
, VALIDTILL NUMBER(4) NOT NULL
);

INSERT INTO Z VALUES (1,2000,2003);

INSERT INTO Z VALUES (1,2003,2004);

INSERT INTO Z VALUES (2,2004,2005);

INSERT INTO Z VALUES (2,2005,2008);

INSERT INTO Z VALUES (1,2008,2010);

SELECT Z, MINVALIDFROM, MAX(VALIDTILL) MAXVALIDTILL
FROM (
SELECT Z, VALIDFROM,VALIDTILL,PREVTILL
, MAX(CASE WHEN VALIDFROM <= PREVTILL
THEN NULL
ELSE VALIDFROM
END)
OVER ( PARTITION BY Z ORDER BY VALIDFROM, VALIDTILL
ROWS UNBOUNDED PRECEDING) MINVALIDFROM
FROM (
SELECT Z
, VALIDFROM,VALIDTILL
, LAG(VALIDTILL)
OVER (PARTITION BY Z ORDER BY VALIDTILL) PREVTILL
FROM Z
)
)
GROUP BY Z, MINVALIDFROM
ORDER BY MINVALIDFROM, Z
;

1 2000 2004
2 2004 2008
1 2008 2010


The simpler part is to use LAG.

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.