Untuk MySQL, saya memerlukan laporan baris yang menyimpang lebih daripada 1 sisihan piawai daripada min (dinyatakan sebagai peratusan)

Question

Saya cuba menulis pertanyaan MySQL untuk mengira baris yang berbeza dengan ketara daripada baris yang paling biasa untuk setiap produk dan melaporkan perbezaan peratusan, di mana di bawah 100% harga adalah di bawah harga purata untuk produk tersebut dan melebihi 100% lebih tinggi . Harga kurang daripada 1 sisihan piawai daripada min diabaikan.

Sampel data:

_rowid	_Cap Masa	Kod	fk_product_id	fk_po_id	Kos
5952	2021-01-10 10:19:01	00805	1367	543	0.850
9403	2022-05-23 14:54:34	00805	1367	2942	0.850
41595	2022-11-23 11:20:26	00805	1367	3391	1.350
39635	2022-01-18 12:49:32	Air 1	344	3153	0.140
40134	2022-04-06 22:39:34	Air 1	344	2747	0.190
41676	2022-12-09 16:28:28	Air 1	344	3398	0.140
39634	2022-01-18 12:49:31	gr309203 344400	1024	3154	0.770
35634	2021-03-03 15:23:23	gr309203 344400	1024	3203	0.790
41264	2022-11-16 11:41:44	gr309203 344400	1024	3357	0.970

SELECT code, fk_product_id, cost, cost/
  (SELECT avg(cost) FROM po_line aa WHERE aa.code = code) AS percent 
FROM po_line 
WHERE (SELECT STDDEV(cost) FROM po_line ss WHERE ss.code = code)>1;

Ini tidak akan mengembalikan sebarang baris, tetapi tiga baris (satu untuk setiap produk) akan muncul dalam laporan.

Hasil yang diharapkan ialah:

Kod	fk_product_id	Kos	Peratusan
00805	1367	1.350	133
Air 1	344	0.190	121
gr309203 344400	1024	0.970	115

Answer 1

Pertanyaan ini menunjukkan cara menggunakan fungsi tetingkap untuk mengira bilangan sisihan piawai dan kos sebagai peratusan kos purata bagi setiap kod yang diberikan.

SELECT code, cost, avg, std, 
  ABS(cost-avg)/std AS num_std, 
  cost*100/avg AS pct
FROM (
  SELECT code, cost,
    AVG(cost) OVER (PARTITION BY code) AS avg,
    STDDEV(cost) OVER (PARTITION BY code) AS std
  FROM po_line
) AS p;

Hasil:

+-----------------+-------+-----------+---------------------+--------------------+-------------+
| code            | cost  | avg       | std                 | num_std            | pct         |
+-----------------+-------+-----------+---------------------+--------------------+-------------+
| 00805           | 0.850 | 1.0166667 | 0.23570226039551592 | 0.7071069226079035 |  83.6065546 |
| 00805           | 0.850 | 1.0166667 | 0.23570226039551592 | 0.7071069226079035 |  83.6065546 |
| 00805           | 1.350 | 1.0166667 | 0.23570226039551592 | 1.4142134209517383 | 132.7868809 |
| gr309203 344400 | 0.770 | 0.8433333 |  0.0899382504215469 | 0.8153738777025533 |  91.3043514 |
| gr309203 344400 | 0.790 | 0.8433333 |  0.0899382504215469 | 0.5929990827042229 |  93.6758930 |
| gr309203 344400 | 0.970 | 0.8433333 |  0.0899382504215469 | 1.4083740722807512 | 115.0197674 |
| Water1          | 0.140 | 0.1566667 | 0.02357022603955158 | 0.7071081954001099 |  89.3616831 |
| Water1          | 0.190 | 0.1566667 | 0.02357022603955158 | 1.4142121481595331 | 121.2765699 |
| Water1          | 0.140 | 0.1566667 | 0.02357022603955158 | 0.7071081954001099 |  89.3616831 |
+-----------------+-------+-----------+---------------------+--------------------+-------------+

(Sila ambil perhatian bahawa fungsi tetingkap memerlukan MySQL 8.0).

Pertanyaan ini hanya menunjukkan kepada anda cara pengiraan dilakukan. Untuk mendapatkan hasil yang anda inginkan:

SELECT code, fk_product_id, cost, ROUND(cost*100/avg) AS pct
FROM (
  SELECT code, cost, fk_product_id,
    AVG(cost) OVER (PARTITION BY code) AS avg,
    STDDEV(cost) OVER (PARTITION BY code) AS std
  FROM po_line
) AS p
WHERE ABS(cost-avg)/std > 1;

+-----------------+---------------+-------+------+
| code            | fk_product_id | cost  | pct  |
+-----------------+---------------+-------+------+
| 00805           |          1367 | 1.350 |  133 |
| gr309203 344400 |          1024 | 0.970 |  115 |
| Water1          |           344 | 0.190 |  121 |
+-----------------+---------------+-------+------+