[SQL] 2 rijen combineren naar 1 regel.

[Yagermeister]

Mijn vraag

Ik heb een database met daarin een hele berg regels welke verplaatsingen zijn die door ons gestart zijn. Voor elke verplaatsing zijn er 2 regels. de eerste met het magazijn waar het vandaan komt (magazijn A om het makkelijk te maken) en de 2de regel is het magazijn waar het heen gaat (magazijn .

Onderstaand zijn een 2-tal verplaatsingen waarbij het het eerste veld laat zien welk van de 2 regels als eerste komt voor het geval dat het niet geheel duidelijk is.

SQL:

idproduct_stock_history idproduct   idwarehouse iduser  old_stock   stock_change    new_stock   change_type reason  changed_at  sku

90137019    13833740    4863    6408    1   -1  0   moved   Interco 2020-09-01 17:03:05 A0190378
90137020    13833740    4861    6408    0   1   1   moved   Interco 2020-09-01 17:03:05 A0190378

90137035    13833740    4861    6408    1   -1  0   moved   Interco 2020-09-01 17:03:16 A0190378
90137036    13833740    4862    6408    0   1   1   moved   Interco 2020-09-01 17:03:16 A0190378

Wat ik nu graag zou willen hebben is dat deze op 1 regel staan zodat ik daar verdere bewerkingen mee kan doen: (datum wordt formatted op alleen de dag)

code:

changed_at  sku stock_change(oudste regel)  stock_change(nieuwste regel)    idwarehouse (oudste regel)  idwarehouse (nieuwste regel)    reason
2020-09-01  A0190378    -1  1   4863    4861    Interco

Het probleem wat ik heb is dat er geen unieke identifier is wat beide regels met elkaar "verbindt". Het enigste wat in de buurt komt is het idproduct_stock_historyid wat steeds 1 hoger is als de vorige regel. Voor zover bekend is dit nooit anders. Ik heb echter geen flauw idee hoe ik deze regels aan elkaar koppel met het id. Wel heb ik onderstaande queries geprobeerd echter ben ik bang dat hierdoor verschil kan optreden.

SQL:

SELECT
    DATE_FORMAT(picqer_historie.changed_at, "%d-%m-%Y") AS Date, 
    picqer_historie.sku, 
    picqer_products.`name`, 
    naar.stock_change AS qty, 
    IF(picqer_historie.idwarehouse = '4861', 'Magazijn B', IF(picqer_historie.idwarehouse = '4862', 'Magazijn C', IF(picqer_historie.idwarehouse = '4863', 'Magazijn A', 'Onbekend'))) AS van_idwarehouse, 
    IF(naar.idwarehouse = '4861', 'Magazijn B', IF(naar.idwarehouse = '4862', 'Magazijn C', IF(naar.idwarehouse = '4863', 'Magazijn A', 'Onbekend'))) AS naar_idwarehouse, 
    picqer_historie.reason
FROM
    picqer_historie
    LEFT JOIN
    picqer_historie AS naar
    ON 
        DATE_FORMAT(picqer_historie.changed_at, "%H:%i %d-%m-%Y") = DATE_FORMAT(naar.changed_at, "%H:%i %d-%m-%Y") AND
        picqer_historie.sku = naar.sku
    INNER JOIN
    picqer_products
    ON 
        picqer_historie.sku = picqer_products.sku
WHERE
    picqer_historie.stock_change < '0' AND
    picqer_historie.change_type LIKE '%moved%' AND
    naar.stock_change > '0' AND
    YEAR(picqer_historie.changed_at) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH) AND
    MONTH(picqer_historie.changed_at) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)

De kans is klein dat dit mis kan gaan maar aangezien het totaal niet netjes is ben ik eigenlijk op zoek naar een oplossing zodat dit altijd klopt. Weet iemand hoe ik deze regels het beste kan koppelen als het ID niet een goed idee is?

[Gomez12]

Hoe bedoel je dat er geen unieke identifier is?

Uit je gewenste rapportage blijkt toch gewoon een samengestelde identifier, wat moet er anders met je overzicht gebeuren?

Namelijk : changed_at sku idwarehouse (oudste regel) reason

Oftewel eerst haal je de unieke changed_at / sku / reason op en daarbij de oudste idwarehouse
En daarna join je daarna de nieuwe informatie eraan vast.

Nog wat sums voor je changes en je hebt toch wat je hebben wil?

[Yagermeister]

Gomez12 schreef op woensdag 14 oktober 2020 @ 14:43:
Hoe bedoel je dat er geen unieke identifier is?

Uit je gewenste rapportage blijkt toch gewoon een samengestelde identifier, wat moet er anders met je overzicht gebeuren?

Namelijk : changed_at sku idwarehouse (oudste regel) reason

Oftewel eerst haal je de unieke changed_at / sku / reason op en daarbij de oudste idwarehouse
En daarna join je daarna de nieuwe informatie eraan vast.

Nog wat sums voor je changes en je hebt toch wat je hebben wil?

Ik denk niet dat je helemaal snap. De waardes die jij aangeeft zijn namelijk niet uniek waardoor ik beide regels niet kan koppelen. Ik heb geprobeerd om sku en reason tegelijk te matchen echter kreeg ik daardoor een aantal dubbele regels waardoor dit niet goed werkte.

[Gomez12]

De combinatie van die waardes die moet uniek zijn, als dat niet zo is hoe wil je het dan gerapporteerd hebben?

Want of je moet meerdere regels toevoegen aan je rapportage waardoor blijkt dat mijn gegeven velden niet uniek zijn (en dan kan je uit die rapportage wel de unieke velden vissen) of je rapportage klopt niet.

Simpel gezegd : In je rapportage is het uniek gemaakt, oftewel ga na waarop je rapportage uniek is en dat is je identifier.

Ik neem tenminste aan dat het volgende niet toegestaan is :

code:

changed_at  sku stock_change(oudste regel)  stock_change(nieuwste regel)    idwarehouse (oudste regel)  idwarehouse (nieuwste regel)    reason
2020-09-01  A0190378    -1  1   4863    4861    Interco
2020-09-01  BBBBBBB    -8  4   4863    4861    Interco
2020-09-01  A0190378    -8  4   4863    4861    Interco
2020-09-01  A0190378    -3  2   4863    4861    Interco

Maar dat dat moet worden :

code:

changed_at  sku stock_change(oudste regel)  stock_change(nieuwste regel)    idwarehouse (oudste regel)  idwarehouse (nieuwste regel)    reason
2020-09-01  A0190378    -12  7   4863    4861    Interco
2020-09-01  BBBBBBB    -8  4   4863    4861    Interco

[MSteverink]

Uit mijn hoofd:

SQL:

SELECT *
 FROM tabel AS ta
 JOIN tabel AS tb
  ON ta.idproduct_stock_history+1=tb.idproduct_stock_history
  AND ta.idproduct=tb.idproduct
 WHERE ta.idproduct_stock_history IsEenOnevenGetal

Ik ga ervan uit dat idproduct_stock_history een oplopende, ononderbroken reeks is die begint bij 1.

Je kunt ook nog JOINen op SKU, tenzij die 1 op 1 is gekoppeld aan idproduct. (Dan kan het nog steeds, maar dat is niet zinvol meer.)

[ Voor 36% gewijzigd door MSteverink op 14-10-2020 18:00 ]

[CyBeRSPiN]

Met een LEAD() window function moet dit ook kunnen: https://www.sqltutorial.org/sql-window-functions/sql-lead/
Dan heb je toegang tot de volgende rij, als je dan op id sorteert moet het goedkomen.
Even een learning curve, maar zo'n window function is VEEEL sneller dan je complete stock history tabel gaan self-joinen, als dat een omvangrijke tabel is.

[Voutloos]

Yagermeister schreef op woensdag 14 oktober 2020 @ 13:55:
Het enigste wat in de buurt komt is het idproduct_stock_historyid wat steeds 1 hoger is als de vorige regel. Voor zover bekend is dit nooit anders.

De join clause van @MSteverink lijkt me de oplossing. Misschien de where clause ipv oneven/even dan op ‘from < 0’ en je bent er?

Als je ook maar enig invloed hebt op toekomstige data, zou ik een feature request indienen om een transactieId te introduceren. Opeenvolgende id’s klinkt enigszins gevoelig voor race condities ook.

[ Voor 71% gewijzigd door Voutloos op 14-10-2020 16:17 ]

[Yagermeister]

Gomez12 schreef op woensdag 14 oktober 2020 @ 15:38:
De combinatie van die waardes die moet uniek zijn, als dat niet zo is hoe wil je het dan gerapporteerd hebben?

Want of je moet meerdere regels toevoegen aan je rapportage waardoor blijkt dat mijn gegeven velden niet uniek zijn (en dan kan je uit die rapportage wel de unieke velden vissen) of je rapportage klopt niet.

Simpel gezegd : In je rapportage is het uniek gemaakt, oftewel ga na waarop je rapportage uniek is en dat is je identifier.

Ik neem tenminste aan dat het volgende niet toegestaan is :

code:

changed_at  sku stock_change(oudste regel)  stock_change(nieuwste regel)    idwarehouse (oudste regel)  idwarehouse (nieuwste regel)    reason
2020-09-01  A0190378    -1  1   4863    4861    Interco
2020-09-01  BBBBBBB    -8  4   4863    4861    Interco
2020-09-01  A0190378    -8  4   4863    4861    Interco
2020-09-01  A0190378    -3  2   4863    4861    Interco

Maar dat dat moet worden :

code:

changed_at  sku stock_change(oudste regel)  stock_change(nieuwste regel)    idwarehouse (oudste regel)  idwarehouse (nieuwste regel)    reason
2020-09-01  A0190378    -12  7   4863    4861    Interco
2020-09-01  BBBBBBB    -8  4   4863    4861    Interco

Het kan niet voorkomen dat een SKU meerdere keren voorkomt in dezelfde "transfer". Het is meteen de gehele voorraad van dat product wat in dat magazijn staat wat omgeplaatst wordt. De eerste code blok wat je aangeeft kan dus al niet.

Even vanuit de 2de blok gezien (omdat die wel correcter is) zou het goede voorbeeld als volgt zijn:

code:

changed_at  sku stock_change(oudste regel)  stock_change(nieuwste regel)    idwarehouse (oudste regel)  idwarehouse (nieuwste regel)    reason
2020-09-01  A0190378    -12  12   4863    4861    Interco
2020-09-01  BBBBBBB    -8  8   4863    4861    Interco

Wat ik bij magazijn A eraf haal moet bij magazijn B erbij komen. Als hier verschil in is kan het dus niet de correcte regel zijn waarmee die samengevoegd is. De reden dat wij het wel eruit kunnen vissen met de hand is dat wij wel weten wat we getransfered hebben echter willen we juist het makkelijker maken voor diegene die het doet controleren.

MSteverink schreef op woensdag 14 oktober 2020 @ 15:47:
Uit mijn hoofd:

SQL:

SELECT *
 FROM tabel AS ta
 JOIN tabel AS tb
  ON ta.idproduct_stock_history+1=tb.idproduct_stock_history
  AND ta.idproduct=tb.idproduct
 WHERE ta.idproduct_stock_history IsEenOnevenGetal

Ik ga ervan uit dat idproduct_stock_history een oplopende, ononderbroken reeks is die begint bij 1.

Je kunt ook nog JOINen op SKU, tenzij die 1 op 1 is gekoppeld aan idproduct. (Dan kan het nog steeds, maar dat is niet zinvol meer.)

De stock_history is opeenlopend maar kan onderbroken zijn (picqer heeft volgens mij geen losse database per klant). Wel is het zo dat bij alle regels wat ik gecontroleerd heb de 2de regel altijd +1 is op de vorige.

Joinen op sku doe ik wel al maar is net zo nuttig om idproduct te gebruiken aangezien dit hetzelfde is. Aan onze eigen achterkant matchen we juist alles op sku waardoor ik deze ook het liefste gebruik. Wel ga ik jouw code even gebruiken morgen om te zien wat die oplevert. Ik zie namelijk dat jij bij de join een +1 doet wat ik ook geprobeerd had maar hij me de melding gaf dat ik dit niet kon doen met gegroepeerde velden (wat ik overigens niet heb)

CyBeRSPiN schreef op woensdag 14 oktober 2020 @ 15:54:
Met een LEAD() window function moet dit ook kunnen: https://www.sqltutorial.org/sql-window-functions/sql-lead/
Dan heb je toegang tot de volgende rij, als je dan op id sorteert moet het goedkomen.
Even een learning curve, maar zo'n window function is VEEEL sneller dan je complete stock history tabel gaan self-joinen, als dat een omvangrijke tabel is.

Dit ga ik zeker even nakijken. Het is inderdaad een zeer omvangrijke tabel en hij groeit behoorlijk elke dag.

Voutloos schreef op woensdag 14 oktober 2020 @ 16:06:
[...]

De join clause van @MSteverink lijkt me de oplossing. Misschien de where clause ipv oneven/even dan op ‘from < 0’ en je bent er?

Als je ook maar enig invloed hebt op toekomstige data, zou ik een feature request indienen om een transactieId te introduceren. Opeenvolgende id’s klinkt enigszins gevoelig voor race condities ook.

Ik heb helaas geen invloed op de toekomstige data. Zoals wij het doen is helaas vrij uniek blijkbaar en is niet zoals de grijze massa het nodig heeft. Er zijn meer dingen in Picqer wat helaas nogal simpel gemaakt zijn maar dat is een heel ander iets. Het is een leuk systeem maar heeft ook zijn nukjes.

Die join clause ga ik morgen even heen kijken. Alleen die from < 0 snap ik niet helemaal wat je ermee bedoelt. Als ik het misschien toch goed begrijp zou dit betekenen dat hij kan joinen op elk willekeurig veld toch?

[Voutloos]

Yagermeister schreef op woensdag 14 oktober 2020 @ 21:19:
[...]

Die join clause ga ik morgen even heen kijken. Alleen die from < 0 snap ik niet helemaal wat je ermee bedoelt. Als ik het misschien toch goed begrijp zou dit betekenen dat hij kan joinen op elk willekeurig veld toch?

Ik bedoel dat je de WHERE picqer_historie.stock_change < '0' ofzo doet om te zorgen dat je correct paar van rijen hebt ipv even/oneven. De where uit je startpost dus.
(De quotes hoeven hier niet want je vergelijkt ints btw)

[Sander]

Waarom mail je de mannen van Picqer niet even? Ze zullen dit misschien eerder bij de hand gehad hebben en je kunnen helpen? Je data lijkt uit hun API te komen (https://picqer.com/en/api/stockhistory) dus mogelijk kunnen ze je daar ook helpen om iets wat wel uniek is toe te voegen aan de response?

[Yagermeister]

Voutloos schreef op woensdag 14 oktober 2020 @ 22:12:
[...]
Ik bedoel dat je de WHERE picqer_historie.stock_change < '0' ofzo doet om te zorgen dat je correct paar van rijen hebt ipv even/oneven. De where uit je startpost dus.
(De quotes hoeven hier niet want je vergelijkt ints btw)

Die heb ik zelf daar niet gezet dus die zal van navicat komen. Waarschijnlijk per ongeluk verkeerd geklikt.

Sander schreef op woensdag 14 oktober 2020 @ 22:25:
Waarom mail je de mannen van Picqer niet even? Ze zullen dit misschien eerder bij de hand gehad hebben en je kunnen helpen? Je data lijkt uit hun API te komen (https://picqer.com/en/api/stockhistory) dus mogelijk kunnen ze je daar ook helpen om iets wat wel uniek is toe te voegen aan de response?

Die data komt inderdaad uit hun API. Helaas is de dat as is en helpen zij daar vrij weinig mee. Dit heb ik al met meerdere dingen gehad die ik via hun api heb opgehaald.


[Update]
Ik heb even de code van @MSteverink aangepast naar de goede kolommen die ik nodig heb en heb onderstaande query eruit gekregen.

SQL:

SELECT 
    DATE_FORMAT(ta.changed_at, "%d-%m-%Y") AS Date,
    ta.sku, 
  picqer_products.`name`,
    ta.stock_change AS van_qty,
    tb.stock_change AS naar_qty,
    IF(ta.idwarehouse = '4861', 'Magazijn B', IF(ta.idwarehouse = '4862', 'Magazijn C', IF(ta.idwarehouse = '4863', 'Magazijn A', 'Onbekend'))) AS van_idwarehouse, 
  IF(tb.idwarehouse = '4861', 'Magazijn B', IF(tb.idwarehouse = '4862', 'Magazijn C', IF(tb.idwarehouse = '4863', 'Magazijn A', 'Onbekend'))) AS naar_idwarehouse, 
    ta.reason
 FROM picqer_historie AS ta
 INNER JOIN picqer_products ON ta.sku = picqer_products.sku
 
 JOIN picqer_historie AS tb
  ON ta.idproduct_stock_history+1=tb.idproduct_stock_history
  AND ta.idproduct=tb.idproduct
 WHERE ta.idproduct_stock_history %2 != 0 AND
     YEAR(ta.changed_at) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH) AND
    MONTH(ta.changed_at) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)

Dit lijkt te werken omdat ik results terug krijg in ieder geval. Wat ik alleen raar vindt is dat ik maar 126 regels krijg van de afgelopen maand terwijl ik bij mijn oude query een 426 stuks terug krijg. Ik ga ze maar even naast elkaar leggen en vergelijken met Picqer zelf om te zien waar dit verschil in zit

[Update2]

Ok het lijkt erop dat ik het probleem gevonden heb. Doordat er nog meer in de betreffende database staat kan het dus voorkomen dat ik niet de where op oneven moet zetten maar op even. Wat ik even zo snel als oplossing heb is dan 1x de query op oneven laten lopen en 1x op even. Die 2 files samenvoegen en probleem lijkend opgelost.

Als iemand hier toevallig een idee voor heeft houdt ik me aanbevolen.

[Update3]
Als ik dan ineens nadenk en ik haal "ta.idproduct_stock_history %2 = 0 AND" weg uit mijn query krijg ik wel het volle aantal. Daarmee lijkt het ook opgelost te zijn. Hier ga ik echter nog even mee verder testen.

[ Voor 49% gewijzigd door Yagermeister op 15-10-2020 13:48 . Reden: Toevoeging ]