Re: To all the pgsql developers..Have a look at the operators proposed by me in my researc

> CC: pgsql-hackers(at)postgresql(dot)org> From: decibel(at)decibel(dot)org> Subject: Re: [HACKERS] To all the pgsql developers..Have a look at the operators proposed by me in my research paper.> Date: Fri, 1 Jun 2007 19:13:54 -0500> To: tasneememon(at)hotmail(dot)com> > On Jun 1, 2007, at 8:24 AM, Tasneem Memon wrote:> > NEAR> >> > It deals with the NUMBER and DATE datatypes simulating the human > > behavior and processing the> > Why just number and date?


I have just started working on it for my MS research work.. for the moment I have written algorithms for these two datatypes only, but I intend to implement these operators for the other datatypes also. As for other datatypes, especially those involving "strings", its very complicated.



> > > information contained in NEAR in the same way as we humans take it. > > This is a binary operator with the syntax:> > op1 NEAR op2> > Here, the op1 refers to an attribute, whereas op2 is a fixed value, > > both of the same datatype.> > Suppose we want a list of all the VGAs, price of which should be > > somewhere around 30$ .. the query will look like:> >> > SELECT *> > FROM accessories> > WHERE prod_name = ‘VGA’> > AND prod_price NEAR 30> >> > A query for the datatype DATE will look like:> >> > SELECT *> > FROM sales> > WHERE item = ’printer’> > AND s_date NEAR 10-7-06> >> >> > The algorithm for the NEAR operator works as follows:> >> > The margins to the op2, i.e. m1 and m2, are added dynamically on > > both the sides, considering the value it contains. To keep this > > margin big is important for a certain reason discussed later.> > The NEAR operator is supposed to obtain the values near to the op2, > > thus the target membership degree(md) is initially set to 0.8.> > The algorithm compares the op1(column) values row by row to the > > elements of the set that NEAR defined, i.e. the values from md 1.0 > > to 0.8, adding matching tuples to the result set.> > How would one change 0.8 to some other value?



We can make the system ask the user as to what membership degree s/he wants to get the values, but we don’t want to make the system interactive, where a user gives a membership degree value of his/her choice. These operators are supposed to work just like the other operators in SQL.. you just put them in the query and get a result. I have put 0.8 because all the case studies I have made for the NEAR, 0.8 seems to be the best choice.. 0.9 narrows the range.. 0.75 or 0.7 gets those values also that are irrelevant.. However, these values will no more seem to be irrelevant when we haven’t got any values till the md 0.8, so the operator fetches them when they are the NEARest.

I would like to mention another thing here that this looks like defining the range like BETWEEN operator does, but its different in a way that with BETWEEN we define an exact, strict range. Anything outside that range wont be included no matter that value might be of interest of the user querying the system, and if there are no values between that range, the result set is empty.

> > > 4. It is very much possible that the result set is empty since > > no values within the range exist in the column. Thus, the algorithm > > checks for empty result set, and in that case, decreases the target > > md by 0.2 and jumps to step 3. This is the reason big margins to > > the op2 are added.> > 5. In case there are no values in op1 that are between m1 and > > m2 (where the membership degree of the values with respect to NEAR > > becomes 0.1) and the result set is empty, the algorithm fetches the > > two nearest values (tuples) to op2, one smaller and one larger than > > the op2, as the result.> >> > The algorithm will give an empty result only if the table referred > > to in the query is empty.> >> > 2. NOT NEAR> >> > This operator is also a binary operator, dealing with > > the datatype NUMBER and DATE. It has the syntax:> > op1 NOT NEAR op2> > The op1 refers to an attribute, whereas op2 is a fixed value, both > > of the same data type.> > A query containing the operator looks like:> >> > SELECT id, name, age, history> > FROM casualties> > WHERE cause = ‘heart attack’> > AND age NOT NEAR 55> >> > Or suppose we need a list of some event that is not clashing with > > some commitment of ours:> >> > SELECT *> > FROM events> > WHERE e_name= ‘concert’> > AND date NOT NEAR 8/28/2007> >> > The algorithm for NOT NEAR works like this:> > First of all it adds the margins to the op2, i.e. m1 and m2, > > dynamically on both the sides, considering the value op2 contains.> > op1 values outside the scope of the op2 (m1, m2) are retrieved and > > added to the result.> > If the result set is empty, the farthest values within the op2 > > fuzzy set (those possessing the least membership degree) are > > retrieved. This is done by continuing the search from values with > > md=0.1 till the md=0.6, where the md for NOT NEAR reaches 0.4.> > Why isn't this just the exact opposite set of NEAR?


Because we are talking about the Fuzzy behavior, so it doesn’t have to be exact opposite.. it shouldn’t be. If it is, we might don’t see the information that’s important to us in the result set. This is something you can not define precisely, if it has to be precise, then the BETWEEN is doing a good job, there would have been no need to introduce these operators.

And even for different datatypes, different values of md seem to work better. Like for NUMBER, op2 md = 0.6 (NOT NEAR md = 0.4) looks just right to identify the margins.. but for DATE, it seems better to take the margins to the op2 md = 0.85 (NOT NEAR md = 0.15) if we don’t get the values till the previous margins.

> --> Jim Nasby jim(at)nasby(dot)net> EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)> >

I hope I explained things better.. and my English is not very good, so I am sorry if you couldn’t get my point.
Any other comments/critics welcome..
Regards,

-Tasneem Memon

_________________________________________________________________
News, entertainment and everything you care about at Live.com. Get it now!
http://www.live.com/getstarted.aspx