[SQL] opeenvolgende rijen samenvoegen

[Big Womly]

Ik heb een tabel

seq	from	to	amount
1	01JAN2011	02JAN2011	1
2	03JAN2011	04JAN2011	2
3	05JAN2011	05JAN2011	2
4	06JAN2011	06JAN2011	3
5	07JAN2011	08JAN2011	2

Wat ik nu zou willen is dat er per amount de minimum from-date en maximum to-date op 1 regel komen, zolang ze niet worden gescheiden door een andere amount.

amount	from	to
1	01JAN2011	02JAN2011
2	03JAN2011	05JAN2011
3	06JAN2011	06JAN2011
2	07JAN2011	08JAN2011

Er zijn wel een aantal vaste gegevens:
- Data overlappen elkaar nooit. Een nieuwe from-datum zal dus nooit tussen de from en to van een ander record vallen, noch zal de from-datum samenvallen met een to-datum
- De seq kolom loopt in stijgende lijn, analoog met de tijdslijn. Een latere from-datum betekent dus ook een hogere seq-waarde.

Deze query zal uitgevoerd worden op een Oracle database.

Voorlopig kom ik op

SQL:

select amount, min("from") over (partition by amount order by seq), max("to") over (partition by amount order by seq) from (
select 1 seq, to_date('01JAN2011', 'DDMONYYYY') "from", to_date('02JAN2011', 'DDMONYYYY') "to", 1 amount from dual union all
select 2 seq, to_date('03JAN2011', 'DDMONYYYY') "from", to_date('04JAN2011', 'DDMONYYYY') "to", 2 amount from dual union all
select 3 seq, to_date('05JAN2011', 'DDMONYYYY') "from", to_date('05JAN2011', 'DDMONYYYY') "to", 2 amount from dual union all
select 4 seq, to_date('06JAN2011', 'DDMONYYYY') "from", to_date('06JAN2011', 'DDMONYYYY') "to", 3 amount from dual union all
select 5 seq, to_date('07JAN2011', 'DDMONYYYY') "from", to_date('08JAN2011', 'DDMONYYYY') "to", 2 amount from dual
)

Maar dan wordt er dus eerst gepartitioneerd en dan pas geordend, wat niet echt is wat ik zoek.

Iemand suggesties?

[Big4SMK]

Ik zou zeggen dat je hier met een group by wel uit zou moeten komen. Iets in de richting min(begindatum), max(enddatum) group by amount ofzo.

oeps, ik was duidelijk te snel met lezen. Als je met bovenstaande aan de gang gaat verlies je je volgorde, en krijg je gewoon de minimale en maximale datum waarbij een specifiek amount ingevuld was.

Vanwege je volgorde moet je misschien in de richting kijken van een variable die je bijhoudt. Even nadenken...

[ Voor 52% gewijzigd door Big4SMK op 01-09-2011 14:39 . Reden: beter lezen ]

[Big Womly]

De group by en over-functie zijn vrij analoog. Group by zal ook eerst groeperen per amount, dan pas sorteren op seq. Wat dus als gevolg heeft dat die 3e "2" samengevoegd wordt met de vorige 2, en dat is net hetgeen wat niet zou mogen gebeuren.

[Big4SMK]

Ik heb er even over nagedacht, en volgens mij moet je met deze constructie er wel kunnen komen:

set @group := 0;
SELECT cur.from, cur.to, cur.amount, @group as thisGroup, @group := if(cur.amount = next.amount, @group, @group + 1) as nextGroup
FROM <table> as cur left outer join <table> as next on (cur.seq+1 = next.seq)

de kolom thisGroup is dan een identifier voor de groep waarin de rij thuishoort. Je kan daarna naar hartelust groupby-en en minnen/maxen op data.

Kom je er zo verder uit ?

disclaimer: getest op MySQL, geen idee of oracle hetzelfde doet

[Big Womly]

Bedankt Big4MSK, het heeft me alleszins al een stap in de goede richting gezet
Via de case statement en de Lead en Lag functie kan ik tot 3 records met elkaar vergelijke, maar wat jij doe met MySQL heb ik tot hiertoe nog geen Oracle equivalent voor gevonden.

[justmental]

Dan krijg je zoiets:

SQL:

SELECT v.amount,
       (SELECT MIN (v3."from")
          FROM (SELECT 1 seq, TO_DATE ('01JAN2011', 'DDMONYYYY') "from",
                       TO_DATE ('02JAN2011', 'DDMONYYYY') "to", 1 amount
                  FROM DUAL
                UNION ALL
                SELECT 2 seq, TO_DATE ('03JAN2011', 'DDMONYYYY') "from",
                       TO_DATE ('04JAN2011', 'DDMONYYYY') "to", 2 amount
                  FROM DUAL
                UNION ALL
                SELECT 3 seq, TO_DATE ('05JAN2011', 'DDMONYYYY') "from",
                       TO_DATE ('05JAN2011', 'DDMONYYYY') "to", 2 amount
                  FROM DUAL
                UNION ALL
                SELECT 4 seq, TO_DATE ('06JAN2011', 'DDMONYYYY') "from",
                       TO_DATE ('06JAN2011', 'DDMONYYYY') "to", 3 amount
                  FROM DUAL
                UNION ALL
                SELECT 5 seq, TO_DATE ('07JAN2011', 'DDMONYYYY') "from",
                       TO_DATE ('08JAN2011', 'DDMONYYYY') "to", 2 amount
                  FROM DUAL) v3
         WHERE v3."from" <= v."from"
           AND v3.amount = v.amount
           AND NOT EXISTS (
                  SELECT 'x'
                    FROM (SELECT 1 seq,
                                 TO_DATE ('01JAN2011', 'DDMONYYYY') "from",
                                 TO_DATE ('02JAN2011', 'DDMONYYYY') "to",
                                 1 amount
                            FROM DUAL
                          UNION ALL
                          SELECT 2 seq,
                                 TO_DATE ('03JAN2011', 'DDMONYYYY') "from",
                                 TO_DATE ('04JAN2011', 'DDMONYYYY') "to",
                                 2 amount
                            FROM DUAL
                          UNION ALL
                          SELECT 3 seq,
                                 TO_DATE ('05JAN2011', 'DDMONYYYY') "from",
                                 TO_DATE ('05JAN2011', 'DDMONYYYY') "to",
                                 2 amount
                            FROM DUAL
                          UNION ALL
                          SELECT 4 seq,
                                 TO_DATE ('06JAN2011', 'DDMONYYYY') "from",
                                 TO_DATE ('06JAN2011', 'DDMONYYYY') "to",
                                 3 amount
                            FROM DUAL
                          UNION ALL
                          SELECT 5 seq,
                                 TO_DATE ('07JAN2011', 'DDMONYYYY') "from",
                                 TO_DATE ('08JAN2011', 'DDMONYYYY') "to",
                                 2 amount
                            FROM DUAL) v4
                   WHERE v4.amount != v.amount
                     AND v4."from" > v3."from"
                     AND v4."from" <= v."from")) "from",
       v."to"
  FROM (SELECT 1 seq, TO_DATE ('01JAN2011', 'DDMONYYYY') "from",
               TO_DATE ('02JAN2011', 'DDMONYYYY') "to", 1 amount
          FROM DUAL
        UNION ALL
        SELECT 2 seq, TO_DATE ('03JAN2011', 'DDMONYYYY') "from",
               TO_DATE ('04JAN2011', 'DDMONYYYY') "to", 2 amount
          FROM DUAL
        UNION ALL
        SELECT 3 seq, TO_DATE ('05JAN2011', 'DDMONYYYY') "from",
               TO_DATE ('05JAN2011', 'DDMONYYYY') "to", 2 amount
          FROM DUAL
        UNION ALL
        SELECT 4 seq, TO_DATE ('06JAN2011', 'DDMONYYYY') "from",
               TO_DATE ('06JAN2011', 'DDMONYYYY') "to", 3 amount
          FROM DUAL
        UNION ALL
        SELECT 5 seq, TO_DATE ('07JAN2011', 'DDMONYYYY') "from",
               TO_DATE ('08JAN2011', 'DDMONYYYY') "to", 2 amount
          FROM DUAL) v
 WHERE NOT EXISTS (
          SELECT 'x'
            FROM (SELECT 1 seq, TO_DATE ('01JAN2011', 'DDMONYYYY') "from",
                         TO_DATE ('02JAN2011', 'DDMONYYYY') "to", 1 amount
                    FROM DUAL
                  UNION ALL
                  SELECT 2 seq, TO_DATE ('03JAN2011', 'DDMONYYYY') "from",
                         TO_DATE ('04JAN2011', 'DDMONYYYY') "to", 2 amount
                    FROM DUAL
                  UNION ALL
                  SELECT 3 seq, TO_DATE ('05JAN2011', 'DDMONYYYY') "from",
                         TO_DATE ('05JAN2011', 'DDMONYYYY') "to", 2 amount
                    FROM DUAL
                  UNION ALL
                  SELECT 4 seq, TO_DATE ('06JAN2011', 'DDMONYYYY') "from",
                         TO_DATE ('06JAN2011', 'DDMONYYYY') "to", 3 amount
                    FROM DUAL
                  UNION ALL
                  SELECT 5 seq, TO_DATE ('07JAN2011', 'DDMONYYYY') "from",
                         TO_DATE ('08JAN2011', 'DDMONYYYY') "to", 2 amount
                    FROM DUAL) v2
           WHERE v2.amount = v.amount AND v2."from" = v."to" + 1)

met andere woorden: niet in sql oplossen
met een eenvoudig loopje door de resultaten los je het in de aanroepende applicatie op

[Verwijderd]

Of, als je het per se op de database wil oplossen: een table-function die loopt over de resultaten en aan de hand daarvan een nieuwe resultset terug geeft.

[Big Womly]

Dank heren

De tool is vast, daar kan ik niets aan veranderen. Enkel de SQL querries kan ik schrijven.
Dit heeft ook tot gevolg dat het definieren van table-functions geen optie is.
PL/SQL is wel een optie, maar een redelijk complexe oplossing. Niet zozeer door de PL/SQL zelf, maar door het te combineren met de tool.
Blijkbaar wordt dit toch de enige mogelijke oplossing... Achja, werkzekerheid voor binnen een paar weken