Организация хранилища данных для дистанционного практикума по программированию с целью анализа языковых предпочтений

truncate table ldr_sh.det_problem;

------- ШАГ ЗАГРУЗКИ В ХРАНИЛИЩЕ -------

-- обновляем существующие записи

merge into dwh.det_problem dwh

using (select * from ldr_bf.det_problem bf

where bf.rec_status = 0) t

on (dwh.code = t.code)

when matched

then update set dwh.id_creator= t.id_creator,

dwh.name_ru= t.name_ru,

dwh.name_en= t.name_en,

dwh.hard_level= t.hard_level,

dwh.time_limit= t.time_limit,

dwh.memory_limit= t.memory_limit,

dwh.min_unique_percent= t.min_unique_percent,

dwh.dt_open= t.dt_open,

dwh.dt_change = trunc(sysdate),

dwh.dt_close= t.dt_close,

dwh.sysmoment = t.sysmoment;

commit;

-- вставляем новые записи

insert into dwh.det_problem(id_problem, id_creator, code, name_ru, name_en, hard_level, time_limit, memory_limit, min_unique_percent, dt_open, dt_change, dt_close, sysmoment)

select dwh.objseq.nextval, id_creator, code, name_ru, name_en, hard_level, time_limit, memory_limit, min_unique_percent, dt_open, dt_open, dt_close, sysmoment

from ldr_bf.det_problem bf

where bf.code not in (select code from dwh.det_problem)

and bf.rec_status = 0;

commit;

-- закрываем удаленные в СИ записи

merge into dwh.det_problem dwh

using (select * from ldr_bf.det_problem bf

where bf.rec_status = 1) t

on (dwh.code = t.code)

when matched

then update set dwh.dt_close = trunc(sysdate),

dwh.sysmoment = t.sysmoment;

commit;

-- чистим буфер

truncate table ldr_bf.det_problem;

--заносим информацию о загрузке

insert into dwh.load_status(table_name, sysmoment)

select

'DET_PROBLEM',

sysdate

from dual;

commit;



Приложение 8

Листинг скрипта загрузки данных в таблицу-ассоциатор

------- ШАГ ЗАГРУЗКУ В ШЛЮЗ -------

-- создаем промежуточную таблицу для регистрации изменений в STG

create table stg.temp_table as

select

case

when src.user_code is not null and stg.user_code is null then 'DD_INSERT'

when src.user_code is null and stg.user_code is not null then 'DD_DELETE'

else 'DD_REJECT' end as action,

nvl(src.user_code, stg.user_code) as user_code,

nvl(src.group_code, stg.group_code) as group_code,

src.dt as dt,

to_char(sysdate, 'yyyymmdd') as sysmoment

from (

select

'AVT#'||t.id_grp as GROUP_CODE,

'AVT#'||t.id_publ as USER_CODE,

'19800101' as DT

from groups_authors@acmlink t) src

full join stg.ass_user_group stg on src.user_code= stg.user_code and src.group_code= stg.group_code;

--вставляем данные в шлюз

insert into ldr_sh.ass_user_group (user_code, group_code, dt, rec_status, sysmoment)

select

user_code,

group_code,

dt,

decode(action , 'DD_DELETE', '1', '0') as rec_status,

sysmoment

from stg.temp_table s

where action <> 'DD_REJECT';

-- регистрируем изменения в STG

insert into stg.ass_user_group (user_code, group_code)

select user_code, group_code from stg.temp_table where action = 'DD_INSERT';

delete from stg.ass_user_group where (user_code, group_code)

in (select user_code, group_code from stg.temp_table where action = 'DD_DELETE');

-- удаляем промежуточную таблицу

drop table stg.temp_table;

------- ШАГ ТРАНСФОРМАЦИИ -------

insert into ldr_bf.ass_user_group (id_user, id_group, user_code, group_code, dt_open, dt_close, rec_status, sysmoment)

select

(select id_user from dwh.det_user dwh where dwh.code = sh.user_code) as id_user,

(select id_group from dwh.det_group dwh where dwh.code = sh.group_code) as id_group,

sh.*

from (

select

substr(user_code, 1, 100) as user_code,

substr(group_code , 1, 100) as group_code ,

to_date(dt, 'yyyymmdd') as dt_open,

to_date('30010101', 'yyyymmdd') as dt_close,

to_number(rec_status) as rec_status,

to_date(sysmoment, 'yyyymmdd') as sysmoment

from ldr_sh.ass_user_group) sh;

commit;

-- чистим шлюз

truncate table ldr_sh.ass_user_group;

------- ШАГ ЗАГРУЗКИ В ХРАНИЛИЩЕ -------

-- обновляем существующие записи (если запись была удалена, а потом добавлена повторно

merge into dwh.ass_user_group dwh

using (select * from ldr_bf.ass_user_group bf

where bf.rec_status = 0) t

on (dwh.id_user= t.id_user and dwh.id_group= t.id_group)

when matched

then update set dwh.dt_close= t.dt_close,

dwh.sysmoment = t.sysmoment;

commit;

-- вставляем новые записи

insert into dwh.ass_user_group (id_user, id_group, dt_open, dt_close, sysmoment)

select id_user, id_group, dt_open, dt_close, sysmoment

from ldr_bf.ass_user_group bf

where (id_user, id_group) not in (select id_user, id_group from dwh.ass_user_group)

and bf.rec_status = 0;

commit;

-- закрываем удаленные в СИ записи

merge into dwh.ass_user_group dwh

using (select * from ldr_bf.ass_user_group bf

where bf.rec_status = 1) t

on (dwh.id_user= t.id_user and dwh.id_group= t.id_group)

when matched

then update set dwh.dt_close = trunc(sysdate),

dwh.sysmoment = t.sysmoment;

commit;

-- чистим буфер

truncate table ldr_bf.ass_user_group;

--заносим информацию о загрузке

insert into dwh.load_status(table_name, sysmoment)

select

'ASS_USER_GROUP',

sysdate

from dual;

commit;



Приложение 9

Листинг скрипта загрузки данных в таблицу-факт

------- ШАГ ЗАГРУЗКУ В ШЛЮЗ -------

-- создаем промежуточную таблицу для регистрации изменений в STG

create table stg.temp_table as

select

case

when src.code is not null and stg.code is null then 'DD_INSERT'

when src.code is not null and stg.code is not null and src.md5 <> stg.md5 then 'DD_UPDATE'

when src.code is null and stg.code is not null then 'DD_DELETE'

else 'DD_REJECT' end as action,

nvl(src.code, stg.code) as code,

src.md5 as md5 ,

src.PROBLEM_CODE as PROBLEM_CODE,

src.USER_CODE as USER_CODE,

src.COMPILER_CODE as COMPILER_CODE,

src.RESULT_CODE as RESULT_CODE,

src.FALSE_TEST_NUMBER as FALSE_TEST_NUMBER,

src.TIME_WORK as TIME_WORK,

src.MEMORY_USE as MEMORY_USE,

src.UNIQUE_PERCENT as unique_precent,

src.POINTS as POINTS,

src.COMMENTARY as COMMENTARY,

dt as DT,

to_char(sysdate, 'yyyymmdd') as sysmoment

from (

select

'AVT#'||t.id_stat as CODE,

'AVT#'||t.id_prb as PROBLEM_CODE,

'AVT#'||t.id_publ as USER_CODE,

'AVT#'||t.id_cmp as COMPILER_CODE,

'AVT#'||t.id_rsl as RESULT_CODE,

test_no as FALSE_TEST_NUMBER,

time_work as TIME_WORK,

mem_use as MEMORY_USE,

uniq_proc as UNIQUE_PERCENT,

points as POINTS,

"COMMENT" as COMMENTARY,

to_char(dt_tm, 'yyyymmdd') as DT,

substr(ora_hash(t.id_stat||t.id_prb||t.id_publ||t.id_cmp||t.id_rsl||time_work||mem_use||uniq_proc||points||"COMMENT"||to_char(dt_tm, 'yyyymmdd')), 1, 50) as md5

from status@acmlink t) src

full join stg.fct_solution stg on src.code = stg.code;

--вставляем данные в шлюз

insert into ldr_sh.fct_solution (code, problem_code, user_code, compiler_code, result_code,false_test_number, time_work, memory_use, unique_precent, points, commentary, dt, rec_status, sysmoment)

select

code,

problem_code,

user_code,

compiler_code,

result_code,

false_test_number,

time_work,

memory_use,

unique_precent,

points,

commentary,

dt,

decode(action , 'DD_DELETE', '1', '0') as rec_status,

sysmoment

from stg.temp_table s

where action <> 'DD_REJECT';

-- регистрируем изменения в STG

insert into stg.fct_solution (code, md5)

select code, md5 from stg.temp_table where action = 'DD_INSERT';

merge into stg.fct_solution stg

using (select * from stg.temp_table where action = 'DD_UPDATE') t

on (stg.code = t.code)

when matched

then update set stg.md5 = t.md5;

delete from stg.fct_solution where code in (select code from stg.temp_table where action = 'DD_DELETE');

-- удаляем промежуточную таблицу

drop table stg.temp_table;

------- ШАГ ТРАНСФОРМАЦИИ -------

insert into ldr_bf.fct_solution (id_solution, id_problem, id_user, id_compiler, id_result, code, problem_code, user_code, compiler_code, result_code, false_test_number, time_work, memory_use, unique_precent, points, commentary, dt_open, dt_close, rec_status, sysmoment)

select

(select id_solution from dwh.fct_solution dwh where dwh.code = sh.code) as id_solution,

(select id_problem from dwh.det_problem dwh where dwh.code = sh.problem_code) as id_problem,

(select id_user from dwh.det_user dwh where dwh.code = sh.user_code) as id_user,

(select id_compiler from dwh.det_compiler dwh where dwh.code = sh.compiler_code) as id_compiler,

(select id_result from dwh.det_result dwh where dwh.code = sh.result_code) as id_result,

sh.*

from (

select

substr(code, 1, 100) as code,

substr(problem_code, 1, 100) as problem_code,

substr(user_code, 1, 100) as user_code,

substr(compiler_code, 1, 100) as compiler_code,

substr(result_code, 1, 100) as result_code,

to_number(false_test_number) as false_test_number,

to_number(time_work) as time_work,

to_number(memory_use) as memory_use,

to_number(unique_precent) as unique_precent,

to_number(points) as points,

substr(commentary, 1, 250) as commentary,

to_date(dt, 'yyyymmdd') as dt_open,

to_date('30010101', 'yyyymmdd') as dt_close,

to_number(rec_status) as rec_status,

to_date(sysmoment, 'yyyymmdd') as sysmoment

from ldr_sh.fct_solution) sh;

commit;

-- чистим шлюз

truncate table ldr_sh.fct_solution;

------- ШАГ ЗАГРУЗКИ В ХРАНИЛИЩЕ -------

-- обновляем существующие записи

merge into dwh.fct_solution dwh

using (select * from ldr_bf.fct_solution bf

where bf.rec_status = 0) t

on (dwh.code = t.code)

when matched

then update set dwh.id_problem= t.id_problem,

dwh.id_user= t.id_user,

dwh.id_compiler= t.id_compiler,

dwh.id_result= t.id_result,

dwh.false_test_number= t.false_test_number,

dwh.time_work= t.time_work,

dwh.memory_use= t.memory_use,

dwh.unique_precent= t.unique_precent,

dwh.points= t.points,

dwh.commentary= t.commentary,

dwh.dt_open= t.dt_open,

dwh.dt_close= t.dt_close,

dwh.sysmoment = t.sysmoment;

commit;

-- вставляем новые записи

insert into dwh.fct_solution(id_solution, id_problem, id_user, id_compiler, id_result, code, false_test_number, time_work, memory_use, unique_precent, points, commentary, dt_open, dt_close, sysmoment)

select dwh.objseq.nextval, id_problem, id_user, id_compiler, id_result, code, false_test_number, time_work, memory_use, unique_precent, points, commentary, dt_open, dt_close, sysmoment

from ldr_bf.fct_solution bf

where bf.code not in (select code from dwh.fct_solution)

and bf.rec_status = 0;

commit;

-- закрываем удаленные в СИ записи

merge into dwh.fct_solution dwh

using (select * from ldr_bf.fct_solution bf

where bf.rec_status = 1) t

on (dwh.code = t.code)

when matched

then update set dwh.dt_close = trunc(sysdate),

dwh.sysmoment = t.sysmoment;

commit;

-- чистим буфер

truncate table ldr_bf.fct_solution;

--заносим информацию о загрузке

insert into dwh.load_status(table_name, sysmoment)

select

'FCT_SOLUTION',

sysdate

from dual;

commit;

51

Приложение 10

Статистика по применению языков программирования (все решения)

	sql	cpp	java	pascal	asm	vbscript	perl	python	ruby	lua	basic	csharp	javascript	txt	Всего
2005		102		282											384
2006		511		487											998
2007	1242	1422	42	548	3	9							5		3271
2008	1786	1434	165	581	107	3	49	4	17				1		4147
2009	1982	882	146	825	29	2	6	42	12				5		3931
2010	3067	1339	126	620	18		6	15	3						5194
2011	2422	1143	201	383	3	1	8	2	1						4164
2012	3047	2547	161	582	12		6	55	3	93			1		6507
2013	2157	1437	23	411		3		50	4	31	1	4	1		4122
2014	2564	1482	67	555	4	2		13	3	15	3	9			4717
2015	2510	1201	307	656	12	1	4	46		2	9	13	1		4762
2016	2340	1624	705	560	10	4	1	68	1	4	9	30	7	27	5390



Приложение 11

Статистика по применению языков программирования (решения студентов)

	sql	cpp	java	pascal	asm	vbscript	perl	python	ruby	lua	basic	csharp	javascript	Всего
2005		11												11
2006		174		28										202
2007	921	908	6	186	1	3								2025
2008	459	995	126	287	11		32	4	16					1930
2009	580	710	119	447	26		6	40	12				3	1943
2010	1317	1133	44	527	16		6	14	3					3060
2011	1141	583	23	297	3		5	2	1					2055
2012	1717	1705	41	422	10		5	50	3	6				3959
2013	736	1059	3	287				21	4	2	1	3		2116
2014	1849	1035	36	408	2			11	2		2	1		3346
2015	924	618	34	284	11			23			1	3		1898
2016	1350	868	164	157	8			11		1		3		2562



Приложение 12

Статистика по применению языков программирования (решения участников олимпиад)

	sql	cpp	java	pascal	asm	vbscript	perl	python	ruby	lua	basic	csharp	javascript	txt	Всего
2005		102		278											380
2006		480		477											957
2007	1242	1370	41	548	2	9							5		3217
2008	1785	1421	165	579	107	3	49	4	17				1		4131
2009	1982	872	146	825	29	2	6	42	12				5		3921
2010	3059	1336	126	620	18	1	6	15	3						5184
2011	2421	1143	201	382	3		8	2	1						4161
2012	3047	2547	161	582	12		6	55	3	93			1		6507
2013	2157	1437	23	411		3		50	4	31	1	4	1		4122
2014	2564	1482	67	555	4	2		13	3	15	3	9			4717
2015	2510	1201	307	656	12	1	4	46		2	9	13	1		4762
2016	2340	1624	705	560	10	4	1	68	1	4	9	30	7	27	5390

Размещено на Allbest.ru