> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-home-button.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # 重写 PostgreSQL 查询 > 从 PostgreSQL 迁移到 ClickHouse 指南的第 2 部分 > 这是从 PostgreSQL 迁移到 ClickHouse 指南的**第 2 部分**。本文通过一个实际示例，展示如何采用 CDC (变更数据捕获) 的实时复制方式高效完成迁移。文中介绍的许多概念也同样适用于将数据从 PostgreSQL 手动批量迁移到 ClickHouse。 你在 PostgreSQL 环境中的大多数 SQL 查询都应该无需修改即可在 ClickHouse 中运行，而且往往会执行得更快。
## 使用 CDC (变更数据捕获) 进行去重
使用 CDC (变更数据捕获) 进行实时复制时，请注意，更新和删除操作可能会导致出现重复行。为此，你可以采用基于 View 和可刷新 materialized view 的技术。 请参阅这份[指南](/zh/integrations/clickpipes/postgres/deduplication#query-like-with-postgres)，了解在使用 CDC (变更数据捕获) 进行实时复制迁移时，如何以尽可能小的改动将应用程序从 PostgreSQL 迁移到 ClickHouse。
## 优化 ClickHouse 中的查询
虽然只需极少的查询改写即可完成迁移，但仍建议充分利用 ClickHouse 的特性，以显著简化查询并进一步提升查询性能。 这里的示例涵盖了常见的查询模式，并展示了如何借助 ClickHouse 对其进行优化。这些示例基于 PostgreSQL 和 ClickHouse 的同等资源配置 (8 核、32GiB RAM) ，并使用完整的 [Stack Overflow 数据集](/zh/get-started/sample-datasets/stackoverflow) (截至 2024 年 4 月) 。 > 为简化说明，下面的查询省略了数据去重相关技术。 > 此处的计数结果会略有差异，因为 Postgres 中的数据只包含满足外键引用完整性的行。ClickHouse 不施加这类约束，因此包含完整数据集，例如也包括匿名用户。 浏览量最高的用户 (提问数超过 10 个) ： ```sql theme={null} -- ClickHouse SELECT OwnerDisplayName, sum(ViewCount) AS total_views FROM stackoverflow.posts WHERE (PostTypeId = 'Question') AND (OwnerDisplayName != '') GROUP BY OwnerDisplayName HAVING count() > 10 ORDER BY total_views DESC LIMIT 5 ``` ```response theme={null} ┌─OwnerDisplayName─┬─total_views─┐ │ Joan Venge │ 25520387 │ │ Ray Vega │ 21576470 │ │ anon │ 19814224 │ │ Tim │ 19028260 │ │ John │ 17638812 │ └──────────────────┴─────────────┘ 5 rows in set. Elapsed: 0.360 sec. Processed 24.37 million rows, 140.45 MB (67.73 million rows/s., 390.38 MB/s.) 峰值内存占用: 510.71 MiB. ``` ```sql theme={null} --Postgres SELECT OwnerDisplayName, SUM(ViewCount) AS total_views FROM public.posts WHERE (PostTypeId = 1) AND (OwnerDisplayName != '') GROUP BY OwnerDisplayName HAVING COUNT(*) > 10 ORDER BY total_views DESC LIMIT 5; ownerdisplayname | total_views -------------------------+------------- Joan Venge | 25520387 Ray Vega | 21576470 Tim | 18283579 J. Pablo Fernández | 12446818 Matt | 12298764 Time: 107620.508 ms (01:47.621) ``` 哪些 `tags` 的 `views` 最多： ```sql theme={null} --ClickHouse SELECT arrayJoin(arrayFilter(t -> (t != ''), splitByChar('|', Tags))) AS tags, sum(ViewCount) AS views FROM posts GROUP BY tags ORDER BY views DESC LIMIT 5 ``` ```response theme={null} ┌─tags───────┬──────views─┐ │ javascript │ 8190916894 │ │ python │ 8175132834 │ │ java │ 7258379211 │ │ c# │ 5476932513 │ │ android │ 4258320338 │ └────────────┴────────────┘ 5 rows in set. Elapsed: 0.908 sec. Processed 59.82 million rows, 1.45 GB (65.87 million rows/s., 1.59 GB/s.) ``` ```sql theme={null} --Postgres WITH tags_exploded AS ( SELECT unnest(string_to_array(Tags, '|')) AS tag, ViewCount FROM public.posts ), filtered_tags AS ( SELECT tag, ViewCount FROM tags_exploded WHERE tag <> '' ) SELECT tag AS tags, SUM(ViewCount) AS views FROM filtered_tags GROUP BY tag ORDER BY views DESC LIMIT 5; tags | views ------------+------------ javascript | 7974880378 python | 7972340763 java | 7064073461 c# | 5308656277 android | 4186216900 (5 rows) Time: 112508.083 ms (01:52.508) ``` **聚合函数** 在条件允许的情况下，建议优先使用 ClickHouse 聚合函数。下面展示如何使用 [argMax](/zh/reference/functions/aggregate-functions/argMax) 函数来计算每年浏览量最高的问题。 ```sql theme={null} --ClickHouse SELECT toYear(CreationDate) AS Year, argMax(Title, ViewCount) AS MostViewedQuestionTitle, max(ViewCount) AS MaxViewCount FROM stackoverflow.posts WHERE PostTypeId = 'Question' GROUP BY Year ORDER BY Year ASC FORMAT Vertical ``` ```response theme={null} Row 1: ────── Year: 2008 MostViewedQuestionTitle: How to find the index for a given item in a list? MaxViewCount: 6316987 Row 2: ────── Year: 2009 MostViewedQuestionTitle: How do I undo the most recent local commits in Git? MaxViewCount: 13962748 ... Row 16: ─────── Year: 2023 MostViewedQuestionTitle: How do I solve "error: externally-managed-environment" every time I use pip 3? MaxViewCount: 506822 Row 17: ─────── Year: 2024 MostViewedQuestionTitle: Warning "Third-party cookie will be blocked. Learn more in the Issues tab" MaxViewCount: 66975 17 rows in set. Elapsed: 0.677 sec. Processed 24.37 million rows, 1.86 GB (36.01 million rows/s., 2.75 GB/s.) 峰值内存占用: 554.31 MiB. ``` 相比等效的 Postgres 查询，这要简单得多 (也快得多) ： ```sql theme={null} --Postgres WITH yearly_views AS ( SELECT EXTRACT(YEAR FROM CreationDate) AS Year, Title, ViewCount, ROW_NUMBER() OVER (PARTITION BY EXTRACT(YEAR FROM CreationDate) ORDER BY ViewCount DESC) AS rn FROM public.posts WHERE PostTypeId = 1 ) SELECT Year, Title AS MostViewedQuestionTitle, ViewCount AS MaxViewCount FROM yearly_views WHERE rn = 1 ORDER BY Year; year | mostviewedquestiontitle | maxviewcount ------+-----------------------------------------------------------------------------------------------------------------------+-------------- 2008 | How to find the index for a given item in a list? | 6316987 2009 | How do I undo the most recent local commits in Git? | 13962748 ... 2023 | How do I solve "error: externally-managed-environment" every time I use pip 3? | 506822 2024 | Warning "Third-party cookie will be blocked. Learn more in the Issues tab" | 66975 (17 rows) Time: 125822.015 ms (02:05.822) ``` **条件函数与数组** 条件函数和数组函数可以让查询大为简化。以下查询会找出从 2022 年到 2023 年百分比增幅最大的标签 (出现次数超过 10000 次) 。请注意，下面这个 ClickHouse 查询之所以如此简洁，是因为它利用了条件函数、数组函数，以及在 HAVING 和 SELECT 子句中复用别名的能力。 ```sql theme={null} --ClickHouse SELECT arrayJoin(arrayFilter(t -> (t != ''), splitByChar('|', Tags))) AS tag, countIf(toYear(CreationDate) = 2023) AS count_2023, countIf(toYear(CreationDate) = 2022) AS count_2022, ((count_2023 - count_2022) / count_2022) * 100 AS percent_change FROM stackoverflow.posts WHERE toYear(CreationDate) IN (2022, 2023) GROUP BY tag HAVING (count_2022 > 10000) AND (count_2023 > 10000) ORDER BY percent_change DESC LIMIT 5 ``` ```response theme={null} ┌─tag─────────┬─count_2023─┬─count_2022─┬──────percent_change─┐ │ next.js │ 13788 │ 10520 │ 31.06463878326996 │ │ spring-boot │ 16573 │ 17721 │ -6.478189718413183 │ │ .net │ 11458 │ 12968 │ -11.644046884639112 │ │ azure │ 11996 │ 14049 │ -14.613139725247349 │ │ docker │ 13885 │ 16877 │ -17.72826924216389 │ └─────────────┴────────────┴────────────┴─────────────────────┘ 5 rows in set. Elapsed: 0.247 sec. Processed 5.08 million rows, 155.73 MB (20.58 million rows/s., 630.61 MB/s.) 峰值内存占用: 403.04 MiB. ``` ```sql theme={null} --Postgres SELECT tag, SUM(CASE WHEN year = 2023 THEN count ELSE 0 END) AS count_2023, SUM(CASE WHEN year = 2022 THEN count ELSE 0 END) AS count_2022, ((SUM(CASE WHEN year = 2023 THEN count ELSE 0 END) - SUM(CASE WHEN year = 2022 THEN count ELSE 0 END)) / SUM(CASE WHEN year = 2022 THEN count ELSE 0 END)::float) * 100 AS percent_change FROM ( SELECT unnest(string_to_array(Tags, '|')) AS tag, EXTRACT(YEAR FROM CreationDate) AS year, COUNT(*) AS count FROM public.posts WHERE EXTRACT(YEAR FROM CreationDate) IN (2022, 2023) AND Tags <> '' GROUP BY tag, year ) AS yearly_counts GROUP BY tag HAVING SUM(CASE WHEN year = 2022 THEN count ELSE 0 END) > 10000 AND SUM(CASE WHEN year = 2023 THEN count ELSE 0 END) > 10000 ORDER BY percent_change DESC LIMIT 5; tag | count_2023 | count_2022 | percent_change -------------+------------+------------+--------------------- next.js | 13712 | 10370 | 32.22757955641273 spring-boot | 16482 | 17474 | -5.677005837243905 .net | 11376 | 12750 | -10.776470588235295 azure | 11938 | 13966 | -14.520979521695546 docker | 13832 | 16701 | -17.178612059158134 (5 rows) Time: 116750.131 ms (01:56.750) ``` [点击此处前往第 3 部分](/zh/get-started/migrate/postgres/migration-guide/migration-guide-part3)