[SQL] group by obv order

[.Johnny]

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.

[P_de_B]

Dit moet je met een Group By wel kunnen oplossen

[.Johnny]

Niet zo 1-2-3 als de lengte van je post suggereert. Wanneer ik

SQL:

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 ]

[Voutloos]

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.

[.Johnny]

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 ]

[Skinny]

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:

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.

[.Johnny]

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 ]

[Skinny]

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.

[Voutloos]

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.

[.Johnny]

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]

moet toch zo kunnen?

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

[cariolive23]

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.