Multi-Column Integer Ordering

Question

I don't know if I've chosen the appropriate title for this question (if not, please change it accordingly) but consider the following simplified table structure I'm working with:

----------------------------------------------
|  date  |  i  |  j  |  k  |  x  |  y  |  z  |
----------------------------------------------
| 100209 |  1  |  2  |  3  |  4  |  5  |  6  |
----------------------------------------------
| 100210 |  2  |  3  |  4  |  5  |  6  |  7  |
----------------------------------------------
| 100211 |  0  |  1  |  2  |  3  |  4  |  5  |
----------------------------------------------
| 100212 |  1  |  2  |  3  |  4  |  5  |  6  |
----------------------------------------------
| 100213 |  6  |  5  |  4  |  3  |  2  |  1  |
----------------------------------------------

i, j, k, x, y, z are all unrelated integers / floats, they all represent different factors and can have very different orders of magnitude (i can range from 1 - 10 while j can range from 100 - 1000).

I'm trying to select dates that share similar conditions; Given a set of i, j, k, x, y, z values I need to return all results ordered by closeness of all values as a whole for instance, if i = 1, j = 2, k = 3, x = 4, y = 5 and z = 6 the query should return the following dates in this order:

1. 100209
2. 100212
3. 100210
4. 100211
5. 100213

I'm not sure if this is relevant or not to the question, but some values (i, j, k) mean more is better while other values (x, y, z) mean the opposite: less is better.

How I should build such a query? Is this possible with SQL alone?

---

@Pentium10:

I'll try to answer your comment the best way I can. Here is a sample of my data:

---------------------------------------------------------------------------------
  date  |  temperature  |  humidity  |  pressure  |  windSpeed  |  moonDistance  
---------------------------------------------------------------------------------
 090206 |  7            |  87        |  998.8     |  3          |  363953        
---------------------------------------------------------------------------------
 ...... |  ...          |  ...       |  ....      |  ...        |  ......        
---------------------------------------------------------------------------------
 100206 |  10           |  86        |  1024      |  2          |  386342        
---------------------------------------------------------------------------------
 100207 |  9            |  90        |  1015      |  1          |  391750        
---------------------------------------------------------------------------------
 100208 |  13           |  90        |  1005      |  2          |  396392        
---------------------------------------------------------------------------------
 100209 |  12           |  89        |  1008      |  2          |  400157        
---------------------------------------------------------------------------------
 100210 |  11           |  92        |  1007      |  3          |  403012        
---------------------------------------------------------------------------------
 100211 |  6            |  86        |  1012      |  2          |  404984        
---------------------------------------------------------------------------------
 100212 |  6            |  61        |  1010      |  3          |  406135        
---------------------------------------------------------------------------------
 100213 |  7            |  57        |  1010      |  2          |  406542        
---------------------------------------------------------------------------------

My table structure has more columns and thousands of rows but hopefully this will be enough to get my point clear. I'm not going to attempt to order these values like I did in my previous example because I would probably get it wrong, but I basically need to do two types of queries with this data:

1. show me all dates, ordered by the resemblance of the conditions provided by me
2. show me all dates, ordered by the resemblance of the conditions observed in date X

I understand that the second query can easily be archived by using the first one, but my problem lies in sorting by resemblance using several columns, that's what I meant by "ordered by closeness of all values as a whole". As in, if I was dealing with only one column it would be a lot easier to order by likeness but my head really starts spinning when dealing with several columns.

The goal is to to be able to produce results like this:

Today is really similar to d/m/yy, very similar to d/m/yy, somewhat similar to d/m/yy, ...

In my case I'm working with weather and atmospheric data but if it helps I guess you can think in this problem in terms of employees (having attendance, hoursPerWeek and monthlySalary columns) and order by employees that most closely resemble attendance = 100%, hoursPerWeek = 40 and monthlySalary = $5000, for instance.

PS: Now that I've given this employees example I'm not really sure anymore if it can be compared to the weather example I'm working with, since with the employees table you can compute (rating = monthlySalary / hoursPerWeek * attendance for instance) and kinda weight the columns, but I don't think the same can be done with the weather table - input is appreciated anyhow.

PS2: I'm not sure if I expressed myself well enough, if you still have doubts please let me know.

---

Bounty

Some good suggestions so far, however none of them truly solve my problem. I'm setting up a bounty to hopefully gather even more possible solutions to this problem. Thanks.

Answer 1

The problem you seem to have is that each column has a different scale and so you can't easily combine them. This problem can be solved using a technique called whitening. This involves calculating the average and standard deviation of each column (you could do this in 1 SQL statement) and then rescaling each column to this when selecting:

colSortPos = (colValue-colMean) / colStdev

Doing this will give you each column ranging around 0 which +/- 1 standard deviation within the range +/- 1. The trick then is to combine these so that similar dates are together. The problem here is that this is not a 2 dimensional problem and so you need to think multidimensionally. So my suggestion its to take the the Euclidean distance as your sort order.

SELECT
    date,
    i,
    j,
    k,
    SQRT( POW((i-@iMean)/@iStdDEv, 2) + POW((j-@jMean)/@jStdDEv, 2) + POW((k-@kMean)/@kStdDEv, 2) )
AS
    sort_order
FROM
    table
ORDER BY
    sort_order

The only problem with this is that it projects your problem onto a 1 dimensional space that may make you miss some correlations. To work around this I suggest using a clustering technique like K-means which is pretty simple to implement and is really fast. This will allow you to group your dates into k clusters that display the most similarity [ http://en.wikipedia.org/wiki/K-means_clustering ]. If you have the raw data and want to play around with these (and other) techniques then I suggest trying the weka toolkit [ http://www.cs.waikato.ac.nz/ml/weka/ ] which will let you play around with these techniques.

Answer 2

I think it's possible. Try using a function that adds up the difference. For instance:

SELECT *, ABS(i - @i) + ABS(j - @j) + ABS(k - @k) + ABS(x - @x) + ABS(y - @y) + ABS(z - @z) FROM table

This will give you your fuzzy logic difference value. The lower, the closer the match. You should also be able to sort by this expression: ie, order by ABS(i - @i)... ASC

A couple of suggestions, perhaps select the results to a temp table and sort on this.

Alternatively, if you are using SQL server, look in to using the .NET CLR and doing it in an .NET assembly - this will give you more power and options. It will also allow you to attach extra weight to certain fields more easily (if you meant that some fields should have additional weighting).

Hope this helps, Steve

Answer 3

I'm not sure if this is relevant or not to the question, but some values (i, j, k) mean more is better while other values (x, y, z) mean the opposite: less is better.

Since you are dealing with dates, you may want to derive other dates based on these criteria.
For example you can compute new dates, for a value having a higher effect you add couple of hours/days/weeks/months to your date, and if a value has a lover effect you add just seconds/minutes to your dates. Then you sort based on this computed new date.
You can subtract dates using negative compensations if they reach a peak level

Check out Fuzzy SQL (FSQL)

EDIT 1

Try this: std(i)*abs(i-@i) ...

SELECT 
  (select std(i) from fuzzysearch)*abs(i-7) as v1,
  (select std(j) from fuzzysearch)*abs(j-90) as v2,
  f.* 
FROM fuzzysearch as f 
order by 
  v1 asc, 
  v2 desc

Answer 4

This is hard (if not impossible) to do in SQL, but there's a multivariate analysis technique called Factor Analysis. It's a way of creating a "factor" - a linear combination of your variables which puts a weight on each input to get a "factor score". Basically it does a bunch of linear algebraic manipulations on your data to create a singles set of scores that minimizes some objective (like total errors).

I did a factor analysis on 3 days of hourly weather data and it looks pretty good. You can see that entries with similar factor scores generally are very close in values across all four measures. I used a principal component extractions with an Equimax rotation:

Wind    Air    Dewpoint    BP         Score
-------------------------------------------
3       12     9           1012.2     -2.72
5       17     11          1011.9     -1.77
5       16     10          1010.8     -1.75
6       15     10          1010.4     -1.68
3       19     13          1012.1     -1.57
5       17     11          1010.8     -1.54
7       17     12          1012.0     -1.53
8       19     12          1012.0     -1.24
5       20     14          1012.0     -1.18
7       17     12          1009.8     -1.06
9       20     13          1012.5     -1.05
8       21     12          1012.1     -1.05
7       19     11          1009.9     -0.98
6       18     13          1009.6     -0.90
0       24     17          1012.1     -0.90
8       20     12          1010.8     -0.88
5       22     15          1012.0     -0.87
10      21     12          1012.0     -0.86
8       21     12          1010.8     -0.78
9       19     12          1010.2     -0.78
8       23     10          1010.7     -0.76
6       23     15          1012.3     -0.75
7       20     12          1009.7     -0.73
7       20     12          1009.6     -0.71
10      22     14          1011.5     -0.45
7       19     15          1009.3     -0.45
6       20     16          1009.7     -0.41
7       20     15          1009.4     -0.37
10      24     12          1010.6     -0.26
5       26     18          1012.5     -0.26
9       23     15          1011.0     -0.22
12      24     16          1012.3     -0.04
6       25     16          1009.5      0.15
5       28     20          1012.5      0.16
0       28     17          1009.0      0.16
8       24     17          1010.2      0.17
3       30     20          1012.5      0.19
12      23     16          1010.6      0.21
8       25     15          1009.5      0.21
13      25     13          1010.5      0.22
13      25     14          1010.6      0.30
5       25     20          1010.1      0.35
6       25     19          1009.9      0.37
9       23     18          1009.5      0.40
13      25     15          1010.3      0.46
5       30     21          1012.4      0.48
7       26     19          1010.0      0.54
12      27     16          1010.9      0.56
8       24     20          1009.7      0.58
7       27     17          1009.2      0.60
7       27     18          1009.6      0.62
7       27     17          1009.0      0.64
8       26     21          1010.6      0.70
9       28     17          1009.8      0.75
8       25     22          1010.0      0.82
8       26     23          1010.8      0.86
8       25     22          1009.8      0.87
8       25     23          1010.1      0.91
9       26     22          1010.5      0.91
8       26     22          1009.8      0.97
14      29     17          1010.8      1.06
12      26     22          1010.8      1.09
10      31     18          1010.3      1.14
14      30     18          1010.7      1.28
17      29     17          1010.9      1.29
10      27     22          1009.4      1.32
12      26     22          1009.7      1.32
8       27     24          1009.3      1.38
14      27     22          1010.2      1.49
12      28     22          1009.8      1.51
16      31     19          1010.7      1.66
14      28     23          1009.6      1.82