By Patch ~ November 11th, 2012. Filed under: Oracle, SQL.
I have been appointed Track Leader for the Developer’s Toolkit for KScope13. This involves voting on abstracts. Everyone in the group should rate the abstracts on a scale of 1 to 5. Where 1 is not good (Not appropriate…) and 5 is very good (Highlight of the conference…). After all the members of the group have voted, my task is to figure out which abstracts have the highest rating.
The website shows the different abstract, your own rating and the average voting over all voters.
Coming up with the top sessions involves a lot of scrolling, writing stuff down and trying to make out the rankings. Luckily the site also provides the possibility to download the voting in a Comma Separated Values format so you can open it in Excel.
Since I am not (yet) an Excel Wizard this is not what I am looking for. Still takes a lot of work, which I don’t want to do.
Oracle provides the possibility to use a file with Comma Separated Values as an External Table. Taking this approach gives me the opportunity to apply my SQL skills to the data and have the database do the heavy lifting on figuring out which abstracts get the highest rating.
To create the external table I have to take these steps:
1) create a directory
1: -- Create directory
2: create or replace directory EXTERNALDATA
3: as 'C:\MySources\ExternalData';
2) save the file to this folder on the database server (in this case my own machine)
3) create an External Table:
1: -- Create table
2: create table KSCOPE13_EXT_CSV
4: session_num VARCHAR2(4000),
5: session_title VARCHAR2(4000),
6: session_submitter VARCHAR2(4000),
7: primary_presenter VARCHAR2(4000),
8: co_presenter VARCHAR2(4000),
9: voter VARCHAR2(4000),
10: total VARCHAR2(4000),
11: vote VARCHAR2(4000),
12: session_comment VARCHAR2(4000)
14: organization external
16: type ORACLE_LOADER
17: default directory EXTERNALDATA
18: access parameters
20: records delimited by newline
24: SKIP 1
25: fields terminated by ','
26: optionally enclosed by '"'
27: missing field values are null
28: ( Session_Num char(4000)
29: ,Session_title char(4000)
30: ,Session_Submitter char(4000)
31: ,Primary_Presenter char(4000)
32: ,Co_Presenter char(4000)
33: ,Voter char(4000)
34: ,Total char(4000)
35: ,Vote char(4000)
36: ,Session_Comment char(4000)
39: location (EXTERNALDATA:'kscope2013.csv')
41: reject limit UNLIMITED;
4) Create the query:
1: SELECT DISTINCT t.session_title
3: ,round(avg(t.total) over (partition by t.session_title, t.session_submitter),4) average
4: FROM kscope13_ext_csv t
5: order by 3 desc;
5) Check the results
This makes my job a lot easier. Well, maybe not a lot, just a bit. Still had to build the schedule for the top abstracts. But at least it’s easier to decide which session get selected and which don’t and, maybe most important, I got to use my Oracle skills on a non-Oracle related issue.