Vsechny kombinace - TIP

[tomas.emresz]

Zdarec,

ať se jen neptám, taky nabidnu tip. Klasická uloha (kterou, ale dost firem nemá vyrešenou dobře, či spíše vůbec). Máme zboží, máme jeho parametry a tudíž máme kombinace těhto parametrů. Je třeba vzít v potaz, že kartézský součin je prostě obří, nicméně zde se nezabývám ukládáním milionu kombinací, ale třeba výpočtem typového čísla, definicí ceny apod (a v menších množstvích i oním ukládáním).

Tato úloha je - například v Shoptetu - omezena na 3 parametry a onehdá se mě jeden truhlář ptal, jak tam narve stůl (výška,šířka,délka) a k tomu typ nohou a třeba barvy. Nijak. Poslali jsm tedy dotaz a je to omezeno na 3 parametry z důvodu zatěžování serverů.

Takové Etsy je omezeno dokonce na parametry 2.

Protože jsem se nepustil myšlenky, že jednou napíšu vlastní eshopové řešení (a taky budu astronaut), přemýšlel jsem, jak udělat tohle universálně? Jasně, můžeme použít 1x,2x,3x,10x Cross join, ale to je porád omezeno na onen počet vstupů a já nemám rád natvrdo omezené věci.

Výsledek je možno použít ať již pro vygenerování (a uložení) všech kombinací, ale třeba také pro výpočet ceny - jednotlivé parametry mohou mít koeficienty ceny a s těmi můžeme dále počítat, nebo třeba pro výpočet typového čísla - zde pro truhláře např ST_100_160-70-A5-MODRA.

Vezměme tedy základ a to jsou jakási ID (barev, rozměrů, materiálů, šířky palce, ale i příplatkové parametry - olemovat, zalakovat, zalakovat čím atd atd atd).

BARVY : "1LG","2mh","3mo","4rh","5ro"

VELIKOSTI : "1S","2M","3L"

LEMOVANI : "1lem","2neLEM"

PARAMETR A : "a","b"

PARAMETR B : "1","2"

Předpokládám, že víceméně kdokoliv je schopen si představit a sestavit takové tabulky na svém shopu. Je jedno, jestli se nyní bavíme o hodnotách, či identitách, či json objektech s dalšími daty (to v této verzi nemám, ale není to tolik podstatná úprava).

Převedeme si tedy tyto kombinace zboží na pole JSON(b)

["1LG","2mh","3mo","4rh","5ro"],["1S","2M","3L"],["1lem","2neLEM"],["a","b"],["1","2"]

src_data as(select '[["1LG","2mh","3mo","4rh","5ro"],["1S","2M","3L"],["1lem","2neLEM"],["a","b"],["1","2"]]'::jsonb as pole)

což je náš vstup do algoritmu.

Jak ale docílit zrušení onoho omezení? Rekursivní funkcí. 

Nejprve vstup převedeme na jednotlivá pole:

dle_parametru as (select x.* from src_data,jsonb_array_elements (pole) with ordinality as x(parametry,id))

Zjistíme počet parametrů:

pocet as (select max(id) as pocet from dle_parametru)

jednotlivé parametry taktéž převedeme na jednotlivé položky:

parametry as (select dp.id as id_parametru, y.id as id_hodnoty,y.parametr as hodnota from dle_parametru dp,jsonb_array_elements (dp.parametry) with ordinality as y(parametr,id))

A nyní onen zázrak - rekurze přes n=1..pocet parametru

      t(lev,id_parametru,id_hodnoty,hodnota,hodnoty) as

    (

       select 1,*,array[hodnota::text] from parametry where id_parametru=1

       UNION ALL

    SELECT lev+1,p.id_parametru,p.id_hodnoty,p.hodnota,hodnoty || p.hodnota::text

    from parametry p,t

    where p.id_parametru=lev+1 and lev < (select pocet from pocet)

    )

Abychom ziskáli trochu zobrazitelný výsledek, jsou zapotřebí ještě dvě věci a sice převést pole na řetezec (samozřejmě v algoritmu použitém v živém prostředí to již bude úplně jinak)

select array_to_string(hodnoty,' | ') as txt from t, pocet 

A omezit výstup pouze na položky, které obsahují stejný počet prvků, jako délka vstupního pole. Toto je možno vynechat, například pro stromovou definici ceny s dedičností (opravdu nechci, aby nějaký šmudla tohle zadal při 5 parametrech kařdý s 5 hodnotami, vypsal to do HTML a sekretářko dodej tam ceny.. :) ) Tuto dědičnost zatím nemám, ale obecně s ní počítám. Například tak, že stůl 100x80x70 bude mít nějakou cenu a k ní se připočítá koeficient za barvu a absolutní cena za nohy. Ostatně i cena za stůl 100x80 může být nějaka a koeficientem dořešit i samotnou výšku.

where lev=pocet 

Jak jsem psal, podobně je možnost takto zkombinovat libovolná data, tudíž fantasii se meze nekladou. Například pro švadlenku asi nemá smysl předgenerovat x milionu kombinací oblečku, na druhou stranu pro zadání v eshopu (a hlavně eliminaci natvrdo zadaných počtu parametrů), proč ne. Samotné hodnoty rozměrových parametrů mohou vést k výpočtu ceny (minimálně orientační, když už ne přesné). Po upravení tohoto algoritmu je možné poskládat komplet JSON s daty výpočtu, neb vstupm klidně mohou být pole objektů:

{

   "typ" : "barva",

   "hodnota" : "zelena A6",

   "koeficient_rozmeru" : 0.15,

   "cena_barvy" : 555

}

Co dalšího od algoritmu chci v budoucnu?

Přizpůsobení vstupu pro JSON(b) item v poli

Možnost "rozdělení" pole dle variant - pro například export na Shoptet se šikně vygenerovat X typů zboží každé s max třemi parametry, výsledkem by tedy mělo být totéž co nyní ale s přidaným sloupečkem sub_id_zbozi, (my například máme klasickou linii produktu a k tomu limited edition nějaké barvy, tu chci mít obecně v shopu zvlášť, byť se jedná o to samé zboží. Zde bych tedy pro danou barvu zadal výjimku a ve výsledcích by byla označena třeba hodnotou 2, zatímco klasická linie 1 (opět se bavím o tom, udělat to obecně, v tomto případě si to samozřejmě mohu šoupnout do JSONu vstupních dat.

A abych nezapomel, cely kod:

with recursive

    src_data as(select '[["1LG","2mh","3mo","4rh","5ro"],["1S","2M","3L"],["1lem","2neLEM"],["a","b"],["1","2"]]'::jsonb as pole),

    dle_parametru as (select x.* from src_data,jsonb_array_elements (pole) with ordinality as x(parametry,id)),

    pocet as (select max(id) as pocet from dle_parametru),

    parametry as (select dp.id as id_parametru, y.id as id_hodnoty,y.parametr as hodnota from dle_parametru dp,jsonb_array_elements (dp.parametry) with ordinality as y(parametr,id)),

    t(lev,id_parametru,id_hodnoty,hodnota,hodnoty) as

    (

       select 1,*,array[hodnota::text] from parametry where id_parametru=1

       UNION ALL

    SELECT lev+1,p.id_parametru,p.id_hodnoty,p.hodnota,hodnoty || p.hodnota::text

    from parametry p,t

    where p.id_parametru=lev+1 and lev < (select pocet from pocet)

    )

select array_to_string(hodnoty,' | ') as txt from t, pocet where lev=pocet    

order by hodnoty

P.S.: Chlapi, buďte milostivi, jistě to jde napsat i lépe, jen mi to přišlo jako zajímavé použití rekurze.

Tomas