Multi-Column Integer Ordering

Answer 1

Hi Alix,

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 2

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 3

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 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