sql 6个指标采用熵值法进行权重分析的代码

熵值法是一种常用的多指标决策方法，可以对各指标的权重进行分配。下面是使用熵值法进行权重分析的 SQL 代码：

1. 创建一个表，包含各个指标的数据。

CREATE TABLE indicators (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  value1 INT,
  value2 INT,
  value3 INT,
  value4 INT,
  value5 INT,
  value6 INT
);

2. 插入数据。

INSERT INTO indicators VALUES
(1, 'Indicator 1', 10, 20, 30, 40, 50, 60),
(2, 'Indicator 2', 20, 30, 40, 50, 60, 70),
(3, 'Indicator 3', 30, 40, 50, 60, 70, 80),
(4, 'Indicator 4', 40, 50, 60, 70, 80, 90),
(5, 'Indicator 5', 50, 60, 70, 80, 90, 100),
(6, 'Indicator 6', 60, 70, 80, 90, 100, 110);

3. 计算每个指标的归一化矩阵。

SELECT 
  id, 
  name, 
  value1 / (SELECT SUM(value1) FROM indicators) AS norm_value1,
  value2 / (SELECT SUM(value2) FROM indicators) AS norm_value2,
  value3 / (SELECT SUM(value3) FROM indicators) AS norm_value3,
  value4 / (SELECT SUM(value4) FROM indicators) AS norm_value4,
  value5 / (SELECT SUM(value5) FROM indicators) AS norm_value5,
  value6 / (SELECT SUM(value6) FROM indicators) AS norm_value6
FROM indicators;

4. 计算每个指标的熵值。

SELECT 
  id,
  name,
  -norm_value1 * LOG(norm_value1) AS entropy1,
  -norm_value2 * LOG(norm_value2) AS entropy2,
  -norm_value3 * LOG(norm_value3) AS entropy3,
  -norm_value4 * LOG(norm_value4) AS entropy4,
  -norm_value5 * LOG(norm_value5) AS entropy5,
  -norm_value6 * LOG(norm_value6) AS entropy6
FROM (
  SELECT 
    id, 
    name, 
    value1 / (SELECT SUM(value1) FROM indicators) AS norm_value1,
    value2 / (SELECT SUM(value2) FROM indicators) AS norm_value2,
    value3 / (SELECT SUM(value3) FROM indicators) AS norm_value3,
    value4 / (SELECT SUM(value4) FROM indicators) AS norm_value4,
    value5 / (SELECT SUM(value5) FROM indicators) AS norm_value5,
    value6 / (SELECT SUM(value6) FROM indicators) AS norm_value6
  FROM indicators
) AS norm_matrix;

5. 计算每个指标的权重。

SELECT 
  id, 
  name, 
  (entropy1 + entropy2 + entropy3 + entropy4 + entropy5 + entropy6) / 
  (LOG(6) - (norm_value1 * LOG(norm_value1) + norm_value2 * LOG(norm_value2) + norm_value3 * LOG(norm_value3) + norm_value4 * LOG(norm_value4) + norm_value5 * LOG(norm_value5) + norm_value6 * LOG(norm_value6))) AS weight
FROM (
  SELECT 
    id,
    name,
    -norm_value1 * LOG(norm_value1) AS entropy1,
    -norm_value2 * LOG(norm_value2) AS entropy2,
    -norm_value3 * LOG(norm_value3) AS entropy3,
    -norm_value4 * LOG(norm_value4) AS entropy4,
    -norm_value5 * LOG(norm_value5) AS entropy5,
    -norm_value6 * LOG(norm_value6) AS entropy6,
    norm_value1,
    norm_value2,
    norm_value3,
    norm_value4,
    norm_value5,
    norm_value6
  FROM (
    SELECT 
      id, 
      name, 
      value1 / (SELECT SUM(value1) FROM indicators) AS norm_value1,
      value2 / (SELECT SUM(value2) FROM indicators) AS norm_value2,
      value3 / (SELECT SUM(value3) FROM indicators) AS norm_value3,
      value4 / (SELECT SUM(value4) FROM indicators) AS norm_value4,
      value5 / (SELECT SUM(value5) FROM indicators) AS norm_value5,
      value6 / (SELECT SUM(value6) FROM indicators) AS norm_value6
    FROM indicators
  ) AS norm_matrix
) AS entropy_matrix;

以上 SQL 代码可以计算出每个指标的权重，可以根据实际情况进行调整和应用。