时间戳

首页 > 解决方案 > SQL - 数学（最小 - 最大标准化）

c# - SQL - 数学（最小 - 最大标准化）

问题描述

我想在我的代码中进行规范化操作。我有一个 SQL - Server 表，它有 3 列；第一列有单个词，第二列有单个词，第三列有整数值。我研究了它，我认为我应该做一个 sql 查询而不是编写复杂的 C# 代码。我有一个 DataGridView，它看起来像那样（示例 1）；

           Column 1          Column 2
         ------------------------------
          hello my             4500
          crazy day            3200
          such a               2885
          new coder            1010

归一化公式：（（值/最大值）* 100）

问题：第一步效果很好，规范化操作后，我的 DataGridView 看起来像这样；

           Column 1          Column 2
         ------------------------------
          hello my             %100
          crazy day            %71.11
          such a               %64.11
          new coder            %22.44

但是当我像这样将其他链接上传到系统时（示例 2）；

           Column 1          Column 2
         ------------------------------
          maybe today          2560
          it mine              1405
          the world            800
          welcome there        400

它会像这样返回我的标准化值，因为它每次都取最大值 4500；

           Column 1          Column 2
         ------------------------------
          maybe today          %56.88
          it mine              %31.22
          the world            %17.77
          welcome there        %8.88

但是，我希望它本身被考虑。当我上传新链接时，它应该找到链接中的最大值并据此返回规范化值。所以，我的 DataGridView 应该是这样的；

           Column 1          Column 2
         ------------------------------
          maybe today          2560
          it mine              1405
          the world            800
          welcome there        400




           Column 1          Column 2
         ------------------------------
          maybe today          %100
          it mine              %54.88
          the world            %31.25
          welcome there        %15.62

这是我的代码；

   string myCommand = "SELECT c1, c2, ((CONVERT(DECIMAL(18,2), c3) / (SELECT MAX(c3) FROM myTableName)) * 100) AS normalizedc3 FROM myTableName WHERE c1='" + sr[mssi1] + "' AND c2='" + sr[mssi2] + "' OR c2='" + sr[mssi1] + "' AND c1='" + sr[msii2] + "'";

这是我的第二个代码；

    string myCommand = "select c1, c2, c3 / m.max_c3 * 100 normalizedvalue from myTableName inner join (select convert(float, max(c3)) max_c3 from myTableName) m on 1 = 1 WHERE c1='" + sr[mssi1] + "' AND c2='" + sr[mssi2] + "' OR c2='" + sr[mssi1] + "' AND c1='" + sr[mssi2] + "'";

这是我的完整代码；

            for (int mssi1 = 0; mssi1 < sr.Length; mssi1++)
            {
                for (int mssi2 = mssi1 + 1; mssi 2< sr.Length; mssi2++)
                {
                    string myCommand = "SELECT c1, c2, ((CONVERT(DECIMAL(18,2), c3) / (SELECT MAX(c3) FROM myTableName)) * 100) AS normalizedc3 FROM myTableName WHERE c1='" + sr[mssi1] + "' AND c2='" + sr[mssi2] + "' OR c2='" + sr[mssi1] + "' AND c1='" + sr[mssi2] + "'";
                    SqlDataAdapter sadapter = new SqlDataAdapter(mc, conection);
                    DataTable dt = new DataTable();
                    sadapter.Fill(dt);
                    foreach (DataRow row in dt.Rows)
                    {
                        bool removeMyDup = dgv2.Rows.Cast<DataGridViewRow>().AsEnumerable().Any(x =>
                            Convert.ToString(x.Cells["Column1"].Value).Split(' ')[0] == row["c1"].ToString() &&
                            Convert.ToString(x.Cells["Column1"].Value).Split(' ')[1] == row["c2"].ToString() &&
                            Convert.ToInt32(x.Cells["Column2"].Value) == Convert.ToInt32(row["c3"])
                        );
                        if (!removeMyDup)
                            dgv2.Rows.Add(row["c1"].ToString() + " " + row["c2"].ToString(), row["c3"]);
                    }
                 }
            }

注 1：4500 不是我的最大值，它只是一个例子。我的最大值是 1400000，我的最小值是 10。但是每次我上传不同的链接系统。最大和最小数量总是变化...

注意 2：如果您希望我可以分享我的所有代码，请发表评论。

我应该如何在 sql 命令或 c# 代码中修复它，我正在等待您的帮助。谢谢你。

标签： c#sqlsql-servermathsqlcommand

解决方案

You could do something like add a BATCH_ID column to your table, and each set of data that you add gets a unique BATCH_ID. Then your query can become something like: 

string myCommand = 
"SELECT c1, c2, 
((CONVERT(DECIMAL(18,2), c3) / (SELECT MAX(c3) FROM myTableName b where b.BATCH_ID = A.BATCH_ID)) * 100) AS normalizedc3 
FROM myTableName a 
WHERE c1='" + mfa[msi] + "' AND c2='" + msa[mssi] + "' OR c1='" + msa[mssi] + "' AND c2='" + mfa[msi] + "'";

If you mean just the max of the resultset then you can use Gordon's answer:

string myCommand = 
"SELECT c1, c2, 
c3 * 100.0 / max(c3) over () as normalizedc3
FROM myTableName a 
WHERE c1='" + mfa[msi] + "' AND c2='" + msa[mssi] + "' OR c1='" + msa[mssi] + "' AND c2='" + mfa[msi] + "'";

推荐阅读