Decimal(65,3)存储tf-idf值遇截断，求排查SQL查询问题

你遇到的截断问题根本不是Decimal(65,3)的范围不够，而是因为你用了format()函数处理数值，导致插入的是带千分位逗号的字符串，数据库解析时出错了。

举个例子：你的tf-idf值计算后是2606.806，用format(tf*idf, 3)处理后会变成字符串"2,606.806"（注意中间的逗号）。当你把这个字符串插入Decimal类型字段时，数据库会从左到右解析数字，遇到非数字的逗号就停止，只取前面的2，最终存储为2.000，同时触发截断警告。

而改成Text类型时，数据库直接存储这个带逗号的字符串，所以看起来正常，但显然没法做算术运算。

FORMAT()是用来生成格式化字符串的（比如带千分位、货币符号），而你需要的是保留三位小数的数值类型，应该用ROUND()函数。ROUND()会返回一个数值，不会引入千分位逗号，能正确被Decimal字段解析。

修正后的SQL语句如下：

INSERT INTO features (id_komentar, kata, tf, tfglobal, IDF, tfidf, tfidfglobal) 
SELECT 
    tf.id_komentar, 
    tf.kata, 
    tf.tf, 
    tfglobal.tfglobal, 
    ROUND(idf.idf, 3) AS idf, 
    ROUND(tf.tf * idf.idf, 3) AS tfidf, 
    ROUND(tfglobal.tfglobal * idf.idf, 3) AS tfidfglobal 
FROM (
    (SELECT id_komentar, kata, COUNT(kata) AS tf FROM words GROUP BY id_komentar, kata) tf 
    INNER JOIN (SELECT kata, COUNT(kata) AS tfglobal FROM words GROUP BY kata) tfglobal ON tf.kata = tfglobal.kata 
    INNER JOIN (
        SELECT kata, ROUND(LOG(n.n / df.df), 3) AS idf 
        FROM (
            SELECT kata, COUNT(id_komentar) AS df 
            FROM (SELECT DISTINCT kata, id_komentar FROM words) df 
            GROUP BY kata
        ) df, 
        (SELECT COUNT(id) AS n FROM comments) n 
    ) idf ON tfglobal.kata = idf.kata 
) 
INNER JOIN (
    SELECT kata, COUNT(id_komentar) as df 
    FROM (SELECT DISTINCT kata, id_komentar FROM words) df 
    GROUP BY kata
) df ON idf.kata = df.kata 
ORDER BY tf.id_komentar ASC;

你的SQL里重复写了两次计算df的子查询，可以把它提取成一个CTE（公共表表达式），让代码更简洁易维护：

WITH df AS (
    SELECT kata, COUNT(id_komentar) AS df 
    FROM (SELECT DISTINCT kata, id_komentar FROM words) temp 
    GROUP BY kata
),
n AS (
    SELECT COUNT(id) AS n FROM comments
)
INSERT INTO features (id_komentar, kata, tf, tfglobal, IDF, tfidf, tfidfglobal) 
SELECT 
    tf.id_komentar, 
    tf.kata, 
    tf.tf, 
    tfglobal.tfglobal, 
    ROUND(LOG(n.n / df.df), 3) AS idf, 
    ROUND(tf.tf * LOG(n.n / df.df), 3) AS tfidf, 
    ROUND(tfglobal.tfglobal * LOG(n.n / df.df), 3) AS tfidfglobal 
FROM (
    SELECT id_komentar, kata, COUNT(kata) AS tf FROM words GROUP BY id_komentar, kata
) tf 
INNER JOIN (SELECT kata, COUNT(kata) AS tfglobal FROM words GROUP BY kata) tfglobal ON tf.kata = tfglobal.kata 
INNER JOIN df ON tf.kata = df.kata
CROSS JOIN n
ORDER BY tf.id_komentar ASC;

这样既避免了重复代码，也让逻辑更清晰。

内容的提问来源于stack exchange，提问作者Ruddy Cahyanto