[MySQL] SUM alle DISTINCT COUNTs in één query

[Matis]

Beste Devvers,

Wij hebben een priority queue in MySQL gebouwd. Hierin worden SKU's (code kolom) en het bijbehorende gewicht per SKU (weight kolom) opgeslagen.
Deze priority queue bevat SKU's welke opnieuw geïndexeerd dienen te worden. Naargelang de belangrijkheid van het aangepaste attribuut van het product wordt deze SKU in de tabel weggeschreven met corresponderend "gewicht". Een consumer lepelt op zijn gemak die lijst leeg, de SKU's met het hoogste gewicht komen als eerste aan de beurt en zo werkt hij de lijst langzaam weg / af.

Op dit moment heb ik de volgende MySQL query geschreven die per gewicht het aantal producten aangeeft dat nog geïndexeerd dient te worden:

SQL:

SELECT
    FORMAT(weight,0) AS weight,
    FORMAT(COUNT(DISTINCT(`code`)),0) AS total
FROM ProductService.product_export_update_list cpeul 
GROUP BY cpeul.weight;

Dit levert onderstaande lijst op:

+---------+-----------+
| weight  | total     |
+---------+-----------+
| 1,000   | 21,362    |
| 2,000   | 36,210    |
| 40,000  | 1,401,756 |
| 75,000  | 4,125,421 |
| 80,000  | 628,652   |
| 100,000 | 69,386    |
| 150,000 | 496,404   |
| 170,000 | 168       |
| 200,000 | 6,367     |
| 340,000 | 645       |
| 700,000 | 3,542     |
+---------+-----------+

Bovenstaande uitkomst wordt op een statuspagina weergegeven. Dit werkt allemaal naar behoren.

Nu is het verzoek gekomen of ook het totaal (row count) van de gehele lijst in hetzelfde tabelletje getoond kan worden.
Wat ik dus probeer te bereiken is het volgende:

+---------+-----------+
| weight  | total     |
+---------+-----------+
| 1,000   | 21,362    |
| 2,000   | 36,210    |
| 40,000  | 1,401,756 |
| 75,000  | 4,125,421 |
| 80,000  | 628,652   |
| 100,000 | 69,386    |
| 150,000 | 496,404   |
| 170,000 | 168       |
| 200,000 | 6,367     |
| 340,000 | 645       |
| 700,000 | 3,542     |
| foobar  | 6,757,324 |
+---------+-----------+

Waar foobar ieder willekeurige tekst kan hebben, zo lang het maar duidelijk is dat het geen echt gewicht betreft maar het totaal.

Ik heb geprobeerd middels de volgende query het totaal te bemachtigen:

SQL:

SELECT
    FORMAT(weight,0) AS weight,
    FORMAT(COUNT(DISTINCT(`code`)),0) AS total,
    FORMAT(COUNT(`code`),0) AS backlog
FROM ProductService.product_export_update_list cpeul 
GROUP BY cpeul.weight;

Dit levert niet het gewenste resultaat op. Maar voegt aan het eerdergenoemde resultaat de kolom backlog toe met daarin exact dezelfde waardes als in de total kolom.

Ook heb ik middels een subquery geprobeerd de totalen te tellen:

SQL:

SELECT FORMAT(SUM(total),0) AS backlog FROM (
    SELECT
        weight,
        COUNT(DISTINCT(`code`)) AS total
    FROM ProductService.product_export_update_list cpeul 
    GROUP BY cpeul.weight
) AS total_test;

Maar nu heb ik feitelijk een hele dure COUNT(*) geschreven

+-----------+
| backlog   |
+-----------+
| 6,790,952 |
+-----------+

Ik weet niet zeker of het wel mogelijk is om dit in 1 query voor elkaar te krijgen. Kan het anders misschien in een VIEW?

Relevante software die ik gebruik: 10.0.38-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04

Wie kan mij een duwtje in de goede richting geven?

Alvast bedankt

Matis

[Kalentum]

Een oplossing is om een UNION query te maken die uit twee queries bestaat:
- 1 voor de aantallen per weight (jouw eerste query)
- 1 voor de totalen.

Een andere oplossing: je zegt dat je een statuspagina hebt waar het resultaat van die eerste query wordt getoond. Je kan ook op dat moment, dus als je daar door die resultset heen ploegt, een totaal berekenen. Misschien is dat nog wel het meest eenvoudig.

[Reinier]

Dit levert niet het gewenste resultaat op. Maar voegt aan het eerdergenoemde resultaat de kolom backlog toe met daarin exact dezelfde waardes als in de weight kolom.

Ten eerste snap ik niet dat dit dezelfde waardes oplevert (dat zou betekenen dat elke SKU maar éénmaal voorkomt), ten tweede snap ik niet dat het dezelfde waarde als de weight-kolom is. Bedoel je niet total?

Anyways, ik zou een eenvoudige union aan je query toevoegen

[Matis]

Reinier schreef op zaterdag 25 januari 2020 @ 13:00:
Ten eerste snap ik niet dat dit dezelfde waardes oplevert (dat zou betekenen dat elke SKU maar éénmaal voorkomt),

Klopt, iedere SKU komt maar 1 keer voor in de tabel. In het geval de code al voorkomt en de waarde van de nieuwe weight hoger is dan van de reeds bestaande weight wordt deze middels INSERT ON DUPLICATE KEY UPDATE

ten tweede snap ik niet dat het dezelfde waarde als de weight-kolom is. Bedoel je niet total?

Ik bedoel inderdaad total. Scherp Ik heb de TS er op aangepast.

Anyways, ik zou een eenvoudige union aan je query toevoegen

Daar had ik nog niet aan gedacht. Ga me er eens in verdiepen.

Kalentum schreef op zaterdag 25 januari 2020 @ 12:42:
Een oplossing is om een UNION query te maken die uit twee queries bestaat:
- 1 voor de aantallen per weight (jouw eerste query)
- 1 voor de totalen.

Dank voor (dezelfde) suggestie. Ik ga me hier in verdiepen.

Een andere oplossing: je zegt dat je een statuspagina hebt waar het resultaat van die eerste query wordt getoond. Je kan ook op dat moment, dus als je daar door die resultset heen ploegt, een totaal berekenen. Misschien is dat nog wel het meest eenvoudig.

Ja, dat zou té makkelijk zijn Zonder dollen, natuurlijk is dat mogelijk, maar ik wilde proberen om dit vraagstuk in MySQL op te lossen.

---

Het is me gelukt

SQL:

SELECT
    FORMAT(weight,0) AS weight,
    FORMAT(COUNT(DISTINCT(`code`)),0) AS total
FROM ProductService.product_export_update_list cpeul 
GROUP BY cpeul.weight
UNION SELECT 
    'total' AS weight, 
    FORMAT(COUNT(*),0) AS total
FROM ProductService.product_export_update_list;

Resulteert in

+---------+-----------+
| weight  | total     |
+---------+-----------+
| 1,000   | 21,362    |
| 2,000   | 36,210    |
| 40,000  | 1,401,569 |
| 75,000  | 4,125,638 |
| 80,000  | 627,136   |
| 100,000 | 69,393    |
| 150,000 | 500,804   |
| 170,000 | 168       |
| 200,000 | 6,572     |
| 340,000 | 645       |
| 700,000 | 3,542     |
| total   | 6,793,039 |
+---------+-----------+

Bedankt voor de duwtjes in de goede richting! Ik ga het antwoord van @Kalentum als beste markeren, omdat hij het eerste was. Wel wil ik @Reinier bedanken voor de scherpe analyse

[ Voor 54% gewijzigd door Matis op 25-01-2020 13:46 ]

[Reinier]

Als SKU's uniek zijn mag je de DISTINCT dus weglaten

[ Voor 3% gewijzigd door Reinier op 25-01-2020 13:47 ]

[Knutselsmurf]

Dit kun je ook oplossen zonder UNION,maar met een 'WITH ROLLUP'
Die is speciaal voor dit soort situaties:
https://dev.mysql.com/doc...n/group-by-modifiers.html

[Reinier]

Ja kan ook. Dat is wel fraai.
Als je nog wilt sorteren heb je dan wel een derived table nodig (select * from (select * with rollup...) as x order by whatever).

[Knutselsmurf]

Reinier schreef op zaterdag 25 januari 2020 @ 14:44:
Ja kan ook. Dat is wel fraai.
Als je nog wilt sorteren heb je dan wel een derived table nodig (select * from (select * with rollup...) as x order by whatever).

Op die manier blijft de totaal-regel niet onderaan. Je kunt gewoon sorteren, en als laatste wordt deze extra regel toegevoegd.

[Reinier]

Oh okee, ik zag dit:

When you use ROLLUP, you cannot also use an ORDER BY clause to sort the results. In other words, ROLLUP and ORDER BY are mutually exclusive in MySQL. However, you still have some control over sort order. To work around the restriction that prevents using ROLLUP with ORDER BY and achieve a specific sort order of grouped results, generate the grouped result set as a derived table and apply ORDER BY to it.

Maar goed, ik ben dan ook handiger met SQL Server

[Matis]

Knutselsmurf schreef op zaterdag 25 januari 2020 @ 14:18:
Dit kun je ook oplossen zonder UNION,maar met een 'WITH ROLLUP'
Die is speciaal voor dit soort situaties:
https://dev.mysql.com/doc...n/group-by-modifiers.html

Dat is helemaal een nette query, alleen staat NULL dan weer zo slordig.

Ik zie dat je daarvoor weer COALESCE kunt gebruiken.

* Matis slingert de database weer aan

[Reinier]

Matis schreef op zaterdag 25 januari 2020 @ 15:07:
[...]

Dat is helemaal een nette query, alleen staat NULL dan weer zo slordig.

Ik zie dat je daarvoor weer COALESCE kunt gebruiken.

* Matis slingert de database weer aan

Toch een derived table en de lege waarde met isnull() afvangen.
Dat zou nog mis kunnen gaan als er een SKU bestaat met gewicht NULL

[Knutselsmurf]

Reinier schreef op zaterdag 25 januari 2020 @ 14:56:
Oh okee, ik zag dit:


[...]


Maar goed, ik ben dan ook handiger met SQL Server

Dat zag ik later ook. Je hebt dus helemaal gelijk als het om sorteren gaat. Je heb dan een extra DERIVED TABLE nodig.

Welke versie van MySQL gebruik je? Versie 8 heeft een Grouping()-functi, waarmee je onderscheid kunt maken tussen een 'echte' NULL-value en de NULL die uit de ROLLUP komt rollen.

[ Voor 22% gewijzigd door Knutselsmurf op 25-01-2020 15:30 ]

[Matis]

Knutselsmurf schreef op zaterdag 25 januari 2020 @ 15:26:
Dat zag ik later ook. Je hebt dus helemaal gelijk als het om sorteren gaat. Je heb dan een extra DERIVED TABLE nodig.

Welke versie van MySQL gebruik je? Versie 8 heeft een Grouping()-functi, waarmee je onderscheid kunt maken tussen een 'echte' NULL-value en de NULL die uit de ROLLUP komt rollen.

Uit de TS

Relevante software die ik gebruik: 10.0.38-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04

[Matis]

Reinier schreef op zaterdag 25 januari 2020 @ 15:20:
Toch een derived table en de lege waarde met isnull() afvangen.
Dat zou nog mis kunnen gaan als er een SKU bestaat met gewicht NULL

Dat is niet mogelijk, immers is de tabel zo opgebouwd

SQL:

CREATE TABLE `product_export_update_list` (
  `code` varchar(255) NOT NULL,
  `weight` int(11) NOT NULL DEFAULT '1000',
  PRIMARY KEY (`code`),
  UNIQUE KEY `code_UNIQUE` (`code`),
  KEY `idx_weight` (`weight`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

Ik heb zojuist de WITH ROLLUP query geschreven, maar deze doet er consequent 4x langer over dan de UNION SELECT

code:

MariaDB [(none)]> SELECT
    ->     FORMAT(weight,0) AS weight,
    ->     FORMAT(COUNT(DISTINCT(`code`)),0) AS total
    -> FROM ProductService.product_export_update_list cpeul 
    -> GROUP BY cpeul.weight
    -> UNION SELECT 
    ->     'total' AS weight, 
    ->     FORMAT(COUNT(*),0) AS total
    -> FROM ProductService.product_export_update_list;
+---------+-----------+
| weight  | total     |
+---------+-----------+
| 1,000   | 21,299    |
| 2,000   | 36,086    |
| 40,000  | 1,400,210 |
| 75,000  | 4,122,112 |
| 80,000  | 624,035   |
| 100,000 | 77,932    |
| 150,000 | 489,825   |
| total   | 6,771,499 |
+---------+-----------+
8 rows in set (29.26 sec)

MariaDB [(none)]> SELECT
    ->     FORMAT(weight,0) AS weight,
    ->     FORMAT(COUNT(DISTINCT(`code`)),0) AS total
    -> FROM ProductService.product_export_update_list cpeul 
    -> GROUP BY cpeul.weight WITH ROLLUP;
+---------+-----------+
| weight  | total     |
+---------+-----------+
| 1,000   | 21,299    |
| 2,000   | 36,086    |
| 40,000  | 1,400,210 |
| 75,000  | 4,122,112 |
| 80,000  | 624,035   |
| 100,000 | 77,932    |
| 150,000 | 489,808   |
| NULL    | 6,771,482 |
+---------+-----------+
8 rows in set (2 min 8.95 sec)

Ik denk dat ik maar bij de UNION SELECT blijf, omdat dit ook een mooi geformatte output geeft met een minimaal extra aan regels SQL en database load.`

Hierbij de EXPLAIN van beide queries:

MariaDB [(none)]> EXPLAIN SELECT
    FORMAT(weight,0) AS weight,
    FORMAT(COUNT(DISTINCT(`code`)),0) AS total
FROM ProductService.product_export_update_list cpeul 
GROUP BY cpeul.weight
UNION SELECT 
    'total' AS weight, 
    FORMAT(COUNT(*),0) AS total
FROM ProductService.product_export_update_list;
+------+--------------+-------------------------------+-------+---------------+------------+---------+------+---------+-------------------------------------+
| id   | select_type  | table                         | type  | possible_keys | key        | key_len | ref  | rows    | Extra                               |
+------+--------------+-------------------------------+-------+---------------+------------+---------+------+---------+-------------------------------------+
|    1 | PRIMARY      | cpeul                         | range | NULL          | idx_weight | 771     | NULL | 6655958 | Using index for group-by (scanning) |
|    2 | UNION        | product_export_update_list    | index | NULL          | PRIMARY    | 767     | NULL | 6655957 | Using index                         |
| NULL | UNION RESULT | <union1,2>                    | ALL   | NULL          | NULL       | NULL    | NULL |    NULL |                                     |
+------+--------------+-------------------------------+-------+---------------+------------+---------+------+---------+-------------------------------------+
3 rows in set (0.01 sec)

MariaDB [(none)]> EXPLAIN SELECT
    FORMAT(weight,0) AS weight,
    FORMAT(COUNT(DISTINCT(`code`)),0) AS total
FROM ProductService.product_export_update_list cpeul 
GROUP BY cpeul.weight WITH ROLLUP;
+------+-------------+-------+-------+---------------+------------+---------+------+---------+-------------+
| id   | select_type | table | type  | possible_keys | key        | key_len | ref  | rows    | Extra       |
+------+-------------+-------+-------+---------------+------------+---------+------+---------+-------------+
|    1 | SIMPLE      | cpeul | index | NULL          | idx_weight | 6       | NULL | 6655957 | Using index |
+------+-------------+-------+-------+---------------+------------+---------+------+---------+-------------+
1 row in set (0.00 sec)

Ik kan, aan de hand van deze EXPLAIN niet ontdekken waarom het zoveel trager gaat.

---

Ik zie zojuist dat de DISTINCT weer was teruggeslopen in de query. Wanneer ik deze verwijder, zijn zowel de UNION SELECT als de WITH ROLLUP even snel.

Het grote voordeel van de UNION SELECT blijft dat ik daarin vrij eenvoudig de output van de UNION direct ,zonder tussenkomst van een hulptabel, kan formatten en voorzien van een omschrijvende titel.

Nogmaals bedankt, ik ga dit morgen verder bespreken en kom er op terug.

[ Voor 97% gewijzigd door Matis op 26-01-2020 10:17 ]