You can create the pair of triggers ON INSERT and ON UPDATE that perform any desired transformations 'on the fly'
CREATE TRIGGER `myuuid_autoinserter`
BEFORE INSERT ON `table` -- or BEFORE UPDATE
FOR EACH ROW
BEGIN
SET loooonguuid = CONCAT(OLD.uuid, OLD.uuid, OLD.uuid, OLD.uuid)
SET NEW.uuid = loooonguuid;
END
When you run some INSERT or UPDATE query on the table trigger will replace submitted value of the uuid by string stored in the loooonguuid variable.
Sure you can do as complex and randomized transformation as you want.
"Explode-Implode" syndrome causes the inflated SUM.
- The
JOINs are done. This gives an intermediate table with lots of rows.
SUM() and other aggregates are performed.- The
GROUP BY shrinks it back down to a small number of rows.
Where practical, this is a convenient solution; note that there is no JOIN or GROUP BY:
SELECT
( SELECT SUM(...) ... ) AS sum_1,
( SELECT SUM(...) ... ) AS sum_2
FROM ...
But that won't work here. Instead, let's compute the aggregates in "derived" subqueries:
SELECT COALESCE(uk.qty, 0) AS QTY_sold_in_uk,
COALESCE(de.qty, 0) AS QTY_sold_in_de,
...
FROM tbl_inventory AS ti
LEFT JOIN ( SELECT sku, SUM(qty) AS qty ) AS uk USING(sku)
LEFT JOIN ( SELECT sku, SUM(qty) AS qty ) AS de USING(sku)
This way, the aggregation will be confined to the the one table. Also, the LEFT allows for an item not to be sold in some country, then the COALESCE deals with the NULL. Again, no GROUP BY.
This pattern begs for at least 5.6 because the derived tables have no index. However 5.6 will dynamically build one.
Final form
SELECT
ti.sku,
ti.quantity,
-- UK
COALESCE(suo_uk.sku, 'Not Sold') AS Unit_sold_in_uk,
COALESCE(suo_uk.units_ordered, 0) AS QTY_in_uk,
-- De
COALESCE(suo_de.sku, 'Not Sold') AS Unit_sold_in_de,
COALESCE(suo_de.units_ordered, 0) AS QTY_in_de,
-- Fr
COALESCE(suo_fr.sku, 'Not Sold') AS Unit_sold_in_fr,
COALESCE(suo_fr.units_ordered, 0) AS QTY_in_fr,
-- ES
COALESCE(suo_es.sku, 'Not Sold') AS Unit_sold_in_es,
COALESCE(suo_es.units_ordered, 0) AS QTY_in_es,
-- It
COALESCE(suo_it.sku, 'Not Sold') AS Unit_sold_in_it,
COALESCE(suo_it.units_ordered, 0) AS QTY_in_it
FROM
tbl_inventory AS ti
LEFT JOIN ( SELECT sku, sum(units_ordered) as units_ordered
FROM tbl_sku_units_order
GROUP BY sku ) AS suo_uk
ON suo_uk.sku = ti.sku
LEFT JOIN ( SELECT sku, sum(units_ordered) as units_ordered
FROM tbl_sku_units_order_de
GROUP BY sku ) AS suo_de
ON suo_de.sku = ti.sku
LEFT JOIN ( SELECT sku, sum(units_ordered) as units_ordered
FROM tbl_sku_units_order_es
GROUP BY sku ) AS suo_es
ON suo_es.sku = ti.sku
LEFT JOIN ( SELECT sku, sum(units_ordered) as units_ordered
FROM tbl_sku_units_order_fr
GROUP BY sku ) AS suo_fr
ON suo_fr.sku = ti.sku
LEFT JOIN ( SELECT sku, sum(units_ordered) as units_ordered
FROM tbl_sku_units_order_it
GROUP BY sku ) AS suo_it
ON suo_it.sku = ti.sku
GROUP BY ti.sku
Best Answer
Instead of proliferating the number of rows, let's add a cumulative total:
Then get the last number, 9, for use below.
Index the new column.
Fetch random item thus:
To set the column and get the
9, perform this task:Whenever you add items or change the weights, rerun the two statements above.
(This code should work on any version of MySQL/MariaDB. There may be a "window function" that works better in new versions.)