我正在做一个项目,人们可以在维度上搜索。
我们有以下表格结构:
产品表:宽度长高度
人们可以通过给出以下维度来搜索这个数据库:
300x200x250
我们想要的是根据最佳匹配对结果进行排序。我们在数据库中找到了确切的尺寸,你会有100%的匹配。因此,我们不仅想找到100%匹配的结果,而且例如310x200x250或300x220x260也可以,只是匹配百分比会更低。
我们希望允许所有结果匹配90%或更多,或者限制为50个结果。
基本查询当然很简单:
SELECT * FROM products WHERE length >= 300 AND height >= 200 AND height >= 100 AND(FORMULE TO CALCULATE THE MATCH WHERE HIGHER THEN 90% OR MAX OF 50 RESULTS.
任何关于如何解决百分比部分和最大极限部分的问题都可以给我一个正确的方向。
问候,
欧文
您可以尝试类似的
SELECT *,
CEIL((300/ width * 0.333 +
200/ length * 0.333 +
250 / height * 0.333) * 100) pcnt
FROM products
WHERE width >= 300
AND length >= 200
AND height >= 250
HAVING pcnt > 90
ORDER BY pcnt DESC
LIMIT 50
输出:
|ID|WIDTH|LENGTH|HEIGHT|PCNT||----|-------|--------|--------|------||1 | 300 | 200 | 250 | 100||2 | 310 | 200 | 250 | 99||3 | 300 | 220 | 260 | 96|
这是SQLFiddle演示
您可以尝试以下代码
SELECT * FROM products,
(ABS(width-300)/300)-1 as percwidth,
(ABS(length-200)/200)-1 as perclength,
(ABS(length-200)/200)-1 as percheigth
WHERE percwidth >=0.9
AND
perclength >=0.9
AND
percheigth >=0.9
ORDER BY percwidth,perclength,percheigth DESC
LIMIT 50
我想,当你不能返回超过90%的50个结果时,你想要更高的百分比。您还应该通过来决定哪个维度在订单中更重要
这是一个简单的版本:我假设您正在使用传递的变量创建此查询?让我们称之为$length,$width,$height。假设您的列被定义为INT或其他数字,您可以在不加引号的情况下进行比较。
您希望使用<>或"BETWEEN"比较
"SELECT * FROM products
WHERE (length>($length - 10) AND length<($length+10)
AND (height>($height- 10) AND length<($height+10)
AND (width>($width- 10) AND width<($height+10)";
其中10是你的射程。如果$length=100翻译为"长度>(100-10)"等
您也可以使用BETWEEN(length BETWEEN $length - 10 AND $length + 10)
BETWEEN首先需要较小的值,其次需要较高的值
您还可以创建百分比:length BETWEEN $length-($length*.1) AND $length+($length*.1)
然后ORDER BY (height=$height AND width=$width AND length=$length) DESC, height*width*length
这将具有精确尺寸的产品放在列表的顶部,然后是其他产品,产品尺寸越接近所需尺寸,它们的排序就越好。
您需要这样的东西才能使此查询工作:
SELECT *
FROM (
SELECT whatever,
whatever,
MATCH_METRIC(length, 300, width, 200, height, 100) AS match
FROM products
WHERE length >= 300
AND width >= 200
AND height >= 100
) AS a
WHERE match >= 0.9
ORDER BY match DESC
LIMIT 50
这假设您有一个匹配度量公式,其中完全匹配会产生1.0的值,而部分或接近匹配会产生较小的值。
当然,你需要计算出你的MATCH_METRIC()
公式;你没有解释这是怎么回事。
让我们假设,如果你匹配一个维度,你想使用一个分数来衡量这两个值之间的距离。
1.0 - (ABS(A-B)/A)
如果你把300和300代入这个公式,你会得到1.0,一个完美的匹配。如果你加上300和330,你会得到0.9,这意味着有10%的差异。然后,假设您想在所有三个维度上使用该公式,并选择匹配最差的维度——三个维度中最小的匹配度量。然后,您将在MATCH_METRIC中获得此信息。
LEAST(1.0-(ABS(length-300)/length),
1.0-(ABS(width-200)/width),
1.0-(ABS(height-100)/height)
)
对于CCD_ 6函数。还有很多其他方法可以计算度量。你必须弄清楚哪一个最适合你的申请。
以下是计算MATCH_METRIC()
的存储过程的定义。
DELIMITER $$
DROP FUNCTION IF EXISTS MATCH_METRIC$$
CREATE FUNCTION MATCH_METRIC(
len FLOAT, targetlen FLOAT,
wid FLOAT, targetwid FLOAT,
hgt FLOAT, targethgt FLOAT
) RETURNS FLOAT
NO SQL DETERMINISTIC
COMMENT 'computes match between dimension and target dimension'
BEGIN
RETURN LEAST(1.0-(ABS(len-targetlen)/len),
1.0-(ABS(wid-targetwid)/wid),
1.0-(ABS(hgt-targethgt)/hgt)
);
END$$
DELIMITER ;