Toon posts:

[SQL] group by obv order

Pagina: 1
Acties:

Onderwerpen


Acties:
  • 0Henk 'm!

  • .Johnny
  • Registratie: September 2002
  • Laatst online: 16-05 12:24
Ik probeer (het liefst dmv één of een serie queries) een speciale grouping te realiseren. Misschien is het heel simpel en zie ik door het staren niet meer hoe simpel het is, misschien vraag ik het onmogelijke.

Het beste uit te leggen door een voorbeeld. Stel je hebt een tabel zoals deze (voorbeeld matchet de werkelijkheid totaal niet, maar het gaat even om het idee):
+------------+----------+---------------------+
| product_id | sales_id | time                |
+------------+----------+---------------------+
|          1 |        1 | 2011-05-22 16:12:18 |
|          1 |        1 | 2011-05-23 16:12:27 |
|          1 |        2 | 2011-05-24 16:12:46 |
|          2 |        1 | 2011-05-24 16:14:35 |
|          1 |        3 | 2011-05-25 16:12:59 |
|          2 |        2 | 2011-05-25 16:14:39 |
|          1 |        1 | 2011-05-26 16:13:12 |
|          2 |        2 | 2011-05-26 16:14:42 |
|          2 |        2 | 2011-05-27 16:14:44 |
|          2 |        1 | 2011-05-28 16:14:46 |
+------------+----------+---------------------+

Nu wil ik uit zo'n tabel bv per product een historisch overzicht van de verkopers (sales) die het product verkocht hebben. E.g. voor product 1: het werd eerst verkocht door 1, daarna door 2, toen 3 en daarna weer 1. De relevante gegevens die je daarover zou kunnen weergeven zou dit zijn:
+------------+----------+---------------------+---------------------+
| product_id | sales_id | MAX(time)           | MIN(time)           |
+------------+----------+---------------------+---------------------+
|          1 |        1 | 2011-05-23 16:12:27 | 2011-05-22 16:12:18 |
|          1 |        2 | 2011-05-24 16:12:46 | 2011-05-24 16:12:46 |
|          1 |        3 | 2011-05-25 16:12:59 | 2011-05-25 16:12:59 |
|          1 |        1 | 2011-05-26 16:13:12 | 2011-05-26 16:13:12 |
+------------+----------+---------------------+---------------------+

Dus wanneer begon de verkoop door die sales en wanneer eindigde die voor het weer door een ander verkocht werd. Met andere woorden, ik zoek een group by die rekening houdt met de volgorde van de rows, en dus gaat groeperen zodra er een nieuwe waarde voorkomt. Kan dit eigenlijk wel zoals ik het wil, of kan ik beter handmatig een table scan gaan doen? dat doe ik liever niet, het gaat uiteindelijk om >30M rows.

Ik zat zelf te denken in de richting van een temp table met key constraint en daar een INSERT IGNORE of ON DUPLICATE KEY naar te schrijven (kun je mooi de MAX(time) mee vullen). Ik wil het overzicht maar eenmalig genereren dus het mag wel even duren.

Acties:
  • 0Henk 'm!

  • P_de_B
  • Registratie: Juli 2003
  • Niet online
Dit moet je met een Group By wel kunnen oplossen

Oops! Google Chrome could not find www.rijks%20museum.nl


Acties:
  • 0Henk 'm!

  • .Johnny
  • Registratie: September 2002
  • Laatst online: 16-05 12:24
Niet zo 1-2-3 als de lengte van je post suggereert. Wanneer ik
SQL:
1
GROUP BY product_id, sales_id

krijg ik geen aparte results als tussendoor voor het product, sales een andere waarde heeft gehad.

-edit; conceptueel zou ik misschien de products tabel met zichzelf moeten joinen, zodat per product de volgende rij gejoined wordt met de vorige. Wanneer dan de sales id anders is tussen de 2 heb ik een grouping momentje. Maar dat is behoorlijk omslachtig, want hoe join je op "de volgende"

[Voor 43% gewijzigd door .Johnny op 30-05-2011 16:44]


Acties:
  • 0Henk 'm!

  • Voutloos
  • Registratie: Januari 2002
  • Niet online
Groeperen per 'niet nader gedefinieerde aaneengesloten periode' is geen feature. ;)

Is groeperen op product, verkoper, dag niet uberhaupt nuttiger? Is bovendien triviaal te implementeren, en ik denk ook een makkelijker te behappen statistiek voor de eindgebruiker. :Y)

{signature}


Acties:
  • 0Henk 'm!

  • .Johnny
  • Registratie: September 2002
  • Laatst online: 16-05 12:24
Ja, allemaal waar. Mijn voorbeeld is misschien niet heel sterk, maar de werkelijkheid weer te ingewikkeld. Het gaat erom dat ik een dergelijk "historisch overzicht" uit een tabel met 2 ids en order column probeer te halen. Maar dat wordt dus zelf de tabel aflopen in een php loopje ben ik bang?

misschien een beter voorbeeld: stel het gaat om personen en opdrachten bij bedrijven; ik doe eerst een opdracht voor bedrijf A, dan B, dan weer B, dan weer A. Het overzicht zou dan moeten zeggen: A, B, A. En niet A, B, B, A, of A, B.

[Voor 28% gewijzigd door .Johnny op 30-05-2011 16:55]


Acties:
  • 0Henk 'm!

  • Skinny
  • Registratie: Januari 2000
  • Laatst online: 11:05

Skinny

DIRECT!

Had een heel verhaal getypt maar .Johnny hierboven zet het conceptueel al neer, alleen ben ik vaak sneller met sql. Join dezelfde tabel op basis van time en product, zodat je een 'volgorde' in sales_id's krijgt.

SQL:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT a.product_id, a.sales_id, a.timeofsale as start_of_sale, MIN(b.timeofsale) AS end_of_sale
FROM 
(
        SELECT product_id,sales_id, [time] as timeofsale
        FROM sales 
) a
LEFT JOIN 
(
    SELECT product_id,sales_id, [time] as timeofsale
    FROM sales 
) b 

ON (a.product_id = b.product_id AND b.timeofsale > a.timeofsale )

GROUP BY a.product_id, a.sales_id, a.timeofsale

ORDER BY a.product_id, a.timeofsale


Hiermee genereer je dus een overzicht per product_id en geeft dan de 'periodes' weer die een bepaalde sales_id 'bezig' was met dat product. Althans ik denk dat je die richting op wil. In MSSQL zou je nog met een CTE kunnen werken zodat je niet twee keer dezelfde subquery hebt staan.

Op basis van dit overzicht kun je per sales_id weer een min/max doen per product.

SIZE does matter.
"You're go at throttle up!"


Acties:
  • 0Henk 'm!

  • .Johnny
  • Registratie: September 2002
  • Laatst online: 16-05 12:24
Ha, ja precies de kant die ik op zat te denken. Alleen zit je dan ermee dat je niet alleen de volgende joined, maar ook degene daarna en daarna en daarna etc. Want niet alleen voor de enige "volgende" maar elke volgende geldt dat de tijd groter is dan elke vorige. Elke rij zou dus maar maximaal met 1 rij van zichzelf mogen joinen.
-- edit, die haal je er weer uit met de MIN(b.timeofsale). dat moet wel een optie zijn ja. Vraag me wel af hoe efficient die 33M*33M join gaat worden.

[Voor 18% gewijzigd door .Johnny op 30-05-2011 17:02]


Acties:
  • 0Henk 'm!

  • Skinny
  • Registratie: Januari 2000
  • Laatst online: 11:05

Skinny

DIRECT!

Inderdaad kan dit wellicht een zware query worden afhankelijk van de echte situatie. Tevens hangt het een beetje af van hoe de resultaat van deze query gebruikt moet gaan worden of mijn idee ook echt bruikbaar is. TS zal daar evt. wat meer info over moeten geven.

SIZE does matter.
"You're go at throttle up!"


Acties:
  • 0Henk 'm!

  • Voutloos
  • Registratie: Januari 2002
  • Niet online
Het is historische data, aka de uitkomst van records uit het verleden (op de laatste verkoopstreak na) zullen niet meer veranderen. Je hoeft de data dus maar 1x te schrijven, dus dan voldoet de botte oplossing wellicht ook gewoon wel. ;) Dwz. Maak een tabeldefinitie voor hetgeen je wilt hebben (de resultset in de ts) en vul die gewoon met een loopje. Doet nu 1 keer pijn, maar kan ook op een aparte DB gedaan worden. En dan voortaan bijhouden en dan stelt het niets meer voor. :)

{signature}


Acties:
  • 0Henk 'm!

  • .Johnny
  • Registratie: September 2002
  • Laatst online: 16-05 12:24
Ik doe het nu uiteindelijk toch maar met een script ipv een enkelvoudige query. Het voorbeeld van Skinny zou nog een keer met zichzelf gejoined moeten worden, omdat het per record de volgende aangeeft; als die dan ook nog eens dezelfde timestamp zouden hebben heb je weer een probleem, dus moet je dat ook gaan ondervangen. Een enkele query die de select op ORDER doet werkt nu prima; ik schrijf alles naar een tekst bestand en die lees ik in 1x terug. Dat werkt soepel genoeg gelukkig!

  • n3ck
  • Registratie: Mei 2002
  • Laatst online: 04-06 05:50
moet toch zo kunnen?

select t.*, lead(t.time) over (partition by product_id order by time asc) next_time from table t

Acties:
  • 0Henk 'm!

  • cariolive23
  • Registratie: Januari 2007
  • Laatst online: 30-03 10:13
n3ck schreef op donderdag 02 juni 2011 @ 07:50:
moet toch zo kunnen?

select t.*, lead(t.time) over (partition by product_id order by time asc) next_time from table t
Vast wel, maar MySQL (en daar lijkt het over te gaan: INSERT IGNORE, ON DUPLICATE KEY) kent deze functionaliteit niet.

Met SQL Server en PostgreSQL lijkt mij dit wel de meest handige aanpak.
Pagina: 1


Tweakers maakt gebruik van cookies

Tweakers plaatst functionele en analytische cookies voor het functioneren van de website en het verbeteren van de website-ervaring. Deze cookies zijn noodzakelijk. Om op Tweakers relevantere advertenties te tonen en om ingesloten content van derden te tonen (bijvoorbeeld video's), vragen we je toestemming. Via ingesloten content kunnen derde partijen diensten leveren en verbeteren, bezoekersstatistieken bijhouden, gepersonaliseerde content tonen, gerichte advertenties tonen en gebruikersprofielen opbouwen. Hiervoor worden apparaatgegevens, IP-adres, geolocatie en surfgedrag vastgelegd.

Meer informatie vind je in ons cookiebeleid.

Sluiten

Toestemming beheren

Hieronder kun je per doeleinde of partij toestemming geven of intrekken. Meer informatie vind je in ons cookiebeleid.

Functioneel en analytisch

Deze cookies zijn noodzakelijk voor het functioneren van de website en het verbeteren van de website-ervaring. Klik op het informatie-icoon voor meer informatie. Meer details

janee

    Relevantere advertenties

    Dit beperkt het aantal keer dat dezelfde advertentie getoond wordt (frequency capping) en maakt het mogelijk om binnen Tweakers contextuele advertenties te tonen op basis van pagina's die je hebt bezocht. Meer details

    Tweakers genereert een willekeurige unieke code als identifier. Deze data wordt niet gedeeld met adverteerders of andere derde partijen en je kunt niet buiten Tweakers gevolgd worden. Indien je bent ingelogd, wordt deze identifier gekoppeld aan je account. Indien je niet bent ingelogd, wordt deze identifier gekoppeld aan je sessie die maximaal 4 maanden actief blijft. Je kunt deze toestemming te allen tijde intrekken.

    Ingesloten content van derden

    Deze cookies kunnen door derde partijen geplaatst worden via ingesloten content. Klik op het informatie-icoon voor meer informatie over de verwerkingsdoeleinden. Meer details

    janee