[SQL] Dubbele rijen groeperen

[NLAnaconda]

Hallo,

Ik heb een vraagje over hoe ik iets moet aanpakken in mysql, als het tenminste mogelijk is met sql.

Ik heb een datbase wat een soort logboek is. Bijvoorbeeld:

ID	Username	Actie	value
1	Piet	artikel1 gekocht	50
2	Piet	artikel2 gekocht	10
3	Piet	artikel3 gekocht	30
4	Piet	artikel1 gekocht	60
5	Piet	artikel1 gekocht	60
6	Piet	artikel1 gekocht	60
7	Piet	artikel1 gekocht	60
8	Piet	artikel3 gekocht	30

Nu wil ik dus dat hij de dubbele rijen op actie groepeert, maar alleen als ze onder elkaar staan.
Dus ik wil dit als uitvoer hebben. met een sum op value

Username	Actie	value
Piet	artikel1 gekocht	50
Piet	artikel2 gekocht	10
Piet	artikel3 gekocht	30
Piet	artikel1 gekocht	240	<-- gegroepeerd
Piet	artikel3 gekocht	30

Waarbij sql dus artikel 1 groepeert maar alleen daar waar de rijen onder elkaar liggen.
Enig idee hoe ik dit kan bereiken met sql?

[whoami]

Lees je eens in in SUM & GROUP BY .... Er staat daar een mooi artikeltje over in onze FAQ.

Maar, alleen als ze onder elkaar staan ? Tja, wanneer staan ze onder elkaar ? SQL werkt met sets en vziw is een SET een 'unordered collection'. Je zal dus wel eerst zelf moeten bepalen of die items onder elkaar staan of niet.
En met die extra voorwaarde, denk ik niet dat je dit efficient met één statement kunt oplossen.

[ Voor 65% gewijzigd door whoami op 13-08-2008 16:30 ]

[Voutloos]

whoami schreef op woensdag 13 augustus 2008 @ 16:17:
En met die extra voorwaarde, denk ik niet dat je dit met één statement kunt oplossen.

Subquery welke de 'volgende row' erbij haalt.

En nee, dat ga ik niet uitwerken, want dit kan alleen maar een stomme query worden en ik vraag me serieus af wie ooit iets zou hebben aan een dergelijk overzicht. Pak dan per artikel de laatste verkoopdatum, of per dag/week/maand whatever het aantal artikelen. Doe iets aan waar deze requirement vandaan komt (zeg me na: "Nee, beste klant, dat kan niet" )

[ Voor 8% gewijzigd door Voutloos op 13-08-2008 16:28 ]

[whoami]

Voutloos schreef op woensdag 13 augustus 2008 @ 16:27:
[...]
Subquery welke de 'volgende row' erbij haalt.

[NLAnaconda]

Bij deze: "Nee, beste klant, dat kan niet"

Denk dat het fantsoenlijker is om bij het invoeren te controleren of de laatste row dezelfde actie heeft en zo ja de value erbij optellen ipv een nieuwe row aan te maken. Scheelt ook nog eens in het aantal rijen in de database.

Bedankt in ieder geval voor de antwoorden.

[Voutloos]

Of je laat mensen meteen een aantal invullen ipv dat ze elke keer 1 ding moeten kopen.

[NLAnaconda]

Voutloos schreef op woensdag 13 augustus 2008 @ 16:36:
Of je laat mensen meteen een aantal invullen ipv dat ze elke keer 1 ding moeten kopen.

Het hoeft niet dezelfde bestelling te zijn en het is een soort log, geen winkelmandje oid.

[ Voor 8% gewijzigd door NLAnaconda op 13-08-2008 16:43 ]

[whoami]

... Je zou natuurlijk wel kunnen groeperen op datum aankoop ofzo ...

[Verwijderd]

NLAnaconda schreef op woensdag 13 augustus 2008 @ 16:13:
Waarbij sql dus artikel 1 groepeert maar alleen daar waar de rijen onder elkaar liggen.
Enig idee hoe ik dit kan bereiken met sql?

Als je 't in SQL wilt doen, zou ik een stored procedure maken die de gegevens zonder SUM / GROUP BY ophaalt, maar zelf sommeert wanneer de huidige action gelijk is aan de vorige, en anders een nieuw record aan het result toevoegt.
Is dit je te lastig, dan zou je 't ook client side (waarbij client niet de browser is, maar bv. het PHP script op je webserver) kunnen doen, maar dan gaat er wel veel meer data over het netwerk.

Een subquery die het resultaat van het volgende record ophaalt is technisch mogelijk, maar komt op mij over als een 'bend over backwards' oplossing...
't Is handiger om bij te houden wat de vorige actie was. Wijkt íe af? Zo nee: gegevens aan de huidige regel toevoegen, zo ja: nieuwe regel toevoegen.

Ik zou gaan voor een stored proc...

[KopjeThee]

NLAnaconda schreef op woensdag 13 augustus 2008 @ 16:13:
Hallo,

Ik heb een vraagje over hoe ik iets moet aanpakken in mysql, als het tenminste mogelijk is met sql.

Ik heb een datbase wat een soort logboek is. Bijvoorbeeld:

ID	Username	Actie	value
1	Piet	artikel1 gekocht	50
2	Piet	artikel2 gekocht	10
3	Piet	artikel3 gekocht	30
4	Piet	artikel1 gekocht	60
5	Piet	artikel1 gekocht	60
6	Piet	artikel1 gekocht	60
7	Piet	artikel1 gekocht	60
8	Piet	artikel3 gekocht	30

Nu wil ik dus dat hij de dubbele rijen op actie groepeert, maar alleen als ze onder elkaar staan.
Dus ik wil dit als uitvoer hebben. met een sum op value

Username	Actie	value
Piet	artikel1 gekocht	50
Piet	artikel2 gekocht	10
Piet	artikel3 gekocht	30
Piet	artikel1 gekocht	240	<-- gegroepeerd
Piet	artikel3 gekocht	30

Waarbij sql dus artikel 1 groepeert maar alleen daar waar de rijen onder elkaar liggen.
Enig idee hoe ik dit kan bereiken met sql?

Lastig, denk ik, maar zoiets??? (Volledig ongetest...)

code:

create table log_met_eerste_ind
select 
  a.id,
  a.username,
  a.actie,
  case when a.username = b.username and 
                  a.actie = b.actie 
  then 0
  else 1
  end as eerste_ind
from log a left join log b
on a.id - 1 = b.id;

create table eerste_id as
select id, username, actie from log_met_eerste_ind where eerste_ind = 1;

create table log_met_laatste_ind
select 
  a.id,
  a.username,
  a.actie,
  case when a.username = b.username and 
                  a.actie = b.actie 
  then 0
  else 1
  end as laatste_ind
from log a left join log b
on a.id + 1 = b.id;

create table laatste_id as
select id, username, actie from log_met_laatste_ind where laatste_ind = 1;

create table eerste_laatste as
select e.id as eerste,
          l.id as laatste,
         e.username,
         e.actie
from eerste_id e inner join laatste_id l
on e.username = l.username and
    e.actie = l.actie
where e.id <= l.id;

create table eerste_laatste_2 as
select eerste, min(laatste), username, actie 
from eerste_laatste 
group by eerste, username, actie;

create table verrijkt_log as
select e.eerste as groep_id,
          l.username,
          l.actie,
          l.value
from log l inner join eerste_laatste_2 e
on l.username = e.username and
    l.actie = e.actie and
    l.id >= e.eerste and
    l.id <= e.laatste;

create table result as
select
  a.groep_id
  a.username
  a.actie
  sum(a.value) as value
from verrijkt_log a
group by a.groep_id a.username a.actie

[pedorus]

Ik vraag me af wat hier aan de hand is. Waarom koopt Piet niet in 1x 240 stuks? Doet hij dat om unieke transactienummers te krijgen voor zijn interne administratie? (Zo ja, dan is 'optellen bij de vorige transactie' misschien iets te simpel gedacht.)

En verder was dit probleem te leuk. Kon het niet laten, dus even een testje gedaan in het 'normaliseren' van zo'n tabel (geen subqueries, mysql maar simpel om te zetten naar ISO/ANSI):

SQL:

drop table if exists logtest;
drop view if exists logtestrealids, logtestwithrealid, logtestnorm;
create table logtest(id int,username varchar(50),actie varchar(50),value int);
insert into logtest values (1,"Piet","a1",50),(2,"Piet","a2",10),
  (3,"Piet","a3",30),(4,"Piet","a1",60),(5,"Piet","a1",60),
  (6,"Piet","a1",60),(7,"Piet","a1",60),(8,"Piet","a3",30);

-- view met de ids waar een 'echte' nieuwe transactie is begonnen:
create view logtestrealids as 
  select l1.id
  from logtest l1 left join logtest l2 on l1.id=l2.id+1 and 
    l1.username=l2.username and l1.actie=l2.actie
  where l2.id is null;
-- view met de echte id bij iedere originele transactie: 
create view logtestwithrealid as 
  select max(logtestrealids.id) as id, username, actie, value 
  from logtest inner join logtestrealids on logtest.id>=logtestrealids.id 
  group by logtest.id, username, actie;
-- view met alleen maar echte transacties waarbij de waardes zijn opgeteld:
create view logtestnorm as 
  select id, username, actie, sum(value) as value 
  from logtestwithrealid 
  group by id, username, actie;

select * from logtestnorm;

[Voutloos]

Voutloos schreef op woensdag 13 augustus 2008 @ 16:27:
dit kan alleen maar een stomme query worden

Lekker naïef de vraag van de TS uitwerken en stomme queries leveren die hopelijk toch echt nooit direct in een applicatie gehangen gaan worden.

[dik_voormekaar]

Is toch gewoon zo:

SQL:

SELECT username,actie,sum(value) 
FROM logboek 
ORDER BY id 
GROUP BY username,actie

[pedorus]

Voutloos schreef op donderdag 14 augustus 2008 @ 08:02:
stomme queries leveren

offtopic:
Goede argumenten Heerlijk als je dan dit soort topics zonder voorbeelduitwerkingen vind op je zoektocht. "Nee sorry, we kunnen je niet helpen met de juiste data voor je analyse omdat dhr Voutloos dat 'stom' vind. Je moet maar genoegen met de aggregaten per dag..."

dik_voormekaar schreef op donderdag 14 augustus 2008 @ 08:20:
Is toch gewoon zo:

TS wilde eerst dubbele opeenvolgende rijen groeperen. Die order by is trouwens niet ok.

[dik_voormekaar]

... Die order by is trouwens niet ok.

Oops. Dit mag niet, eerst ORDER BY en dan GROUP BY.

[winkbrace]

Voutloos schreef op woensdag 13 augustus 2008 @ 16:27:
[...]
Subquery welke de 'volgende row' erbij haalt.

En nee, dat ga ik niet uitwerken, want dit kan alleen maar een stomme query worden en ik vraag me serieus af wie ooit iets zou hebben aan een dergelijk overzicht. Pak dan per artikel de laatste verkoopdatum, of per dag/week/maand whatever het aantal artikelen. Doe iets aan waar deze requirement vandaan komt (zeg me na: "Nee, beste klant, dat kan niet" )

Hoewel zijn toon akelig en betweterig is, ben ik het wel eens met de strekking van het verhaal van Voutloos.

De oplossing is het elegantst: de tabel self joinen met id +1 = id

Maar ook de kanttekening: Waarom zou je dergelijke netjes gescheiden informatie willen groeperen zonder naar datum ofzo te kijken?