主页 / user-227749

Thomas's questions

Martin Hope
Thomas
Asked: 2024-08-13 00:14:48 +0800 CST
  • 6

假设我有一张带有时间戳 ( ts) 和值 ( a) 的表。

使用一些示例数据：

ts, a
1, 10
2, 30
3, 10
4, 20
5, 40
6, 10
7, 20
8, 30
etc

我想按 ts 顺序对行进行分组，通过求和a并输出总和a> 50 的行。

在这种情况下，输出应该是两个时间戳（第一行和最后一行）：

1, 3  -- 10 + 30 + 10
4, 5  -- 20 + 40
6, 8  -- 10 + 20 + 30

但我不知道该怎么做。我对 Copilot 所做的所有尝试都产生了永远无法完成的复杂代码。

postgresql
Martin Hope
Thomas
Asked: 2023-06-23 22:41:35 +0800 CST
  • 6

我有一个表，其中有返回某个时间范围内的数据的查询。行数稀疏，活动可能相隔几天。

我正在寻找一种方法来从范围中获取数据+在范围开始之前添加行（可以是小于开始的任何时间戳）。

例如：

2020-01-01 xxxxxxx
2020-01-12 xxxxxxx
2020-02-10 xxxxxxx
2020-02-11 xxxxxxx
2020-02-15 xxxxxxx

如果我查询 from 2020-02-08to 2020-02-14，我会得到：

2020-02-10 xxxxxxx
2020-02-11 xxxxxxx

但我想得到：

2020-01-12 xxxxxxx
2020-02-10 xxxxxxx
2020-02-11 xxxxxxx

如何才能做到这一点？

postgresql
Martin Hope
Thomas
Asked: 2023-02-04 00:20:21 +0800 CST
  • 5

我有以下对象：

CREATE TABLE IF NOT EXISTS {schemaName}.{tableName}
(
    id                   VARCHAR      NOT NULL,
    ticker               VARCHAR(16)  NOT NULL,
    interval             VARCHAR(3)   NOT NULL,
    ts                   TIMESTAMP    NOT NULL,
    ...
    UNIQUE (ticker, interval, ts)
);

我想做一个将返回这样的行的查询：

SELECT * FROM analysis
WHERE ticker = 'BTCUSDT' AND ts BETWEEN '2020-1-1' AND '2020-1-30'
ORDER BY ts;

但是，对于每一行，添加一个列，其中包含下一行的时间戳（按 ts 排序）。

由于我只有 SQL 的基本知识，所以我使用 ChatGPT 生成了一个解决方案，但它非常慢（以分钟计与毫秒计）

SELECT t1.ts as ts_start, t2.ts as ts_end, t1.ticker, t1.interval, ...
FROM analysis t1
JOIN analysis t2
ON t1.ticker = t2.ticker AND t1.interval = t2.interval AND t1.ts < t2.ts
WHERE t1.ticker = 'BTCUSDT' AND t1.ts BETWEEN '2020-1-1' AND '2020-1-30'
ORDER BY t1.ts;

实现这一目标的更好方法是什么？

postgresql
Martin Hope
Thomas
Asked: 2022-08-07 08:17:23 +0800 CST
  • 0

我试图找出最快的方法：

  • 如果不存在则插入一行
  • 替换（一次所有列）该行（如果存在）

我考虑过DELETE+ INSERT，但这看起来像 2 次扫描，如果我执行INSERT+ ON CONFLICT，我会得到一个巨大的查询，其中所有内容都必须复制。

有没有公​​认的方法来做到这一点？

postgresql upsert
Martin Hope
Thomas
Asked: 2022-06-30 06:02:22 +0800 CST
  • 3

这不会编译：

SELECT add_compression_policy(
    hypertable => 'exchange.candles',
    compress_after => INTERVAL ((SELECT now()::DATE::timestamp - (SELECT min(last_ts) FROM exchange.capture_tracker))),
    if_not_exists => TRUE);

问题似乎SELECT如下INTERVAL。

但这编译：

WITH delay AS (SELECT now()::DATE::timestamp - (SELECT min(last_ts) FROM exchange.capture_tracker) myinterval)
SELECT add_compression_policy(
    hypertable => 'exchange.candles',
    compress_after => myinterval,
    if_not_exists => TRUE) FROM delay;

我的同事和我不明白为什么。
谁能解释一下？

postgresql cast
Martin Hope
Thomas
Asked: 2022-04-21 12:15:52 +0800 CST
  • 1

我有一个具有这种结构的表：

ticker         VARCHAR                     NOT NULL,
interval       VARCHAR                     NOT NULL,
ts             TIMESTAMP WITHOUT TIME ZONE NOT NULL,
price          FLOAT8                      NOT NULL,
UNIQUE (ticker, interval, ts)

有 40 个代码（最终将扩展到 130 个左右）和 8 个间隔。每 10 秒添加新行 (40 * 8) 作为大容量副本，这表示 115k 行/小时。它们只写一次，从不修改。

读取操作总是在相当大的时间范围（多天）内完成，并为其请求一个代码和 3 个间隔，使用以下方法：

SELECT * FROM exchange.{tableName}
WHERE ticker = '{ticker}' AND \"interval\" IN ({intervalsText})
AND ts >= '{fromTime.Format}' AND ts < '{toTime.Format}'
ORDER BY ts

我的问题是，将所有间隔分组在每个行情中是否有益。像这样：

ticker           VARCHAR                     NOT NULL,
ts               TIMESTAMP WITHOUT TIME ZONE NOT NULL,
price_interval0  FLOAT8                      NOT NULL,
price_interval1  FLOAT8                      NOT NULL,
...
price_interval7  FLOAT8                      NOT NULL,
UNIQUE (ticker, ts)

这意味着表中的行数减少了 8 倍，索引更小，但每个查询可能需要加载整行以仅返回 3 个值并丢弃 5 个。

我不知道 Postgres 如何在内部组织数据，以及是否会一次检索一整行（这是我的假设），然后提取其中的一部分，等等......

任何建议将不胜感激。

database-design postgresql
Martin Hope
Thomas
Asked: 2021-11-21 01:14:34 +0800 CST
  • 1

我在 postgres 实例的日志中有这个：

Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2021-11-20 06:20:02.277 UTC [78602] FATAL:  password authentication failed for user "postgres"
2021-11-20 06:20:02.277 UTC [78602] DETAIL:  Role "postgres" does not exist.
Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2021-11-20 06:41:05.211 UTC [79412] FATAL:  password authentication failed for user "postgres"
2021-11-20 06:41:05.211 UTC [79412] DETAIL:  Role "postgres" does not exist.
Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2021-11-20 06:41:45.448 UTC [79517] FATAL:  password authentication failed for user "pgsql"
2021-11-20 06:41:45.448 UTC [79517] DETAIL:  Role "pgsql" does not exist.
Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2021-11-20 07:01:53.432 UTC [80321] FATAL:  password authentication failed for user "postgres"
2021-11-20 07:01:53.432 UTC [80321] DETAIL:  Role "postgres" does not exist.
Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2021-11-20 07:22:44.231 UTC [81197] FATAL:  password authentication failed for user "postgres"
2021-11-20 07:22:44.231 UTC [81197] DETAIL:  Role "postgres" does not exist.
Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2021-11-20 07:25:30.380 UTC [81317] FATAL:  password authentication failed for user "pgsql"
2021-11-20 07:25:30.380 UTC [81317] DETAIL:  Role "pgsql" does not exist.
Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2021-11-20 07:43:48.611 UTC [82115] FATAL:  password authentication failed for user "postgres"
2021-11-20 07:43:48.611 UTC [82115] DETAIL:  Role "postgres" does not exist.
Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2021-11-20 07:47:12.673 UTC [82233] FATAL:  password authentication failed for user "pgsql"
2021-11-20 07:47:12.673 UTC [82233] DETAIL:  Role "pgsql" does not exist.
Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2021-11-20 08:04:33.369 UTC [82925] FATAL:  password authentication failed for user "postgres"
2021-11-20 08:04:33.369 UTC [82925] DETAIL:  Role "postgres" does not exist.
Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2021-11-20 08:09:09.023 UTC [83149] FATAL:  password authentication failed for user "pgsql"
2021-11-20 08:09:09.023 UTC [83149] DETAIL:  Role "pgsql" does not exist.
Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2021-11-20 08:25:46.146 UTC [83736] FATAL:  password authentication failed for user "postgres"
2021-11-20 08:25:46.146 UTC [83736] DETAIL:  Role "postgres" does not exist.
Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2021-11-20 08:30:54.629 UTC [83963] FATAL:  password authentication failed for user "pgadmin"
2021-11-20 08:30:54.629 UTC [83963] DETAIL:  Role "pgadmin" does not exist.
Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"

该实例用于测试，包含无价值数据，因此不安全，并且有一个公共开放端口，便于测试。

如果这是一次攻击，我觉得奇怪的是时间戳间隔很大：6:20、6:41、7:01、7:22、7:25、7:43 等请求在很短的时间内，但连接之间有很大的差距

“连接匹配”消息是什么意思？

postgresql
Martin Hope
Thomas
Asked: 2021-04-16 10:40:41 +0800 CST
  • 0

作为参考，我几乎所有的数据库经验都是使用 mongodb，这是我的第一个 postgres 项目。

我正在存储来自去中心化金融加密池的数据更新。

我正在探索的数据结构非常简单。有令牌：

type Token =
    {
        Address:    string     // primary key
        Decimals:   int
        Symbol:     string
    }

这些数据永远不会改变，但会在任何地方被引用；大约 2k 个条目

type LiquidityToken =
    {
        Address:        string    // primary key
        Symbol:         string
        Token0Address:  string    // matches the address from a token above
        Token1Address:  string    // matches the address from a token above
        Timestamp:      DateTime  // changes at every update
        Token0Amount:   decimal   // changes at every update
        Token1Amount:   decimal   // changes at every update
    }

这每 5 分钟更新一次，但是只有 Token0Amount 和 Token1Amount 发生变化，我想保留历史记录。

我大约有 500 个这样的对象，每 5 分钟更新一次，因此每天有 140k 新条目。

所以我想做的第一件事是在 Token0/1Address 字段和 Token 记录之间进行连接，但是由于 Token 只是添加了一个 int 和一个短（最多 32 个字符，但通常 < 8 个字符）字符串，我想知道这是否节省了很多（我不知道加入占用了多少空间）。

然后我想知道将对象分成两部分是否值得：

type LiquidityToken =
    {
        Address:        string    // primary key
        Symbol:         string
        Token0Address:  string    // matches the address from a token above
        Token1Address:  string    // matches the address from a token above
    }

和

type LiquidityTokenUpdate =
    {
        Address:        string    // matches the address of the liquidity token 
        Timestamp:      DateTime  // changes at every update
        Token0Amount:   decimal   // changes at every update
        Token1Amount:   decimal   // changes at every update
    }

并复制这个对象。但是现在一个查询可能会拉取更新，这会拉取流动性代币，该代币必须拉取 2 个代币对象。

由于这是针对公共 GraphQL 接口的，因此可能会弹出很多使用模式。我无法预测特定类型的查询，因为这是我想在准备好后向社区公开的内容。

我知道缓存是我的朋友，因为静态数据很小，所以它都适合 RAM，但总的来说，尝试节省空间（每条记录不是很大）并增加连接或查找的成本是否有意义在本地缓存中，还是在每次更新时保存所有内容更好，因为磁盘毕竟便宜？

请在上下文中说明这不是我有过的经验；我已经阅读过有关联接、规范化与非规范化表等的信息，但我没有这方面的实践经验。我处理了非常大的数据集，但在非 SQL 上下文中，这是我第一次来这里，所以我希望这个问题提供了所需的信息：D

postgresql join