sql-weighted-sum.sql

-- PostgreSQL schema

BEGIN;

DROP TABLE IF EXISTS wsm;

CREATE TABLE wsm
(
  payload   jsonb
);

INSERT INTO wsm
(
  payload
)
SELECT
  '{
    "alternatives": [
        {
            "name": "VW Gol"
        },
        {
            "name": "Renault Clio"
        },
        {
            "name": "Toyota Corolla"
        },
        {
            "name": "Audi A1"
        }
    ],
    "criterias": [
        {
            "name": "lower price",
            "weight": 40,
            "values": [
                5,
                5,
                3,
                2
            ]
        },
        {
            "name": "passengers",
            "weight": 25,
            "values": [
                3,
                3,
                5,
                2
            ]
        },
        {
            "name": "hp",
            "weight": 35,
            "values": [
                3,
                2,
                4,
                4
            ]
        }
    ]
}'
 FROM generate_series(1,1) AS t (x);

WITH criterias AS (
    SELECT
      v.name AS criteria,
      round(v.weight/100,2) AS weight,
      v.VALUES
    FROM
      wsm w,
    LATERAL
      jsonb_each(w.payload) AS p,
    LATERAL
      jsonb_to_recordset(p.value) AS v(name text, weight numeric, VALUES jsonb)
    WHERE
      p.key='criterias'),
alternatives AS (
    SELECT
      r.name AS "alternative",
      row_number() OVER () AS "index",
      r.grouping
    FROM
      wsm w,
    LATERAL
      jsonb_each(w.payload) AS p,
    LATERAL
      jsonb_to_recordset(p.value) AS r(name text, GROUPING text)
    WHERE
      p.key='alternatives'
),
matrix AS (
    SELECT
      r.alternative,
      c.criteria,
      c.weight,
      coalesce((VALUES::jsonb->>(INDEX-1)::int)::int, 0) AS value,
      c.weight * coalesce((VALUES::jsonb->>(INDEX-1)::int)::int, 0) relative_weight,
      sum(c.weight * coalesce((VALUES::jsonb->>(INDEX-1)::int)::int, 0))
        OVER ( PARTITION BY
                r.alternative
        ) AS score
    FROM
      alternatives AS r
    CROSS JOIN
      criterias AS c
)
SELECT
  dense_rank() over(ORDER BY score DESC) AS "ranking",
  m.alternative,
  m.criteria,
  m.weight,
  m.value,
  m.relative_weight,
  m.score
FROM
  matrix AS m
ORDER BY
  ranking, alternative, criteria;

ROLLBACK;