使用C＃排序SqlServer数据 [英] Rank SqlServer data using C#

问题描述

大家好，



以下问题因某些原因被删除了我不明白（没有评​​论）。也许删除者并不熟悉模糊逻辑的概念。这很好 - 只要想一下与SELECT语句中的排名有关的问题就可以处理大数据了。所以我再试一次，并且尽我所知也试着改进这个问题。问题本身对我来说非常重要，因为它对我正在研究的研究问题至关重要。 / Petter







大家好，



我正在研究一种模糊逻辑方法来检索和排序（排序/称重）数据库中的数据。例如，如果一个人在每晚100到200欧元之间寻找一个酒店房间，那么花费150欧元的房间可能会被排名第一，花费199欧元的房间在结果列表中排名相当低，并且如果只有少数（如果有的话）结果满足用户的要求，结果中可能仍会包含202欧元的房间。另一个例子：一位顾客想要在30-50欧元的价格范围内寻找红色衬衫，数据库也显示深色衬衫，因为它们相当接近，价格为40欧元。



我现在正在考虑如何使用SqlServer数据库来完成此行为。 （注意：我不是在寻找内置的自由文本搜索功能。）



一种方法可能意味着使用Sql函数，我可以使用它发送用户输入的值（例如通过网站表单 - >存储过程），这样我最终会得到各种模糊逻辑情况的函数。



另一种方法可能是使用Linq到Sql，然后在C＃中编写函数（我可能在这里不正确）。我在编写C＃代码方面比使用高级Sql代码要好得多，我可以检查代码，因为我在Visual Studio中测试它，所以如果这是可行的，那么这可能是一件好事，但话又说回来，这种方法不会那么独立，我希望在CodeProject上实现一个模糊逻辑项目供任何人使用。



第三种选择是做一个简单的使用 http://stackoverflow.com/questions/9329678/sql-search-query-how-to-assign-weight-to-each-input-parameters-and-how-订购 [ ^ ]然而，这种方法不易维护或在其他项目中使用。



也许我可能错过了第四个选项，但我已经对模糊逻辑进行了广泛的搜索和阅读，这些选项就是我想出来的。



我还没有看到这些选项的任何可用代码，所以目前，我真的可以使用那里的任何帮助。换句话说，如果有人熟悉这个概念，我会很高兴任何指示。



非常感谢提前，



Petter

Hi all,

The question below was deleted for some reason I don''t understand (no comment was being made). Perhaps the deleter wasn''t familiar with the concept of fuzzy logic. This is okey - just think of the question as relating to ranking in SELECT statements for dealing with big data. So I try again, and have also tried to improve the question to the best of my knowledge. The question itself is very important to me, as it is fundamental to a research problem I am working on. /Petter



Hi all,

I''m working on a "fuzzy logic" way to retrieve and order (ranking/weighing) data from a database. For example, if a person looks for a hotel room in between 100 and 200 euros per night, a room that costs 150 euros might be ranked as number one, a room that costs 199 euros will be ranked rather low in the result list, and a room that costs 202 euros might still be included in the results, in case there are just a few (if any) results that fulfil the user''s requirements. Another example: A customer wants to look for red shirts in the 30-50 euro price range, and the database displays darkred shirts as well, as they are a rather close match and cost 40 euros.

I''m now thinking of how to accomplish this behaviour using a SqlServer database. (NOTE: I''m not looking for the built-in freetext search features.)

One way to go ahead would probably mean using Sql functions, to which I send the values that the user enters (for example via a web site form --> stored procedure), so that I end up with a number of functions for various cases of fuzzy logic.

Another way would perhaps be to use Linq to Sql and then write the functions in C# (I might be incorrect here). I''m much better at writing C# code than advanced Sql code plus I can check the code as I test it in Visual Studio, so if this would be feasible, then that would probably be a good thing, but then again, this approach wouldn''t be that independent, and I hope to implement a fuzzy logic project here at CodeProject for anyone to use.

A third option would be to do a simple rank in long, nasty-looking stored procedures, using syntax like in http://stackoverflow.com/questions/9329678/sql-search-query-how-to-assign-weight-to-each-input-parameters-and-how-to-order[^] However, this approach would not be easy to maintain or to use in other projects.

Perhaps there is a fourth option that I might have missed, but I have done extensive searching and reading on fuzzy logic, and these options are what I have come up with.

I also have yet to see any usable code of these options, so for the moment, I could really use any help that''s out there. In other words, if someone is familiar with working with this concept, I would be happy for any pointers.

Thanks very much in advance,

Petter

推荐答案

数据的存储，检索，过滤，排序，排序和聚合是数据库的优化和更好的数据库。数学和演示不是这样的。



你过滤模糊逻辑的情况并没有真正影响它，除非你在数学中加入了大量的数学计算。查询。

你应该看看的是分析函数，例如 CUME_DIST [ ^ ]。



问题是，您对数据库的设计有多大影响？

如何存储数据将是最重要的，它对查询性能至关重要。



例如，你不应该只在一个颜色列中存储红色，而是在每个列中存储RGB值，然后例如搜索 CUME_DIST（）OVER（ORDER BY ABS（@RED - RED） ））获取接近您正在搜索的红色的值。





我的意见简而言之：模糊逻辑中的数学应该在应用程序层完成，获取/创建要计算的值应该在数据库中完成。

Storing, retrieving, filtering, ordering, ranking and aggregation of data is what databases are optimized for and better at. Math and presentation not so.

Your case of fuzzy logics in the filtering is not really affecting it that badly unless you''re putting in a lot of math in the queries.
What you definitely should take a look at is Analytic Functions, for example CUME_DIST[^].

The question is, how much can you affect the design of the database?
How you store your data will be of the highest importance, it''s really crucial for the query performance.

For example, you shouldn''t just store "Red" in a colour column, but rather RGB values, in a column each, and then for example search for CUME_DIST () OVER (ORDER BY ABS(@RED - RED)) to get values close to the red colour you''re searching for.


My opinion in short: The math in the fuzzy logics should be done in the application layer, getting/creating the values to calculate from should be done in the database.

我会保持模糊化/解模糊化 C＃方面的代码，因为在我看来，像 C＃这样强大的编程语言更适合任务（更优雅，更干净的代码）。

我的两分钱。

I would keep the fuzzification/defuzzification code on the C# side, because, in my opinion, a powerful programming language like C# is better suited for the task (more elegant and clean code).
My two cents.

这篇关于使用C＃排序SqlServer数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！