AskOverflow.Dev

AskOverflow.Dev Logo AskOverflow.Dev Logo

AskOverflow.Dev Navigation

  • 主页
  • 系统&网络
  • Ubuntu
  • Unix
  • DBA
  • Computer
  • Coding
  • LangChain

Mobile menu

Close
  • 主页
  • 系统&网络
    • 最新
    • 热门
    • 标签
  • Ubuntu
    • 最新
    • 热门
    • 标签
  • Unix
    • 最新
    • 标签
  • DBA
    • 最新
    • 标签
  • Computer
    • 最新
    • 标签
  • Coding
    • 最新
    • 标签
主页 / user-158432

Jean Louis's questions

Martin Hope
Jean Louis
Asked: 2023-02-05 19:23:32 +0800 CST

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

  • 7

表定义:

                                                      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
  • 2 个回答
  • 47 Views
Martin Hope
Jean Louis
Asked: 2022-12-25 09:25:37 +0800 CST

如何优化此 SQL UNION 语句?

  • 5
这个问题是从 Super User迁移过来的,因为它可以在 Database Administrators Stack Exchange 上回答。 8 天前迁移 。

我正在使用 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
  • 2 个回答
  • 83 Views
Martin Hope
Jean Louis
Asked: 2018-08-14 23:54:37 +0800 CST

如何在 Postgresql 中使用 regexp_replace 清除除字符串开头的 + 以外的所有非数字?

  • 1

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

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

postgresql regex
  • 1 个回答
  • 688 Views

Sidebar

Stats

  • 问题 205573
  • 回答 270741
  • 最佳答案 135370
  • 用户 68524
  • 热门
  • 回答
  • Marko Smith

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

    • 12 个回答
  • Marko Smith

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

    • 3 个回答
  • Marko Smith

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

    • 3 个回答
  • Marko Smith

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

    • 4 个回答
  • Marko Smith

    列出指定表的所有列

    • 5 个回答
  • Marko Smith

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

    • 4 个回答
  • Marko Smith

    你如何mysqldump特定的表?

    • 4 个回答
  • Marko Smith

    使用 psql 列出数据库权限

    • 10 个回答
  • Marko Smith

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

    • 4 个回答
  • Marko Smith

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

    • 7 个回答
  • Martin Hope
    Jin 连接到 PostgreSQL 服务器:致命:主机没有 pg_hba.conf 条目 2014-12-02 02:54:58 +0800 CST
  • Martin Hope
    Stéphane 如何列出 PostgreSQL 中的所有模式? 2013-04-16 11:19:16 +0800 CST
  • Martin Hope
    Mike Walsh 为什么事务日志不断增长或空间不足? 2012-12-05 18:11:22 +0800 CST
  • Martin Hope
    Stephane Rolland 列出指定表的所有列 2012-08-14 04:44:44 +0800 CST
  • Martin Hope
    haxney MySQL 能否合理地对数十亿行执行查询? 2012-07-03 11:36:13 +0800 CST
  • Martin Hope
    qazwsx 如何监控大型 .sql 文件的导入进度? 2012-05-03 08:54:41 +0800 CST
  • Martin Hope
    markdorison 你如何mysqldump特定的表? 2011-12-17 12:39:37 +0800 CST
  • Martin Hope
    Jonas 如何使用 psql 对 SQL 查询进行计时? 2011-06-04 02:22:54 +0800 CST
  • Martin Hope
    Jonas 如何从 PostgreSQL 中的选择查询中将值插入表中? 2011-05-28 00:33:05 +0800 CST
  • Martin Hope
    Jonas 如何使用 psql 列出所有数据库和表? 2011-02-18 00:45:49 +0800 CST

热门标签

sql-server mysql postgresql sql-server-2014 sql-server-2016 oracle sql-server-2008 database-design query-performance sql-server-2017

Explore

  • 主页
  • 问题
    • 最新
    • 热门
  • 标签
  • 帮助

Footer

AskOverflow.Dev

关于我们

  • 关于我们
  • 联系我们

Legal Stuff

  • Privacy Policy

Language

  • Pt
  • Server
  • Unix

© 2023 AskOverflow.DEV All Rights Reserve