database-design - 计算两列之间的百分比变化

Question

我有一个包含 2 列的表格，其中列出了每次发布的产品中特定项目的数量。我需要计算两列之间的百分比增加并将其附加到表中的新列，但是我没有找到与此相关的任何文档？我正在使用 Postgres 9.0，作为 QC 流程的一部分，我需要检查两列之间的百分比增加，以确保版本之间没有丢失/错误的数据。

这是表定义:

oid oid[] NOT NULL,
"State" character varying(2)[] NOT NULL,
release_1121 numeric NOT NULL,
release_1122 numeric NOT NULL,
CONSTRAINT oid PRIMARY KEY (oid)

我想添加百分比增长列并用正确的百分比填充它。

Answer 1

我认为这是您真正需要的:

表格应该看起来像这样:

CREATE TABLE release (
release_id integer PRIMARY KEY,    -- pk is NOT NULL automatically
-- state varchar(2)[] NOT NULL,    -- ??
amount numeric NOT NULL
);

测试数据:

INSERT INTO release VALUES (release_id, amount)
  (1121, 25)
, (1122, 30)
, (1123, 90)
, (1124, 10);

查询:

WITH x AS (
    SELECT *
         , lag(amount) OVER (ORDER BY release_id) as last_amount
    FROM   release
    )
SELECT release_id, amount
     , (amount - last_amount) AS abs_change
     , round((100 * (amount - last_amount)) / last_amount, 2) AS percent_change
FROM   x
ORDER  BY release_id;

CTE (WITH clause)和窗口函数 lag()需要 PostgreSQL 8.4 或更高版本。
结果:

release_id | amount | abs_change | percent_change
-----------+--------+------------+---------------
1121       | 25     | <NULL>     | <NULL>
1122       | 30     | 5          |  20.00
1123       | 90     | 60         | 200.00
1124       | 10     | -80        | -88.89

• 不要使用oid 作为主键!那是不好的做法。在 PostgreSQL 9.0 中 WITHOUT OIDS是默认值。 Read more here.

• 不要使用 mixed case identifiers如果可以避免的话，比如 "State"。见:

  • Are PostgreSQL column names case-sensitive?