[SQL] Conditioneel Groupby over meerdere colommen

[Armageddon_2k]

Hey hey,

Ik zou graag een beroep op jullie expertise willen doen.
Al een tijdje worstel ik met dit probleem, en ik kom er niet uit.
Er moeten regels samengevoegd worden, maar met alleen een group by where kom ik er niet.
Ik ben al diverse fora afgeweest om een oplossing te zoeken, maar ik ben het spoor bijster

De volgende tabel definitie heb ik, de kolommen die er niet toe doen, heb ik achterwege gelaten.
Het doel, is een group op de reas_cd kolom.
Maar dit moet alleen gebeuren als de end_time_utc overeenkomt met de start_time_utc van de volgende regel. Van die regels wil ik de duration optellen.
Uiteraard zit het probleem niet in de Min(start_time_utc), Max(end_time_utc), Sum(Duration).
Ik krijg de grouping gewoon niet voor elkaar.

Van de regels die gejoined moeten worden, heb ik de reas_cd kolom bold gemaakt.

start_time_utc	end_time_utc	duration	reas_cd
2014-01-28 14:16:01.000	2014-01-29 10:03:00.000	71219	17
2014-01-29 10:03:00.000	2014-01-29 14:00:00.000	14220	20
2014-01-29 14:00:00.000	2014-01-29 22:00:00.000	28800	20
2014-01-29 22:00:00.000	2014-01-30 06:00:00.000	28800	20
2014-01-30 07:00:00.000	2014-01-30 14:00:00.000	28800	20
2014-01-30 14:10:00.000	2014-01-30 14:36:19.000	2179	5
2014-01-30 14:36:19.000	2014-01-30 14:47:43.000	684	18

[Chrizzly]

een having toevoegen aan de group by?
Of bedoel je dat je alleen wilt groupen bij een bepaalde conditie en anders niet?

[ Voor 52% gewijzigd door Chrizzly op 07-10-2014 09:18 ]

[Armageddon_2k]

Chrizzly schreef op dinsdag 07 oktober 2014 @ 09:16:
een having toevoegen aan de group by?
Of bedoel je dat je alleen wilt groupen bij een bepaalde conditie en anders niet?

Met een Having kom ik er niet uit. Want ik heb Having over 2 rijen
Want dit wordt hem volgens mij niet:

SQL:

Group by(reas_cd) Having start_time_utc = end_time_utc

Ik wil de duration van alle aansluitende tijden optellen, maar alleen als de reas_cd gelijk is

[ Voor 11% gewijzigd door Armageddon_2k op 07-10-2014 09:25 ]

[Chrizzly]

Sorry negeer mijn opmerking, ik had het niet goed begrepen.

[ATS]

Eigenlijk wil je dus een soort van custom aggregator die op twee kolommen werkt: als je end_time_utc van item 1 en je start_time_utc van item 2 gelijk zijn, dan wil je deze twee aggregeren met als resultaat de start_time_utc van item 1 en de end_time_utc van regel 2.

Ik zie niet hoe je dat met standaard SQL voor elkaar gaat krijgen. Wellicht heeft je db engine een mogelijkheid om zelf custom aggregators te maken in C of zoiets en kan je het daarmee doen. Als je die eenmaal hebt, dan kan je die weer combineren met een normale GroupBy lijkt me.

[Voutloos]

1e query/procedure:, ga door hele tabel en zet in een nieuwe kolom een waarde die oploopt als rows niet 'aansluiten'.

2e query: triviale group by.

[Armageddon_2k]

Voutloos schreef op dinsdag 07 oktober 2014 @ 09:44:
1e query/procedure:, ga door hele tabel en zet in een nieuwe kolom een waarde die oploopt als rows niet aansluiten.

2e query: triviale group by.

Dan group je toch alle rows die aanlsuiten. Je wilt alleen rows die direct op erlkaar aansluiten.
Zou je een klein voorbeeld kunnen geven?

[ATS]

Zoiets bedoelt Voutloos denk ik:

Stap 1: maak deze tabel

start_time_utc	end_time_utc	duration	reas_cd	time_block_id
2014-01-28 14:16:01.000	2014-01-29 10:03:00.000	71219	17	1
2014-01-29 10:03:00.000	2014-01-29 14:00:00.000	14220	20	1
2014-01-29 14:00:00.000	2014-01-29 22:00:00.000	28800	20	1
2014-01-29 22:00:00.000	2014-01-30 06:00:00.000	28800	20	1
2014-01-30 07:00:00.000	2014-01-30 14:00:00.000	28800	20	2
2014-01-30 14:10:00.000	2014-01-30 14:36:19.000	2179	5	3
2014-01-30 14:36:19.000	2014-01-30 14:47:43.000	684	18	3

Stap 2:Doe een triviale group by op de nieuwe time_block_id én je reas_cd, waarbij je als aggregator voor start_time_utc MIN gebruikt, voor end_time_utc MAX en voor duration SUM.

[CurlyMo]

Ongeveer wat ATS zegt kan je bereiken via deze query (uitgaande van MySQL):

code:

SELECT start_time_utc,
       IF(@endutc = start_time_utc AND @reascd = reas_cd, @match := 1, @match := 0)      AS `match`,
       IF(@match = 1, @duration := (SELECT @duration + duration), @duration := duration) AS duration,
       @endutc := end_time_utc                                                           AS end_time_utc,
       @reascd := reas_cd                                                                AS reas_cd
FROM   test,
       (SELECT @endutc := 0,
               @duration := 0,
               @reascd := 0,
               @match := 0) SQLVars

Het resultaat hiervan is:

code:

 start_time_utc      match  duration    end_time_utc         reas_cd
2014-01-28 14:16:01 0      71219       2014-01-29 10:03:00  17
2014-01-29 10:03:00 0      14220       2014-01-29 14:00:00  20
2014-01-29 14:00:00 1      43020       2014-01-29 22:00:00  20
2014-01-29 22:00:00 1      71820       2014-01-30 06:00:00  20
2014-01-30 07:00:00 0      28800       2014-01-30 14:00:00  20
2014-01-30 14:10:00 0       2179       2014-01-30 14:36:19   5
2014-01-30 14:36:19 0        684       2014-01-30 14:47:43  18

[Voutloos]

Armageddon_2k schreef op dinsdag 07 oktober 2014 @ 10:24:
[...]


Dan group je toch alle rows die aanlsuiten. Je wilt alleen rows die direct op erlkaar aansluiten.
Zou je een klein voorbeeld kunnen geven?

Goh, ik zei aansluiten, maar dat is dus 'die voldoen aan waarom jij iets wil groeperen'.

Wat ATS zegt, maar ik zou dan ook teller ophogen indien reas_cd niet gelijk. Anders krijg je ook bugs als je een continu periode hebt met reas_cd waarde X, dan Y en dan weer X.

Al met al leek mijn post genoeg hint om zelf verder te puzzelen.

[Armageddon_2k]

Okay, ik snap nu wat de bedoeling is.
Ik zal kijken of ik op die manier een 'tussen tabel' kan creeren.

Het voorbeeld van ATS kan ik alleen helaas niet gebruiken. Want wat daar gebeurt, mag niet in SQL:

"A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."

[Armageddon_2k]

Okay ik heb het voor elkaar. Thanks guys.

Het probleem is dat SQL normaliter niet sequenced is, dus dit moet je hem eerste nog vertellen.
Dit heb ik opgelost via een Partition Order By.

SQL:

WITH
sequenced_data AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY ent_id                ORDER BY event_time_utc) NameSequenceID,
ROW_NUMBER() OVER (PARTITION BY ent_id, reas_cd       ORDER BY event_time_utc) NameStateSequenceID,
d.*
FROM
 [MESDB].[dbo].[util_log] d
) 

SELECT
  ent_id,
  MIN(event_time_utc) start_inst,
  MAX(DATEADD(second ,[duration],[event_time_utc])) end_inst,
  SUM(Duration),
  reas_cd

FROM
  sequenced_data

Where 
    ent_id = 8
    AND event_time_utc between '2014-01-01' and '2014-02-01'

GROUP BY
  ent_id,
  reas_cd,
  NameSequenceID - NameStateSequenceID
ORDER BY start_inst