SQL 性能难点比较

十月 22, 2022 3161

汇聚各个时间段，关于SQL的一些难点问题，他们掌握之后又很容易忘记，全部汇总在这里

1. IN 与 EXIST 的对比

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
WITH
  boundays AS (
    SELECT
        code,
        MIN(close) AS min_all,
        MAX(close) AS max_all,
        MIN(CASE WHEN date BETWEEN '2023-05-19'::DATE - INTERVAL '2 year' AND '2023-05-19' THEN close END) AS min_2y,
        MAX(CASE WHEN date BETWEEN '2023-05-19'::DATE - INTERVAL '2 year' AND '2023-05-19' THEN close END) AS max_2y,
        MIN(CASE WHEN date BETWEEN '2023-05-19'::DATE - INTERVAL '1 year' AND '2023-05-19' THEN close END) AS min_1y,
        MAX(CASE WHEN date BETWEEN '2023-05-19'::DATE - INTERVAL '1 year' AND '2023-05-19' THEN close END) AS max_1y,
        MIN(CASE WHEN date BETWEEN '2023-05-19'::DATE - INTERVAL '3 month' AND '2023-05-19' THEN close END) AS min_3m,
        MAX(CASE WHEN date BETWEEN '2023-05-19'::DATE - INTERVAL '3 month' AND '2023-05-19' THEN close END) AS max_3m,
        MIN(CASE WHEN date BETWEEN '2023-05-19'::DATE - INTERVAL '1 month' AND '2023-05-19' THEN close END) AS min_1m,
        MAX(CASE WHEN date BETWEEN '2023-05-19'::DATE - INTERVAL '1 month' AND '2023-05-19' THEN close END) AS max_1m
    FROM
        history.kline_day AS d
    WHERE date <= '2023-05-19'
        AND EXISTS(
            SELECT dd.code FROM history.kline_day AS dd WHERE dd.date = d.date AND dd.date = '2023-05-19'
        )
--         AND code IN (
--             SELECT code
--             FROM history.kline_day
--             WHERE date = '2023-05-19'
--         )
    GROUP BY
        code
)
SELECT
    code,min_1m,min_3m,min_1y,min_2y,min_all,max_1m,max_3m,max_1y,max_2y,max_all
FROM boundays
;

– 这个比较中
– EXISTS 用时 626ms
– IN 用时 7896ms

2. PostgreSQL 日期字符串转时间戳

1
2
3
4
5
6
7
8
9
10

INSERT INTO public.speed_test_log (id, server_id, client_id, status, ping, download, uploading, time) 
VALUES (EXTRACT(EPOCH FROM TIMESTAMP '2024-04-05 13:00:06.181'), 101, 100, 0, 7.18, 9.90, null, TIMESTAMP '2024-04-05 13:00:18.211');

INSERT INTO public.speed_test_log (id, server_id, client_id, status, ping, download, uploading, time) 
VALUES (EXTRACT(EPOCH FROM TIMESTAMP '2024-04-05 13:00:23.511'), 100, 100, 0, 7.74, 1.96, null, TIMESTAMP '2024-04-05 13:00:35.539');

SELECT id, time, TO_TIMESTAMP(((id::FLOAT)/1000000)) AT TIME ZONE 'Asia/Shanghai' AS id_time
FROM speed_test_log

4. PostgreSQL 判断和识别数据类型

1
2
3
4

SELECT id, pg_typeof(time) AS time, pg_typeof(meta_ping) AS meta_ping, pg_typeof(EXTRACT(EPOCH FROM (time - INTERVAL '8 H'))) AS timestamp
FROM speed_test_log

查询结果

id time meta_ping timestamp
1712639156782552 timestamp without time zone json double precision

5. PostgreSQL 数组转多行

1
2
3
4
5
SELECT u.element
FROM UNNEST(ARRAY['a', 'b', 'c']) AS u(element);

SELECT u.element, u.position
FROM UNNEST(ARRAY['a', 'b', 'c']) WITH ORDINALITY AS u(element, position);

查询结果

element position
a 1
b 2
c 3