Многостолбцовое целочисленное упорядочивание
Я не знаю, выбрал ли я соответствующий заголовок для этого вопроса (в противном случае измените его соответственно), но рассмотрите следующую упрощенную структуру таблицы, с которой я работаю:
----------------------------------------------
| 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 все несвязанные целые числа / плавания, они все представляют различные факторы и могут иметь совсем другие порядки величины (i может колебаться от 1 - 10 в то время как j может колебаться от 100 - 1000).
Я пытаюсь выбрать даты, которые совместно используют подобные условия; Учитывая ряд i, j, k, x, y, z значения я должен возвратить все результаты, заказанные близостью всех значений в целом, например, если i = 1, j = 2, k = 3, x = 4, y = 5 и z = 6 запрос должен возвратить следующие даты в этом порядке:
- 100209
- 100212
- 100210
- 100211
- 100213
Я не уверен, относится ли это или не к вопросу, но некоторым значениям (i, j, k) средний больше лучше в то время как другие значения (x, y, z) имейте в виду противоположное: меньше лучше.
Как я должен создать такой запрос? Действительно ли это возможно с одним только SQL?
@Pentium10:
Я попытаюсь ответить на Ваш комментарий лучшим способом, я могу. Вот образец моих данных:
---------------------------------------------------------------------------------
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
---------------------------------------------------------------------------------
Моя структура таблицы имеет больше столбцов и тысячи строк, но надо надеяться этого будет достаточно для понимания моей ясной мысли. Я не собираюсь пытаться заказать эти значения как, я сделал в своем предыдущем примере, потому что я, вероятно, пойму его превратно, но я в основном должен сделать два типа запросов с этими данными:
- покажите мне все даты, заказанные подобием условий, обеспеченных мной
- покажите мне все даты, заказанные подобием условий, наблюдаемых на дате X
Я понимаю, что второй запрос может легко быть заархивирован при помощи первого, но моя проблема заключается в сортировке по использованию подобия несколько столбцов, это - то, под чем я подразумевал "заказанный близостью всех значений в целом". Как в, если бы я имел дело только с одним столбцом, было бы намного легче заказать сходством, но моя голова действительно начинает вращаться при контакте с несколькими столбцами.
Цель состоит в том, чтобы быть смочь привести к результатам как это:
Сегодня действительно подобно d/m/yy, очень похож на d/m/yy, несколько подобен d/m/yy...
В моем случае я работаю с погодой и атмосферными данными, но если помогает, что я предполагаю, что можно думать в этой проблеме с точки зрения employees (наличие attendance, hoursPerWeek и monthlySalary столбцы) и порядок сотрудниками, которые наиболее тесно напоминают attendance = 100%, hoursPerWeek = 40 и monthlySalary = $5000, например.
PS: Теперь, когда я дал этот пример сотрудников, я больше не действительно уверен, если он может сравниться с погодным примером, я работаю с, с тех пор со списком сотрудников можно вычислить (rating = monthlySalary / hoursPerWeek * attendance например) и вид веса столбцы, но я не думаю, что то же может быть сделано с погодной таблицей - вход ценится во всяком случае.
PS2: я не уверен, выразился ли я достаточно хорошо, если у Вас все еще есть сомнения, сообщенные мне.
Щедрость
Некоторые хорошие предложения до сих пор, однако ни один из них действительно не решает мою проблему. Я настраиваю щедрость для обнадеживающего сбора еще более возможных решений этой проблемы.Спасибо.
4 ответа
Кажется, у вас возникла проблема в том, что у каждого столбца разный масштаб, и вы не можете легко их объединить. Решить эту проблему можно с помощью техники, называемой отбеливанием. Это включает в себя вычисление среднего и стандартного отклонения для каждого столбца (вы можете сделать это в одном операторе SQL), а затем масштабирование каждого столбца до следующего значения при выборе:
colSortPos = (colValue-colMean) / colStdev
Это даст вам каждый столбец в диапазоне от 0, что +/- 1 стандарт отклонение в пределах +/- 1. Уловка состоит в том, чтобы объединить их так, чтобы схожие даты были вместе. Проблема здесь в том, что это не двумерная проблема, и поэтому вам нужно мыслить многомерно. Поэтому я предлагаю использовать евклидово расстояние в качестве порядка сортировки.
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
Единственная проблема заключается в том, что он проецирует вашу проблему на одномерное пространство, что может привести к тому, что вы пропустите некоторые корреляции. Чтобы обойти это, я предлагаю использовать метод кластеризации, такой как K-means, который довольно просто реализовать и работает очень быстро. Это позволит вам сгруппировать ваши даты в k кластеров, которые покажут наибольшее сходство [ http://en.wikipedia.org/wiki/K-means_clustering ].Если у вас есть необработанные данные и вы хотите поиграть с этими (и другими) методами, я предлагаю попробовать инструментарий weka [ http://www.cs.waikato.ac.nz/ml/weka/ ], что позволит вам поиграть с этими методами.
Я не уверен, имеет ли это отношение или не к вопросу, а к некоторым значениям (i, j , k) означают, что больше - лучше, а другие значения (x, y, z) означают противоположное: чем меньше, тем лучше.
Поскольку вы имеете дело с датами, вы можете получить другие даты на основе этих критериев.
Например, вы можете вычислить новые даты, для значения, имеющего более высокий эффект , вы добавляете пару часов / дней / недель / месяцев к вашей дате, и если значение имеет эффект любовника вы добавляете секунды / минуты к вашим датам. Затем вы сортируете на основе этой вычисленной новой даты.
Вы можете вычесть даты, используя отрицательные компенсации, если они достигают пикового уровня
Проверьте нечеткий SQL ( FSQL )
РЕДАКТИРОВАТЬ 1
Попробуйте следующее: 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
Думаю, это возможно. Попробуйте использовать функцию, которая суммирует разницу. Например:
SELECT *, ABS (i - @i) + ABS (j - @j) + ABS (k - @k) + ABS (x - @x) + ABS (y - @y) + ABS. (z - @z) ИЗ таблицы
Это даст вам значение разности нечеткой логики. Чем ниже, тем точнее совпадение. Вы также должны иметь возможность сортировать по этому выражению: т.е. упорядочить по ABS (i - @i) ... ASC
Несколько предложений, возможно, выберите результаты во временную таблицу и отсортируйте по ней.
В качестве альтернативы, если вы используете SQL-сервер, попробуйте использовать .NET CLR и сделать это в сборке .NET - это даст вам больше возможностей и возможностей. Это также позволит вам более легко придать дополнительный вес определенным полям (если вы имели в виду, что некоторые поля должны иметь дополнительный вес).
Надеюсь, это поможет, Стив
Это сложно (если не невозможно) сделать в SQL, но есть метод многовариантного анализа, называемый факторным анализом. Это способ создания «фактора» - линейной комбинации ваших переменных, которая придает вес каждому входу, чтобы получить «факторную оценку». По сути, он выполняет кучу линейных алгебраических манипуляций с вашими данными, чтобы создать единичный набор оценок, который минимизирует некоторые цели (например, общие ошибки).
Я провел факторный анализ на основе ежечасных данных о погоде за 3 дня, и он выглядит неплохо. Вы можете видеть, что записи с похожими факторными оценками обычно очень близки по значениям по всем четырем параметрам. Я использовал извлечение главных компонентов с вращением Equimax:
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