Re: Things that are hard to do in SQL (datamining?)

In article <38q2li$p83@infmx.informix.com> proberts@informix.com "Paul Roberts" writes: > I seem to keep re-encountering a certain kind of problem for which, > as far as I can see, it particularly hard to attack with SQL. I was > wondering whether anyone else has any deep - or shallow - insights > about it. > > For instance, imagine that we had a table which associated each > employee with a department, and another table which told us which > courses each employee has taken: > > -- EMPLOYEE -- -- ATTENDED -- > > emp_id dept emp_id course_id date_taken > ------ ---- ------ --------- ---------- > > 1 FIN 2 AA 01/01/93 > 2 SALES 2 AD 01/01/94 > 3 SALES 3 AA 01/01/92 > 4 MIS 1 AA 01/01/92 > 5 FIN ..... > 6 FIN > ..... > > Now, someone comes along and says "You can see that the Finance (FIN) > people all get to attend pretty much the same classes eventually. In > fact it generally works out that all the people in any given department > mostly attend the same classes in the end. We'd like you to draw up a > list of who should attend which class in order to cover material they > have missed. That is, who hasn't attended a class that most of the people > in their department have attended?" > > And to prevent it being too easy, there is overlap: maybe FIN and > MIS people tend to take "principles of accounting", MIS and SALES > tend to take "negotiating to Yes!". And there may be exceptions: > just because employee #7 took "how not to harrass coworkers" doesn't > mean that everyone is his department ought to take the same course. > > Crucially, there is no table that relates DEPARTMENT to COURSE. We > are expected to figure out this relationship by looking at the data in > the two tables given and seeing "what's normal". > > I don't know whether this sounds like a very contrived problem, and > whether you'll believe me when I say that it keeps coming up. A similar > situation is "All our products come in a number of types (development, > runtime, maint, etc). You'd expect products in the same product line > to all come in the same set of types. So, locate missing product > types" - e.g. `hey, where is runtime maintenance for I-SQL V1.2.3.4 ; > all the other versions of I-SQL have a runtime maintenance!' > > Is this a kind of "datamining", in that we are trying to (a) uncover > regularities in the data and then (b) find the exceptions? > > The basic problem, it seems to me, is that we are really comparing > the relation between an entity (an employee, a software product) > and a SET of items (courses taken, types-available), when the data > given are relations between those entities and individual items. > > The problem is perhaps too vaguely stated to ask for "the solution", > but I curious as to whether others have come across the same sort > of thing, and if so do you have any insights. > > Yours relationally, > > Paul I know you don't want the solution but rather a discussion of the problem but following is (not very efficient) SQL to find a list of courses to be booked for employees who have not attended courses that 30% of their departmental colleagues have attended. select c.course_id, e.employee_id from course c, employee e where 30/100 >= ( select count(distinct employee_id) from attended where department = e.department and course_id = a.course_id ) / ( select count(*) from employee where department = e.department ) and not exists ( select 1 from attended where course_id = c.course_id and employee_id = e.employee_id ) and to find exceptions (those that have attended courses that few of their departmental colleagues have attended) simply replace '>=' with '<' and 'not exists' with 'exists'. Or replace the 'exists' sub-query by a join onto attended. I suppose you are interested in a GENERAL solution to the problem. For example, a tool that you would let loose on a database that didn't know anything about the data dictionary but would tell you that only one of 5000 male employees has a maiden name. [Useful.] Or that only three customers have zip codes equal to their credit balance [Less useful.] -- Paul Beardsell SAM Business Systems Ltd ~~~~~~~~~~~~~~ 21 Finn House, Bevenden St, pbeardsell@cix.compulink.co.uk London, N1-6BN, UK. psb@sambusys.demon.co.uk (+44 or 0)71 608-2391