Jean Louis's questions

表定义：

                                                      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")？

我正在使用 PostgreSQL 14.6，我的目标是找出是否有更简单、更快的方法来实现相同的结果，但仍然使用类似的 UNION 查询。

这是我要优化的 SQL 语句：

SELECT hyobjects_id, hyobjects_globalpriority
  FROM hyobjects, hyobjectypes, actionlltypes
WHERE hyobjects_hyobjectypes = hyobjectypes_id
  AND hyobjectypes_actionlltypes = actionlltypes_id
  AND actionlltypes_id = 1 
UNION
SELECT hyobjects_id, hyobjects_globalpriority
  FROM hyobjects, actionstatuses, actionlltypes
WHERE actionstatuses_id = hyobjects_actionstatuses 
  AND actionstatuses_actionlltypes = actionlltypes_id
  AND actionlltypes_id = 1
EXCEPT
SELECT hyobjects_id, hyobjects_globalpriority
  FROM hyobjects, actionstatuses, actionlltypes
WHERE hyobjects_actionstatuses = actionstatuses_id
  AND actionstatuses_actionlltypes = actionlltypes_id
  AND actionlltypes_id = 2
UNION
SELECT hyobjects_id, hyobjects_globalpriority
  FROM hyobjects, hyobjectypes, actionstatuses, actionlltypes
WHERE hyobjects_hyobjectypes = hyobjectypes_id
  AND hyobjectypes_actionlltypes = actionlltypes_id
  AND actionlltypes_id = 1 
  AND hyobjects_actionstatuses = 1
  ORDER BY hyobjects_globalpriority DESC

表中的哪里actionlltypes有actionlltypes_id = 1什么意思ACTIVE，actionlltypes_id = 2什么意思INACTIVE。

以及列hyobjectypes_actionlltypes引用actionlltypes和列hyobjects_actionstatuses引用的位置actionlltypes。我需要的结果actionstatuses_id可能是 1 或有actionlltypes_id = 1.

与评论相关，我想找到一种方法来清除PostgreSQL 中除字符串开头regexp_replace以外的任何非数字。+

例如+(7)9125415501应成为+79125415501