Advertisement
cdsatrian

myql-weighting_moving_average

Nov 3rd, 2024
178
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 2.33 KB | Source Code | 0 0
  1. DROP IF EXISTS table t_trx_penjualan;
  2.  
  3. CREATE TABLE t_trx_penjualan(
  4.   bulan VARCHAR(4) NOT NULL,
  5.   penjualan INT4 NOT NULL DEFAULT 0,
  6.   PRIMARY KEY (bulan)
  7. );
  8.  
  9. INSERT INTO t_trx_penjualan(bulan,penjualan)
  10. VALUES
  11.   ('2201',1618),
  12.   ('2202',1625),
  13.   ('2203',1643),
  14.   ('2204',1661),
  15.   ('2205',1678),
  16.   ('2206',1740),
  17.   ('2207',1746),
  18.   ('2208',1749),
  19.   ('2209',1757),
  20.   ('2210',1793),
  21.   ('2211',1810),
  22.   ('2212',1865)
  23. ;
  24.  
  25. WITH data AS
  26. (
  27.     SELECT
  28.         a.bulan,
  29.         a.penjualan,
  30.         b.penjualan AS penjualan_sebelumnya,
  31.         RANK() OVER (PARTITION BY a.bulan ORDER BY b.bulan) AS pengali
  32.     FROM t_trx_penjualan a
  33.         LEFT JOIN t_trx_penjualan b
  34.             ON b.bulan BETWEEN a.bulan-3 AND a.bulan-1
  35.     GROUP BY a.bulan,b.bulan
  36.     ORDER BY a.bulan,b.bulan desc
  37. ),
  38. prediksi AS
  39. (
  40.   SELECT
  41.     bulan,
  42.     penjualan y,
  43.     CASE
  44.       WHEN MAX(pengali)<3 THEN 0
  45.       ELSE SUM(penjualan_sebelumnya*pengali)/SUM(pengali)
  46.     END AS x
  47.   FROM data
  48.   GROUP BY bulan
  49. )
  50. SELECT
  51.   bulan,
  52.   y AS penjualan,
  53.   x AS prediksi,
  54.   IF(x>0,ABS((y-x)/y),0) AS MAPE,
  55.   IF(x>0,ABS(y-x),0) AS MAD,
  56.   IF(x>0,(y-x)*(y-x),0) AS MSE
  57. FROM prediksi
  58. ORDER BY bulan
  59. ;
  60.  
  61. +-------+-----------+-----------+------------+---------+---------------+
  62. | bulan | penjualan | prediksi  | MAPE       | MAD     | MSE           |
  63. +-------+-----------+-----------+------------+---------+---------------+
  64. | 2201  |      1618 |    0.0000 |          0 |       0 |             0 |
  65. | 2202  |      1625 |    0.0000 |          0 |       0 |             0 |
  66. | 2203  |      1643 |    0.0000 |          0 |       0 |             0 |
  67. | 2204  |      1661 | 1632.8333 | 0.01695768 | 28.1667 |  793.36298889 |
  68. | 2205  |      1678 | 1649.0000 | 0.01728248 | 29.0000 |  841.00000000 |
  69. | 2206  |      1740 | 1666.5000 | 0.04224138 | 73.5000 | 5402.25000000 |
  70. | 2207  |      1746 | 1706.1667 | 0.02281403 | 39.8333 | 1586.69178889 |
  71. | 2208  |      1749 | 1732.6667 | 0.00933865 | 16.3333 |  266.77668889 |
  72. | 2209  |      1757 | 1746.5000 | 0.00597610 | 10.5000 |  110.25000000 |
  73. | 2210  |      1793 | 1752.5000 | 0.02258784 | 40.5000 | 1640.25000000 |
  74. | 2211  |      1810 | 1773.6667 | 0.02007365 | 36.3333 | 1320.10868889 |
  75. | 2212  |      1865 | 1795.5000 | 0.03726542 | 69.5000 | 4830.25000000 |
  76. +-------+-----------+-----------+------------+---------+---------------+
  77. 12 rows in set (0.08 sec)
Tags: mysql wma
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement