我可以在使用数据库后激活 PITR 吗？

Question

Jean Louis

Asked: 2023-02-05 19:23:32 +0800 CST2023-02-05 19:23:32 +0800 CST 2023-02-05 19:23:32 +0800 CST

如何在计算列上使用窗口函数？

772

表定义：

                                                      Table "public.transactions"
┌────────────────────────────┬──────────────────────────┬───────────┬──────────┬───────────────────────────────────────────────────────┐
│           Column           │           Type           │ Collation │ Nullable │                        Default                        │
├────────────────────────────┼──────────────────────────┼───────────┼──────────┼───────────────────────────────────────────────────────┤
│ transactions_id            │ integer                  │           │ not null │ nextval('transactions_transactions_id_seq'::regclass) │
│ transactions_datecreated   │ timestamp with time zone │           │ not null │ CURRENT_TIMESTAMP                                     │
│ transactions_datemodified  │ timestamp with time zone │           │          │                                                       │
│ transactions_usercreated   │ text                     │           │ not null │ CURRENT_USER                                          │
│ transactions_usermodified  │ text                     │           │ not null │ CURRENT_USER                                          │
│ transactions_date          │ timestamp with time zone │           │ not null │ CURRENT_TIMESTAMP                                     │
│ transactions_name          │ text                     │           │ not null │                                                       │
│ transactions_description   │ text                     │           │          │                                                       │
│ transactions_amount        │ numeric                  │           │ not null │                                                       │
│ transactions_currencies    │ integer                  │           │ not null │                                                       │
│ transactions_fromaccount   │ integer                  │           │ not null │                                                       │
│ transactions_toaccount     │ integer                  │           │ not null │                                                       │
│ transactions_uuid          │ uuid                     │           │ not null │ gen_random_uuid()                                     │
│ transactions_hyobjects     │ integer                  │           │          │                                                       │
│ transactions_receiver      │ integer                  │           │          │                                                       │
│ transactions_reporter      │ integer                  │           │          │                                                       │
│ transactions_payer         │ integer                  │           │          │                                                       │
│ transactions_receipt       │ integer                  │           │          │                                                       │
│ transactions_locations     │ integer                  │           │          │                                                       │
│ transactions_exchangerates │ integer                  │           │          │                                                       │
└────────────────────────────┴──────────────────────────┴───────────┴──────────┴───────────────────────────────────────────────────────┘
Indexes:
    "transactions_pkey" PRIMARY KEY, btree (transactions_id)
    "transactions_transactions_uuid_key" UNIQUE CONSTRAINT, btree (transactions_uuid)
Foreign-key constraints:
    "transactions_transactions_currencies_fkey" FOREIGN KEY (transactions_currencies) REFERENCES currencies(currencies_id)
    "transactions_transactions_exchangerates_fkey" FOREIGN KEY (transactions_exchangerates) REFERENCES exchangerates(exchangerates_id)
    "transactions_transactions_fromaccount_fkey" FOREIGN KEY (transactions_fromaccount) REFERENCES accounts(accounts_id)
    "transactions_transactions_hyobjects_fkey" FOREIGN KEY (transactions_hyobjects) REFERENCES hyobjects(hyobjects_id)
    "transactions_transactions_locations_fkey" FOREIGN KEY (transactions_locations) REFERENCES locations(locations_id)
    "transactions_transactions_payer_fkey" FOREIGN KEY (transactions_payer) REFERENCES people(people_id)
    "transactions_transactions_receipt_fkey" FOREIGN KEY (transactions_receipt) REFERENCES hyobjects(hyobjects_id)
    "transactions_transactions_receiver_fkey" FOREIGN KEY (transactions_receiver) REFERENCES people(people_id)
    "transactions_transactions_reporter_fkey" FOREIGN KEY (transactions_reporter) REFERENCES people(people_id)
    "transactions_transactions_toaccount_fkey" FOREIGN KEY (transactions_toaccount) REFERENCES accounts(accounts_id)
Triggers:
    account_update_balance AFTER INSERT OR UPDATE ON transactions FOR EACH STATEMENT EXECUTE FUNCTION account_update_balance()
    insert_username_transactions BEFORE INSERT OR UPDATE ON transactions FOR EACH ROW EXECUTE FUNCTION insert_username('transactions_usermodified')
    transactions_moddatetime BEFORE UPDATE ON transactions FOR EACH ROW EXECUTE FUNCTION moddatetime('transactions_datemodified')

    transactions_id integer NOT NULL,
    transactions_datecreated timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    transactions_datemodified timestamp with time zone,
    transactions_usercreated text DEFAULT CURRENT_USER NOT NULL,
    transactions_usermodified text DEFAULT CURRENT_USER NOT NULL,
    transactions_date timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    transactions_name text NOT NULL,
    transactions_description text,
    transactions_amount numeric NOT NULL,
    transactions_currencies integer NOT NULL,
    transactions_fromaccount integer NOT NULL,
    transactions_toaccount integer NOT NULL,
    transactions_uuid uuid DEFAULT gen_random_uuid() NOT NULL,
    transactions_hyobjects integer,
    transactions_receiver integer,
    transactions_reporter integer,
    transactions_payer integer,
    transactions_receipt integer,
    transactions_locations integer,
    transactions_exchangerates integer
);

在上面的表格定义中，我只有一个条目，其中的金额表示从一个帐户转移到另一个帐户的金额。

我仍然想在一天结束时使用窗口函数来获取余额。

这是我的尝试，但还不起作用的 SQL：

SELECT transactions_id AS "ID", 
       transactions_date AS "Date", 
       CASE 
       WHEN transactions_fromaccount = accounts_id
     THEN - transactions_amount
       WHEN transactions_toaccount = accounts_id
     THEN transactions_amount
       END AS "Amount",
       account_name(transactions_fromaccount) AS "From account",
       account_name(transactions_toaccount) AS "To account",
       CASE WHEN transactions_fromaccount = accounts_id
     THEN '' 
       ELSE transactions_amount::numeric::text END AS "Debit", 
       CASE WHEN transactions_toaccount = accounts_id
        THEN ''
       ELSE transactions_amount::numeric::text END AS "Credit",
       sum("Amount")
     OVER (PARTITITON BY transactions_id 
           ORDER BY transactions_date)
  FROM accounts, transactions 
 WHERE (transactions_fromaccount = accounts_id
    OR transactions_toaccount = accounts_id) 
   AND accounts_id = 65
 ORDER BY transactions_date;

使用 PostgreSQL 14.6

我正在尝试生成列"Amount"然后使用sum("Amount")，但是，我做错了什么。我收到此错误：

ERROR:  column "Amount" does not exist
LINE 17:        sum("Amount")

有没有办法解决它得到sum("Amount")？

2 个回答

Voted

Andriy M · Answer 1 · 2023-02-06T01:39:30+08:00

当您的 SELECT 中有计算列时，如下所示：

SELECT
  ...
  some_expression AS "alias",
  ...
FROM
  ...

并且您想在同一个 SELECT 中重用它的结果，最通用的解决方案是将查询重写为派生表，以便您可以"alias"在外层引用：

SELECT
  ...
  "alias",
  ...
  some_expression_involving_"alias" AS "another_alias"
FROM
  ( /* the original query */
    SELECT
      ...
      some_expression AS "alias",
      ...
    FROM
      ...
  ) AS derived /* assign an appropriate alias here */

现在“别名”成为一个有效的引用，允许您按原样返回列并在表达式中重新使用它以生成基于它的其他列（在您的情况下，聚合结果）。

一个类似的选择是将原来的重写为 CTE（公用表表达式）：

WITH
  my_cte AS /* change "my_cte" to whatever name seems more appropriate */
  ( /* the original query */
    SELECT
      ...
      some_expression AS "alias",
      ...
    FROM
      ...
  )
SELECT
  ...
  "alias",
  ...
  some_expression_involving_"alias" AS "another_alias"
FROM
  my_cte

这两种方法可以被认为是等效的，唯一的区别（也是一个重要的区别）是 CTE 在 PostgreSQL 中是可实现的，这意味着选择一种方式而不是另一种方式可能会对性能产生影响。

还有第三种方法，它不如前两种方法普遍，但适用于您的情况。PostgreSQL 支持LATERAL连接。"alias"您可以在一个单独的FROM-less中定义SELECT，然后您可以LATERAL-join 到您的其他表。更具体地说，它看起来像这样：

SELECT
  ...
  x."Amount",  /* just the reference here */
  ...
  SUM(x."Amount") OVER (PARTITITON BY transactions_id 
                        ORDER BY transactions_date)
FROM
  accounts,
  transactions,
  LATERAL
  ( /* the definition of "Amount" is moved here from the main SELECT */
    SELECT
      CASE 
        WHEN transactions_fromaccount = accounts_id
        THEN -transactions_amount
        WHEN transactions_toaccount = accounts_id
        THEN transactions_amount
      END AS "Amount"
  ) AS x ("Amount")
WHERE
  ...
ORDER BY
  ...
;

这些是我所知道的唯一不涉及在数据库中创建新对象的选项。如果创建新对象不是问题，那么您还可以考虑将原始查询定义为视图的可能性：

CREATE VIEW my_view  /* again, rename as appropriate */
AS
  /* your original query */
SELECT
  ...

然后你的主要查询将从视图中选择，使你能够"Amount"以任何必要的方式引用已经定义的列：

SELECT
  ...
  "Amount",
  ...
  SUM("Amount") OVER (PARTITITON BY transactions_id 
                      ORDER BY transactions_date)  
FROM
  my_view

此方法与前两个选项非常相似，不同之处在于您的原始查询会保留在数据库中。其他查询现在也可以从该视图读取，允许您在更大范围内重用您的代码，而不仅仅是单个列定义（当然，这是视图的主要目的之一）。

Jean Louis · Answer 2 · 2023-02-23T13:17:45+08:00

使用以下 SQL，我设法获得了每个特定帐户的借方和贷方的适当运行余额。感谢贡献者。

WITH DATA AS ( SELECT transactions_id, transactions_date::date as "Date", transactions_name, account_name(transactions_fromaccount) AS "From account", account_name(transactions_toaccount) AS "To account",
             CASE WHEN transactions_fromaccount = 65 THEN '' 
             ELSE transactions_amount::integer::text END AS "Debit", 
             CASE WHEN transactions_toaccount = 65 THEN ''
             ELSE transactions_amount::integer::text END AS "Credit",
             CASE WHEN transactions_fromaccount = 65 THEN - transactions_amount
             WHEN transactions_toaccount = 65 THEN transactions_amount
             END AS amount
         FROM transactions, accounts
             WHERE (accounts_id = transactions_fromaccount
              OR accounts_id = transactions_toaccount) 
            AND transactions_toaccount= 65 or transactions_fromaccount = 65
            GROUP BY transactions_id )
        SELECT transactions_id, "Date", transactions_name, "From account", "To account", "Debit", "Credit", 
        cast( cast (sum(amount) OVER (ORDER BY "Date", transactions_id  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as integer) as text) AS "Balance"
        FROM DATA
        WHERE "Date" <= current_date

如何在计算列上使用窗口函数？

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

如何在计算列上使用窗口函数？

2 个回答

相关问题