全部问题(dba)

假设我们有以下数据，每秒测量一次温度。数据跨度为几年，因此有相当多的行。这是来自现场设备的测量数据。该表还有其他列，但它们与问题无关。

我知道我可以使用一个GROUP BY子句来获取特定开始和结束时间之间一小时窗口内的平均值，如下所示：

  SELECT to_char("time", 'YYYY-MM-DD HH'), AVG("Temperature_deg_in_C")
    FROM "measurements"
   WHERE "time" BETWEEN '2024-11-01' AND '2024-12-01'
GROUP BY to_char("time", 'YYYY-MM-DD HH')

但是，这种语法感觉像是一种黑客行为，并且它不能完全适应例如 10 秒或其他更复杂的窗口框架（或分区）。

此外，我想具体了解如何使用 PostgreSQL 手册中概述的、、、、和类似工具正确且高效地完成OVER此操作。WINDOWPARTITION BYRANGEROWS

更新：窗口函数不能用在 Postgres 上实现这个目标，因为它们不会像我想象的那样创建“存储桶”或“框架”。GROUP BY是实现这一目标的方法。

最终目标是找到一种解决方案，其中窗口长度和测量单位（分钟、小时、天）以及观察期的开始和结束时间是灵活的，但查询主体本身保持不变。也就是说，一个简单易懂的参数化查询。

我有两张表：“Flight”和“Person”。Person 有三列：“id”、“last”、“first”。

航班有三列：“id”、“P1_id”、“P2_id”，其中 P1_id 是“人员”的行 id，P2_id 也是“人员”的行 id。

我需要从“航班”中选择每一行，并从“人员”中选择相应的“最后”和“第一个”。表格数据如下所示 -

人员（身份证号、姓氏、名字）

1. 史密斯，约翰
2. 弗雷德·琼斯
3. 玻璃，迈克尔

航班（id、p1_id、p2_id）

1. 1、2
2. 2、3
3. 3、
4. 3、2

我需要的输出看起来像 -

1. 史密斯·琼斯
2. 琼斯，玻璃
3. 玻璃
4. 玻璃，琼斯

感谢您的帮助。

对于数据库管理，我还是个新手，所以请放轻松！

我有一个 MySQL 数据库。其中大约有 100 个表。目前，每个表大约有 50 行。因此，当从这些行中选择一行时，查询时间约为 0.0004 秒。

现在，我的问题是：假设其中一个表增长到大约 500 万行。那么，如果我只想从该表中选择 1 行，它的查询时间会增加吗？大约会增加多少？

如果是，那么表增长后我该怎么做才能减少查询时间？

表的一点背景：每个表大约有 10 到 11 列。其中大约 4 到 5 列是长度为 11 的整数，1 列是 longtext，其他列是长度为 255 的 varchar。

我正在向一个大表添加检查约束，因为我想准备使用分区切换将其切换为分区表。

该检查是对列的简单不等式检查，并且该列上有索引。但是当我添加约束时，SQL Server 仍然会扫描整个表。是否可以快速添加约束并保持其可信？我希望这可以通过索引来实现。

以下是该扫描的再现图：

USE tempdb;
GO

SELECT * 
INTO MY_MESSAGES
FROM sys.messages;

CREATE CLUSTERED INDEX IX_MY_MESSAGES ON dbo.MY_MESSAGES(message_id);

SET STATISTICS IO ON;
ALTER TABLE dbo.MY_MESSAGES
    ADD CONSTRAINT CK_mymessages CHECK (message_id < 50000);
SET STATISTICS IO OFF;

DROP TABLE IF EXISTS dbo.MY_MESSAGES;

这显示在消息选项卡中：

Table 'MY_MESSAGES'. Scan count 1, logical reads 10955

有几个类似的问题（例如https://dba.stackexchange.com/questions/72419/filling-in-missing-dates-in-record-set-from-generate-series），但解决方案似乎对我的情况不起作用...本质上，我正在尝试为系列中不存在的日期生成零个条目，但我怀疑问题是我必须从时间戳中提取日期值？我已经使用 SQL 多年了，但对 postgres 还很陌生 - 到目前为止印象深刻。在这里尝试了左连接和右连接，但没有成功......

这是一个小测试用例（仍然鼓励使用 SQL 语句吗？）：

-- temp test table - works as expected
WITH incomplete_data(payment_date, payment_id) AS (
   VALUES 
     ('2024-09-06 11:26:57.509429+01'::timestamp with time zone, 'uuid01')
    ,('2024-09-06 12:26:57.509429+01', 'uuid02')
    ,('2024-09-07 07:26:57.509429+01', 'uuid03')
    ,('2024-09-08 10:26:57.509429+01', 'uuid05')
    ,('2024-09-08 12:26:57.509429+01', 'uuid08')
    ,('2024-09-08 14:26:57.509429+01', 'uuid11')
    ,('2024-09-10 09:26:57.509429+01', 'uuid23')
   )
select * from incomplete_data;

-- generated dates - work as expected
select * FROM  (
   SELECT generate_series(timestamp '2024-01-01'
                        , timestamp '2024-01-01' + interval '1 year - 1 day'
                        , interval  '1 day')::date
   ) d(day)
;
   
-- join - failing to do what I was hoping..
WITH incomplete_data(payment_date, payment_id) AS (
   VALUES 
     ('2024-09-06 11:26:57.509429+01'::timestamp with time zone, 'uuid01')
    ,('2024-09-06 12:26:57.509429+01', 'uuid02')
    ,('2024-09-07 07:26:57.509429+01', 'uuid03')
    ,('2024-09-08 10:26:57.509429+01', 'uuid05')
    ,('2024-09-08 12:26:57.509429+01', 'uuid08')
    ,('2024-09-08 14:26:57.509429+01', 'uuid11')
    ,('2024-09-10 09:26:57.509429+01', 'uuid23')
   )
select count(payment_id), date_trunc('day',payment_date)::date as time
FROM  (
   SELECT generate_series(timestamp '2024-01-01'
                        , timestamp '2024-01-01' + interval '1 year - 1 day'
                        , interval  '1 day')::date
   ) d(day)
right  JOIN incomplete_data p ON date_trunc('day',payment_date) = d.day
where payment_date BETWEEN '2024-09-01T12:55:36.824Z' AND '2024-09-30T13:55:36.824Z'
GROUP  BY date_trunc('day',payment_date)
ORDER  BY date_trunc('day',payment_date);

 count |    time
-------+------------
     2 | 2024-09-06
     1 | 2024-09-07
     3 | 2024-09-08
     1 | 2024-09-10
(4 rows)

我希望获取该月中每一天的一行，其中未填充的天数为零。背景是这是为了填充 grafana 查询。

有人能指出我做错了什么吗？或者我没能理解这里更大的问题吗？我的版本是：PostgreSQL 15.9 (Debian 15.9-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

更新
下面的 jjanes 答案帮助我澄清了连接和过滤的顺序 - 这是所需的选择：

WITH incomplete_data(payment_date, payment_id) AS (
   VALUES 
     ('2024-09-06 11:26:57.509429+01'::timestamp with time zone, 'uuid01')
    ,('2024-09-06 12:26:57.509429+01', 'uuid02')
    ,('2024-09-07 07:26:57.509429+01', 'uuid03')
    ,('2024-09-08 10:26:57.509429+01', 'uuid05')
    ,('2024-09-08 12:26:57.509429+01', 'uuid08')
    ,('2024-09-08 14:26:57.509429+01', 'uuid11')
    ,('2024-09-10 09:26:57.509429+01', 'uuid23')
   )
select count(payment_id), d.day as time
FROM  (
   SELECT generate_series(timestamp '2024-01-01'
                        , timestamp '2024-01-01' + interval '1 year - 1 day'
                        , interval  '1 day')::date
   ) d(day)
left JOIN incomplete_data p ON date_trunc('day',payment_date) = d.day
where d.day BETWEEN '2024-09-01T12:55:36.824Z' AND '2024-09-30T13:55:36.824Z'
GROUP  BY d.day
ORDER  BY d.day
;

在阅读有关流复制协议的文档时，我发现我可以获取快照名称。这似乎很方便在开始流式传输复制消息之前执行同步并确保我拥有所有数据。但是，在调用 pg_create_logical_replication_slot sql 函数时我无法获取快照（并且我的客户端未实现流复制协议）。我如何获取此快照名称以执行初始同步？

就上下文而言，我有一个仅附加表，并想将其用作某种“事件生产者”，使用逻辑复制来构建订阅机制。

我想配置我的 PostgreSQL 服务器以同时启用使用 TLS 证书和密码的身份验证（对于同一个连接，而不仅仅是启用两种身份验证机制）。

然后，对于要进行身份验证的客户端，它必须：

• 提供有效的证书，其通用名称中设置了用户名，并由受信任的根 CA 签名
• 为该特定用户提供密码

我想这样做是为了提高安全性。这样，即使我的客户端证书或我的根 CA 被泄露，你仍然需要密码进行身份验证。

我不知道该怎么做。在PostgreSQL 文档的这个页面中，说当你使用 TLS 证书时：

不会向客户端发送任何密码提示

那么，这可能吗？如果可以，如何做？

据记录，递归变量赋值（像这样↓↓↓↓）有时会返回不正确的结果：

SELECT @var = @var + [Name]
FROM dbo.People
...

但即使在我确信只返回一行的情况下这是否适用？

示例（小提琴）：

CREATE TABLE dbo.People
(
  Id INT IDENTITY PRIMARY KEY,
  [Name] varchar(50) NOT NULL
);

INSERT INTO dbo.People([Name]) VALUES ('Bob');

DECLARE @var varchar(50) = '';


/*Is this bad even if the query always returns only one row?*/
SELECT @var = @var + [Name]
FROM dbo.People
WHERE Id = 1;

SELECT [@var] = @var;

文档说“在这种情况下，不能保证@Var会逐行更新。 ”这（对我来说）表明单行选择应该没问题，对吗？

另一个可能想要使用它的例子是计算一些聚合值并将它们添加到某个地方：

SELECT @Total = @Total + SUM(InvoiceTotal) /*single row, aggregate of several values*/
FROM dbo.Invoice
WHERE ...;
/*Notice no GROUP BY*/

我有一个可以从任何主机访问的用户（我们目前正在开发中）。但我想限制存储过程的执行仅限于本地主机。问题是localhost无法识别该用户。

DROP USER IF EXISTS 'admin'@'%';
CREATE USER IF NOT EXISTS 'admin'@'%' IDENTIFIED WITH 'caching_sha2_password' BY 'my_password';

###########################
### STORED PROCEDURES   ###
###########################
GRANT EXECUTE ON MY_DATABASE.* TO 'admin'@'127.0.0.1';

###########################
### SELECT              ###
###########################
GRANT SELECT ON MY_DATABASE.MY_TABLE TO 'admin'@'%' WITH GRANT OPTION;
GRANT SELECT ON MY_DATABASE.MY_TABLE2 TO 'admin'@'%' WITH GRANT OPTION;
.
.
.

执行此代码会引发下一个错误。

[2024-11-23 20:59:09] [42000][1410] You are not allowed to create a user with GRANT

更改主机“解决”了错误，但我想将执行限制在本地主机。我不知道是否只需像这样添加新用户。

CREATE USER IF NOT EXISTS 'admin'@'127.0.0.1' IDENTIFIED WITH 'caching_sha2_password' BY 'my_password';

###########################
### STORED PROCEDURES   ###
###########################
GRANT EXECUTE ON MY_DATABASE.* TO 'admin'@'127.0.0.1';

###########################
### SELECT              ###
###########################
GRANT SELECT ON MY_DATABASE.MY_TABLE1 TO 'admin'@'127.0.0.1' WITH GRANT OPTION;
GRANT SELECT ON MY_DATABASE.MY_TABLE2 TO 'admin'@'127.0.0.1' WITH GRANT OPTION;
.
.
.

或者是否有另一种方法，无需重复用户定义

我所在的服务器存在严重的阻塞问题。不是死锁，只是普通的阻塞。我们知道这是服务器的主要瓶颈。但是，他们不知道块领导者是什么，所以我不知道要调整哪些查询。我可以安装更多监控，但我注意到他们启用了查询存储。查询存储中是否有任何信息可用于查找块领导者？