以倒傳遞類神經網路來解決傳統的 XOR 問題。本套程式共包括鏈結值資料庫、學習程式、回憶程式三部份 --
- 加權值資料庫 ：儲存各類已計算出的加權值 (Weight) 資料。
- 學習程式 (for Learning Phase) ：此程式會依輸入數值及預期結果，計算出該類資料的各種鏈結值，並儲存於上述資料庫中；下次學習或回憶功能時便可使用。
- 回憶程式 (for Retrieving Phase) ：依照資料庫中存在的鏈結值，推算出欲查詢的資料。
加權值資料庫 (Weight Database)
if object_id('TEST') is null
create database TEST
go
use TEST
go
if object_id('Weight_Input_Hidden') is not null
drop table Weight_Input_Hidden
if object_id('Weight_Hidden_Output') is not null
drop table weight_hidden_output
create table Weight_Input_Hidden (
IH_Group_ID int,
IH_No int,
I_No int,
H_No int,
Weight_Value float
constraint PK_Weight_Input_Hidden primary key (IH_Group_ID, IH_No)
)
create table Weight_Hidden_Output (
HO_Group_ID int,
HO_No int,
H_No int,
O_No int,
Weight_Value float
constraint PK_Weight_Hidden_Output primary key (HO_Group_ID, HO_No)
)
go
學習程式 (Learning Phase)
於 TEST 資料庫中 Compile 以下程式碼，再依程式碼第 20 ~ 23 行的說明執行四次學習程序。
Create Proc usp_BPN_Learning @prm_input_item_1 int,
@prm_input_item_2 int,
@prm_expecting_value int,
@prm_eta float
/*-----------------------------------------------------------------------------------+
| Author: Wei-jie Yang |
| Date: 2012/06/19 |
| All Rights Reserved |
| |
| [Description] |
| Backpropagation training program for XOR problem |
| |
| [Parameters] |
| - @prm_input_item_1 Training Value No.1 |
| - @prm_input_item_2 Training Value No.2 |
| - @prm_expecting_value Training Target Value |
| - @prm_eta: Learning Rate |
| |
| [Usage] |
| (1). exec usp_BPN_Learning 1, 1, 1, 0.5 |
| (2). exec usp_BPN_Learning 0, 0, 1, 0.5 |
| (3). exec usp_BPN_Learning 1, 0, 0, 0.5 |
| (4). exec usp_BPN_Learning 0, 1, 0, 0.5 |
+-----------------------------------------------------------------------------------*/
as
declare @current_ho_weight numeric(38, 2),
@current_ih_weight numeric(38, 2),
@delta_1 float,
@delta_2 float,
@delta_3 float,
@exec_counter int,
@exit_main_procedure char(1),
@group_id varchar(10),
@h int,
@hidden_item_amount int,
@hidden_value float,
@ho_weight_1 float,
@ho_weight_2 float,
@ho_weight_matched char(1),
@i int,
@i_value float,
@ih_weight_matched char(1),
@input_item_amount int,
@o int,
@ones int,
@output_item_amount int,
@output_value float,
@pre_ho_weight numeric(38, 2),
@pre_ih_weight numeric(38, 2),
@record_counter int,
@stop_value float,
@temp_hidden_value float,
@temp_output_value float,
@total_exec_times int,
@Y1 float,
@Y2 float,
@zeros int
declare @input_layer table (
i_no int,
i_name varchar(20),
i_value float
)
declare @hidden_layer table (
h_no int,
h_name varchar(20),
h_value float
)
declare @weight_input_hidden table ( -- Weights between Input & Hidden Layers
ih_group_id int,
ih_no int,
i_no int,
h_no int,
weight_value float
)
declare @temp_weight_input_hidden table (
ih_group_id int,
ih_no int,
i_no int,
h_no int,
weight_value float
)
declare @weight_hidden_output table ( -- Weights between Hidden & Output Layers
ho_group_id int,
ho_no int,
h_no int,
o_no int,
weight_value float
)
declare @temp_weight_hidden_output table (
ho_group_id int,
ho_no int,
h_no int,
o_no int,
weight_value float
)
set nocount on
-- Initialization (begin) =============================================================================================
select @input_item_amount = 2
select @hidden_item_amount = 2
select @output_item_amount = 1
select @total_exec_times = 100
if @prm_input_item_1 = @prm_input_item_2
select @group_id = 1
else
select @group_id = 0
-- Get Weights (between Input & Hidden Layers) ----------------------------------------------------
select @record_counter = count(*)
from Weight_Input_Hidden
where IH_Group_ID = @group_id
if @record_counter > 0
begin
insert into @weight_input_hidden (ih_group_id, ih_no, i_no, h_no, weight_value)
select IH_Group_ID, IH_No, I_No, H_No, Weight_Value
from TEST.dbo.Weight_Input_Hidden
where IH_Group_ID = @group_id
end
else
begin
select @i = 1
while @i <= @input_item_amount
begin
select @h = 1
while @h <= @hidden_item_amount
begin
insert into @weight_input_hidden (ih_group_id, ih_no, i_no, h_no, weight_value)
values(@group_id, @i * 100 + @h, @i, @h, round(rand(), 2))
select @h = @h + 1
end
select @i = @i + 1
end
end
-- Get Weights (between Hidden & Output Layers) ---------------------------------------------------
select @record_counter = count(*)
from Weight_Hidden_Output
where HO_Group_ID = @group_id
if @record_counter > 0
begin
insert into @weight_hidden_output (ho_group_id, ho_no, h_no, o_no, weight_value)
select HO_Group_ID, HO_No, H_No, O_No, Weight_Value
from TEST.dbo.Weight_Hidden_Output
where HO_Group_ID = @group_id
end
else
begin
select @o = 1
while @o <= @output_item_amount
begin
select @h = 1
while @h <= @hidden_item_amount
begin
insert into @weight_hidden_output (ho_group_id, ho_no, h_no, o_no, weight_value)
values(@group_id, @h * 100 + @o, @h, @o, round(rand(), 2))
select @h = @h + 1
end
select @o = @o + 1
end
end
-- Initialization (end) ===============================================================================================
-- Generate Input Items (begin) =======================================================================================
insert into @input_layer (i_no, i_name, i_value) values(1, 'X1', @prm_input_item_1)
insert into @input_layer (i_no, i_name, i_value) values(2, 'X2', @prm_input_item_2)
-- Generate Input Items (end) =========================================================================================
-- Main Procedure (begin) =============================================================================================
select @exec_counter = 1
select @output_value = 99
select @exit_main_procedure = 'N'
WHILE (@exec_counter <= @total_exec_times) and (@exit_main_procedure = 'N')
BEGIN
select @ih_weight_matched = 'N'
select @ho_weight_matched = 'N'
-- Generate Hidden Items (begin) ---------------------------------------------------------------
delete from @hidden_layer
where 1 = 1
select @h = 1
while @h <= @hidden_item_amount
begin
select @temp_hidden_value = sum(i.i_value * w.weight_value)
from @weight_input_hidden w,
@input_layer i
where w.i_no = i.i_no
select @hidden_value = 1 / (1 + exp((-1) * @temp_hidden_value))
delete from @hidden_layer
where h_no = @h
insert into @hidden_layer (h_no, h_name, h_value)
values (@h, 'Y' + cast(@h as varchar(10)), @hidden_value)
select @h = @h + 1
end
-- Generate Hidden Items (end) -----------------------------------------------------------------
-- Generate Output Item (begin) ----------------------------------------------------------------
select @temp_output_value = sum(h_value * w.weight_value)
from @weight_hidden_output w,
@hidden_layer h
where w.h_no = h.h_no
select @output_value = 1 / (1 + exp((-1) * @temp_output_value))
-- Generate Output Item (end) ------------------------------------------------------------------
-- Delta Calculation (begin) -------------------------------------------------------------------
select @delta_3 = (@prm_expecting_value - @output_value) * @output_value * (1 - @output_value)
select @Y1 = h_value
from @hidden_layer
where h_no = 1
select @Y2 = h_value
from @hidden_layer
where h_no = 2
select @ho_weight_1 = weight_value
from @weight_hidden_output
where h_no = 1
select @ho_weight_2 = weight_value
from @weight_hidden_output
where h_no = 2
select @delta_1 = @Y1 * (1 - @Y1) * @delta_3 * @ho_weight_1
select @delta_2 = @Y2 * (1 - @Y2) * @delta_3 * @ho_weight_2
-- Delta Calculation (end) ---------------------------------------------------------------------
-- Adjustment (begin) --------------------------------------------------------------------------
-- Input - Hidden Weights ........................................
delete from @temp_weight_input_hidden
where ih_group_id = @group_id
insert into @temp_weight_input_hidden (ih_group_id, ih_no, i_no, h_no, weight_value)
select w.ih_group_id, w.ih_no, w.i_no, w.h_no, w.weight_value + @prm_eta * @delta_1 * i.i_value
from @input_layer i,
@weight_input_hidden w
where i.i_no = w.i_no
and w.h_no = 1
and w.ih_group_id = @group_id
insert into @temp_weight_input_hidden (ih_group_id, ih_no, i_no, h_no, weight_value)
select w.ih_group_id, w.ih_no, w.i_no, w.h_no, w.weight_value + @prm_eta * @delta_2 * i.i_value
from @input_layer i,
@weight_input_hidden w
where i.i_no = w.i_no
and w.h_no = 2
and w.ih_group_id = @group_id
select @pre_ih_weight = weight_value
from @weight_input_hidden
where ih_group_id = @group_id
delete from @weight_input_hidden
where ih_group_id = @group_id
insert into @weight_input_hidden (ih_group_id, ih_no, i_no, h_no, weight_value)
select ih_group_id, ih_no, i_no, h_no, weight_value
from @temp_weight_input_hidden
where ih_group_id = @group_id
order by ih_no
select @current_ih_weight = weight_value
from @weight_input_hidden
where ih_group_id = @group_id
if @pre_ih_weight = @current_ih_weight
select @ih_weight_matched = 'Y'
-- Hidden - Output Weights .......................................
delete from @temp_weight_hidden_output
where ho_group_id = @group_id
insert into @temp_weight_hidden_output (ho_group_id, ho_no, h_no, o_no, weight_value)
select w.ho_group_id, w.ho_no, w.h_no, w.o_no, w.weight_value + @prm_eta * @delta_3 * h.h_value
from @hidden_layer h,
@weight_hidden_output w
where h.h_no = w.h_no
and ho_group_id = @group_id
select @pre_ho_weight = weight_value
from @weight_hidden_output
where ho_group_id = @group_id
delete from @weight_hidden_output
where ho_group_id = @group_id
insert into @weight_hidden_output (ho_group_id, ho_no, h_no, o_no, weight_value)
select ho_group_id, ho_no, h_no, o_no, weight_value
from @temp_weight_hidden_output
where ho_group_id = @group_id
order by ho_no
select @current_ho_weight = weight_value
from @weight_hidden_output
where ho_group_id = @group_id
if @pre_ho_weight = @current_ho_weight
select @ho_weight_matched = 'Y'
-- Adjustment (end) ----------------------------------------------------------------------------
if (@ih_weight_matched = 'Y') and (@ho_weight_matched = 'Y')
select @exit_main_procedure = 'Y'
select @exec_counter = @exec_counter + 1
END
-- Main Procedure (end) ===============================================================================================
-- Save Result (begin) ================================================================================================
delete from TEST.dbo.Weight_Input_Hidden
where IH_Group_ID = @group_id
insert into TEST.dbo.Weight_Input_Hidden (IH_Group_ID, IH_No, I_No, H_No, Weight_Value)
select ih_group_id, ih_no, i_no, h_no, weight_value
from @weight_input_hidden
where ih_group_id = @group_id
delete from TEST.dbo.Weight_Hidden_Output
where HO_Group_ID = @group_id
insert into TEST.dbo.Weight_Hidden_Output (HO_Group_ID, HO_No, H_No, O_No, Weight_Value)
select ho_group_id, ho_no, h_no, o_no, weight_value
from @weight_hidden_output
where ho_group_id = @group_id
-- Save Result (end) ==================================================================================================
set nocount off
return
回憶程式 (Retrieving Phase)
於 TEST 資料庫中 Compile 以下程式碼，再依程式碼第 18 ~ 20 行的說明執行回憶程序。
Create Proc usp_BPN_Retrieving @prm_input_item_1 int,
@prm_input_item_2 int,
@rtn_result int output
/*---------------------------------------------------------------+
| Author: Wei-jie Yang |
| Date: 2012/06/19 |
| All Rights Reserved |
| |
| [Description] |
| Backpropagation training program for XOR problem |
| |
| [Parameters] |
| - @prm_input_item_1: Input Value No.1 |
| - @prm_input_item_2: Input Value No.2 |
| - @rtn_result: Result |
| |
| [Usage] |
| declare @result int |
| exec usp_BPN_Retrieving 1, 1, @result output |
| select @result |
+---------------------------------------------------------------*/
as
declare @group_id varchar(10),
@h int,
@hidden_item_amount int,
@hidden_value float,
@i int,
@input_item_amount int,
@o int,
@output_item_amount int,
@output_value float,
@record_counter int,
@temp_hidden_value float,
@temp_output_value float
declare @input_layer table (
i_no int,
i_name varchar(20),
i_value float
)
declare @hidden_layer table (
h_no int,
h_name varchar(20),
h_value float
)
declare @weight_input_hidden table ( -- Weights between Input & Hidden Items
ih_group_id int,
ih_no int,
i_no int,
h_no int,
weight_value float
)
declare @temp_weight_input_hidden table (
ih_group_id int,
ih_no int,
i_no int,
h_no int,
weight_value float
)
declare @weight_hidden_output table ( -- Weights between Hidden & Output Items
ho_group_id int,
ho_no int,
h_no int,
o_no int,
weight_value float
)
declare @temp_weight_hidden_output table (
ho_group_id int,
ho_no int,
h_no int,
o_no int,
weight_value float
)
set nocount on
-- Initialization (begin) =============================================================================================
select @input_item_amount = 3
select @hidden_item_amount = 3
select @output_item_amount = 1
if @prm_input_item_1 = @prm_input_item_2
select @group_id = 1
else
select @group_id = 0
-- Get Weights (between Input & Hidden Layers) ----------------------------------------------------
select @record_counter = count(*)
from Weight_Input_Hidden
where IH_Group_ID = @group_id
if @record_counter > 0
begin
insert into @weight_input_hidden (ih_group_id, ih_no, i_no, h_no, weight_value)
select IH_Group_ID, IH_No, I_No, H_No, Weight_Value
from TEST.dbo.Weight_Input_Hidden
where IH_Group_ID = @group_id
end
else
begin
select @i = 0
while @i <= @input_item_amount - 1
begin
select @h = 1
while @h <= @hidden_item_amount - 1
begin
insert into @weight_input_hidden (ih_group_id, ih_no, i_no, h_no, weight_value)
values(@group_id, @i * 100 + @h, @i, @h, round(rand(), 2))
select @h = @h + 1
end
select @i = @i + 1
end
end
-- Get Weights (between Hidden & Output Layers) ---------------------------------------------------
select @record_counter = count(*)
from Weight_Hidden_Output
where HO_Group_ID = @group_id
if @record_counter > 0
begin
insert into @weight_hidden_output (ho_group_id, ho_no, h_no, o_no, weight_value)
select HO_Group_ID, HO_No, H_No, O_No, Weight_Value
from TEST.dbo.Weight_Hidden_Output
where HO_Group_ID = @group_id
end
else
begin
select @o = 1
while @o <= @output_item_amount
begin
select @h = 0
while @h <= @hidden_item_amount - 1
begin
insert into @weight_hidden_output (ho_group_id, ho_no, h_no, o_no, weight_value)
values(@group_id, @h * 100 + @o, @h, @o, round(rand(), 2))
select @h = @h + 1
end
select @o = @o + 1
end
end
-- Initialization (end) ===============================================================================================
-- Generate Input Items (begin) =======================================================================================
insert into @input_layer (i_no, i_name, i_value) values(0, 'X0', -1)
insert into @input_layer (i_no, i_name, i_value) values(1, 'X1', @prm_input_item_1)
insert into @input_layer (i_no, i_name, i_value) values(2, 'X2', @prm_input_item_2)
-- Generate Input Items (end) =========================================================================================
-- Main Procedure (begin) =============================================================================================
select @output_value = 99
-- Generate Hidden Items (begin) ---------------------------------------------------------------
delete from @hidden_layer
where 1 = 1
insert into @hidden_layer (h_no, h_name, h_value)
values(0, 'X0', -1)
select @h = 1
while @h <= @hidden_item_amount - 1
begin
select @temp_hidden_value = sum(i.i_value * w.weight_value)
from @weight_input_hidden w,
@input_layer i
where w.i_no = i.i_no
select @hidden_value = 1 / (1 + exp((-1) * @temp_hidden_value))
delete from @hidden_layer
where h_no = @h
insert into @hidden_layer (h_no, h_name, h_value)
values (@h, 'Y' + cast(@h as varchar(10)), @hidden_value)
select @h = @h + 1
end
-- Generate Hidden Items (end) -----------------------------------------------------------------
-- Generate Output Item (begin) ----------------------------------------------------------------
select @temp_output_value = sum(h_value * w.weight_value)
from @weight_hidden_output w,
@hidden_layer h
where w.h_no = h.h_no
select @output_value = 1 / (1 + exp((-1) * @temp_output_value))
-- Generate Output Item (end) ------------------------------------------------------------------
if @output_value < 0.5
select @output_value = 0
else
select @output_value = 1
select @rtn_result = @output_value
set nocount off
return
