4、LLM微调生成高级SQL查询
引言
在日常的数据分析工作中，我们频繁面临对复杂数据指标进行计算的任务，其中包括环比、同比、占比、累计、以及累计占比等。这些计算通常基于星型和雪花模型构建的数据仓库，而我们当前的案例中选用的是TPC-DS数据集便其采用了雪花模型。
执行这些指标计算时，我们需要撰写SQL查询，这些查询牵涉到复杂的多表联接、聚合操作、子查询、窗口函数以及繁琐的过滤条件。这一过程一方面要求对数据仓库的元数据及关联模型有深刻的理解，另一方面则要求数据分析专业人员具备高级的SQL编程技能，以便有效地处理和分析数据。
此篇文章将基于TPC-DS数据集进行一系列复杂指标的计算，旨在验证大型语言模型（LLM）在生成复杂查询方面的性能和效果。同时，我们还将探讨少样本提示词技术，通过对模型进行微调，以便更精准地生成符合特定要求的SQL语句。
零样本学习（Zero-Shot Learning）
在数据分析时，深刻理解元数据和数据模型（Schema）具有重要意义。这种理解主要包括对元数据的字段业务口径和关联模型的全面把握。这不仅确保了指标计算的准确性，还有助于更深入地理解数据的含义和关系，从而提高了数据分析和解释的效力。
到目前为止我们一直采用零样本学习（Zero-Shot Learning） 的方 式，即直接向ChatGPT提出问题而未提供任何提示，完全依赖ChatGPT对数据库DDL的理解。零样本学习的方法通过评估模型对未见过数据的泛化能力，来测试模型在不同任务上的适应性。
接下来，我们将尝试向ChatGPT提出更为复杂的问题，以深入测试大型语言模型（LLM）对数据库模式（Schema）的理解程度。完整的TPC-DS的数据模型可以阅读TPC-DS数据库Schema。这样的测试将考察模型是否能够在更复杂、抽象的数据库结构中给出我们所期望的精准答案。
db_chain.invoke("统计每年的销售额");
> Entering new SQLDatabaseChain chain...
统计每年的销售额
SQLQuery:SELECT
d.d_year,
SUM(cs.cs_sales_price) AS total_sales
FROM
date_dim d
JOIN
catalog_sales cs ON d.d_date_sk = cs.cs_sold_date_sk
GROUP BY
d.d_year;
SQLResult: [(1998, Decimal('154931.95')), (1999, Decimal('156070.09')), (2000, Decimal('171209.92')), (2001, Decimal('158277.49')), (2002, Decimal('145220.29'))]
Answer:1998: $154,931.95
1999: $156,070.09
2000: $171,209.92
2001: $158,277.49
2002: $145,220.29
> Finished chain.
以下是LangChain返回结果的分析。我们可以观察到ChatGPT正确地理解了事实表catalog_sales和维度表date_dim之间的关系。然而，在返回的信息中存在一些问题：
- 选择的源表不正确
ChatGPT选取了catalog_sales作为源表，而我们预期得到的 总销售额的应该包括catalog_sales、store_sales以及web_sales三个渠道的销售额之和。这是一个重要的纰漏，我们需要确保在计算总销售额时涵盖所有相关的渠道。 - 选择的源字段不正确
在选择源字段时，ChatGPT使用了cs_sales_price，该字段代表商品的总销售价格而非实际发生的销售金额。正确的字段应该是涉及实际销售发生的金额，以确保计算的准确性。 - 存在幻觉情况
在多次提交相同问题后，我们观察到ChatGPT给出的答案并不一致。源表和源字段的选择会发生变化，甚至会出现虚构字段的情况，这被描述为大模型的幻觉。这可能是由于模型在处理某些问题时存在随机性，导致结果的不稳定性。
少样本学习
少样本学习（Few-shot Learning） 是一项提示词技术，其核心旨在解决在极为有限的数据条件下训练机器学习模型的挑战。此技术的根本目的是使模型能够在只接触到极少量训练样本的情况下，仍能有效地学习并具备良好的泛化能力。这与传统机器学习方法形成鲜明对比，后者通常依赖于大量的数据来训练模型，以实现可靠的性能。少样本学习特别关注于克服数据稀缺的挑战，它要求模型能够从有限的样本中提取出对完成特定任务至关重要的知识和模式。
在进行大型语言模型（LLM）自动生成SQL查询任务时，不仅需要确保模型准确地识别和选择适当的源表和字段，还需要处理数据加工逻辑，并确保生成的SQL代码符合特定的格式规范。举例来说，模型应 当避免生成包含嵌套查询的代码，而应该优先采用WITH子句，以提升代码的可读性，使得后续的调试或错误定位更为便捷。
因此，在应用少样本学习技术来解决这类自动生成SQL查询任务时，我们期望模型不仅能够从有限的示例中快速学习到如何正确选择数据源和加工逻辑，还应该能够掌握并遵循一系列编码规范，以确保输出的查询结果既清晰又一致。下面是一个简单的示例：
用户输入：统计销售笔数，总金额
SQL查询：WITH ... AS (...) SELECT ... FROM ... LEFT JOIN ... GROUP BY ...
用户输入：统计每年、每个渠道的销售笔数，总金额
SQL查询：SELECT ... FROM ... LEFT JOIN ... GROUP BY ...
用户输入：2002年销售额排名前10的品牌名称
SQL查询：......
用户输入：统计每年、每个渠道的销售笔数，总金额
SQL查询：......
NLP2SQL微调(Fine-Tuning)
示例说明
在本研究案例中，我们针对ChatGPT在SQL生成任务上进行了少样本的微调。通过向ChatGPT输入一系列SQL示例，我们旨在提升其在生成多样化统计查询SQL方面的能力。这些SQL示例范围具体包括：
-
原子指标统计
这部分的核心在于让ChatGPT学会如何精确地选取事实表和基础字段（例如金额字段），以确保生成的SQL语句能够准确地反映出所需的数据指标。 -
复合指标统计
基于原子指标统计之上，复合指标统计的学习挑战在于引入了更为复杂的SQL特性，比如窗口分析函数等，以便计算更高级的衡量指标，如各类占比、环比和同比数据。 -
多维度统计
此部分的示例旨在扩展ChatGPT在数据分析上的深度和广度，通过引入对数据的多维度分析来实现。这要求ChatGPT具备对数据仓库的关系模型有深入理解，能够灵活地将查询扩展到多个维度，如时间（年、月）、销售渠道等，从而实现更加全面和深入的数据分析。
SQL示例
在以下SQL示例中，我们向ChatGPT展示了如何使用SQL来进行基本指标的统计，包括销售笔数和销售金额。此外，我们还展示了如何通过多个维度，如日期、品牌和渠道等，进行多维分析的SQL查询。
用户输入：统计销售笔数，总金额
SQL查询：
WITH sales AS (
SELECT cs_bill_customer_sk AS customer_sk,
cs_order_number AS order_number,
'catalog' AS channel,
cs_sold_date_sk AS sold_date_sk,
cs_sold_time_sk AS sold_time_sk,
cs_item_sk AS item_sk,
cs_net_paid_inc_ship_tax AS net_paid
FROM catalog_sales
UNION ALL
SELECT ss_customer_sk AS customer_sk,
ss_ticket_number AS order_number,
'store' AS channel,
ss_sold_date_sk AS sold_date_sk,
ss_sold_time_sk AS sold_time_sk,
ss_item_sk AS item_sk,
ss_net_paid_inc_tax AS net_paid
FROM store_sales
UNION ALL
SELECT ws_bill_customer_sk AS customer_sk,
ws_order_number AS order_number,
'web' AS channel,
ws_sold_date_sk AS sold_date_sk,
ws_sold_time_sk AS sold_time_sk,
ws_item_sk AS item_sk,
ws_net_paid_inc_tax AS net_paid
FROM web_sales
)
SELECT COUNT(DISTINCT order_number), SUM(net_paid) FROM sales
用户输入：统计每年、每个渠道的销售笔数，总金额
SQL查询：
WITH sales AS (
SELECT cs_bill_customer_sk AS customer_sk,
cs_order_number AS order_number,
'catalog' AS channel,
cs_sold_date_sk AS sold_date_sk,
cs_sold_time_sk AS sold_time_sk,
cs_item_sk AS item_sk,
cs_net_paid_inc_ship_tax AS net_paid
FROM catalog_sales
UNION ALL
SELECT ss_customer_sk AS customer_sk,
ss_ticket_number AS order_number,
'store' AS channel,
ss_sold_date_sk AS sold_date_sk,
ss_sold_time_sk AS sold_time_sk,
ss_item_sk AS item_sk,
ss_net_paid_inc_tax AS net_paid
FROM store_sales
UNION ALL
SELECT ws_bill_customer_sk AS customer_sk,
ws_order_number AS order_number,
'web' AS channel,
ws_sold_date_sk AS sold_date_sk,
ws_sold_time_sk AS sold_time_sk,
ws_item_sk AS item_sk,
ws_net_paid_inc_tax AS net_paid
FROM web_sales
)
SELECT dd.d_year, s.channel, COUNT(DISTINCT order_number), SUM(net_paid)
FROM sales s
LEFT JOIN date_dim dd ON s.sold_date_sk = dd.d_date_sk
GROUP BY s.channel
用户输入：请给出2002年销售额排名前10的品牌名称
SQL查询：
WITH sales AS (
SELECT cs_bill_customer_sk AS customer_sk,
cs_order_number AS order_number,
'catalog' AS channel,
cs_sold_date_sk AS sold_date_sk,
cs_sold_time_sk AS sold_time_sk,
cs_item_sk AS item_sk,
cs_net_paid_inc_ship_tax AS net_paid
FROM catalog_sales
UNION ALL
SELECT ss_customer_sk AS customer_sk,
ss_ticket_number AS order_number,
'store' AS channel,
ss_sold_date_sk AS sold_date_sk,
ss_sold_time_sk AS sold_time_sk,
ss_item_sk AS item_sk,
ss_net_paid_inc_tax AS net_paid
FROM store_sales
UNION ALL
SELECT ws_bill_customer_sk AS customer_sk,
ws_order_number AS order_number,
'web' AS channel,
ws_sold_date_sk AS sold_date_sk,
ws_sold_time_sk AS sold_time_sk,
ws_item_sk AS item_sk,
ws_net_paid_inc_tax AS net_paid
FROM web_sales
)
SELECT dd.d_year, i.i_brand, SUM(net_paid)
FROM sales s
LEFT JOIN item i ON s.item_sk = i.i_item_sk
LEFT JOIN date_dim dd ON s.sold_date_sk = dd.d_date_sk
WHERE dd.d_year = 2002
GROUP BY i.i_brand
ORDER BY SUM(net_paid) DESC
LIMIT 10
在上述多表联接和聚合操作的基础上，我们进一步扩展了基于窗口分析函数的占比和环比的统计指标示例。这一拓展旨在确保ChatGPT支持更为复杂的SQL生成，以应对用户在数据分析任务中更高级、多样化的需求。通过引入这些更复杂的统计指标示例，我们的目标是进一步提升模型的性能，使其能够更好地应对用户对于SQL查询的复杂性和多样性的需求。
用户输入：统计每年每个渠道的销售额、销售额占比
SQL查询：
WITH sales AS (
SELECT cs_bill_customer_sk AS customer_sk,
cs_order_number AS order_number,
'catalog' AS channel,
cs_sold_date_sk AS sold_date_sk,
cs_sold_time_sk AS sold_time_sk,
cs_item_sk AS item_sk,
cs_net_paid_inc_tax AS net_paid
FROM catalog_sales
UNION ALL
SELECT ss_customer_sk AS customer_sk,
ss_ticket_number AS order_number,
'store' AS channel,
ss_sold_date_sk AS sold_date_sk,
ss_sold_time_sk AS sold_time_sk,
ss_item_sk AS item_sk,
ss_net_paid_inc_tax AS net_paid
FROM store_sales
UNION ALL
SELECT ws_bill_customer_sk AS customer_sk,
ws_order_number AS order_number,
'web' AS channel,
ws_sold_date_sk AS sold_date_sk,
ws_sold_time_sk AS sold_time_sk,
ws_item_sk AS item_sk,
ws_net_paid_inc_tax AS net_paid
FROM web_sales
),
sales_channel AS (
SELECT dd.d_year,
s.channel,
SUM(net_paid) AS sales
FROM sales s
LEFT JOIN date_dim dd ON s.sold_date_sk = dd.d_date_sk
GROUP BY dd.d_year,s.channel
ORDER BY dd.d_year,s.channel
)
SELECT d_year,
channel,
sales,
sales * 100 / SUM(sales) OVER(PARTITION BY d_year)
FROM sales_channel
用户输入：统计每年销售额、销售额环比
SQL查询：
WITH sales AS (
SELECT cs_bill_customer_sk AS customer_sk,
cs_order_number AS order_number,
'catalog' AS channel,
cs_sold_date_sk AS sold_date_sk,
cs_sold_time_sk AS sold_time_sk,
cs_item_sk AS item_sk,
cs_net_paid_inc_tax AS net_paid
FROM catalog_sales
UNION ALL
SELECT ss_customer_sk AS customer_sk,
ss_ticket_number AS order_number,
'store' AS channel,
ss_sold_date_sk AS sold_date_sk,
ss_sold_time_sk AS sold_time_sk,
ss_item_sk AS item_sk,
ss_net_paid_inc_tax AS net_paid
FROM store_sales
UNION ALL
SELECT ws_bill_customer_sk AS customer_sk,
ws_order_number AS order_number,
'web' AS channel,
ws_sold_date_sk AS sold_date_sk,
ws_sold_time_sk AS sold_time_sk,
ws_item_sk AS item_sk,
ws_net_paid_inc_tax AS net_paid
FROM web_sales
)
SELECT dd.d_year,
SUM(net_paid) AS sales_year,
(SUM(net_paid)
-
LAG(SUM(net_paid), 1, 0) OVER (ORDER BY dd.d_year)) * 100
/
LAG(SUM(net_paid), 1, 0) OVER (ORDER BY dd.d_year) AS sales_mom
FROM sales s
LEFT JOIN date_dim dd ON s.sold_date_sk = dd.d_date_sk
GROUP BY dd.d_year
ORDER BY dd.d_year
输出示例
同时，我们对ChatGPT的统计输出格式进行了规范化，包括表格的呈现方式以及数字的格式化要求，旨在更便于用户阅读和理解。
统计输出格式要求为表格, 指标订单笔数和金额要求格式化，表格表头为中文，示例如下：
| 年份 | 销售额 | 销售额 |
| -------- | ------- |------- |
| 2000 | 100 |$250,000 |
| 2001 | 200 |$80,000 |
代码实现
在此，我们将深入探讨代码实现的细节，具体实现步骤如下：
- 读取SQL样本示例： 首先，从文件中读取SQL样本示例用于ChatGPT模型微调。
- 构建自定义提示词模板： 在此我们未采用SQLDatabaseChain的默认提示词模板，我们选择设计一个自定义的提示词模板。在提示词模版中加入了SQL样本示例以及期望的输出示例。
- 部分填充提示词模板： 接下来，我们将SQL样本示例部分填充进提示词模板中。
- 创建SQLDatabaseChain实例： 在这一步骤中，我们将创建一个SQLDatabaseChain实例。同时提供几个关键参数：一个大型语言模型（LLM）对象， 一个SQLDatabase对象，启用输出调试信息（设为True），以及之前构建的自定义提示词模板。
通过上述步骤，我们现在拥有了一个配置完善的db_chain对象，可以利用它来执行数据库问答分析。
# 读取SQL示例
with open('few-shot-examples.sql', 'r') as file:
examples = file.read()
# 创建自定义提示词模版
template="""You are a MySQL expert. Given an input question, first create a syntactically correct MySQL query to run, then look at the results of the query and return the answer to the input question.
Please carefully refer to the provided SQL examples to generate SQL query. Unless the user specifies in the question a specific number of examples to obtain, query for at most {top_k} results using the LIMIT clause as per MySQL. You can order the results to return the most informative data in the database.
Never query for all columns from a table. You must query only the columns that are needed to answer the question. Wrap each column name in backticks (\`) to denote them as delimited identifiers.
Pay attention to use only the column names you can see in the tables below. Be careful to not query for columns that do not exist. Also, pay attention to which column is in which table.
Use the following format:
Question: "Question here"
SQLQuery: "SQL Query to run"
SQLResult: "Result of the SQLQuery"
Answer: "Final answer here"
Only use the following tables:
{table_info}
下面是一些用户输入的问题和对应的查询示例：
{examples}
统计输出格式要求为表格, 指标订单笔数和金额要求格式化，表格表头为中文，示例如下：
| 年份 | 销售额 | 销售额 |
| -------- | ------- |------- |
| 2000 | 100 |$250,000 |
| 2001 | 200 |$80,000 |
Question: {input}
"""
prompt = PromptTemplate(
input_variables=["input", "table_info", "examples", "top_k"], template=template
)
# 创建新的提示词对象
few_shot_prompt = prompt.partial(examples=examples)
# db工具
db = SQLDatabase.from_uri(database_uri)
# llm
llm = ChatOpenAI(openai_api_key=openai_api_key,openai_api_base=openai_api_base)
db_chain = SQLDatabaseChain.from_llm(llm, db, verbose=True, prompt=few_shot_prompt)
db_chain.invoke("提问：统计2002年每月销售笔数和销售额")
运行结果
下面我们将展示经过我们微调后生成的自动生成SQL的效果，分别通过三个提问案例来展示。
- 提问：统计2002年每月销售笔数和销售额
提问：统计2002年每月销售笔数，销售额
SQLQuery:WITH sales AS (
SELECT cs_bill_customer_sk AS customer_sk,
cs_order_number AS order_number,
'catalog' AS channel,
cs_sold_date_sk AS sold_date_sk,
cs_sold_time_sk AS sold_time_sk,
cs_item_sk AS item_sk,
cs_net_paid_inc_tax AS net_paid
FROM catalog_sales
UNION ALL
SELECT ss_customer_sk AS customer_sk,
ss_ticket_number AS order_number,
'store' AS channel,
ss_sold_date_sk AS sold_date_sk,
ss_sold_time_sk AS sold_time_sk,
ss_item_sk AS item_sk,
ss_net_paid_inc_tax AS net_paid
FROM store_sales
UNION ALL
SELECT ws_bill_customer_sk AS customer_sk,
ws_order_number AS order_number,
'web' AS channel,
ws_sold_date_sk AS sold_date_sk,
ws_sold_time_sk AS sold_time_sk,
ws_item_sk AS item_sk,
ws_net_paid_inc_tax AS net_paid
FROM web_sales
)
SELECT dd.d_year, dd.d_moy, COUNT(DISTINCT order_number) AS 订单笔数, FORMAT(SUM(net_paid), 2) AS 销售额
FROM sales s
LEFT JOIN date_dim dd ON s.sold_date_sk = dd.d_date_sk
WHERE dd.d_year = 2002
GROUP BY dd.d_year, dd.d_moy
ORDER BY dd.d_year, dd.d_moy;
SQLResult: [(2002, 1, 798, '1,676,044.95'), (2002, 2, 590, '1,546,101.02'), (2002, 3, 651, '1,393,923.28'), (2002, 4, 575, '1,428,323.01'), (2002, 5, 648, '1,451,680.80'), (2002, 6, 618, '1,317,430.29'), (2002, 7, 614, '1,506,504.06'), (2002, 8, 1416, '3,618,673.06'), (2002, 9, 1425, '3,615,857.00'), (2002, 10, 1446, '3,653,730.67'), (2002, 11, 2091, '4,795,306.40'), (2002, 12, 2198, '5,686,245.27')]
Answer:| 年份 | 月份 | 订单笔数 | 销售额 |
| ---- | ---- | ------- |------- |
| 2002 | 1 | 798 |$1,676,044.95 |
| 2002 | 2 | 590 |$1,546,101.02 |
| 2002 | 3 | 651 |$1,393,923.28 |
| 2002 | 4 | 575 |$1,428,323.01 |
| 2002 | 5 | 648 |$1,451,680.80 |
| 2002 | 6 | 618 |$1,317,430.29 |
| 2002 | 7 | 614 |$1,506,504.06 |
| 2002 | 8 | 1416 |$3,618,673.06 |
| 2002 | 9 | 1425 |$3,615,857.00 |
| 2002 | 10 | 1446 |$3,653,730.67 |
| 2002 | 11 | 2091 |$4,795,306.40 |
| 2002 | 12 | 2198 |$5,686,245.27 |
> Finished chain.
- 提问：统计2002年每月销售额、销售额占比
提问：统计2002年每月销售额、销售额占比
SQLQuery:WITH sales AS (
SELECT cs_bill_customer_sk AS customer_sk,
cs_order_number AS order_number,
'catalog' AS channel,
cs_sold_date_sk AS sold_date_sk,
cs_sold_time_sk AS sold_time_sk,
cs_item_sk AS item_sk,
cs_net_paid_inc_tax AS net_paid
FROM catalog_sales
UNION ALL
SELECT ss_customer_sk AS customer_sk,
ss_ticket_number AS order_number,
'store' AS channel,
ss_sold_date_sk AS sold_date_sk,
ss_sold_time_sk AS sold_time_sk,
ss_item_sk AS item_sk,
ss_net_paid_inc_tax AS net_paid
FROM store_sales
UNION ALL
SELECT ws_bill_customer_sk AS customer_sk,
ws_order_number AS order_number,
'web' AS channel,
ws_sold_date_sk AS sold_date_sk,
ws_sold_time_sk AS sold_time_sk,
ws_item_sk AS item_sk,
ws_net_paid_inc_tax AS net_paid
FROM web_sales
)
SELECT dd.d_year AS 年份,
dd.d_moy AS 月份,
SUM(net_paid) AS 销售额,
SUM(net_paid) * 100 / SUM(SUM(net_paid)) OVER(PARTITION BY dd.d_year) AS 销售额占比
FROM sales s
LEFT JOIN date_dim dd ON s.sold_date_sk = dd.d_date_sk
WHERE dd.d_year = 2002
GROUP BY dd.d_year, dd.d_moy
ORDER BY dd.d_moy;
SQLResult: [(2002, 1, Decimal('1676044.95'), Decimal('5.288907')), (2002, 2, Decimal('1546101.02'), Decimal('4.878857')), (2002, 3, Decimal('1393923.28'), Decimal('4.398647')), (2002, 4, Decimal('1428323.01'), Decimal('4.507198')), (2002, 5, Decimal('1451680.80'), Decimal('4.580906')), (2002, 6, Decimal('1317430.29'), Decimal('4.157267')), (2002, 7, Decimal('1506504.06'), Decimal('4.753905')), (2002, 8, Decimal('3618673.06'), Decimal('11.419040')), (2002, 9, Decimal('3615857.00'), Decimal('11.410153')), (2002, 10, Decimal('3653730.67'), Decimal('11.529667')), (2002, 11, Decimal('4795306.40'), Decimal('15.132009')), (2002, 12, Decimal('5686245.27'), Decimal('17.943445'))]
Answer:| 年份 | 月份 | 销售额 | 销售额占比 |
| ---- | ---- | ----- | --------- |
| 2002 | 1 | $1,676,044.95 | 5.29% |
| 2002 | 2 | $1,546,101.02 | 4.88% |
| 2002 | 3 | $1,393,923.28 | 4.40% |
| 2002 | 4 | $1,428,323.01 | 4.51% |
| 2002 | 5 | $1,451,680.80 | 4.58% |
| 2002 | 6 | $1,317,430.29 | 4.16% |
| 2002 | 7 | $1,506,504.06 | 4.75% |
| 2002 | 8 | $3,618,673.06 | 11.42% |
| 2002 | 9 | $3,615,857.00 | 11.41% |
| 2002 | 10 | $3,653,730.67 | 11.53% |
| 2002 | 11 | $4,795,306.40 | 15.13% |
| 2002 | 12 | $5,686,245.27 | 17.94% |
> Finished chain.
- 提问：统计2002年12月每日销售额、以及销售额环比
提问 ：统计2002年12月每日销售额、以及销售额环比
SQLQuery:WITH sales AS (
SELECT cs_bill_customer_sk AS customer_sk,
cs_order_number AS order_number,
'catalog' AS channel,
cs_sold_date_sk AS sold_date_sk,
cs_sold_time_sk AS sold_time_sk,
cs_item_sk AS item_sk,
cs_net_paid_inc_ship_tax AS net_paid
FROM catalog_sales
UNION ALL
SELECT ss_customer_sk AS customer_sk,
ss_ticket_number AS order_number,
'store' AS channel,
ss_sold_date_sk AS sold_date_sk,
ss_sold_time_sk AS sold_time_sk,
ss_item_sk AS item_sk,
ss_net_paid_inc_tax AS net_paid
FROM store_sales
UNION ALL
SELECT ws_bill_customer_sk AS customer_sk,
ws_order_number AS order_number,
'web' AS channel,
ws_sold_date_sk AS sold_date_sk,
ws_sold_time_sk AS sold_time_sk,
ws_item_sk AS item_sk,
ws_net_paid_inc_tax AS net_paid
FROM web_sales
)
SELECT dd.d_date AS 年份, SUM(net_paid) AS 销售额,
(SUM(net_paid) - LAG(SUM(net_paid), 1, 0) OVER (ORDER BY dd.d_date)) * 100
/ LAG(SUM(net_paid), 1, 0) OVER (ORDER BY dd.d_date) AS 销售额环比
FROM sales s
LEFT JOIN date_dim dd ON s.sold_date_sk = dd.d_date_sk
WHERE dd.d_date >= '2002-12-01' AND dd.d_date <= '2002-12-31'
GROUP BY dd.d_date
ORDER BY dd.d_date;
SQLResult: [(datetime.date(2002, 12, 1), Decimal('223123.42'), None), (datetime.date(2002, 12, 2), Decimal('205996.16'), Decimal('-7.676137')), (datetime.date(2002, 12, 3), Decimal('76986.41'), Decimal('-62.627260')), (datetime.date(2002, 12, 4), Decimal('114089.62'), Decimal('48.194493')), (datetime.date(2002, 12, 5), Decimal('188652.99'), Decimal('65.355087')), (datetime.date(2002, 12, 6), Decimal('115109.34'), Decimal('-38.983559')), (datetime.date(2002, 12, 7), Decimal('246017.57'), Decimal('113.725116')), (datetime.date(2002, 12, 8), Decimal('462001.98'), Decimal('87.792270')), (datetime.date(2002, 12, 9), Decimal('163052.97'), Decimal('-64.707301')), (datetime.date(2002, 12, 10), Decimal('180133.56'), Decimal('10.475485')), (datetime.date(2002, 12, 11), Decimal('307276.89'), Decimal('70.582811')), (datetime.date(2002, 12, 12), Decimal('69037.09'), Decimal('-77.532612')), (datetime.date(2002, 12, 13), Decimal('263445.47'), Decimal('281.599905')), (datetime.date(2002, 12, 14), Decimal('163938.08'), Decimal('-37.771532')), (datetime.date(2002, 12, 15), Decimal('274886.28'), Decimal('67.676894')), (datetime.date(2002, 12, 16), Decimal('162067.83'), Decimal('-41.041863')), (datetime.date(2002, 12, 17), Decimal('263210.40'), Decimal('62.407555')), (datetime.date(2002, 12, 18), Decimal('209496.26'), Decimal('-20.407302')), (datetime.date(2002, 12, 19), Decimal('257434.21'), Decimal('22.882485')), (datetime.date(2002, 12, 20), Decimal('254331.00'), Decimal('-1.205438')), (datetime.date(2002, 12, 21), Decimal('189707.72'), Decimal('-25.409124')), (datetime.date(2002, 12, 22), Decimal('224321.12'), Decimal('18.245647')), (datetime.date(2002, 12, 23), Decimal('174676.13'), Decimal('-22.131215')), (datetime.date(2002, 12, 24), Decimal('157328.49'), Decimal('-9.931317')), (datetime.date(2002, 12, 25), Decimal('171534.95'), Decimal('9.029808')), (datetime.date(2002, 12, 26), Decimal('280585.67'), Decimal('63.573470')), (datetime.date(2002, 12, 27), Decimal('243700.24'), Decimal('-13.145871')), (datetime.date(2002, 12, 28), Decimal('156334.40'), Decimal('-35.849714')), (datetime.date(2002, 12, 29), Decimal('191094.41'), Decimal('22.234396')), (datetime.date(2002, 12, 30), Decimal('325642.36'), Decimal('70.409150')), (datetime.date(2002, 12, 31), Decimal('192804.44'), Decimal('-40.792580'))]
Answer:| 年份 | 销售额 | 销售额环比 |
| -------- | ------- |------------ |
| 2002-12-01 | $223,123.42 | NULL |
| 2002-12-02 | $205,996.16 | -7.68% |
| 2002-12-03 | $76,986.41 | -62.63% |
| 2002-12-04 | $114,089.62 | 48.19% |
| 2002-12-05 | $188,652.99 | 65.36% |
| 2002-12-06 | $115,109.34 | -38.98% |
| 2002-12-07 | $246,017.57 | 113.73% |
| 2002-12-08 | $462,001.98 | 87.79% |
| 2002-12-09 | $163,052.97 | -64.71% |
| 2002-12-10 | $180,133.56 | 10.48% |
| 2002-12-11 | $307,276.89 | 70.58% |
| 2002-12-12 | $69,037.09 | -77.53% |
| 2002-12-13 | $263,445.47 | 281.60% |
| 2002-12-14 | $163,938.08 | -37.77% |
| 2002-12-15 | $274,886.28 | 67.68% |
| 2002-12-16 | $162,067.83 | -41.04% |
| 2002-12-17 | $263,210.40 | 62.41% |
| 2002-12-18 | $209,496.26 | -20.41% |
| 2002-12-19 | $257,434.21 | 22.88% |
| 2002-12-20 | $254,331.00 | -1.21% |
| 2002-12-21 | $189,707.72 | -25.41% |
| 2002-12-22 | $224,321.12 | 18.25% |
| 2002-12-23 | $174,676.13 | -22.13% |
| 2002-12-24 | $157,328.49 | -9.93% |
| 2002-12-25 | $171,534.95 | 9.03% |
| 2002-12-26 | $280,585.67 | 63.57% |
| 2002-12-27 | $243,700.24 | -13.15% |
| 2002-12-28 | $156,334.40 | -35.85% |
| 2002-12-29 | $191,094.41 | 22.23% |
| 2002-12-30 | $325,642.36 | 70.41% |
| 2002-12-31 | $192,804.44 | -40.79% |
> Finished chain.
结论
通过少样本微调，大型语言模型在处理多表联接、聚合操作和窗口函数等复杂操作时表现出色。其灵活性使得模型能够充分应对数据分析过程中的多样化需求，生成既高效又符合复杂分析要求的SQL查询。这样大大提高数据分析工作的效率，减轻数据专业人员在编写复杂SQL查询时的负担。
综合而言，大型语言模型在数据分析领域的应用呈现出巨大潜力，为处理复杂的数据指标计算提供了高效而智能的解决方案。结合模型的智能与数据分析专业人员的领域知识，我们可以更高效的应对日益复杂的数据分析任务，推动数据驱动决策的不断进步。