Untuk MySQL, saya memerlukan laporan baris yang menyimpang lebih daripada 1 sisihan piawai daripada min (dinyatakan sebagai peratusan)
P粉002572690
P粉002572690 2023-09-13 22:06:57
0
1
619

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

P粉002572690
P粉002572690

membalas semua(1)
P粉141911244

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 |
+-----------------+---------------+-------+------+
Muat turun terkini
Lagi>
kesan web
Kod sumber laman web
Bahan laman web
Templat hujung hadapan