обычные математические вектора. double/float

Покажите лучше пример... а то так ничего не понятно. :(

id | x | y | z -------+--------------------+--------------------+-------------------- 80074 | -0.516964306237286 | 0.672032968938634 | 0.530207124373227 80075 | 0.542653026928013 | -0.609808736454484 | 0.577633964816519

на выходе после перемножения этих двух строк должно быть примерно такое (лень набирать все цифры): -0,516*0,542+0,672*-0,609 +0,53*0,578

то есть надо вытащить 3 числа из какой-то строки, потом пройтись по всей таблице, перемножая эти числа на соответствующие столбцы, потом сложить полученное для каждой строки

на самом деле, может, там и другая БД больше для этих целей подходит, но я не нашел (Кликхаус не умеет в редактирование и в реляционщину)

то есть надо вытащить 3 числа из какой-то строки, потом пройтись по всей таблице, перемножая эти числа на соответствующие столбцы, потом сложить полученное для каждой строки

Т.е. просто перемножить числа в каждом поле, для начала?

да, а потом их всех сложить

https://ru.wikipedia.org/wiki/%D0%A1%D0%BA%D0%B0%D0%BB%D1%8F%D1%80%D0%BD%D0%BE%D0%B5_%D0%BF%D1%80%D0%BE%D0%B8%D0%B7%D0%B2%D0%B5%D0%B4%D0%B5%D0%BD%D0%B8%D0%B5#%D0%90%D0%BB%D0%B3%D0%B5%D0%B1%D1%80%D0%B0%D0%B8%D1%87%D0%B5%D1%81%D0%BA%D0%BE%D0%B5_%D0%BE%D0%BF%D1%80%D0%B5%D0%B4%D0%B5%D0%BB%D0%B5%D0%BD%D0%B8%D0%B5

Вот так, если быть точным

Вот так, если быть точным

Я бы, наверное, вообще custom aggregate создал для начала, если Вам часто такое нужно. Что-то вроде: CREATE OR REPLACE FUNCTION product_numeric_state(numeric, numeric) RETURNS numeric LANGUAGE SQL IMMUTABLE as $f$ SELECT $1 * $2; $f$; CREATE AGGREGATE PROD(NUMERIC) ( SFUNC = product_numeric_state, STYPE = NUMERIC, INITCOND = 1 );

Ну а потом уже просто: SELECT prod(x)+prod(y)+prod(z) FROM table;

эм... а как это запускается в оболочке? я пробовал psql -U postgres -h 127.0.0.1 -p 5432 -d physics -f scalar.sql

ну и вот этот код в файле scalar.sql

ERROR: function "prod" already exists with same argument types, но ERROR: function prod(double precision) does not exist

ERROR: function "prod" already exists with same argument types, но ERROR: function prod(double precision) does not exist

> ERROR: function "prod" already exists with same argument types Это значит, что Вы это уже запускали. > но ERROR: function prod(double precision) does not exist А это значит, что у меня в примере numeric, а если Вас устраивает double precision, замените в определении функции и агрегата соответственно (ну или просто создайте ещё одну функцию и агрегат, будет у Вас и для float, и для numeric).

Название агрегата, кстати, может быть точно такое же (prod), PostgreSQL по типу агрумента определит, какой вызывать.

А почему на выходе одно число вместо столбца?

и какие это строки в примере перемножаются? первая и какая?

а. или дальше предлагается дальше еще одну функцию написать, которая бы проходила по всем строкам?

А почему на выходе одно число вместо столбца?

А потому что я подумал, что Вам это и нужно (по ссылке не ходил). ;) А сколько строк должно быть в результате?

столько же, сколько изначально было

а. или дальше предлагается дальше еще одну функцию написать, которая бы проходила по всем строкам?

Забудьте Вы в SQL о циклах, тут с отношениями (множествами) работают, в основном.

а. я смотрю вы просто все числа в каждом столбце перемножили и потом сложили?

ну ок. тогда на языке множеств без циклов формулировка: есть множество точек, из него выбрали одну конкретную, и создали второе множество пар точек. для каждой пары точек (каждая из которых представляет собой вектор) вывести скалярное произведение

а. я смотрю вы просто все числа в каждом столбце перемножили и потом сложили?

Ну да. Подождите... зря Вы пример только с парой привели. :( > в строках находятся вектора, а на выходе хочется получить скалярное произведение одного из векторов на все остальные Т.е. в каждой строке результаты должно быть произведение... чего на что?

ну ок. тогда на языке множеств без циклов формулировка: есть множество точек, из него выбрали одну конкретную, и создали второе множество пар точек. для каждой пары точек (каждая из которых представляет собой вектор) вывести скалярное произведение

Пусть в множестве 5 точек. Получается 25 пар, правильно?

нет.

5

Если я выбираю из 1 множества одну точку, то остается 4 - значит 4 пары точек мы знаем. пятая - когда точка сама с собой перемножается

Я имел в виду "для всех точек". Т.е. Вы хотите выбирать этот "один из векторов" в запросе?

но вообще всего пар - 25, да

да

Тьфу. Вообще не о том подумал. ;) Это что-то вроде: SELECT point1.id, point2.id, (point1.x * point2.x + point1.y * point2.y + point1.z * point2.z) AS prod FROM point AS point1 CROSS JOIN point AS point2 WHERE point1.id = нужная_точка; CROSS JOIN — декартово произведение.

да, ближе к правде уже

счас проверю

а prod что такое?

а prod что такое?

Alias (просто название для поля результата).

Можете любое использовать, естественно. Аналогично "AS point1", "AS point2" — тоже alias-ы, но для таблиц.

CREATE OR REPLACE FUNCTION product_vectors(point_id numeric) RETURNS SETOF float LANGUAGE SQL IMMUTABLE as $f$ SELECT point1.id, point2.id, (point1.x * point2.x + point1.y * point2.y + point1.z * point2.z) AS prod FROM points_table AS point1 CROSS JOIN points AS point2 WHERE point1.id = point_id; $f$; Вот на такое счас выводит psql:scalar.sql:11: ERROR: return type mismatch in function declared to return double precision DETAIL: Final statement must return exactly one column. CONTEXT: SQL function "product_vectors"

CREATE OR REPLACE FUNCTION product_vectors(point_id numeric) RETURNS SETOF float LANGUAGE SQL IMMUTABLE as $f$ SELECT point1.id, point2.id, (point1.x * point2.x + point1.y * point2.y + point1.z * point2.z) AS prod FROM points_table AS point1 CROSS JOIN points AS point2 WHERE point1.id = point_id; $f$; Вот на такое счас выводит psql:scalar.sql:11: ERROR: return type mismatch in function declared to return double precision DETAIL: Final statement must return exactly one column. CONTEXT: SQL function "product_vectors"

Ну правильно, Вы обявили её как "RETURNS SETOF float", что значит "must return exactly one column." (типа float). Кстати, эта функция не IMMUTABLE, а STABLE. Не обманывайте планировщик (а то результаты могут Вас неприятно поразить). ;)

ну так от нее и ожидается, что вернется одна колонка чисел

ну так от нее и ожидается, что вернется одна колонка чисел

Тогда уберите "point1.id, point2.id," из SELECT. Да, и ещё, у Вас входной параметр point_id типа numeric... точно у Вас в таблице id не (big)integer?

помогло

а вызывать ее как? product_vectors(80069); ERROR: syntax error at or near "product_vectors" LINE 1: product_vectors(80069);

SELECT * FROM product_vectors(80069);

О! работает! Как надо! спасибо огромное

О! работает! Как надо! спасибо огромное

Пожалуйста.

А еще такой вопрос: постгрес как-то оптимизирует функции? есть ли разница в скорости, если я сначала создам через .sql-файл эту функцию, а потом из ORM буду ее вызывать, и если я сразу буду из ORM дергать этот SELECT, который внутри тела функции?

Ребят, кто в курсе, умеет ли pgbouncer авторизовывать юзеров в самом PG? т.е. чтобы не держать файлик с юзерами еще и на нем

Оставлю для истории. pgbouncer умеет авторизовывать юзеров в самой PG, без всяких файликов и проч. Просто запросом SELECT usename, passwd FROM pg_shadow WHERE usename=$1, но для этого ему нужно будет выделить некого админского юзера, под которым pgbouncer будет хоть в базу для авторизации. @Aytuar @uspen2018

@KladovArtem будет жить. Поприветствуем!

Максим будет жить. Поприветствуем!

Оставлю для истории. pgbouncer умеет авторизовывать юзеров в самой PG, без всяких файликов и проч. Просто запросом SELECT usename, passwd FROM pg_shadow WHERE usename=$1, но для этого ему нужно будет выделить некого админского юзера, под которым pgbouncer будет хоть в базу для авторизации. @Aytuar @uspen2018

паранойя моде вкл. А ещё можно соорудить ф-ию с SECURITY DEFINER, которая будет дергать pg_shadow от суперпользователя и возвращать то, что нужно. Дать гранты на выполнение этой ф-ии рядовому пользователю, которому всячески ограничить запуск ф-ии, например, хостом и приложением, ну и вот это всё. И уже из пгбаунсера дёргать эту ф-ию, от имени разрешённого пользователя с разрешённого узла. паранойя моде суспенд ин мемору.

А еще такой вопрос: постгрес как-то оптимизирует функции? есть ли разница в скорости, если я сначала создам через .sql-файл эту функцию, а потом из ORM буду ее вызывать, и если я сразу буду из ORM дергать этот SELECT, который внутри тела функции?

Это зависит от функции. https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions

паранойя моде вкл. А ещё можно соорудить ф-ию с SECURITY DEFINER, которая будет дергать pg_shadow от суперпользователя и возвращать то, что нужно. Дать гранты на выполнение этой ф-ии рядовому пользователю, которому всячески ограничить запуск ф-ии, например, хостом и приложением, ну и вот это всё. И уже из пгбаунсера дёргать эту ф-ию, от имени разрешённого пользователя с разрешённого узла. паранойя моде суспенд ин мемору.

А вот, кстати, вопрос (ко всем): Как Вы пишете SECURITY DEFINER (суперпользовательские) функции (которые могут использовать обычые users) так, чтобы это было собственно secure? Есть какой-то шаблон?

Уважаемые знатоки, UPDATE t SET v=v создаёт новый тупл, внимание вопрос: нахуя так? И будет ли оно ломать HOT если поле проиндексировано?

А еще такой вопрос: постгрес как-то оптимизирует функции? есть ли разница в скорости, если я сначала создам через .sql-файл эту функцию, а потом из ORM буду ее вызывать, и если я сразу буду из ORM дергать этот SELECT, который внутри тела функции?

Запросы внутри функций препарятся, что само по себе может довать выигрыш до двух раз, по сравнению с непрепарнутым запросом.

Уважаемые знатоки, UPDATE t SET v=v создаёт новый тупл, внимание вопрос: нахуя так? И будет ли оно ломать HOT если поле проиндексировано?

1. Чтобы предыдущее значение было достуно тем кому это еще необходимо видеть 2. Если коротко то нет. А вообще it depends

1. Чтобы предыдущее значение было достуно тем кому это еще необходимо видеть 2. Если коротко то нет. А вообще it depends

Так предыдущее значение то же, что и новое

Это да, но вдруг нет? Строго говоря это транзакция а значит есть данне до коммита и после. Возможно да, тут есть что оптимизировать

Уважаемые знатоки, UPDATE t SET v=v создаёт новый тупл, внимание вопрос: нахуя так? И будет ли оно ломать HOT если поле проиндексировано?

А зачем так писать? Или это очередное порождение ORM или какого-то другого генератора SQL? По любому - видимо такой сценрий не был признан столь уж актуальным, чтобы специально оптимизировать такой случай. Посгрес вообще не очень любить "тупые" запросы и не особо пытается изничтожать бесполезный код титпа x=x . HOT ломаться не будет, потому как там явно проверяется, что поле изменилось.

Запросы внутри функций препарятся, что само по себе может довать выигрыш до двух раз, по сравнению с непрепарнутым запросом.

Или проигрыш в N раз, если планировщик ошибётся и выберет generic plan там, где не надо. :( А уж если это SQL language function (non-inlinable), то это куда проще происходит...

Спасибо за разъяснения. назначенное лечение "не делайте так" уже успешно проигнорировано :)

Спасибо за разъяснения. назначенное лечение "не делайте так" уже успешно проигнорировано :)

Для (альтернативно) одарённых, которые не хотят лечиться, есть https://www.postgresql.org/docs/current/static/functions-trigger.html

Спасибо за разъяснения. назначенное лечение "не делайте так" уже успешно проигнорировано :)

> Посгрес вообще не очень любить "тупые" запросы и не особо пытается изничтожать бесполезный код титпа x=x . Вот это правильный общий принцип, ориентируйтесь на него.

Уважаемые, приветствую. Подскажите пожалуйста оптимальное решение для удаления из таблицы строчек в которых значение поля n одинаковое ?

Уважаемые, приветствую. Подскажите пожалуйста оптимальное решение для удаления из таблицы строчек в которых значение поля n одинаковое ?

Имеется ввиду удалить дубликаты, но при этом оставить одно значение?

Имеется ввиду удалить дубликаты, но при этом оставить одно значение?

именно !

именно !

Таблица большая? (Ну или по другому, страшно ли ее лочить?)

Таблица большая? (Ну или по другому, страшно ли ее лочить?)

ну предпологается что после осуществения данной операции не более 100к строк будет всегда

ну предпологается что после осуществения данной операции не более 100к строк будет всегда

В инете много решений Посмотрите тут http://www.postgresqltutorial.com/how-to-delete-duplicate-rows-in-postgresql/

подскажите пожалуйста, можно ли из датывремени извлечь только время для последующего сравнения?

вроде как extract может отдельно часы, одельно минуты

но мне надо отобрать все записи для всех datetime которые после 21:00 до 9:30 утра

подскажите пожалуйста, можно ли из датывремени извлечь только время для последующего сравнения?

Привести к типу Time попробуй

подскажите пожалуйста, можно ли из датывремени извлечь только время для последующего сравнения?

::time?

точно, спасибо :)

В инете много решений Посмотрите тут http://www.postgresqltutorial.com/how-to-delete-duplicate-rows-in-postgresql/

Бладгодарю ! ??

Люди помогите понять как в одном запросе 3 суб запроса прописать

SELECT qr.dates, sum(qr.status_0) AS status0, sum(qr.status_1) AS status1, sum(qr.status_2) AS status2, sum(qr.status_3) AS status3, sum(qr.status_4) AS status4, gw.created, sum(gw.activeProfiles) AS activeProfiles, sum(gw.inactiveProfiles) AS inactiveProfiles, gw.date, sum(gw.cardNotSubmit) AS cardNotSubmit, sum(gw.cardActive) AS cardActive, sum(gw.cardRemoved) AS cardRemoved, with qr as(SELECT date_trunc('year' :: text, tr.created) AS dates, CASE WHEN (tr.state = 0) THEN count(*) ELSE (0) :: bigint END AS status_0, CASE WHEN (tr.state = 1) THEN count(*) ELSE (0) :: bigint END AS status_1, CASE WHEN (tr.state = 2) THEN count(*) ELSE (0) :: bigint END AS status_2, CASE WHEN (tr.state = 3) THEN count(*) ELSE (0) :: bigint END AS status_3, CASE WHEN (tr.state = 4) THEN count(*) ELSE (0) :: bigint END AS status_4 FROM (gw_transactions tr) GROUP BY (date_trunc('year' :: text, tr.created)), tr.state), gw as(SELECT date_trunc('year' :: text, pr.created) AS created, CASE WHEN (pr.state = 1) THEN count(*) ELSE (0) :: bigint END AS activeProfiles, CASE WHEN (pr.state = 0) THEN count(*) ELSE (0) :: bigint END AS inactiveProfiles FROM gw_profiles pr GROUP BY (date_trunc('year' :: text, pr.created)), pr.state), lc as(SELECT date_trunc('year' :: text, c2.created) AS date, CASE WHEN (c2.state = 0) THEN count(*) ELSE (0) :: bigint END AS cardNotSubmit, CASE WHEN (c2.state = 1) THEN count(*) ELSE (0) :: bigint END AS cardActive, CASE WHEN (c2.state = 3) THEN count(*) ELSE (0) :: bigint END AS cardRemoved FROM gw_profiles pr GROUP BY (date_trunc('year' :: text, c2.created)), c2.state), GROUP BY qr.dates ORDER BY qr.dates;

пытаюсь сделать таким способом

но увы

если сделать наоборот , сначало джоинить и потом выводить то выходят не корректные данные

пытаюсь сделать таким способом

Каким способом? Тут ничего не понятно, описали бы Вы подробнее...

Каким способом? Тут ничего не понятно, описали бы Вы подробнее...

ну в общем есть таблица транзакций самое главное что нужно знать там есть дата "created " и статусы (0,1,2,3,4) и что бы вытащить количество статусов за время я пишу так

ну и соответственно результат

хочу дописать этот запрос что бы в таком же виде получить статусы из таблицы пользователей

и карт пользователей

что бы все было в одной запросе

что бы все было в одной запросе

Ну так дописывайте. Только не изобретайте свой синтаксис при этом, WITH пишется в начале (под)запроса, а не посреди него. ;)

Подскажите, а это нормальная ситуация, когда pg_basebackup не скопировал файл pg_control? Бэкап снимал со стендбая.

не получается