6个字段采用熵值法进行分析 得到总分 的sql案例

以下是一个使用熵值法进行字段分析并计算总分的 SQL 示例：

WITH entropy_values AS (
  SELECT 
    field_1, 
    field_2, 
    field_3, 
    field_4, 
    field_5, 
    field_6,
    (-1) * (
      (field_1 / ln(field_1)) + 
      (field_2 / ln(field_2)) + 
      (field_3 / ln(field_3)) + 
      (field_4 / ln(field_4)) + 
      (field_5 / ln(field_5)) + 
      (field_6 / ln(field_6))
    ) AS entropy_value
  FROM your_table
),
normalized_values AS (
  SELECT 
    field_1 / SUM(field_1) OVER() AS norm_field_1,
    field_2 / SUM(field_2) OVER() AS norm_field_2,
    field_3 / SUM(field_3) OVER() AS norm_field_3,
    field_4 / SUM(field_4) OVER() AS norm_field_4,
    field_5 / SUM(field_5) OVER() AS norm_field_5,
    field_6 / SUM(field_6) OVER() AS norm_field_6
  FROM your_table
)
SELECT 
  *, 
  (norm_field_1 * entropy_values.entropy_value) + 
  (norm_field_2 * entropy_values.entropy_value) +
  (norm_field_3 * entropy_values.entropy_value) +
  (norm_field_4 * entropy_values.entropy_value) +
  (norm_field_5 * entropy_values.entropy_value) +
  (norm_field_6 * entropy_values.entropy_value) AS total_score
FROM normalized_values
JOIN entropy_values ON 
  normalized_values.field_1 = entropy_values.field_1 AND 
  normalized_values.field_2 = entropy_values.field_2 AND 
  normalized_values.field_3 = entropy_values.field_3 AND 
  normalized_values.field_4 = entropy_values.field_4 AND 
  normalized_values.field_5 = entropy_values.field_5 AND 
  normalized_values.field_6 = entropy_values.field_6;

在这个 SQL 示例中，我们首先计算了每个字段的熵值，然后通过将每个字段的值除以该字段的总和来标准化它们。最后，我们将每个标准化字段乘以其对应的熵值，并将这些乘积相加以获得总分。