30、数据库维护与数据故事挖掘指南

transformer2023

于 2025-08-28 13:00:44 发布

阅读量73

点赞数

CC 4.0 BY-SA版权

分类专栏：用SQL讲述数据背后的故事文章标签： PostgreSQL 数据库维护数据故事挖掘

本文链接：https://blog.youkuaiyun.com/transformer2023/article/details/150989723

用SQL讲述数据背后的故事专栏收录该内容

31 篇文章 ¥499.90

订阅专栏¥69.90

会员秒杀 ¥9.9 重磅福利

超级会员免费看

数据库维护与数据故事挖掘指南

1. 数据库维护操作

1.1 重新加载PostgreSQL配置

在macOS或Linux系统上，可以使用以下命令重新加载PostgreSQL的配置：

pg_ctl reload -D '/path/to/data/directory/'

若要查找PostgreSQL数据目录的位置，可运行以下查询：

SHOW data_directory;

在Windows系统中， -D 参数后的路径需用双引号括起来；而在macOS或Linux系统中，则使用单引号。此命令需在系统的命令提示符中运行，而非在 psql 应用程序内。输入命令并按回车键后，若出现 “server signaled” 消息，则表示操作成功，此时设置文件将被重新加载，更改也会生效。不过，部分设置（如内存分配）需要重启服务器，若遇到这种情况，PostgreSQL会发出警告。

1.2 数据库备份与恢复

1.2.1 使用 `pg_dump` 备份数据库或表

pg_dump 是PostgreSQL的命令行工具，可用于创建包含数据库所有数据、用于重新创建表和其他数据库对象的SQL命令，以及将数据加载到表中的输出文件。也可以使用它仅保存数据库中选定的表。默认情况下， pg_dump 输出的是纯文本文件。

以下是备份分析数据库的命令：

pg_dump -d analysis -U user_name -Fc > analysis_backup.sql

此命令的参数说明如下：
- pg_dump ：命令起始。
- -d analysis ：指定要备份的数据库名为 analysis 。
- -U user_name ：指定用户名。
- -Fc ：指定以自定义的PostgreSQL压缩格式生成备份。
- > analysis_backup.sql ：将 pg_dump 的输出重定向到名为 analysis_backup.sql 的文本文件。若要将文件保存到当前终端提示符所在目录之外的目录，可在文件名前指定完整的目录路径。

执行该命令时，根据安装情况可能会出现密码提示，输入密码后，根据数据库大小，命令可能需要几分钟才能完成。操作过程中屏幕不会输出任何消息，完成后会返回新的命令提示符，此时当前目录下会出现名为 analysis_backup.sql 的文件。

若要将备份限制为匹配特定名称的一个或多个表，可使用 -t 参数，后跟单引号括起来的表名。例如，仅备份 train_rides 表的命令如下：

pg_dump -t 'train_rides' -d analysis -U user_name -Fc > train_backup.sql

1.2.2 使用 `pg_restore` 恢复数据库备份

使用 pg_dump 备份数据库后，可使用 pg_restore 实用程序轻松恢复。当将数据迁移到新服务器或升级到PostgreSQL的新版本时，可能需要恢复数据库。

以下是恢复分析数据库的命令（假设在分析数据库不存在的服务器上）：

pg_restore -C -d postgres -U user_name analysis_backup.sql

参数说明：
- pg_restore ：命令起始。
- -C ：告知实用程序在服务器上创建分析数据库（从备份文件中获取数据库名称）。
- -d postgres ：指定要连接的数据库名称。
- -U user_name ：指定用户名。

按回车键后恢复操作将开始，完成后可通过 psql 或 pgAdmin 查看恢复的数据库。

1.3 其他备份和恢复选项

pg_dump 可配置多个选项，用于包含或排除特定的数据库对象（如匹配名称模式的表），或指定输出格式。之前备份分析数据库时，使用了 -Fc 选项生成自定义的PostgreSQL压缩格式，该实用程序还支持其他格式选项，包括纯文本。更多详细信息可查看 pg_dump 完整文档，相应的恢复选项可查看 pg_restore 文档。

2. 数据故事挖掘流程

2.1 从问题出发

好奇心、直觉或偶然的运气常常能激发数据分析的想法。若善于观察周围环境，可能会注意到社区随时间的变化，并思考能否衡量这种变化。例如，当地房地产市场中，若看到比平时更多的 “出售” 标志，可能会提出以下问题：
- 今年的房屋销售量与去年相比是否有显著增加？
- 若有增加，增加了多少？
- 哪些社区受到影响？

这些问题为数据分析提供了绝佳机会。对于记者而言，可能会发现一个故事；对于企业经营者来说，可能会发现新的营销机会。同样，若推测所在行业出现某种趋势，通过数据分析进行确认可能会带来商业机会。例如，怀疑某产品的销售变得疲软，可通过数据分析来证实并相应地调整库存或营销工作。

需记录这些想法，并根据其潜在价值进行优先级排序。为满足好奇心而分析数据是可以的，但如果答案能使机构更高效或公司更盈利，那么这些问题就值得深入研究。

2.2 记录分析过程

在进行数据分析之前，应考虑如何使分析过程具有透明度和可重复性。为了保证可信度，组织内外部的人员都应能够重现分析工作。此外，要确保记录足够的信息，以便在搁置项目数周后仍能轻松继续。

记录工作没有固定的正确方法，以下是一些常见的方式：
- 记录研究笔记。
- 创建逐步的SQL查询，以便他人可以复制数据导入、清理和分析过程。
- 一些分析师将笔记和代码存储在文本文件中，另一些则使用版本控制系统（如GitHub）。

关键是创建自己的文档系统并始终如一地使用它。

2.3 收集数据

有了分析想法后，下一步是找到与趋势或问题相关的数据。如果所在组织已经有关于该主题的数据，那就很幸运了，可以利用内部的营销或销售数据库、客户关系管理（CRM）系统，或订阅者或活动注册数据。但如果主题涉及更广泛的问题（如人口统计、经济或特定行业的主题），则需要进行一些挖掘。

以下是一些收集数据的途径：
- 向专家询问他们使用的数据源，分析师、政府决策者和学者通常能指出可用的数据及其用途。
- 在美国，可查看联邦政府的数据目录网站 https://www.data.gov/ 或个别机构的网站（如国家教育统计中心 https://nces.ed.gov/ ）。
- 浏览当地政府网站，若看到用户填写的表单或格式为行和列的报告，可能意味着有结构化数据可用于分析。即使只能访问非结构化数据，也可以像处理文本文件一样进行挖掘。

如果要分析的数据是多年收集的，建议尽可能检查五年、十年或更长时间的数据，而不仅仅是一两年的数据。因为许多趋势是在较长时间内发展的，仅查看一年的数据可能无法发现这些趋势。

2.4 自建数据库

有时，可能找不到所需格式的数据。但如果有时间、耐心和方法，就可以构建自己的数据集。例如，为了研究美国大学校园中学生死亡的相关问题，通过研究新闻文章、警方报告和诉讼，收集了2000年至2005年600多起学生死亡的报告，并与教育专家、警察、学校官员和家长进行了访谈，将每个学生的年龄、学校、死因、年级以及是否涉及毒品或酒精等细节记录下来，构建了一个数据库。分析结果发表了文章 “In College, First Year Is by Far the Riskiest”，发现新生特别容易受到伤害，在研究的学生死亡案例中占比最高。

若缺乏所需数据，也可以创建自己的数据库，关键是确定重要的信息片段，然后系统地收集它们。

2.5 评估数据来源

确定数据集后，应尽可能了解其来源和维护方法。政府和机构收集数据的方式多种多样，有些方法产生的数据更可信、更标准化。

例如，USDA食品生产商数据中，同一家公司的名称可能有多种拼写方式，了解其原因（可能是数据从书面表格手动复制到计算机）很有必要。同样，纽约市出租车数据记录了每次行程的开始和结束时间，需要明确计时器是在乘客上车和下车时启动，还是有其他触发机制。了解这些细节不仅有助于从分析中得出更好的结论，还能将其传达给可能解释分析结果的其他人。

数据集的来源也可能影响数据分析和结果报告的方式。例如，美国人口普查数据中，每十年进行一次的十年一次人口普查是对人口的完整统计，而美国社区调查（ACS）仅从部分家庭样本中抽取数据。因此，ACS计数有误差范围，而十年一次人口普查没有。在报告ACS数据时，不考虑误差范围可能会导致数字之间的差异变得无意义。

2.6 用查询探索数据

获取数据、了解其来源并将其加载到数据库后，可使用查询来探索数据。可以从聚合操作开始，如计数、求和、排序和按列值分组，这些操作可以揭示数据的最小值和最大值、重复条目的潜在问题以及数据的大致范围。如果数据库包含多个相关表，可尝试使用连接操作，如 LEFT JOIN 和 RIGHT JOIN ，以确保理解表之间的关系。这有助于发现一个表中的键值是否在另一个表中缺失，从而识别可能需要解决的潜在问题。记录下问题或疑虑清单，然后进入下一步。

2.7 咨询数据所有者

探索数据库并对观察到的质量和趋势形成初步结论后，应花时间向熟悉数据的人提出问题或表达疑虑。这个人可能是提供数据的机构或公司的工作人员，也可能是之前处理过该数据的分析师。这一步有助于澄清对数据的理解、验证初步发现，并发现数据是否存在不适合需求的问题。

例如，查询表时若发现列中的值似乎是明显的异常值（如过去应该发生的事件的日期却在未来），应询问这种差异的原因。或者，若期望在表中找到某人的名字（甚至可能是自己的名字）却未找到，也应进一步询问是数据不完整还是数据收集存在问题。

咨询数据所有者的目标如下：
- 了解数据的限制，确保知道数据包含什么、排除什么以及内容的任何注意事项，这些可能会影响分析的执行方式。
- 确保拥有完整的数据集，验证是否拥有所有应看到的记录，若有数据缺失，要理解原因。
- 确定数据集是否适合需求，若数据源承认数据质量存在问题，可考虑寻找更可靠的数据。

每个数据集和情况都是独特的，但咨询数据的其他用户或所有者可以帮助避免不必要的错误。

综上所述，数据库维护和数据故事挖掘是数据工作中的重要环节。通过正确的维护操作可以确保数据库的性能和数据的安全性，而有效的数据故事挖掘流程则能帮助我们从数据中发现有价值的信息。希望这些指南能帮助你更好地处理数据，做出更明智的决策。

3. 数据库维护与数据挖掘流程总结

3.1 数据库维护操作总结

操作类型	操作命令	适用系统	参数说明
重新加载配置	`pg_ctl reload -D '/path/to/data/directory/'`	macOS 或 Linux	`-D` ：指定数据目录路径
重新加载配置	`pg_ctl reload -D "C:\path\to\data\directory"`	Windows	`-D` ：指定数据目录路径
备份数据库	`pg_dump -d analysis -U user_name -Fc > analysis_backup.sql`	通用	`-d` ：指定要备份的数据库名； `-U` ：指定用户名； `-Fc` ：指定自定义压缩格式； `>` ：重定向输出到文件
备份特定表	`pg_dump -t 'train_rides' -d analysis -U user_name -Fc > train_backup.sql`	通用	`-t` ：指定要备份的表名；其他同备份数据库
恢复数据库	`pg_restore -C -d postgres -U user_name analysis_backup.sql`	通用	`-C` ：创建数据库； `-d` ：指定连接的数据库名； `-U` ：指定用户名

下面是数据库维护操作的 mermaid 流程图：

graph LR
    A[开始] --> B[重新加载配置]
    B --> C{是否需重启服务器}
    C -- 是 --> D[重启服务器]
    C -- 否 --> E[备份数据库或表]
    E --> F[恢复数据库]
    F --> G[结束]
    D --> E

3.2 数据故事挖掘流程总结

步骤	操作内容
从问题出发	观察现象，提出问题，记录并按价值排序
记录分析过程	选择合适方式记录，保证可重复性
收集数据	从组织内部或外部获取相关数据，考虑时间跨度
自建数据库	在缺乏合适数据时，自行收集和整理
评估数据来源	了解数据收集方式和误差情况
用查询探索数据	使用聚合和连接操作发现数据问题
咨询数据所有者	澄清数据理解，验证发现，确定数据适用性

下面是数据故事挖掘流程的 mermaid 流程图：

graph LR
    A[开始] --> B[从问题出发]
    B --> C[记录分析过程]
    C --> D[收集数据]
    D --> E{是否有合适数据}
    E -- 是 --> F[评估数据来源]
    E -- 否 --> G[自建数据库]
    G --> F
    F --> H[用查询探索数据]
    H --> I[咨询数据所有者]
    I --> J[结束]

4. 操作示例与注意事项

4.1 数据库维护操作示例

4.1.1 重新加载配置

假设在 macOS 系统上，数据目录为 /var/lib/postgresql/13/main ，则重新加载配置的命令为：

pg_ctl reload -D '/var/lib/postgresql/13/main'

4.1.2 备份数据库

假设数据库名为 sales ，用户名为 admin ，备份文件存储在 /home/user/backups 目录下，命令如下：

pg_dump -d sales -U admin -Fc > /home/user/backups/sales_backup.sql

4.1.3 恢复数据库

假设在新服务器上恢复 sales 数据库，命令如下：

pg_restore -C -d postgres -U admin /home/user/backups/sales_backup.sql

4.2 数据故事挖掘操作示例

4.2.1 从问题出发

以电商行业为例，观察到某类商品近期销量下降，提出问题：
- 该类商品销量下降的幅度是多少？
- 哪些地区的销量下降最明显？
- 是哪些客户群体的购买量减少了？

4.2.2 收集数据

从电商平台的数据库中获取该类商品的销售数据，包括销售时间、销售地区、客户信息等。同时，收集竞争对手的相关数据和市场趋势数据。

4.2.3 用查询探索数据

以下是一些查询示例：

-- 计算该类商品的销量下降幅度
SELECT 
    (SUM(CASE WHEN sale_date >= '2024-01-01' THEN quantity ELSE 0 END) - 
     SUM(CASE WHEN sale_date < '2024-01-01' THEN quantity ELSE 0 END)) / 
    SUM(CASE WHEN sale_date < '2024-01-01' THEN quantity ELSE 0 END) AS sales_decrease_rate
FROM sales
WHERE product_category = '某类商品';

-- 找出销量下降最明显的地区
SELECT 
    region,
    SUM(CASE WHEN sale_date >= '2024-01-01' THEN quantity ELSE 0 END) - 
    SUM(CASE WHEN sale_date < '2024-01-01' THEN quantity ELSE 0 END) AS sales_decrease
FROM sales
WHERE product_category = '某类商品'
GROUP BY region
ORDER BY sales_decrease ASC
LIMIT 1;

4.3 注意事项

数据库维护 ：
- 备份和恢复操作前，确保数据库服务正常运行。
- 备份文件要妥善保存，防止丢失或损坏。
- 恢复数据库时，要确保目标服务器有足够的磁盘空间。
数据故事挖掘 ：
- 提出的问题要具有针对性和可分析性。
- 收集数据时要注意数据的准确性和完整性。
- 分析过程中要考虑数据的误差和局限性。

通过以上数据库维护和数据故事挖掘的操作指南及示例，希望能帮助大家更好地管理数据库和挖掘数据价值，做出更科学的决策。