[ MySQL ] Gemiddelde stapgrootte berekenen

[RickyHeijnen]

In een stukje software dat wordt gebruikt in een magazijn wordt per pakbon bijgehouden hoe lang een persoon erover doet om deze bij elkaar te pakken. Dit wordt gebruikt om van alle pakbonnen die nog open staan een indicatie te geven hoe lang het duurt om die te pakken.

Om tot die indicatie te komen kijk ik nu naar de afgelopen 14 dagen en neem de gemiddelde tijd die het duurt om een pakbon te pakken gedeeld door het aantal items op een pakbon. Dat gemiddelde vermenigvuldig ik vervolgens met het totaal openstaande items. Afijn, dat was een goed begin en geeft al een goed beeld voor de medewerkers in het magazijn.

Nu wil ik de indicatie wat verfijnen door meer te kijken naar de afstand die ze moeten lopen. Een pakbon met 2 items die naast elkaar liggen is natuurlijk veel sneller gepakt dan een pakbon met 2 items waarvoor ze 5 minuten moeten lopen.

Ik heb 3 tabellen, ik weet van elk item dat gepakt moet worden waar het ligt en hoe ver deze uit elkaar liggen. De kolom stockplace.rang is zo opgebouwd dat deze een goede weerspiegeling is van de afstanden tussen de verschillende voorraadplaatsen. Dus die kan ik gebruiken.

SQL:

CREATE TABLE `clientpackage` (
    `clientpackage_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `account_id` SMALLINT(5) UNSIGNED NOT NULL,
    `created` TIMESTAMP NOT NULL,
    PRIMARY KEY (`clientpackage_id`)
);

SQL:

CREATE TABLE `clientpackageitem` (
    `clientpackageitem_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `clientpackage_id` INT(10) UNSIGNED NOT NULL,
    `clientorderitem_id` INT(10) UNSIGNED NOT NULL,
    `stockplace_id' INT(10) UNSIGNED NOT NULL,
    `packed` DATETIME NULL DEFAULT NULL,
    `packed_by` INT(10) UNSIGNED NULL DEFAULT NULL,
    PRIMARY KEY (`clientpackageitem_id`)
);

SQL:

CREATE TABLE `stockplace` (
    `stockplace_id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    `stockplacetype_id` INT(11) NOT NULL,
    `stockplace` VARCHAR(45) NOT NULL,
    `description` VARCHAR(512) NULL DEFAULT NULL,
    `rang` INT(12) NULL DEFAULT NULL,
    PRIMARY KEY (`stockplace_id`)
);

Dit is de query die ik nu heb om er wat mee te spelen:

SQL:

SELECT cp.clientpackage_id,
    COUNT(*) AS 'items',
    (MAX(cpi.packed)-MIN(cpi.packed)) AS 'total_time',
    (MAX(sp.rang)-MIN(sp.rang)) AS 'total_length',
    ((MAX(cpi.packed)-MIN(cpi.packed)) / COUNT(*)) AS 'avg_time',
    ((MAX(sp.rang)-MIN(sp.rang)) / COUNT(*)) AS 'avg_length'
FROM clientpackage cp
INNER JOIN clientpackageitem cpi ON cpi.clientpackage_id = cp.clientpackage_id
INNER JOIN stockplace sp ON sp.stockplace_id = cpi.stockplace_id
WHERE cp.created > DATE_SUB(NOW(), INTERVAL 14 DAY)
GROUP BY cp.clientpackage_id
ORDER BY cp.clientpackage_id DESC

Ik heb nu de totale en gemiddelde loop-afstand van een pakbon waar ik wat mee kan rekenen, maar wat ik eigenlijk wil is de gemiddelde stapgrootte weten. Zodat als product A, B, C en D allemaal naast elkaar liggen en alleen product E helemaal aan de andere kant van het magazijn ligt dat de tijdsindicatie lager is dan als A, B, C, D en E allemaal verspreid liggen door het magazijn.

Hoe bereken ik in een query de stapgrootte tussen verschillende clientpackageitem_id en gooi ik daar AVG() overheen?

[BCC]

Wikipedia: Travelling salesman problem - Dit kan vast in MySQL, maar ik denk dat je beter in je software even brute-force de snelste route kan berekenen. Afhankelijk van de layout van je magazijn kun je waarschijnlijk gewoon nearest neighbour doen als je de locatie van item meestuurt vanuit Mysql.

[ Voor 24% gewijzigd door BCC op 24-12-2018 11:40 ]

[RickyHeijnen]

Ik hoef niet de snelste route te berekenen, dat is al geprogrammeerd. Je kunt namelijk maar op 1 manier door het magazijn heen lopen. Dus ze lopen altijd dezelfde route. De route wordt bepaald door stockplace.rang ASC. Die kolom bevat een oplopend nummer die de route door het magazijn bepaald. Door het verschil te pakken tussen 2 rangen weet ik ongeveer hoe ver het lopen is.

Dit is waar ik mee zat te stoeien maar heb het idee dat dit niet de beste manier is:

SQL:

SELECT cp.clientpackage_id,
    sp.rang,
    (
        SELECT sp2.rang FROM
        clientpackageitem cpi2
        INNER JOIN stockplace sp2 ON sp2.stockplace_id = cpi2.stockplace_id
        WHERE sp2.rang > sp.rang AND cpi2.clientpackage_id = cpi.clientpackage_id
        ORDER BY sp2.rang ASC LIMIT 1
     ) AS 'rang_next'
FROM clientpackageitem cpi
INNER JOIN clientpackage cp ON cpi.clientpackage_id = cp.clientpackage_id
INNER JOIN stockplace sp ON sp.stockplace_id = cpi.stockplace_id
WHERE cp.created > DATE_SUB(NOW(), INTERVAL 14 DAY);