Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DROP IF EXISTS table t_trx_penjualan;
- CREATE TABLE t_trx_penjualan(
- bulan VARCHAR(4) NOT NULL,
- penjualan INT4 NOT NULL DEFAULT 0,
- PRIMARY KEY (bulan)
- );
- INSERT INTO t_trx_penjualan(bulan,penjualan)
- VALUES
- ('2201',1618),
- ('2202',1625),
- ('2203',1643),
- ('2204',1661),
- ('2205',1678),
- ('2206',1740),
- ('2207',1746),
- ('2208',1749),
- ('2209',1757),
- ('2210',1793),
- ('2211',1810),
- ('2212',1865)
- ;
- WITH data AS
- (
- SELECT
- a.bulan,
- a.penjualan,
- b.penjualan AS penjualan_sebelumnya,
- RANK() OVER (PARTITION BY a.bulan ORDER BY b.bulan) AS pengali
- FROM t_trx_penjualan a
- LEFT JOIN t_trx_penjualan b
- ON b.bulan BETWEEN a.bulan-3 AND a.bulan-1
- GROUP BY a.bulan,b.bulan
- ORDER BY a.bulan,b.bulan desc
- ),
- prediksi AS
- (
- SELECT
- bulan,
- penjualan y,
- CASE
- WHEN MAX(pengali)<3 THEN 0
- ELSE SUM(penjualan_sebelumnya*pengali)/SUM(pengali)
- END AS x
- FROM data
- GROUP BY bulan
- )
- SELECT
- bulan,
- y AS penjualan,
- x AS prediksi,
- IF(x>0,ABS((y-x)/y),0) AS MAPE,
- IF(x>0,ABS(y-x),0) AS MAD,
- IF(x>0,(y-x)*(y-x),0) AS MSE
- FROM prediksi
- ORDER BY bulan
- ;
- +-------+-----------+-----------+------------+---------+---------------+
- | bulan | penjualan | prediksi | MAPE | MAD | MSE |
- +-------+-----------+-----------+------------+---------+---------------+
- | 2201 | 1618 | 0.0000 | 0 | 0 | 0 |
- | 2202 | 1625 | 0.0000 | 0 | 0 | 0 |
- | 2203 | 1643 | 0.0000 | 0 | 0 | 0 |
- | 2204 | 1661 | 1632.8333 | 0.01695768 | 28.1667 | 793.36298889 |
- | 2205 | 1678 | 1649.0000 | 0.01728248 | 29.0000 | 841.00000000 |
- | 2206 | 1740 | 1666.5000 | 0.04224138 | 73.5000 | 5402.25000000 |
- | 2207 | 1746 | 1706.1667 | 0.02281403 | 39.8333 | 1586.69178889 |
- | 2208 | 1749 | 1732.6667 | 0.00933865 | 16.3333 | 266.77668889 |
- | 2209 | 1757 | 1746.5000 | 0.00597610 | 10.5000 | 110.25000000 |
- | 2210 | 1793 | 1752.5000 | 0.02258784 | 40.5000 | 1640.25000000 |
- | 2211 | 1810 | 1773.6667 | 0.02007365 | 36.3333 | 1320.10868889 |
- | 2212 | 1865 | 1795.5000 | 0.03726542 | 69.5000 | 4830.25000000 |
- +-------+-----------+-----------+------------+---------+---------------+
- 12 rows in set (0.08 sec)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement