Kangen.
Banyak sih yang terjadi, cuman males aja nulisnya.
Kadang pas lagi pingin, malah gak lagi deket sama the net.
Mungkin udah saatnya blogger nyediain akses bloggin dari ponsel ya? apa udah ada?
Hello Blogger people, if you read this, please integrate Mobile Blogging capabilities into your site. Please...
Desperate banget gak tuh kedengarannya?
Anyway,
Gara-gara disuruh analisa trafik lebaran, terus males banget bikin grapik di Spreadsheets (baca: spretsits), aku akhirnya belajar rumus matematik buat nentuin trend data. Judulnya Least Square Methods.
Intinya kita nyari selisih square terkecil dari kumpulan data pada x-y axes graphics gitu.
Terus dapetin konstantanya terus tinggal dikaliin aja ama posisi data tertentu. Rumit deh.
Mungkin bisa dicek di link ini: http://www.efunda.com/math/math_home/math.cfm buat detailnya, cari bagian Least Square Methods.
Hasil Query SQLnya seru deh,
panjang banget, agak-agak serem, tapi seru.
Kurang lebih begini hasilnya:
---
select routeid, a+b*trend_vlr as trend_traff , bh_circuits,
offcap(bh_circuits, 1) as cir_offcap
from
(select mscid, a+b*320 as trend_vlr
from
(select mscid,
case when ((n*x2)-ex2) = 0 then
((y*x2) - (x*xy))
else
(((y*x2) - (x*xy))/((n*x2)-ex2))
end
as a,
case when ((n*x2)-ex2) = 0 then
(n*xy - (x*y))
else
((n*xy - (x*y))/((n*x2) - ex2))
end
as b
from
(select
mscid,
count(day) as n,
sum(bh_vlrsubs) as y,
sum(extract(doy from day) - 186) as x,
sum(pow(extract(doy from day) - 186, 2)) as x2,
sum((extract(doy from day) - 186)*(bh_vlrsubs)) as xy,
pow(sum(extract(doy from day) - 186), 2) as ex2
from
vlr_data_dy as v
where
(v.day>='2004-07-05' and v.day <= '2004-09-06')
group by mscid
) as z
) as y
) as vtr,
(select
routeid, bh_circuits,
case when ((n*x2)-ex2) = 0 then
((y*x2) - (x*xy))
else
(((y*x2) - (x*xy))/((n*x2)-ex2))
end
as a,
case when ((n*x2)-ex2) = 0 then
(n*xy - (x*y))
else
((n*xy - (x*y))/((n*x2) - ex2))
end
as b
from
(select
min(routeid) as routeid, max(bh_circuits) as bh_circuits,
count(weekof) as n,
sum(bh_itraffic+bh_otraffic) as y,
sum(v.bh_vlrsubs) as x,
sum(pow(v.bh_vlrsubs, 2.0)) as x2,
sum(v.bh_vlrsubs*(bh_itraffic+bh_otraffic)) as xy,
pow(sum(v.bh_vlrsubs), 2.0) as ex2
from
route_bh_wk,
vlr_data_dy as v
where v.day=route_bh_wk.bd and
substr(routeid, 1,5) = v.mscid and
(weekof>='2004-07-05' and weekof <= '2004-08-30') and
(routeid like 'M%/T%' or routeid like 'G%/T%')
group by routeid
) as d
) as e
where substr(e.routeid, 1, 5)=vtr.mscid;
phew...
mudah-mudahan gak ada data rahasianya disitu...
:-)
Lumayan, hasil akhirnya bisa langsung aku kopi paste ke spretsits-ku (gnumeric dong... he..he..)
Dan after kutak-katik dikit, jadi laporan buat bos.
