PostgreSQL: порядок по сумме вычисленных значений


У меня есть таблица tips, которая определяется следующим образом:

CREATE TABLE tips
(
  tip_id bigserial NOT NULL,
  tip text NOT NULL,
  author text NOT NULL,
  post_date bigint NOT NULL,
  likers character varying(16)[],
  dislikers character varying(16)[],
  likes integer NOT NULL,
  dislikes integer NOT NULL,
  abuse_history character varying(16)[]
);

Мне нужно получить советы, основанные на популярности, с определением популярности:
нравится-не нравится - (размер (abuse_history)*5)

Запрос ниже дает мне те же результаты независимо от порядка сортировки (ASC / DESC).

select * from tips order by (likes - dislikes - (array_length(abuse_history,1) * 5)) ASC limit 2147483647 offset 0

EDIT

Я вставил 3 записи, которые имеют следующие значения:
1) 1 нравится, 0 не нравится, 0 жалоб на злоупотребление
2) 0 лайков, 1 дислайк, 0 злоупотреблений жалобы
3) 0 лайков, 0 дислайков, 0 злоупотреблений...

Независимо от порядка сортировки (ASC / DESC), я получаю следующий порядок: {3, 1, 2}

Не мог бы кто-нибудь указать мне правильное направление?
3   4   2011-09-30 22:34:34

3 ответа:

Рассмотрим это:

SELECT array_length('{}'::character varying(16)[],1);

Выводится NULL для пустого массива. Кроме того, ваш abuse_history может быть NULL самим собой. Поэтому вам нужно что-то вроде этого:

SELECT *
  FROM tips
 ORDER BY (likes - dislikes - COALESCE(array_length(abuse_history,1) * 5, 0)) DESC;

Редактировать после обратной связи:

Работает в PostgreSQL 9.0 , как показано в этой демонстрации:

CREATE TABLE tips
( tip_id bigserial NOT NULL,
  tip text,
  author text,
  post_date bigint,
  likers character varying(16)[],
  dislikers character varying(16)[],
  likes integer,
  dislikes integer,
  abuse_history character varying(16)[]
);


INSERT INTO tips (likes, dislikes, abuse_history)
VALUES(1,0, '{}')
,(1,0, '{}')
,(0,1, '{}')
,(0,0, '{}')
,(1,0, '{stinks!,reeks!,complains_a_lot}');


SELECT tip_id
        , likes
        , dislikes
        , (likes - dislikes - COALESCE(array_upper(abuse_history,1) * 5,0)) as pop
        , (likes - dislikes - array_upper(abuse_history,1) * 5) as fail_pop
  FROM tips
 ORDER BY (likes - dislikes - COALESCE(array_upper(abuse_history,1) * 5,0)) DESC;

Вывод:

 tip_id | likes | dislikes | pop | fail_pop
--------+-------+----------+-----+----------
      1 |     1 |        0 |   1 |
      2 |     1 |        0 |   1 |
      4 |     0 |        0 |   0 |
      3 |     0 |        1 |  -1 |
      5 |     1 |        0 | -14 |      -14

Чтобы отладить это, поместите то же самое выражение предложения ORDER BY в часть SELECT. Затем изучите результаты-действительно ли они соответствуют вашим ожиданиям?

select *, (likes - dislikes - (array_length(abuse_history,1) * 5)) 
from tips 
order by (likes - dislikes - (array_length(abuse_history,1) * 5)) ASC 
limit 2147483647 offset 0

О, И кстати, снимите эту глупую LIMIT и OFFSET вещь.

Попробуйте это:

select t2.*
from ( select t1.*,
       (likes - dislikes - (array_length(abuse_history,1) * 5)) as popularity
       from tips t1) t2
order by popularity;

Edit: вышеизложенное обычно работает (по крайней мере, с Oracle). Возможно, приведенная ниже вариация, которая использует предложение With, позволит избежать ошибки.

WITH popularity_query as
     ( select t.*,
              (likes - dislikes - (array_length(abuse_history, 1) * 5)) as popularity
       from tips t)
select * from popularity_query order by popularity