sql - PostgreSQL:按计算值的总和排序

Question

我有一个表 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)[]
);

我需要根据流行度获取提示，流行度的定义是:
喜欢 - 不喜欢 - (size(abuse_history)*5)

无论排序顺序 (ASC/DESC) 如何，下面的查询都会给我相同的结果。

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

编辑

我插入了 3 条具有以下值的记录:
1) 1个喜欢，0个不喜欢，0个辱骂投诉
2) 0 喜欢，1 不喜欢，0 滥用投诉
3) 0 喜欢，0 不喜欢，0 辱骂...

无论排序顺序(ASC/DESC)如何，我都会得到以下顺序:{3, 1, 2}

谁能给我指出正确的方向？

Answer 1

考虑一下:

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