Pengiraan dalam pertanyaan penyertaan MySQL

Question

Saya mempunyai beberapa pengiraan dan ingin melakukan ini dalam pertanyaan.

Terdapat ibu bapa dan anakmeja dengan hubungan satu dengan ramai:

CREATE TABLE `parent` (
  `id` int NOT NULL AUTO_INCREMENT,
  `value` decimal(10,2) DEFAULT NULL,
    
  PRIMARY KEY (`id`)
);

CREATE TABLE `children` (
  `id` int NOT NULL AUTO_INCREMENT,
  `parent_id` int NOT NULL,
  `multiple` decimal(10,2) DEFAULT NULL,
  `sum` decimal(10,2) DEFAULT NULL,
    
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
);

Untuk mencari nilai akhir ibu bapa, saya harus mengulangi anak-anak dan mengira formula berikut: newParentValue = childMultiple(parentValue + childSum)

Perlaksanaan dalam kod adalah seperti berikut:

function calculateFinalParentValue($parentValue, $children)
{
    foreach ($children as $child) {
        $parentValue = $child['multiple'] * ($parentValue + $child['sum']);
    }
    return $parentValue;
}

Bagaimana untuk melaksanakan pengiraan dalam pertanyaan?

Saya cuba cara ini (menggunakan pembolehubah sementara):

set @value = 0; 

SELECT 
    p.id,
    @value := (c.multiple * (@value + c.sum)) AS value
FROM
    parent p
JOIN
    children c ON p.id = c.parent_id AND @value := p.value;

Saya tetapkan pembolehubah dalam keadaan gabungan (@value := p.value) untuk menetapkan semula pembolehubah bagi setiap ibu bapa baharu.

Pertanyaan ini mengembalikan baris untuk setiap ibu bapa bersama dengan bilangan anak, saya memerlukan baris terakhir dalam setiap sambungan ibu bapa sebagai jawapannya.

Tetapi cara ini tidak mampan Adakah cara yang lebih baik?

Contoh:

mysql> select * from parent;
+----+-------+
| id | value |
+----+-------+
|  1 | 10.00 |
|  2 | 20.00 |
+----+-------+

mysql> select * from children;
+----+-----------+----------+------+
| id | parent_id | multiple | sum  |
+----+-----------+----------+------+
|  1 |         1 |     1.00 | 1.00 |
|  2 |         1 |     1.00 | 1.00 |
|  3 |         1 |     1.00 | 1.00 |
|  4 |         2 |     2.00 | 2.00 |
|  5 |         2 |     2.00 | 2.00 |
+----+-----------+----------+------+

Berdasarkan data di atas, saya menjangkakan jawapan berikut:

+----+--------+
| id | value  |
+----+--------+
|  1 |  11.00 |
|  1 |  12.00 |
|  1 |  13.00 | <- final value for parant.id = 1
|  2 |  44.00 |
|  2 |  92.00 | <- final value for parant.id = 2
+----+--------+

Untuk parent.id=1, terdapat tiga anak dan parent.value ialah 10, jadi selepas mengira formula untuk anak pertama, nilai baharu ialah 1 * (10 + 1) = 11 ，第二个孩子的值是 1 * (11 + 1) = 12 正如预期的那样，第三个子值是 1 * (12 + 1) = 13 (iaitu gandaan dalam ketiga-tiga kanak-kanak dan jumlahnya bersamaan 1).

Untuk parent.id=2, terdapat dua anak dan parent.value ialah 20, jadi selepas mengira formula untuk anak pertama, nilai baharu ialah 2 * (20 + 2) = 44 ，第二个孩子的值是 2 * (44 + 2) = 92 (kedua-dua kanak-kanak ialah gandaan dan jumlahnya bersamaan 2).

Pada akhirnya saya hanya mahukan nilai akhir setiap ibu bapa, jadi hasil jangkaan akhir saya ialah:

+----+--------+
| id | value  |
+----+--------+
|  1 |  13.00 |
|  2 |  92.00 |
+----+--------+

Hanya untuk memudahkan contoh, semua multiply 和 sum ​​lajur setiap jadual anak ibu bapa adalah sama (dengan mengandaikan nilai yang berbeza) dan nilai akhir ialah maksimum, nilai akhir mungkin tidak maksimum setiap kali. < /p>

Answer 1

Gunakan ROW_NUMBER()窗口函数对children的行进行排名，按parent_id分区并按id和SUM()fungsi tetingkap untuk menyusun untuk mendapatkan jumlah yang dikehendaki.
Akhir sekali gunakan fungsi tetingkap FIRST_VALUE() untuk mendapatkan jumlah terakhir bagi setiap id:

WITH 
  cte_children AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY id) rn FROM children), 
  cte_sums AS (
    SELECT p.id,
           c.rn,
           POW(c.multiple, c.rn) * p.value + SUM(POW(c.multiple, c.rn)) OVER (PARTITION BY p.id ORDER BY c.rn) * c.sum value
    FROM parent p INNER JOIN cte_children c
    ON c.parent_id = p.id
  )
SELECT DISTINCT id, 
       FIRST_VALUE(value) OVER (PARTITION BY id ORDER BY rn DESC) value
FROM cte_sums;

Lihat Demo.

Answer 2

Sedikit rumit kerana anda perlu menetapkan semula nilai anda di tengah apabila ibu bapa berubah.

Cuba pertanyaan berikut:

SELECT 
parentId,
ROUND(iteratingValue, 2) reqValue
 FROM 
        (SELECT 
        parentId,
        `childMultiple`,
        childSum,
        @running_parent,
        (CASE WHEN @current_parent_value=0 THEN @current_parent_value:=parentValue ELSE @current_parent_value=@current_parent_value END) ,
        (CASE WHEN @running_parent!=parentId   THEN @current_parent_value:=parentValue ELSE @current_parent_value:=@current_parent_value END),
        @current_parent_value:=(`childMultiple`*(@current_parent_value+childSum)) AS iteratingValue,
        @running_parent:=parentId
        FROM (SELECT 
        p.`id` parentId,
        c.`multiple`childMultiple,
        p.`value` parentValue,
        c.`sum` AS childSum,
        @current_parent_value:=0,
        @running_parent:=0
        FROM parent p
        JOIN `children` c ON c.`parent_id`=p.`id`
) subTable ORDER BY parentId) finalTable;

Anda juga boleh menggunakan IF语句替换上述提到的CASE pernyataan (lebih mudah dibaca)

IF(@current_parent_value=0, @current_parent_value:=parentValue, @current_parent_value=@current_parent_value),
IF(@running_parent!=parentId, @current_parent_value:=parentValue, @current_parent_value:=@current_parent_value),

Ini sepatutnya memberi anda output yang diingini.

Saya menggunakan dua pembolehubah@current_parent_value和@running_parent

@running_parent将帮助你确定前一行和当前行是否属于同一个parent，而@current_parent_value akan membantu anda menyimpan nilai semasa.