Mio's questions

我有一个看起来像这样的查询：

SELECT
  DISTINCT "articles"."id",
  "articles"."company_uuid",
  "articles"."status",
  "articles"."discount_reference",
  "articles"."created_at"
  --- more columns
FROM
  "articles"
  INNER JOIN "customers" ON "customers"."rec" = 'f'
  AND "customers"."article_id" = "articles"."id"
WHERE
  "articles"."type" = 'sending_request'
  AND "articles"."hidden" = 'f'
  AND "articles"."discount_reference" = 'Discount/737251623'
  AND "articles"."api_domain" = 'company'
  AND "articles"."status" IN ('completed', 'active')
  AND (customers.search_text ILIKE unaccent('%verb%'))
ORDER BY
  authored_on DESC NULLS LAST
LIMIT
  20 OFFSET 0;

第一个查询很慢，但当我重新运行时它总是更快。当我比较计划时，我没有看到明确的答案。我有一个用于客户搜索文本的杜松子酒三元组索引。

https://explain.dalibo.com/plan/b11657f576699fa8

第二轮

https://explain.dalibo.com/plan/g81h74b9g​​521g5e7

IO 和缓冲区的差异是差异的根源吗？
我在 RDS 上运行 PostgreSQL 14。

我正在尝试使用 PostgreSQL 14 和更新时的行级锁定。文档提到：

FOR UPDATE 锁模式也可以通过行上的任何 DELETE 获取，也可以通过修改某些列的值的 UPDATE 获取。目前，UPDATE 案例考虑的列集是那些具有可在外键中使用的唯一索引的列（因此不考虑部分索引和表达式索引），但这在未来可能会改变。

我创建了一个表documents，添加了一个user_id带有 UNIQ 约束的字段。

然后在一个会话

UPDATE documents SET user_id=2 WHERE id=1 AND pg_sleep(5) IS NOT NULL;

在第一个会话上按 Enter 后 1 秒后的另一个会话。

UPDATE documents SET user_id=1 WHERE id=1;

我看到的问题是没有获得锁。第二个查询立即执行，然后第一个查询覆盖提交。此外，慢速查询不遵守pg_sleep时间（在非约束列上更新时没有问题），查询需要 55-60 秒。

1. 为什么没有获取到锁？
2. 为什么更新这么慢？

我想得到两个子查询的结果。在我的情况下，我有用户，用户可以对门进行多个访问，但有时门被禁用，因此访问存在但不指向任何门。我想为同一个用户找到多个访问权限，该用户至少有一个没有门的访问权限和至少一个有一个门组的访问权限。

这是SQL，可以工作。但这非常复杂。我该如何改进它？

DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS doors CASCADE;
DROP TABLE IF EXISTS access CASCADE;
CREATE TABLE users(id SERIAL PRIMARY KEY);
CREATE TABLE doors(id SERIAL PRIMARY KEY, type TEXT NOT NULL);
CREATE TABLE access(
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL, FOREIGN KEY (user_id) REFERENCES users (id),
  door_id INT, FOREIGN KEY (door_id) REFERENCES doors (id)
);
INSERT INTO users VALUES (1), (2), (3), (4);
INSERT INTO doors(type) VALUES ('front'), ('back');
INSERT INTO access(user_id, door_id)
VALUES
  (1, 1), -- user have only access to door
  (2, null), -- user with empty door access
  (3, 1), (3, null), -- user with access and empty access
  (4, 1),(4, 2) -- user with access to front and back access
  ;

--- We want all the door access for the same user with at least one empty access and one door access

WITH users_with_multiple_access AS (
  SELECT access.user_id 
  FROM access
  GROUP BY access.user_id
  HAVING (COUNT(access.user_id) > 1)
),
users_with_multiple_access_and_without AS (
  SELECT access.*, CASE WHEN door_id IS NULL THEN 1 END AS marked 
  FROM access
  JOIN users_with_multiple_access USING(user_id)
),
users_with_multiple_access_marked AS (
  SELECT access.id AS access_id, sum(marked) OVER (PARTITION BY access.user_id) AS marked
  FROM access
  LEFT JOIN users_with_multiple_access_and_without uwmaw ON uwmaw.id = access.id
)
SELECT access.*
FROM access
JOIN users_with_multiple_access_marked awmam ON awmam.access_id = access.id AND marked IS NOT NULL;

小提琴

我有这个数据：

CREATE TABLE tickets(user_id int NOT NULL);
INSERT INTO tickets VALUES (1);
INSERT INTO tickets VALUES (2);
INSERT INTO tickets VALUES (3); -- 3 times
INSERT INTO tickets VALUES (4); -- 10 times

现在我想显示每个用户的票数百分比。

我试过这个：

WITH number_of_tickets AS (
  SELECT user_id, COUNT(user_id) AS number_of_tickets_per_user
  FROM tickets 
  GROUP BY user_id
)
SELECT 
  number_of_tickets_per_user, 
  ROUND((COUNT(user_id) * 100.0) / (SELECT COUNT(DISTINCT(user_id)) FROM tickets), 3) -- No no no no
FROM number_of_tickets
GROUP BY number_of_tickets_per_user
ORDER BY number_of_tickets_per_user;

但我可能不能很好地处理百分比计算。结果总是显示每个用户每张票数的 25%。

谢谢

我想让用户如何不在一个组中。组名是“大家”。

我有一个名为groups_userswith user_idand的连接表group_id。

我做了这个

SELECT users.email
FROM users
  WHERE NOT EXISTS (
    SELECT groups_users.user_id 
    FROM groups_users
    INNER JOIN groups ON groups_users.group_id = groups.id 
    WHERE groups.name = 'everybody'
);

但它不返回任何结果。我有一个没有任何组的用户，它没有出现。我错过了什么？

我有这个 ：

CREATE TABLE tickets(id serial PRIMARY KEY, source text NOT NULL, user_id int NOT NULL, shop_id int NOT NULL, created_at time DEFAULT current_time);
INSERT INTO tickets VALUES (0, 'web-mobile', 1, 2);
INSERT INTO tickets VALUES (1, 'web-destkop', 1, 2);
INSERT INTO tickets VALUES (2, 'web-destkop', 2, 2);

SELECT shop_id, source, COUNT(DISTINCT(user_id)) AS user_count
FROM tickets
WHERE shop_id = 2
GROUP BY shop_id, source;

雷克斯斯特：http: //rextester.com/TDS9913

但我想要的是得到 distinct user_idon的总和web-*。

shop_id source  user_count
2       web     2

我在现有表上添加约束jobs：

DROP TABLE IF EXISTS jobs CASCADE;
DROP TYPE IF EXISTS job_state;
CREATE TYPE job_state AS ENUM ( 'UNSTARTED', 'STARTED', 'FAILED', 'COMPLETED' );
CREATE TABLE jobs(
status job_state NOT NULL DEFAULT 'UNSTARTED',
error_message text[]
);

ALTER TABLE jobs
ADD CONSTRAINT error_message_is_null_if_failed_is_false 
   CHECK ( status IN ('UNSTARTED', 'STARTED', 'COMPLETED') AND ( error_message IS NULL OR error_message = '{}' ));

我想要的是

INSERT INTO jobs VALUES ('UNSTARTED', null);
INSERT INTO jobs VALUES ('STARTED', null);
INSERT INTO jobs VALUES ('FAILED', null); -- I would like to be able to do this

我知道这error_message != '{}' OR error_message IS NOT NULL写得不正确。但是我一直在寻找一种方法来编写它。

我有这个 ：

CREATE TABLE users
(
    first_name varchar(60) NOT NULL, 
    last_name varchar(60) NOT NULL
);

INSERT INTO users
    (first_name, last_name)
VALUES
    ('Sylvia ', 'Hoeks'),
    ('Harrison', 'Ford');

我得到这样的清单

SELECT first_name || ' ' || last_name AS user_full_name FROM users;

但我希望能够在该列上进行搜索user_full_name。没有子查询或 CTE 是否可能（如下所示）？

WITH users_list AS (
    SELECT first_name || ' ' || last_name AS user_full_name
    FROM users
)

SELECT * FROM users_list WHERE user_full_name ILIKE '%lvi%';

我试过第一列！？

SELECT first_name || ' ' || last_name AS user_full_name FROM users WHERE 1 ILIKE '%lvi%';
/*
ERROR:  operator does not exist: integer ~~* unknown
LINE 1: ...|| last_name AS user_full_name FROM users WHERE 1 ILIKE '%lv...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
*/

我希望能够做这样的事情。

SELECT first_name || ' ' || last_name AS user_full_name FROM users WHERE user_full_name ILIKE '%lvi%';

目前我这样做：

SELECT first_name || ' ' || last_name AS user_full_name FROM users WHERE first_name ILIKE '%lvi%' OR last_name ILIKE '%lvi%';

如果需要，小提琴：https ://www.db-fiddle.com/f/c3HANw8M9W7kSWeEwidpne/0

我在 Redshift 上有一个表，在一个查询中，我试图从生成的序列中获取 ID 列表和 ID 列表。

WITH 

missing_rows AS (
  SELECT id, created_at
  FROM logs
  WHERE created_at > GETDATE() - INTERVAL '2 hours'
),

range AS (
  SELECT min(id) AS min_id, max(id) AS max_id
  FROM missing_rows
),

series AS (
  SELECT (row_number() over (ORDER BY 1)) + (range.min_id - 1) AS id, 
  FROM logs
  FULL OUTER JOIN range ON ???? -- here is my issue
  WHERE active_connections.id 
BETWEEN range.min_id -- how to use min_id from range
    AND range.max_id -- how to use max_id from range 
)

SELECT *
FROM series

如何在下一个查询中使用范围值series？

我有一个将事件记录到 Redshift 的后端，它为每个事件生成一个唯一的 ID。id 是一个序列号。

我有类似的东西（events表）：

+-------------------------+------+
| created_at              |  id  |
+-------------------------+------+
| 2017-06-30 09:20:47 UTC | 100  |
| 2017-06-30 09:18:31 UTC | 101  |
| 2017-06-30 09:16:19 UTC | 102  |
| 2017-06-30 09:12:08 UTC | 103  |
| 2017-06-30 09:11:59 UTC | 104  |
| 2017-06-30 09:11:15 UTC | 105  |
| 2017-06-30 07:03:41 UTC | 106  |
+-------------------------+------+

我的任务不是每小时运行一次，将很少的记录移动到另一个表 ( deactivated_events)。

我想验证在过去 3 小时内我没有使用id序列丢失任何记录。首先我考虑过使用 generate_series 但这在 Redshift 中不存在。其他人建议制作一个只有 ID 的表，但是用整数填充数据库仍然很痛苦（这里是一个生成 100 万的例子

我想知道最好的方法是不使用 min max 并像这样计数：

WITH merged_events AS
  (SELECT *
   FROM
     (SELECT id, created_at
      FROM events
      UNION 
      SELECT id, created_at
      FROM deactivated_events
     )
   WHERE created_at > GETDATE() - INTERVAL '3 hours'
   ORDER BY id)
SELECT COUNT(*), (max(id) - min(id) + 1) AS diff
FROM merged_events;

PS：奖金，如何找到丢失或重复的记录？

我有这种数据需要存储在 postgres（ruby 对象）中。

#<InitRes:0x007f8ec3734c78
 @kind="RestrictionsSchema",
 @restrictions=
  [#<ParsedRes:0x007f8ec60ed218
    @description="Disallow user",
    @key="disallowUserConfig",
    @restriction_type="bool",
    @title="Disallow user">,
   #<ParsedRes:0x007f8ec2c96898
    @description="Restriction message",
    @key="disallowUserConfigMessageKO",
    @restriction_type="string",
    @title="Restriction message">,
   #<ParsedRes:0x007f8ec3551730
    @description="List of configurations",
    @key="configurations",
    @nested_res=
     [#<ParsedRes:0x007f8ec2565678
       @description="configuration",
       @key="configuration",
       @nested_res=
        [#<ParsedRes:0x007f8ec2504328
          @description="configuration name",
          @key="name",
          @restriction_type="string",
          @title="Profile Name">,
         #<ParsedRes:0x007f8ec34b35d0
          @description="server URL",
          @key="server",
          @restriction_type="string",
          @title="Server">,
         #<ParsedRes:0x007f8ec2a64228
          @default_value=
           #<ParsedResRestrictionValue:0x007f8ec3437de0
            @type="bool",
            @value_bool=false>,
          @description="Use Web logon",
          @key="weblogonMode",
          @restriction_type="bool",
          @title="Use Web logon for authentication">],
       @restriction_type="bundle",
       @title="Configuration">],
    @restriction_type="Array",
    @title="Configurations">]>

可以有ParsedRes多个nested_res包含和一个列表ParsedRes。它最多可以嵌套两层。

我想知道这种数据的最佳设计是什么。我将需要能够获取同一棵树以在将来显示它。

我想有一张可以链接到父母和女儿的桌子

CREATE TABLE parsed_res (
  id           SERIAL PRIMARY KEY,
  -- other columns
  parent_id    INT,
  daughter_id  INT
);

1. 这是个好主意吗？
2. 获取这棵树的最佳方法是什么（子选择？）
3. 树？

我想了解为什么我有不同的结果

我有一个名为 active_transfert 的表，我在其中记录图像 transfert

user_id | image_id | created_at
--------|----------|-----------
1       |1         |2014-07-10
1       |2         |2015-01-21
2       |1         |2015-05-23
3       |1         |2016-07-22
4       |6         |2017-06-01
4       |6         |2014-08-22

我想为每个 image_id 返回唯一的 user_id。

SELECT user_id,
       image_id
FROM active_transfert
GROUP BY user_id,
         image_id; --50


SELECT user_id,
       image_id
FROM
  (SELECT user_id,
          image_id,
          rank() OVER (PARTITION BY user_id, image_id
                       ORDER BY created_at DESC) AS i_ranked
   FROM active_transfert) AS i
WHERE i.i_ranked = 1; -- 53

我对 Redshift 运行这些查询。为什么我的第二个查询不能防止重复记录（相同的 user_id 和 image_id）？

预期结果 ：

user_id | image_id |
--------|----------|
1       |1         |
1       |2         |
2       |1         |
3       |1         |
4       |6         |

我有 3 个表 ( devices, shops, device_shops_versions)。devices可以有很多device_shops_versions。

device_shops_versions table:

+----+---------+-----------+---------+---------------------------+
| id | version | device_id | shop_id | updated_at                |
+----+---------+-----------+---------+---------------------------+
| 1  | 113     | 1         | 1       | 2014-05-05 17:03:25.04055 |
| 2  | 702     | 1         | 1       | 2015-05-05 17:03:25.04055 |
| 3  | 410     | 2         | 1       | 2014-05-30 09:29:44.88214 |
| 4  | 440     | 4         | 2       | 2013-06-30 08:28:42.98214 |
+----+---------+-----------+---------+---------------------------+

1 )我想归还device_id特定device_shops_versions.shop_id

2) 我想显示最新version的每台设备device_shops_versions- 最新的updated_at.

我应该有这样的东西：

+----+--------------+---------+-----------+---------+--------------------------+
| id | device_model | version | device_id | shop_id | updated_at               |
+----+--------------+---------+-----------+---------+--------------------------+
| 1  | 'iphone'     | 702     | 1         | 1       | 2015-05-05 17:03:25.040  |
| 2  | 'test'       | 410     | 2         | 1       | 2014-05-30 09:28:44.982  |
+----+--------------+---------+-----------+---------+--------------------------+

我的查询看起来像这样，但我不确定我是否正确，而且我想知道是否可以避免重复：

SELECT *
FROM devices
INNER JOIN
  (SELECT device_shops_versions.device_id,
          MAX(device_shops_versions.updated_at)
   FROM device_shops_versions
   GROUP BY device_id ) dcv ON devices.id = dcv.device_id
WHERE devices.id IN
    (SELECT device_shops_versions.device_id
     FROM device_shops_versions
     WHERE device_shops_versions.shop_id = 1);

我有一个名为created_at存储在 UTC 中没有时区的数据的列，例如：2017-02-05 15:43:27.151

我想以特定格式转换为其他时区，如 CET：

2017-02-08 16:43:27 +01:00

我成功将时间转换为特定时区，但需要指定输入的时区。查询看起来像：

SELECT created_at, 
       (created_at AT TIME ZONE 'UTC') AT TIME ZONE 'CET' AS cet 
FROM my_table;

[
  {
    "created_at" : "2017-02-08 15:43:27.151",
    "cet"        : "2017-02-08 16:43:27.151"
  }
]

但我未能从时区代码中提取或生成时区偏移量。

我试过了

SELECT EXTRACT(TIMEZONE_HOUR
               FROM
                 (SELECT '2017-02-08 15:43:27.151' AT TIME ZONE 'CET'));
[
  {
    "date_part" : 0
  }
]

如何获得2017-02-08 16:43:27 +01:00？

编辑 1：

SHOW timezone; --utc
SELECT current_timestamp; -- 2017-02-09 11:00:20.225039+00
SELECT to_char(current_timestamp AT TIME ZONE 'CET', 'YYYY-MM-DD HH24:MI:SS OF'); -- 2017-02-09 12:00:35 +00

为什么偏移量是 00？（我使用OF是因为我在 Redshift 上）？

的 | 与 UTC 的偏移量；仅对 TIMESTAMPTZ 有效

来自文档

编辑2：我想把AT TIME ZONE 'CET'日期改成timestamptz，但我错了。您需要转换日期，::timestamptz然后OF将显示 UTC 偏移量。

SELECT to_char((current_timestamp AT TIME ZONE 'CET')::timestamptz, 'YYYY-MM-DD HH24:MI:SS OF'); 
--2017-02-09 14:48:48 +01

现在我需要添加:00.

我试图了解 Redshift 上的 EXPLAIN。我的情况是我有这样的数据

id | user_id | created_at
---|---------------------------------
1  | 1       | 2017-02-08 14:32:10.96
2  | 1       | 2017-02-07 14:32:10.96
3  | 2       | 2017-02-06 14:32:10.96
4  | 2       | 2017-02-05 14:32:10.96

我想 ：

id | user_id | created_at
---|---------------------------------
1  | 1       | 2017-02-08 14:32:10.96
3  | 2       | 2017-02-06 14:32:10.96

我有这两个查询：

SELECT id,
       user_id,
       created_at
FROM
  ( SELECT user_id,
           created_at,
           row_number() OVER (PARTITION BY user_id
                              ORDER BY created_at) AS rownum
   FROM my_table) x
WHERE rownum = 1;

随着 EXPLAIN 有：

XN Subquery Scan x  (cost=1000001263779.68..1000001513986.60 rows=50042 width=16)
  Filter: (rownum = 1)
  ->  XN Window  (cost=1000001263779.68..1000001388883.14 rows=10008277 width=16)
        Partition: user_id
        Order: created_at
        ->  XN Sort  (cost=1000001263779.68..1000001288800.37 rows=10008277 width=16)
              Sort Key: user_id, created_at
              ->  XN Seq Scan on my_table  (cost=0.00..100082.77 rows=10008277 width=16)

然后是另一个查询：

SELECT ac1.user_id, ac1.created_at FROM my_table ac1
JOIN 
(
   SELECT user_id, MAX(created_at) AS MAXDATE
   FROM my_table
   GROUP BY user_id
) ac2
ON ac1.user_id = ac2.user_id
AND ac1.created_at = ac2.MAXDATE;

和解释：

XN Hash Join DS_DIST_NONE  (cost=150798.74..771939079.62 rows=7257 width=16)
  Hash Cond: (("outer".created_at = "inner".maxdate) AND ("outer".user_id = "inner".user_id))
  ->  XN Seq Scan on my_table ac1  (cost=0.00..100082.77 rows=10008277 width=16)
  ->  XN Hash  (cost=150606.01..150606.01 rows=38548 width=16)
        ->  XN Subquery Scan ac2  (cost=150124.15..150606.01 rows=38548 width=16)
              ->  XN HashAggregate  (cost=150124.15..150220.52 rows=38548 width=16)
                    ->  XN Seq Scan on my_table  (cost=0.00..100082.77 rows=10008277 width=16)

第一个查询的结果有点慢，但是当我尝试理解 EXPLAIN 时，我迷路了。似乎cost在使用的查询中更高，ROW_NUMBER()但与rows.

但是我可以从这些 EXPLAIN 中提取什么（遗憾的是我不能ANALYZE在 Redshift 上使用）？

我是数据库新手，我迷失了transaction。例如，在我有一个sensor_log不断接收 INSERT 的表并且我想在一个事务中将数据移动到另外两个表的情况下。

BEGIN;

INSERT INTO sensor_log_a
SELECT id, location 
FROM sensor_log
INNER JOIN sensor_location_to_insert USING (location);

INSERT INTO sensor_log_b
SELECT id, location 
FROM sensor_log
INNER JOIN sensor_location_to_insert USING (location);

COMMIT;

BEGIN;..COMMIT;如果在事务期间插入数据或阻止这种情况，sensor_log_a 和 sensor_log_b 之间是否存在数据不同的风险？

在 postgres 上我有这样的数据：

id | device_model | device_type
1  | "samsung"    | 1
2  | "iphone 4"   | 1
3  | "samsung"    | 1
4  | "ipad"       | 0
5  | "ipad"       | 0

我希望获得每个 device_type 的前 5 个 device_model。

目前我正在做：

SELECT device_model, COUNT(*)
FROM devices
GROUP BY device_model  
ORDER BY COUNT(*) DESC
limit 5;

但它不会为每个 device_type 呈现 top5。我可以用什么来做到这一点？看来我需要做一个 UNION。

我可以做类似的事情吗：

SELECT device_model, COUNT(*)
FROM devices
WHERE device_type = 1
GROUP BY device_model  
ORDER BY COUNT(*) DESC
LIMIT 5 
UNION
SELECT device_model, COUNT(*)
FROM devices
WHERE device_type = 0
GROUP BY device_model  
ORDER BY COUNT(*) DESC
LIMIT 5;

ERROR:  syntax error at or near "UNION"
LINE 7: UNION ALL
        ^
Query failed
PostgreSQL said: syntax error at or near "UNION"

我正在尝试获取集合的索引。

> db.version()
3.2.6

> db.getCollectionNames()
[ "last_aggregated_stat_ids", "mapps", "stats", "system.indexes" ]

> db.mapps.getIndexes()
[
    {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "app_development.mapps"
    }
]

> db.stats.getIndexes()
2016-07-20T16:51:04.601+0200 E QUERY    [thread1] TypeError: db.stats.getIndexes is not a function :
@(shell):1:1

关于会发生什么的任何想法？