Home

Клонирование строк на PostgreSQL

SQL   PostgreSQL   Добряки  

Или как правильно рассчитать среднеквадратичное отклонение.

Проблема с вычислением среднеквадратичного отклонения (дисперсии и прочего) по товару может решиться благодаря применению приема “клонирование строк”. Для наглядности приведем таблицу:

product price unit
яблоко 15 10
яблоко 14 1
яблоко 28 1

Если вычислим среднеквадратичное отклонение “в лоб” по price, то получим оценку среднеквадратичного отклонения без учета количества проданного товара или учтем только 1 яблоко по цене 15.
Приведем формулу среднеквадратичного отклонения для освежения памяти, на всякий случай:

среднеквадратичное отклонение

Создадим таблицу:

CREATE TABLE test.test_clone_row
(
  product TEXT
, price DOUBLE PRECISION
, unit integer
);
INSERT INTO test.test_clone_row(product, price, unit) VALUES ('яблоко', 15, 10);
INSERT INTO test.test_clone_row(product, price, unit) VALUES ('яблоко', 14, 1);
INSERT INTO test.test_clone_row(product, price, unit) VALUES ('яблоко', 28, 1);

Вычислим среднеквадратичное отклонение price “в лоб”:

SELECT
  test_clone_row.product,
  stddev(test_clone_row.price) as stddev_value
FROM
  test.test_clone_row
group by test_clone_row.product;

Результат работы скрипта. stddev_value = 7.81024967590665, что не верно для нашего случая.

Вот теперь сам скрипт с клонированием строк

Для этого будем использовать generate_series(), которая генерирует серию. В нашем случае идет генерация серии строк от 1 до количества товаров, которые находятся в столбце unit.

SELECT  product, stddev(price) as stddev_value
from
	(
	SELECT 	*,
		generate_series(1, unit)
	FROM
	  test.test_clone_row
	) as t
group by product;

Результат работы скрипта. stddev_value = 3.78993883375924, что является правильным результатом.

Это один из способов, как правильно вычислить среднеквадратичное отклонение и далеко не единственный. Можно и на PDI.

Ссылки на материалы:

  1. Пример с клонированием строк на PDI http://wiki.pentaho.com/display/EAI/Clone+row
  2. Создание определенного количества дублей в таблице http://www.sql.ru/forum/1094152/sozdanie-opredelennogo-kolichestva-dubley-v-tablice
  3. Generate Series or List of Numbers http://sqlbisam.blogspot.ru/2014/01/generate-series-or-list-of-numbers.html
  4. Среднеквадратическое отклонение http://ru.wikipedia.org/wiki/
Подробности
Опубликовано: 20 Май 2014
Просмотров: 4806
   
   
© BIWED.RU