MySQL Forums
Forum List  »  Newbie

Re: Complicated update query
Posted by: Peter Brawley
Date: March 08, 2022 11:09AM

Problems with the DDL ...

1 Doubles & floats have rounding errors, giving spurious differences in the sorts of comparisons you propose. Use decimals.

2 All calculated decimals of precision > max decimals of input precision are junk, need to be filtered out.

3 your requirement as you describe it---for each row calculate weighted average sum(TonnesXSugar_Load)/sum(Tonnes) where sugar >= the current row sugar---is a sql solecism. Making a column a function of values in other rows entails that on every change, all values of that column must be recalculated. Unmanageable. Such grouped values belong in another table.

So fixing those issues...

drop table if exists penalisasiet;
CREATE TABLE `penalisasiet` (
   `Name` text,
   `Variety` text,
   `Class` text,
   `Date` date DEFAULT NULL,
   `Sugar` decimal(6,1) DEFAULT NULL,
   `Tonnes` decimal(6,2) DEFAULT NULL,
   `TonnesXSugar_Load` decimal(8,3) DEFAULT NULL,
   `WeighAve_SugarPerDay` decimal(6,2) DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO penalisasiet VALUES
 ('BOESMAN GILLIOMEE','CHB','KULTIVAR','2021-02-24','22','6.32','139.04','22'),
 ('BOESMAN GILLIOMEE','CHB','KULTIVAR','2021-02-24','21.9','6.86','150.234','21.9'),
 ('BOESMAN GILLIOMEE','CHB','KULTIVAR','2021-02-24','21.6','4.9','105.84','21.6'),
 ('BOESMAN GILLIOMEE','CHB','KULTIVAR','2021-02-24','21.2','6.37','135.044','21.2'),
 ('BOESMAN GILLIOMEE','CHB','KULTIVAR','2021-02-24','21.1','5.95','125.545','21.1'),
 ('BOESMAN GILLIOMEE','CHB','KULTIVAR','2021-02-24','20.8','5.91','122.928','20.8'),
 ('BOESMAN GILLIOMEE','CHB','KULTIVAR','2021-02-24','20.5','5.99','122.795','20.5'),
 ('BOESMAN GILLIOMEE','CHB','BULK','2021-02-24','21.2','5.83','123.596','21.2'),
 ('BOESMAN GILLIOMEE','CHB','BULK','2021-02-24','21','6.3','132.3','21'),
 ('BOESMAN GILLIOMEE','CHB','BULK','2021-02-24','21','5.72','120.12','21'),
 ('BOESMAN GILLIOMEE','CHB','BULK','2021-02-24','20.1','5.43','109.143','20.1'),
 ('BOESMAN GILLIOMEE','CHB','BULK','2021-02-24','19.9','6.11','121.589','19.9'),
 ('BOESMAN GILLIOMEE','CHB','BULK','2021-02-24','19.2','6.09','116.928','19.2');
select * from penalisasiet;
+-------------------+---------+----------+------------+-------+--------+-------------------+----------------------+
| Name              | Variety | Class    | Date       | Sugar | Tonnes | TonnesXSugar_Load | WeighAve_SugarPerDay |
+-------------------+---------+----------+------------+-------+--------+-------------------+----------------------+
| BOESMAN GILLIOMEE | CHB     | KULTIVAR | 2021-02-24 |  22.0 |   6.32 |           139.040 |                22.00 |
| BOESMAN GILLIOMEE | CHB     | KULTIVAR | 2021-02-24 |  21.9 |   6.86 |           150.234 |                21.90 |
| BOESMAN GILLIOMEE | CHB     | KULTIVAR | 2021-02-24 |  21.6 |   4.90 |           105.840 |                21.60 |
| BOESMAN GILLIOMEE | CHB     | KULTIVAR | 2021-02-24 |  21.2 |   6.37 |           135.044 |                21.20 |
| BOESMAN GILLIOMEE | CHB     | KULTIVAR | 2021-02-24 |  21.1 |   5.95 |           125.545 |                21.10 |
| BOESMAN GILLIOMEE | CHB     | KULTIVAR | 2021-02-24 |  20.8 |   5.91 |           122.928 |                20.80 |
| BOESMAN GILLIOMEE | CHB     | KULTIVAR | 2021-02-24 |  20.5 |   5.99 |           122.795 |                20.50 |
| BOESMAN GILLIOMEE | CHB     | BULK     | 2021-02-24 |  21.2 |   5.83 |           123.596 |                21.20 |
| BOESMAN GILLIOMEE | CHB     | BULK     | 2021-02-24 |  21.0 |   6.30 |           132.300 |                21.00 |
| BOESMAN GILLIOMEE | CHB     | BULK     | 2021-02-24 |  21.0 |   5.72 |           120.120 |                21.00 |
| BOESMAN GILLIOMEE | CHB     | BULK     | 2021-02-24 |  20.1 |   5.43 |           109.143 |                20.10 |
| BOESMAN GILLIOMEE | CHB     | BULK     | 2021-02-24 |  19.9 |   6.11 |           121.589 |                19.90 |
| BOESMAN GILLIOMEE | CHB     | BULK     | 2021-02-24 |  19.2 |   6.09 |           116.928 |                19.20 |
+-------------------+---------+----------+------------+-------+--------+-------------------+----------------------+

Your weighavg calculation grouped on date, name, variety, class yields just 2 rows ...

SELECT date,name,variety,class,sugar, Round( SUM(TonnesXSugar_Load)/SUM(Tonnes), 2 ) AS WEIGHAVEG
FROM penalisasiet 
GROUP BY date,name,variety,class; 
+------------+-------------------+---------+----------+-------+----------+
| date       | name              | variety | class    | sugar | WEIGHAVG |
+------------+-------------------+---------+----------+-------+----------+
| 2021-02-24 | BOESMAN GILLIOMEE | CHB     | KULTIVAR |  22.0 |    21.31 |
| 2021-02-24 | BOESMAN GILLIOMEE | CHB     | BULK     |  21.2 |    20.40 |
+------------+-------------------+---------+----------+-------+----------+

But to proceed further, we need a clarification--is the per-row calculation to compare all other rows with equ, dateal or higher sugars, or only those equal or higher sugar rows that match on some combination of name, variety, class? And if the latter, which columns?

Options: ReplyQuote


Subject
Written By
Posted
Re: Complicated update query
March 08, 2022 11:09AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.