SQL（Structured Query Language）是一种用于管理关系型数据库的标准语言。它允许用户创建、查询、更新和删除数据库中的数据。SQL 是数据库管理系统（如 MySQL、PostgreSQL、SQLite 等）的核心工具。
会计应用: 在会计系统中，数据库用于存储： - 会计科目表(Chart of Accounts) - 记账凭证(Journal Entries) - 总分类账(General Ledger) - 明细账(Subsidiary Ledgers) - 财务报表数据
SQLite3 是一个轻量级的、嵌入式的关系型数据库管理系统。它不需要单独的服务器进程，直接在应用程序中运行，非常适合小型项目、原型开发和嵌入式系统。SQLite3 支持标准的 SQL 语法，并且是 ACID 兼容的。
DB Browser for SQLite 是一个免费的开源 GUI 工具，用于查看和编辑 SQLite 数据库文件。
.db 文件Python 标准库中包含了 sqlite3 模块，可以直接使用，无需额外安装。
学习目标: - 理解SQL建表语句 - 掌握数据类型定义 - 学会设置主键和约束
让我们创建一个会计科目表(Chart of Accounts):
import sqlite3
# 连接到数据库（如果不存在会自动创建）
conn = sqlite3.connect('accounting.db')
cursor = conn.cursor()
# 创建会计科目表
cursor.execute('''
CREATE TABLE IF NOT EXISTS chart_of_accounts (
account_code TEXT PRIMARY KEY,
account_name TEXT NOT NULL,
account_type TEXT NOT NULL,
parent_code TEXT,
balance_direction TEXT,
is_active INTEGER DEFAULT 1
)
''')
conn.commit()
print("会计科目表创建成功")会计科目表创建成功字段说明: - account_code: 科目编码(主键) - account_name: 科目名称 - account_type: 科目类型(资产/负债/所有者权益/收入/费用) - parent_code: 上级科目编码 - balance_direction: 余额方向(借/贷) - is_active: 是否启用(1=启用, 0=停用)
# 插入会计科目数据
accounts = [
('1001', '库存现金', '资产', None, '借', 1),
('1002', '银行存款', '资产', None, '借', 1),
('1122', '应收账款', '资产', None, '借', 1),
('1123', '预付账款', '资产', None, '借', 1),
('1401', '存货', '资产', None, '借', 1),
('2202', '应付账款', '负债', None, '贷', 1),
('2203', '预收账款', '负债', None, '贷', 1),
('4001', '实收资本', '所有者权益', None, '贷', 1),
('6001', '主营业务收入', '收入', None, '贷', 1),
('6401', '主营业务成本', '费用', None, '借', 1)
]
cursor.executemany('''
INSERT OR REPLACE INTO chart_of_accounts
(account_code, account_name, account_type, parent_code, balance_direction, is_active)
VALUES (?, ?, ?, ?, ?, ?)
''', accounts)
conn.commit()
print(f"成功插入{len(accounts)}个会计科目")成功插入10个会计科目# 查询所有科目
cursor.execute("SELECT * FROM chart_of_accounts")
rows = cursor.fetchall()
print("所有会计科目：")
for row in rows[:5]: # 只显示前5条
print(row)
# 条件查询：查询资产类科目
cursor.execute("""
SELECT account_code, account_name, balance_direction
FROM chart_of_accounts
WHERE account_type = ?
""", ('资产',))
rows = cursor.fetchall()
print("\n资产类科目：")
for row in rows:
print(f"科目编码: {row[0]}, 科目名称: {row[1]}, 余额方向: {row[2]}")所有会计科目：
('1001', '库存现金', '资产', None, '借', 1)
('1002', '银行存款', '资产', None, '借', 1)
('1122', '应收账款', '资产', None, '借', 1)
('1123', '预付账款', '资产', None, '借', 1)
('1401', '存货', '资产', None, '借', 1)
资产类科目：
科目编码: 1001, 科目名称: 库存现金, 余额方向: 借
科目编码: 1002, 科目名称: 银行存款, 余额方向: 借
科目编码: 1122, 科目名称: 应收账款, 余额方向: 借
科目编码: 1123, 科目名称: 预付账款, 余额方向: 借
科目编码: 1401, 科目名称: 存货, 余额方向: 借# 更新数据：修改科目名称
cursor.execute("""
UPDATE chart_of_accounts
SET account_name = ?
WHERE account_code = ?
""", ('现金', '1001'))
conn.commit()
print("库存现金科目名称已更新为'现金'")
# 停用某个科目（软删除，不是物理删除）
cursor.execute("""
UPDATE chart_of_accounts
SET is_active = 0
WHERE account_code = ?
""", ('1123',))
conn.commit()
print("预付账款科目已停用")
# 再次查询启用的科目
cursor.execute("""
SELECT account_code, account_name, account_type, is_active
FROM chart_of_accounts
ORDER BY account_code
""")
rows = cursor.fetchall()
print("\n更新后的科目列表：")
for row in rows[:7]:
status = "✓" if row[3] == 1 else "✗"
print(f"{status} {row[0]} - {row[1]} ({row[2]})")库存现金科目名称已更新为'现金'
预付账款科目已停用
更新后的科目列表：
✓ 1001 - 现金 (资产)
✓ 1002 - 银行存款 (资产)
✓ 1122 - 应收账款 (资产)
✗ 1123 - 预付账款 (资产)
✓ 1401 - 存货 (资产)
✓ 2202 - 应付账款 (负债)
✓ 2203 - 预收账款 (负债)重要提示: 在会计系统中，通常不会物理删除科目，而是使用”软删除”(设置is_active=0)。这样可以保留历史数据的完整性。
PRAGMA wal_checkpoint;
PRAGMA integrity_check;
PRAGMA optimize;学习目标: - 掌握pandas与SQL的集成使用 - 学会将DataFrame写入数据库 - 理解SQL查询与pandas的结合优势
Pandas 提供了便捷的方法来读取和写入数据库，这对财务数据分析非常有用。
让我们创建一个记账凭证表来演示：
import pandas as pd
# 创建记账凭证数据
journal_entries = pd.DataFrame({
'entry_id': ['JE001', 'JE001', 'JE002', 'JE002', 'JE003', 'JE003', 'JE003', 'JE004', 'JE004'],
'entry_date': ['2024-01-15', '2024-01-15', '2024-01-20', '2024-01-20',
'2024-02-05', '2024-02-05', '2024-02-05', '2024-02-10', '2024-02-10'],
'account_code': ['1002', '6001', '1122', '6001', '1001', '1002', '6001', '2202', '6401'],
'description': ['销售收款', '销售收入', '赊销收入', '销售收入', '现金销售', '银行转账', '销售收入',
'采购商品', '采购成本'],
'debit': [50000, 0, 30000, 0, 20000, 0, 0, 0, 15000],
'credit': [0, 50000, 0, 30000, 0, 20000, 40000, 25000, 0]
})
# 查看数据结构
print("记账凭证数据前5行：")
print(journal_entries.head())
print("\n数据集信息：")
print(journal_entries.info())记账凭证数据前5行：
entry_id entry_date account_code description debit credit
0 JE001 2024-01-15 1002 销售收款 50000 0
1 JE001 2024-01-15 6001 销售收入 0 50000
2 JE002 2024-01-20 1122 赊销收入 30000 0
3 JE002 2024-01-20 6001 销售收入 0 30000
4 JE003 2024-02-05 1001 现金销售 20000 0
数据集信息：
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 9 entries, 0 to 8
Data columns (total 6 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 entry_id 9 non-null object
1 entry_date 9 non-null object
2 account_code 9 non-null object
3 description 9 non-null object
4 debit 9 non-null int64
5 credit 9 non-null int64
dtypes: int64(2), object(4)
memory usage: 564.0+ bytes
None双记账说明: - 每笔会计分录必须借贷平衡 - JE001: 银行存款(借) = 销售收入(贷) = 50,000 - JE002: 应收账款(借) = 销售收入(贷) = 30,000 - JE003: 现金(借) + 销售收入(贷) = 银行存款(借) + 销售收入(贷) = 40,000
# 将记账凭证数据写入数据库
journal_entries.to_sql('journal_entries', conn, if_exists='replace', index=False)
print("记账凭证数据已写入数据库")
# 验证数据
cursor.execute("SELECT COUNT(*) FROM journal_entries")
count = cursor.fetchone()[0]
print(f"数据库中共有 {count} 条分录记录")记账凭证数据已写入数据库
数据库中共有 9 条分录记录# 从数据库读取数据
df_from_db = pd.read_sql_query("SELECT * FROM journal_entries LIMIT 10", conn)
print("从数据库读取的前10行：")
print(df_from_db)从数据库读取的前10行：
entry_id entry_date account_code description debit credit
0 JE001 2024-01-15 1002 销售收款 50000 0
1 JE001 2024-01-15 6001 销售收入 0 50000
2 JE002 2024-01-20 1122 赊销收入 30000 0
3 JE002 2024-01-20 6001 销售收入 0 30000
4 JE003 2024-02-05 1001 现金销售 20000 0
5 JE003 2024-02-05 1002 银行转账 0 20000
6 JE003 2024-02-05 6001 销售收入 0 40000
7 JE004 2024-02-10 2202 采购商品 0 25000
8 JE004 2024-02-10 6401 采购成本 15000 0# 复杂查询：按科目汇总借贷金额
query = """
SELECT
j.account_code,
c.account_name,
c.account_type,
SUM(j.debit) as total_debit,
SUM(j.credit) as total_credit,
SUM(j.debit) - SUM(j.credit) as net_balance
FROM journal_entries j
LEFT JOIN chart_of_accounts c ON j.account_code = c.account_code
GROUP BY j.account_code, c.account_name, c.account_type
ORDER BY j.account_code
"""
result = pd.read_sql_query(query, conn)
print("按科目汇总的借贷金额：")
print(result)按科目汇总的借贷金额：
account_code account_name account_type total_debit total_credit \
0 1001 现金 资产 20000 0
1 1002 银行存款 资产 50000 20000
2 1122 应收账款 资产 30000 0
3 2202 应付账款 负债 0 25000
4 6001 主营业务收入 收入 0 120000
5 6401 主营业务成本 费用 15000 0
net_balance
0 20000
1 30000
2 30000
3 -25000
4 -120000
5 15000 会计应用: 这个查询实际上生成了一个简单的试算平衡表的基础数据。试算平衡表是验证账目借贷平衡的重要工具。
# 检查整体借贷是否平衡
query = """
SELECT
SUM(debit) as total_debits,
SUM(credit) as total_credits,
SUM(debit) - SUM(credit) as difference
FROM journal_entries
"""
balance_check = pd.read_sql_query(query, conn)
print("\n借贷平衡检查：")
print(balance_check)
if balance_check['difference'][0] == 0:
print("✓ 借贷平衡")
else:
print(f"✗ 不平衡，差额: {balance_check['difference'][0]}")
借贷平衡检查：
total_debits total_credits difference
0 115000 165000 -50000
✗ 不平衡，差额: -50000学习目标: - 掌握SQL聚合函数在财务分析中的应用 - 学会使用GROUP BY进行分类汇总 - 理解JOIN操作在多表关联中的作用 - 应用窗口函数进行高级财务计算
本节将详细介绍常见的 SQL 操作，使用会计数据进行演示。
聚合函数对一组值进行计算，返回单个值。在会计中常用于汇总分析。
常用聚合函数: - COUNT(*): 计数 - SUM(column): 求和 - AVG(column): 平均值 - MIN(column): 最小值 - MAX(column): 最大值
# 聚合函数示例：分析记账凭证
query = """
SELECT
COUNT(DISTINCT entry_id) as total_entries,
COUNT(*) as total_lines,
SUM(debit) as total_debits,
SUM(credit) as total_credits,
AVG(debit + credit) as avg_amount_per_line,
MAX(debit) as max_debit,
MAX(credit) as max_credit
FROM journal_entries
"""
result = pd.read_sql_query(query, conn)
print("记账凭证聚合统计：")
print(result)记账凭证聚合统计：
total_entries total_lines total_debits total_credits \
0 4 9 115000 165000
avg_amount_per_line max_debit max_credit
0 31111.111111 50000 50000 会计应用: - total_debits应该等于total_credits(借贷平衡原则) - total_entries表示凭证数量 - total_lines表示分录行数
GROUP BY 用于将结果按指定列分组，HAVING 用于过滤分组后的结果。
# GROUP BY 示例：按科目类型分组统计
query = """
SELECT
c.account_type,
COUNT(DISTINCT j.account_code) as accounts_used,
COUNT(*) as transaction_count,
SUM(j.debit) as total_debits,
SUM(j.credit) as total_credits
FROM journal_entries j
LEFT JOIN chart_of_accounts c ON j.account_code = c.account_code
GROUP BY c.account_type
ORDER BY c.account_type
"""
result = pd.read_sql_query(query, conn)
print("按科目类型分组的统计：")
print(result)按科目类型分组的统计：
account_type accounts_used transaction_count total_debits total_credits
0 收入 1 3 0 120000
1 负债 1 1 0 25000
2 费用 1 1 15000 0
3 资产 3 4 100000 20000# HAVING 示例：找出交易金额超过30000的科目
query = """
SELECT
j.account_code,
c.account_name,
SUM(j.debit + j.credit) as total_amount,
COUNT(*) as transaction_count
FROM journal_entries j
LEFT JOIN chart_of_accounts c ON j.account_code = c.account_code
GROUP BY j.account_code, c.account_name
HAVING SUM(j.debit + j.credit) > 30000
ORDER BY total_amount DESC
"""
result = pd.read_sql_query(query, conn)
print("\n交易金额超过30000的科目：")
print(result)
交易金额超过30000的科目：
account_code account_name total_amount transaction_count
0 6001 主营业务收入 120000 3
1 1002 银行存款 70000 2HAVING vs WHERE: - WHERE: 过滤行数据(在分组之前) - HAVING: 过滤分组结果(在分组之后)
JOIN 用于连接多个表，在会计中常用于关联科目表、凭证表、客户表等。
JOIN 类型: - INNER JOIN: 只返回匹配的行(两表都有的数据) - LEFT JOIN: 返回左表所有行和右表匹配行(保留主表全部数据) - CROSS JOIN: 笛卡尔积(两表的所有组合)
我们已经在前面的查询中使用了JOIN。让我们创建客户主数据表来演示更多JOIN应用：
# 创建客户主数据表
cursor.execute('''
CREATE TABLE IF NOT EXISTS customers (
customer_id TEXT PRIMARY KEY,
customer_name TEXT NOT NULL,
customer_type TEXT,
credit_limit REAL
)
''')
# 插入客户数据
customers_data = [
('C001', '甲公司', 'VIP', 100000),
('C002', '乙公司', '普通', 50000),
('C003', '丙公司', 'VIP', 150000),
('C004', '丁公司', '普通', 30000)
]
cursor.executemany("INSERT OR REPLACE INTO customers VALUES (?, ?, ?, ?)", customers_data)
conn.commit()
print("客户主数据表创建成功")
# 创建应收账款明细表
receivables_data = pd.DataFrame({
'invoice_id': ['INV001', 'INV002', 'INV003', 'INV004', 'INV005'],
'customer_id': ['C001', 'C001', 'C002', 'C003', 'C999'], # C999不存在,用于演示JOIN差异
'invoice_date': ['2024-01-15', '2024-02-10', '2024-01-25', '2024-02-20', '2024-03-01'],
'amount': [30000, 25000, 15000, 50000, 10000],
'paid': [1, 0, 1, 0, 0]
})
receivables_data.to_sql('receivables', conn, if_exists='replace', index=False)
print("应收账款明细表创建成功")客户主数据表创建成功
应收账款明细表创建成功INNER JOIN 示例 - 查询有应收账款的客户:
# INNER JOIN: 只显示有应收账款的客户
query = """
SELECT
c.customer_id,
c.customer_name,
c.customer_type,
r.invoice_id,
r.invoice_date,
r.amount,
CASE WHEN r.paid = 1 THEN '已付' ELSE '未付' END as payment_status
FROM customers c
INNER JOIN receivables r ON c.customer_id = r.customer_id
ORDER BY c.customer_id, r.invoice_date
"""
result = pd.read_sql_query(query, conn)
print("INNER JOIN 示例 - 有应收账款的客户：")
print(result)INNER JOIN 示例 - 有应收账款的客户：
customer_id customer_name customer_type invoice_id invoice_date amount \
0 C001 甲公司 VIP INV001 2024-01-15 30000
1 C001 甲公司 VIP INV002 2024-02-10 25000
2 C002 乙公司 普通 INV003 2024-01-25 15000
3 C003 丙公司 VIP INV004 2024-02-20 50000
payment_status
0 已付
1 未付
2 已付
3 未付 LEFT JOIN 示例 - 查询所有客户的应收情况:
# LEFT JOIN: 显示所有客户,包括没有应收账款的
query = """
SELECT
c.customer_id,
c.customer_name,
c.customer_type,
c.credit_limit,
COUNT(r.invoice_id) as invoice_count,
COALESCE(SUM(r.amount), 0) as total_receivables,
COALESCE(SUM(CASE WHEN r.paid = 0 THEN r.amount ELSE 0 END), 0) as outstanding_amount
FROM customers c
LEFT JOIN receivables r ON c.customer_id = r.customer_id
GROUP BY c.customer_id, c.customer_name, c.customer_type, c.credit_limit
ORDER BY total_receivables DESC
"""
result = pd.read_sql_query(query, conn)
print("\nLEFT JOIN 示例 - 所有客户应收汇总：")
print(result)
LEFT JOIN 示例 - 所有客户应收汇总：
customer_id customer_name customer_type credit_limit invoice_count \
0 C001 甲公司 VIP 100000.0 2
1 C003 丙公司 VIP 150000.0 1
2 C002 乙公司 普通 50000.0 1
3 C004 丁公司 普通 30000.0 0
total_receivables outstanding_amount
0 55000 25000
1 50000 50000
2 15000 0
3 0 0 会计应用: - INNER JOIN: 用于查询有交易的客户/供应商 - LEFT JOIN: 用于生成完整的客户列表(包括无交易的) - COALESCE: 处理NULL值,在汇总时很有用
查找数据质量问题:
# 找出应收账款表中不存在于客户主数据的记录
query = """
SELECT
r.invoice_id,
r.customer_id,
r.amount,
'客户不存在' as issue
FROM receivables r
LEFT JOIN customers c ON r.customer_id = c.customer_id
WHERE c.customer_id IS NULL
"""
result = pd.read_sql_query(query, conn)
print("\n数据质量检查 - 客户主数据缺失：")
print(result)
数据质量检查 - 客户主数据缺失：
invoice_id customer_id amount issue
0 INV005 C999 10000 客户不存在子查询是嵌套在另一个查询中的查询,在会计分析中常用于比较分析和数据筛选。
学习目标: - 掌握标量子查询(返回单个值) - 掌握IN子查询(返回值列表) - 理解子查询在会计分析中的应用
# 子查询示例：查找金额高于平均值的交易
query = """
SELECT
entry_id,
entry_date,
account_code,
description,
debit,
credit,
(debit + credit) as amount
FROM journal_entries
WHERE (debit + credit) > (
SELECT AVG(debit + credit)
FROM journal_entries
WHERE debit + credit > 0
)
ORDER BY (debit + credit) DESC
"""
result = pd.read_sql_query(query, conn)
print("金额高于平均值的记账分录：")
print(result)
# 子查询用于 IN 操作：找出有大额交易的科目
query = """
SELECT
c.account_code,
c.account_name,
c.account_type
FROM chart_of_accounts c
WHERE c.account_code IN (
SELECT account_code
FROM journal_entries
GROUP BY account_code
HAVING MAX(debit + credit) > 40000
)
ORDER BY c.account_type, c.account_code
"""
result = pd.read_sql_query(query, conn)
print("\n存在大额交易(>40000)的会计科目：")
print(result)
# EXISTS 子查询：找出有未付款发票的客户
query = """
SELECT
c.customer_id,
c.customer_name,
c.customer_type,
c.credit_limit
FROM customers c
WHERE EXISTS (
SELECT 1
FROM receivables r
WHERE r.customer_id = c.customer_id
AND r.paid = 0
)
"""
result = pd.read_sql_query(query, conn)
print("\n存在未付款发票的客户：")
print(result)金额高于平均值的记账分录：
entry_id entry_date account_code description debit credit amount
0 JE001 2024-01-15 1002 销售收款 50000 0 50000
1 JE001 2024-01-15 6001 销售收入 0 50000 50000
2 JE003 2024-02-05 6001 销售收入 0 40000 40000
存在大额交易(>40000)的会计科目：
account_code account_name account_type
0 6001 主营业务收入 收入
1 1002 银行存款 资产
存在未付款发票的客户：
customer_id customer_name customer_type credit_limit
0 C001 甲公司 VIP 100000.0
1 C003 丙公司 VIP 150000.0窗口函数对结果集的子集（窗口）进行计算,在会计分析中用于排名、趋势分析和累计计算。
学习目标: - 掌握ROW_NUMBER()、RANK()等排名函数 - 掌握LAG()、LEAD()等偏移函数用于环比分析 - 掌握PARTITION BY进行分组窗口计算 - 理解窗口函数在财务分析中的应用
SQLite3 支持以下窗口函数：
ROW_NUMBER(): 行号RANK(): 排名（跳跃）DENSE_RANK(): 密集排名LAG(): 前一行值LEAD(): 后一行值SUM() OVER(): 窗口内求和# 创建月度收入数据用于演示
monthly_revenue = pd.DataFrame({
'month': ['2024-01', '2024-02', '2024-03', '2024-04', '2024-05', '2024-06',
'2024-07', '2024-08', '2024-09', '2024-10', '2024-11', '2024-12'],
'revenue': [850000, 920000, 880000, 950000, 990000, 1050000,
980000, 1020000, 1100000, 1080000, 1150000, 1200000],
'cost': [600000, 650000, 620000, 670000, 700000, 740000,
690000, 720000, 770000, 760000, 810000, 850000]
})
monthly_revenue.to_sql('monthly_revenue', conn, if_exists='replace', index=False)
print("月度收入成本数据已创建")
# ROW_NUMBER 和 RANK 示例：客户销售额排名
query = """
SELECT
customer_id,
customer_name,
SUM(amount) as total_sales,
ROW_NUMBER() OVER (ORDER BY SUM(amount) DESC) as row_num,
RANK() OVER (ORDER BY SUM(amount) DESC) as rank,
DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) as dense_rank
FROM (
SELECT
c.customer_id,
c.customer_name,
r.amount
FROM customers c
JOIN receivables r ON c.customer_id = r.customer_id
)
GROUP BY customer_id, customer_name
ORDER BY total_sales DESC
"""
result = pd.read_sql_query(query, conn)
print("\n窗口函数：客户销售额排名：")
print(result)
# LAG 和 LEAD 示例：环比分析
query = """
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) as prev_month_revenue,
LEAD(revenue) OVER (ORDER BY month) as next_month_revenue,
revenue - LAG(revenue) OVER (ORDER BY month) as mom_change,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) /
LAG(revenue) OVER (ORDER BY month), 2) as mom_growth_rate
FROM monthly_revenue
ORDER BY month
"""
result = pd.read_sql_query(query, conn)
print("\nLAG 和 LEAD 函数：月度环比分析：")
print(result)
# 累计求和：计算年度累计收入
query = """
SELECT
month,
revenue,
SUM(revenue) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as ytd_revenue,
ROUND(100.0 * revenue / SUM(revenue) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) as pct_of_ytd
FROM monthly_revenue
ORDER BY month
"""
result = pd.read_sql_query(query, conn)
print("\n累计求和：年度累计收入(YTD)：")
print(result)
# 分组窗口函数：按科目类型分析
query = """
SELECT
c.account_type,
c.account_code,
c.account_name,
SUM(j.debit) as total_debit,
SUM(j.credit) as total_credit,
AVG(SUM(j.debit + j.credit)) OVER (PARTITION BY c.account_type) as type_avg_amount,
SUM(SUM(j.debit + j.credit)) OVER (PARTITION BY c.account_type) as type_total_amount
FROM chart_of_accounts c
JOIN journal_entries j ON c.account_code = j.account_code
GROUP BY c.account_type, c.account_code, c.account_name
ORDER BY c.account_type, total_debit + total_credit DESC
"""
result = pd.read_sql_query(query, conn)
print("\n分组窗口函数：按科目类型的交易分析：")
print(result)
# 移动平均：3个月移动平均
query = """
SELECT
month,
revenue,
ROUND(AVG(revenue) OVER (
ORDER BY month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
), 2) as ma_3month
FROM monthly_revenue
ORDER BY month
"""
result = pd.read_sql_query(query, conn)
print("\n移动平均：3个月收入移动平均：")
print(result)月度收入成本数据已创建
窗口函数：客户销售额排名：
customer_id customer_name total_sales row_num rank dense_rank
0 C001 甲公司 55000 1 1 1
1 C003 丙公司 50000 2 2 2
2 C002 乙公司 15000 3 3 3
LAG 和 LEAD 函数：月度环比分析：
month revenue prev_month_revenue next_month_revenue mom_change \
0 2024-01 850000 NaN 920000.0 NaN
1 2024-02 920000 850000.0 880000.0 70000.0
2 2024-03 880000 920000.0 950000.0 -40000.0
3 2024-04 950000 880000.0 990000.0 70000.0
4 2024-05 990000 950000.0 1050000.0 40000.0
5 2024-06 1050000 990000.0 980000.0 60000.0
6 2024-07 980000 1050000.0 1020000.0 -70000.0
7 2024-08 1020000 980000.0 1100000.0 40000.0
8 2024-09 1100000 1020000.0 1080000.0 80000.0
9 2024-10 1080000 1100000.0 1150000.0 -20000.0
10 2024-11 1150000 1080000.0 1200000.0 70000.0
11 2024-12 1200000 1150000.0 NaN 50000.0
mom_growth_rate
0 NaN
1 8.24
2 -4.35
3 7.95
4 4.21
5 6.06
6 -6.67
7 4.08
8 7.84
9 -1.82
10 6.48
11 4.35
累计求和：年度累计收入(YTD)：
month revenue ytd_revenue pct_of_ytd
0 2024-01 850000 850000 100.00
1 2024-02 920000 1770000 51.98
2 2024-03 880000 2650000 33.21
3 2024-04 950000 3600000 26.39
4 2024-05 990000 4590000 21.57
5 2024-06 1050000 5640000 18.62
6 2024-07 980000 6620000 14.80
7 2024-08 1020000 7640000 13.35
8 2024-09 1100000 8740000 12.59
9 2024-10 1080000 9820000 11.00
10 2024-11 1150000 10970000 10.48
11 2024-12 1200000 12170000 9.86
分组窗口函数：按科目类型的交易分析：
account_type account_code account_name total_debit total_credit \
0 收入 6001 主营业务收入 0 120000
1 负债 2202 应付账款 0 25000
2 费用 6401 主营业务成本 15000 0
3 资产 1002 银行存款 50000 20000
4 资产 1122 应收账款 30000 0
5 资产 1001 现金 20000 0
type_avg_amount type_total_amount
0 120000.0 120000
1 25000.0 25000
2 15000.0 15000
3 40000.0 120000
4 40000.0 120000
5 40000.0 120000
移动平均：3个月收入移动平均：
month revenue ma_3month
0 2024-01 850000 850000.00
1 2024-02 920000 885000.00
2 2024-03 880000 883333.33
3 2024-04 950000 916666.67
4 2024-05 990000 940000.00
5 2024-06 1050000 996666.67
6 2024-07 980000 1006666.67
7 2024-08 1020000 1016666.67
8 2024-09 1100000 1033333.33
9 2024-10 1080000 1066666.67
10 2024-11 1150000 1110000.00
11 2024-12 1200000 1143333.33会计应用: 窗口函数在财务分析中的常见应用： - 环比分析：使用LAG/LEAD计算月度、季度环比增长 - 累计计算：YTD收入、累计利润等 - 移动平均：平滑波动,识别趋势 - 排名分析：客户/产品/部门业绩排名 - 占比分析：各项目占总额的比例
CTE 使用 WITH 关键字定义临时结果集,使复杂查询更清晰、可读。在会计报表编制中特别有用。
学习目标: - 掌握WITH语句创建CTE - 掌握多个CTE的组合使用 - 理解CTE在财务报表编制中的应用
# CTE 示例：分析各科目类型的交易统计
query = """
WITH account_type_stats AS (
SELECT
c.account_type,
COUNT(DISTINCT j.entry_id) as entry_count,
COUNT(*) as line_count,
SUM(j.debit) as total_debit,
SUM(j.credit) as total_credit,
AVG(j.debit + j.credit) as avg_amount
FROM chart_of_accounts c
JOIN journal_entries j ON c.account_code = j.account_code
GROUP BY c.account_type
),
ranked_types AS (
SELECT
*,
RANK() OVER (ORDER BY total_debit + total_credit DESC) as activity_rank,
ROUND(100.0 * (total_debit + total_credit) /
SUM(total_debit + total_credit) OVER (), 2) as pct_of_total
FROM account_type_stats
)
SELECT * FROM ranked_types
ORDER BY activity_rank
"""
result = pd.read_sql_query(query, conn)
print("CTE 示例：科目类型交易统计和排名：")
print(result)
# 递归CTE示例：利润表层级结构（简化版）
query = """
WITH RECURSIVE income_statement AS (
-- 收入部分
SELECT 1 as line_order, '营业收入' as line_item, SUM(credit) as amount
FROM journal_entries
WHERE account_code LIKE '6001%'
UNION ALL
-- 成本部分
SELECT 2, '营业成本', SUM(debit)
FROM journal_entries
WHERE account_code LIKE '6401%'
UNION ALL
-- 毛利
SELECT 3, '毛利', (
SELECT SUM(credit) FROM journal_entries WHERE account_code LIKE '6001%'
) - (
SELECT SUM(debit) FROM journal_entries WHERE account_code LIKE '6401%'
)
)
SELECT line_order, line_item, ROUND(amount, 2) as amount
FROM income_statement
ORDER BY line_order
"""
result = pd.read_sql_query(query, conn)
print("\n递归CTE：简化利润表：")
print(result)
# 多个CTE组合：客户价值分析
query = """
WITH customer_sales AS (
SELECT
c.customer_id,
c.customer_name,
c.customer_type,
COUNT(r.invoice_id) as invoice_count,
SUM(r.amount) as total_sales,
AVG(r.amount) as avg_invoice_amount
FROM customers c
LEFT JOIN receivables r ON c.customer_id = r.customer_id
GROUP BY c.customer_id, c.customer_name, c.customer_type
),
customer_payments AS (
SELECT
customer_id,
SUM(CASE WHEN paid = 1 THEN amount ELSE 0 END) as paid_amount,
SUM(CASE WHEN paid = 0 THEN amount ELSE 0 END) as unpaid_amount,
ROUND(100.0 * SUM(CASE WHEN paid = 1 THEN 1 ELSE 0 END) / COUNT(*), 2) as payment_rate
FROM receivables
GROUP BY customer_id
),
customer_classification AS (
SELECT
cs.*,
cp.paid_amount,
cp.unpaid_amount,
cp.payment_rate,
CASE
WHEN cs.total_sales > 60000 THEN '大客户'
WHEN cs.total_sales > 30000 THEN '中客户'
ELSE '小客户'
END as customer_class,
CASE
WHEN cp.payment_rate >= 80 THEN '优质'
WHEN cp.payment_rate >= 50 THEN '良好'
ELSE '需关注'
END as credit_rating
FROM customer_sales cs
LEFT JOIN customer_payments cp ON cs.customer_id = cp.customer_id
)
SELECT * FROM customer_classification
ORDER BY total_sales DESC
"""
result = pd.read_sql_query(query, conn)
print("\n多CTE组合：客户价值与信用评级分析：")
print(result)CTE 示例：科目类型交易统计和排名：
account_type entry_count line_count total_debit total_credit \
0 收入 3 3 0 120000
1 资产 3 4 100000 20000
2 负债 1 1 0 25000
3 费用 1 1 15000 0
avg_amount activity_rank pct_of_total
0 40000.0 1 42.86
1 30000.0 1 42.86
2 25000.0 3 8.93
3 15000.0 4 5.36
递归CTE：简化利润表：
line_order line_item amount
0 1 营业收入 120000.0
1 2 营业成本 15000.0
2 3 毛利 105000.0
多CTE组合：客户价值与信用评级分析：
customer_id customer_name customer_type invoice_count total_sales \
0 C001 甲公司 VIP 2 55000.0
1 C003 丙公司 VIP 1 50000.0
2 C002 乙公司 普通 1 15000.0
3 C004 丁公司 普通 0 NaN
avg_invoice_amount paid_amount unpaid_amount payment_rate \
0 27500.0 30000.0 25000.0 50.0
1 50000.0 0.0 50000.0 0.0
2 15000.0 15000.0 0.0 100.0
3 NaN NaN NaN NaN
customer_class credit_rating
0 中客户 良好
1 中客户 需关注
2 小客户 优质
3 小客户 需关注 会计应用: CTE在财务报表中的应用： - 分步计算：将复杂的报表计算分解为清晰的步骤 - 递归查询：处理科目体系的层级结构 - 中间结果：保存计算结果供后续使用 - 提高可读性：使复杂的SQL逻辑更易理解和维护
SQLite 支持多种日期和时间函数，可以处理日期的存储、格式化和计算。
# 创建包含日期字段的表
cursor.execute('''
CREATE TABLE IF NOT EXISTS sales (
id INTEGER PRIMARY KEY,
product_name TEXT,
sale_date TEXT, -- SQLite 中日期通常存储为 TEXT
quantity INTEGER,
price REAL
)
''')
# 插入包含日期的数据
sales_data = [
('钻石戒指', '2024-01-15', 2, 1500.00),
('项链', '2024-02-20', 1, 800.00),
('耳环', '2024-03-10', 3, 450.00),
('手镯', '2024-01-25', 1, 1200.00),
('钻石项链', '2024-02-28', 1, 2500.00)
]
cursor.executemany("INSERT OR REPLACE INTO sales (product_name, sale_date, quantity, price) VALUES (?, ?, ?, ?)", sales_data)
conn.commit()
print("销售数据表创建并插入数据成功")销售数据表创建并插入数据成功# 使用日期函数查询
query = """
SELECT
product_name,
sale_date,
quantity,
price,
-- 提取年月日
strftime('%Y', sale_date) as year,
strftime('%m', sale_date) as month,
strftime('%d', sale_date) as day,
-- 计算总价
quantity * price as total_amount
FROM sales
ORDER BY sale_date
"""
result = pd.read_sql_query(query, conn)
print("销售数据及日期处理：")
print(result)
# 按月份统计销售额
query = """
SELECT
strftime('%Y-%m', sale_date) as month,
COUNT(*) as sales_count,
SUM(quantity * price) as total_revenue,
AVG(quantity * price) as avg_sale_amount
FROM sales
GROUP BY strftime('%Y-%m', sale_date)
ORDER BY month
"""
result = pd.read_sql_query(query, conn)
print("\n按月份统计销售额：")
print(result)销售数据及日期处理：
product_name sale_date quantity price year month day total_amount
0 钻石戒指 2024-01-15 2 1500.0 2024 01 15 3000.0
1 钻石戒指 2024-01-15 2 1500.0 2024 01 15 3000.0
2 钻石戒指 2024-01-15 2 1500.0 2024 01 15 3000.0
3 钻石戒指 2024-01-15 2 1500.0 2024 01 15 3000.0
4 钻石戒指 2024-01-15 2 1500.0 2024 01 15 3000.0
5 钻石戒指 2024-01-15 2 1500.0 2024 01 15 3000.0
6 钻石戒指 2024-01-15 2 1500.0 2024 01 15 3000.0
7 钻石戒指 2024-01-15 2 1500.0 2024 01 15 3000.0
8 钻石戒指 2024-01-15 2 1500.0 2024 01 15 3000.0
9 手镯 2024-01-25 1 1200.0 2024 01 25 1200.0
10 手镯 2024-01-25 1 1200.0 2024 01 25 1200.0
11 手镯 2024-01-25 1 1200.0 2024 01 25 1200.0
12 手镯 2024-01-25 1 1200.0 2024 01 25 1200.0
13 手镯 2024-01-25 1 1200.0 2024 01 25 1200.0
14 手镯 2024-01-25 1 1200.0 2024 01 25 1200.0
15 手镯 2024-01-25 1 1200.0 2024 01 25 1200.0
16 手镯 2024-01-25 1 1200.0 2024 01 25 1200.0
17 手镯 2024-01-25 1 1200.0 2024 01 25 1200.0
18 项链 2024-02-20 1 800.0 2024 02 20 800.0
19 项链 2024-02-20 1 800.0 2024 02 20 800.0
20 项链 2024-02-20 1 800.0 2024 02 20 800.0
21 项链 2024-02-20 1 800.0 2024 02 20 800.0
22 项链 2024-02-20 1 800.0 2024 02 20 800.0
23 项链 2024-02-20 1 800.0 2024 02 20 800.0
24 项链 2024-02-20 1 800.0 2024 02 20 800.0
25 项链 2024-02-20 1 800.0 2024 02 20 800.0
26 项链 2024-02-20 1 800.0 2024 02 20 800.0
27 钻石项链 2024-02-28 1 2500.0 2024 02 28 2500.0
28 钻石项链 2024-02-28 1 2500.0 2024 02 28 2500.0
29 钻石项链 2024-02-28 1 2500.0 2024 02 28 2500.0
30 钻石项链 2024-02-28 1 2500.0 2024 02 28 2500.0
31 钻石项链 2024-02-28 1 2500.0 2024 02 28 2500.0
32 钻石项链 2024-02-28 1 2500.0 2024 02 28 2500.0
33 钻石项链 2024-02-28 1 2500.0 2024 02 28 2500.0
34 钻石项链 2024-02-28 1 2500.0 2024 02 28 2500.0
35 钻石项链 2024-02-28 1 2500.0 2024 02 28 2500.0
36 耳环 2024-03-10 3 450.0 2024 03 10 1350.0
37 耳环 2024-03-10 3 450.0 2024 03 10 1350.0
38 耳环 2024-03-10 3 450.0 2024 03 10 1350.0
39 耳环 2024-03-10 3 450.0 2024 03 10 1350.0
40 耳环 2024-03-10 3 450.0 2024 03 10 1350.0
41 耳环 2024-03-10 3 450.0 2024 03 10 1350.0
42 耳环 2024-03-10 3 450.0 2024 03 10 1350.0
43 耳环 2024-03-10 3 450.0 2024 03 10 1350.0
44 耳环 2024-03-10 3 450.0 2024 03 10 1350.0
45 限时优惠品 2025-10-19 1 999.0 2025 10 19 999.0
46 限时优惠品 2025-10-19 1 999.0 2025 10 19 999.0
47 限时优惠品 2025-10-19 1 999.0 2025 10 19 999.0
48 限时优惠品 2025-10-19 1 999.0 2025 10 19 999.0
49 限时优惠品 2025-10-19 1 999.0 2025 10 19 999.0
50 限时优惠品 2025-10-19 1 999.0 2025 10 19 999.0
51 限时优惠品 2025-10-19 1 999.0 2025 10 19 999.0
52 限时优惠品 2025-10-19 1 999.0 2025 10 19 999.0
按月份统计销售额：
month sales_count total_revenue avg_sale_amount
0 2024-01 18 37800.0 2100.0
1 2024-02 18 29700.0 1650.0
2 2024-03 9 12150.0 1350.0
3 2025-10 8 7992.0 999.0# 查询最近30天的销售
query = """
SELECT
product_name,
sale_date,
quantity * price as amount
FROM sales
WHERE date(sale_date) >= date('now', '-30 days')
ORDER BY sale_date DESC
"""
result = pd.read_sql_query(query, conn)
print("最近30天的销售记录：")
print(result)
# 计算两个日期之间的天数差
query = """
SELECT
product_name,
sale_date,
-- 计算距今天的天数
julianday('now') - julianday(sale_date) as days_since_sale,
-- 计算距2024年1月1日的天数
julianday(sale_date) - julianday('2024-01-01') as days_from_start
FROM sales
ORDER BY sale_date
"""
result = pd.read_sql_query(query, conn)
print("\n日期计算示例：")
print(result)最近30天的销售记录：
product_name sale_date amount
0 限时优惠品 2025-10-19 999.0
1 限时优惠品 2025-10-19 999.0
2 限时优惠品 2025-10-19 999.0
3 限时优惠品 2025-10-19 999.0
4 限时优惠品 2025-10-19 999.0
5 限时优惠品 2025-10-19 999.0
6 限时优惠品 2025-10-19 999.0
7 限时优惠品 2025-10-19 999.0
日期计算示例：
product_name sale_date days_since_sale days_from_start
0 钻石戒指 2024-01-15 643.330728 14.0
1 钻石戒指 2024-01-15 643.330728 14.0
2 钻石戒指 2024-01-15 643.330728 14.0
3 钻石戒指 2024-01-15 643.330728 14.0
4 钻石戒指 2024-01-15 643.330728 14.0
5 钻石戒指 2024-01-15 643.330728 14.0
6 钻石戒指 2024-01-15 643.330728 14.0
7 钻石戒指 2024-01-15 643.330728 14.0
8 钻石戒指 2024-01-15 643.330728 14.0
9 手镯 2024-01-25 633.330728 24.0
10 手镯 2024-01-25 633.330728 24.0
11 手镯 2024-01-25 633.330728 24.0
12 手镯 2024-01-25 633.330728 24.0
13 手镯 2024-01-25 633.330728 24.0
14 手镯 2024-01-25 633.330728 24.0
15 手镯 2024-01-25 633.330728 24.0
16 手镯 2024-01-25 633.330728 24.0
17 手镯 2024-01-25 633.330728 24.0
18 项链 2024-02-20 607.330728 50.0
19 项链 2024-02-20 607.330728 50.0
20 项链 2024-02-20 607.330728 50.0
21 项链 2024-02-20 607.330728 50.0
22 项链 2024-02-20 607.330728 50.0
23 项链 2024-02-20 607.330728 50.0
24 项链 2024-02-20 607.330728 50.0
25 项链 2024-02-20 607.330728 50.0
26 项链 2024-02-20 607.330728 50.0
27 钻石项链 2024-02-28 599.330728 58.0
28 钻石项链 2024-02-28 599.330728 58.0
29 钻石项链 2024-02-28 599.330728 58.0
30 钻石项链 2024-02-28 599.330728 58.0
31 钻石项链 2024-02-28 599.330728 58.0
32 钻石项链 2024-02-28 599.330728 58.0
33 钻石项链 2024-02-28 599.330728 58.0
34 钻石项链 2024-02-28 599.330728 58.0
35 钻石项链 2024-02-28 599.330728 58.0
36 耳环 2024-03-10 588.330728 69.0
37 耳环 2024-03-10 588.330728 69.0
38 耳环 2024-03-10 588.330728 69.0
39 耳环 2024-03-10 588.330728 69.0
40 耳环 2024-03-10 588.330728 69.0
41 耳环 2024-03-10 588.330728 69.0
42 耳环 2024-03-10 588.330728 69.0
43 耳环 2024-03-10 588.330728 69.0
44 耳环 2024-03-10 588.330728 69.0
45 限时优惠品 2025-10-19 0.330728 657.0
46 限时优惠品 2025-10-19 0.330728 657.0
47 限时优惠品 2025-10-19 0.330728 657.0
48 限时优惠品 2025-10-19 0.330728 657.0
49 限时优惠品 2025-10-19 0.330728 657.0
50 限时优惠品 2025-10-19 0.330728 657.0
51 限时优惠品 2025-10-19 0.330728 657.0
52 限时优惠品 2025-10-19 0.330728 657.0import datetime
# 使用 Python 的 datetime 处理日期
current_date = datetime.datetime.now()
print(f"当前日期时间: {current_date}")
# 格式化日期
formatted_date = current_date.strftime("%Y-%m-%d %H:%M:%S")
print(f"格式化后的日期: {formatted_date}")
# 插入当前日期的数据
cursor.execute("""
INSERT INTO sales (product_name, sale_date, quantity, price)
VALUES (?, ?, ?, ?)
""", ('限时优惠品', current_date.strftime("%Y-%m-%d"), 1, 999.00))
conn.commit()
print("插入了当前日期的销售记录")
# 查询今天的销售
today = datetime.date.today().strftime("%Y-%m-%d")
query = f"""
SELECT * FROM sales
WHERE sale_date = '{today}'
"""
result = pd.read_sql_query(query, conn)
print(f"\n今天的销售记录 ({today}):")
print(result)当前日期时间: 2025-10-19 15:56:14.943748
格式化后的日期: 2025-10-19 15:56:14
插入了当前日期的销售记录
今天的销售记录 (2025-10-19):
id product_name sale_date quantity price
0 6 限时优惠品 2025-10-19 1 999.0
1 12 限时优惠品 2025-10-19 1 999.0
2 18 限时优惠品 2025-10-19 1 999.0
3 24 限时优惠品 2025-10-19 1 999.0
4 30 限时优惠品 2025-10-19 1 999.0
5 36 限时优惠品 2025-10-19 1 999.0
6 42 限时优惠品 2025-10-19 1 999.0
7 48 限时优惠品 2025-10-19 1 999.0
8 54 限时优惠品 2025-10-19 1 999.0# 将字符串日期转换为 datetime
df_sales = pd.read_sql_query("SELECT * FROM sales", conn)
print("原始销售数据：")
print(df_sales)
# 转换日期列
df_sales['sale_date'] = pd.to_datetime(df_sales['sale_date'])
print("\n转换日期类型后的数据：")
print(df_sales.dtypes)
# 日期相关的操作
print("\n日期统计：")
print(f"最早销售日期: {df_sales['sale_date'].min()}")
print(f"最晚销售日期: {df_sales['sale_date'].max()}")
print(f"销售天数: {(df_sales['sale_date'].max() - df_sales['sale_date'].min()).days} 天")
# 按月份分组统计
df_sales['month'] = df_sales['sale_date'].dt.to_period('M')
monthly_sales = df_sales.groupby('month').agg({
'quantity': 'sum',
'price': 'sum',
'id': 'count'
}).rename(columns={'id': 'sales_count'})
print("\n月度销售统计：")
print(monthly_sales)原始销售数据：
id product_name sale_date quantity price
0 1 钻石戒指 2024-01-15 2 1500.0
1 2 项链 2024-02-20 1 800.0
2 3 耳环 2024-03-10 3 450.0
3 4 手镯 2024-01-25 1 1200.0
4 5 钻石项链 2024-02-28 1 2500.0
5 6 限时优惠品 2025-10-19 1 999.0
6 7 钻石戒指 2024-01-15 2 1500.0
7 8 项链 2024-02-20 1 800.0
8 9 耳环 2024-03-10 3 450.0
9 10 手镯 2024-01-25 1 1200.0
10 11 钻石项链 2024-02-28 1 2500.0
11 12 限时优惠品 2025-10-19 1 999.0
12 13 钻石戒指 2024-01-15 2 1500.0
13 14 项链 2024-02-20 1 800.0
14 15 耳环 2024-03-10 3 450.0
15 16 手镯 2024-01-25 1 1200.0
16 17 钻石项链 2024-02-28 1 2500.0
17 18 限时优惠品 2025-10-19 1 999.0
18 19 钻石戒指 2024-01-15 2 1500.0
19 20 项链 2024-02-20 1 800.0
20 21 耳环 2024-03-10 3 450.0
21 22 手镯 2024-01-25 1 1200.0
22 23 钻石项链 2024-02-28 1 2500.0
23 24 限时优惠品 2025-10-19 1 999.0
24 25 钻石戒指 2024-01-15 2 1500.0
25 26 项链 2024-02-20 1 800.0
26 27 耳环 2024-03-10 3 450.0
27 28 手镯 2024-01-25 1 1200.0
28 29 钻石项链 2024-02-28 1 2500.0
29 30 限时优惠品 2025-10-19 1 999.0
30 31 钻石戒指 2024-01-15 2 1500.0
31 32 项链 2024-02-20 1 800.0
32 33 耳环 2024-03-10 3 450.0
33 34 手镯 2024-01-25 1 1200.0
34 35 钻石项链 2024-02-28 1 2500.0
35 36 限时优惠品 2025-10-19 1 999.0
36 37 钻石戒指 2024-01-15 2 1500.0
37 38 项链 2024-02-20 1 800.0
38 39 耳环 2024-03-10 3 450.0
39 40 手镯 2024-01-25 1 1200.0
40 41 钻石项链 2024-02-28 1 2500.0
41 42 限时优惠品 2025-10-19 1 999.0
42 43 钻石戒指 2024-01-15 2 1500.0
43 44 项链 2024-02-20 1 800.0
44 45 耳环 2024-03-10 3 450.0
45 46 手镯 2024-01-25 1 1200.0
46 47 钻石项链 2024-02-28 1 2500.0
47 48 限时优惠品 2025-10-19 1 999.0
48 49 钻石戒指 2024-01-15 2 1500.0
49 50 项链 2024-02-20 1 800.0
50 51 耳环 2024-03-10 3 450.0
51 52 手镯 2024-01-25 1 1200.0
52 53 钻石项链 2024-02-28 1 2500.0
53 54 限时优惠品 2025-10-19 1 999.0
转换日期类型后的数据：
id int64
product_name object
sale_date datetime64[ns]
quantity int64
price float64
dtype: object
日期统计：
最早销售日期: 2024-01-15 00:00:00
最晚销售日期: 2025-10-19 00:00:00
销售天数: 643 天
月度销售统计：
quantity price sales_count
month
2024-01 27 24300.0 18
2024-02 18 29700.0 18
2024-03 27 4050.0 9
2025-10 9 8991.0 9DISTINCT用于返回唯一不重复的值,在会计中用于统计科目类型、客户类别等。
# DISTINCT 示例：查看所有科目类型
query = """
SELECT DISTINCT account_type
FROM chart_of_accounts
WHERE is_active = 1
ORDER BY account_type
"""
result = pd.read_sql_query(query, conn)
print("DISTINCT 示例：活跃的科目类型：")
print(result)
# 组合DISTINCT：科目类型和余额方向组合
query = """
SELECT DISTINCT
account_type,
balance_direction
FROM chart_of_accounts
ORDER BY account_type, balance_direction
"""
result = pd.read_sql_query(query, conn)
print("\n科目类型与余额方向的唯一组合：")
print(result)
# COUNT DISTINCT：统计唯一值数量
query = """
SELECT
COUNT(DISTINCT customer_id) as unique_customers,
COUNT(DISTINCT account_code) as unique_accounts,
COUNT(DISTINCT entry_id) as unique_entries
FROM journal_entries j
LEFT JOIN receivables r ON DATE(j.entry_date) = DATE(r.invoice_date)
"""
result = pd.read_sql_query(query, conn)
print("\n唯一值计数统计：")
print(result)DISTINCT 示例：活跃的科目类型：
account_type
0 所有者权益
1 收入
2 负债
3 费用
4 资产
科目类型与余额方向的唯一组合：
account_type balance_direction
0 所有者权益 贷
1 收入 贷
2 负债 贷
3 费用 借
4 资产 借
唯一值计数统计：
unique_customers unique_accounts unique_entries
0 1 6 4CASE语句用于条件判断和分类,在会计中用于账龄分析、科目分类、风险评级等。
# CASE 语句示例：应收账款账龄分析
query = """
SELECT
invoice_id,
customer_id,
invoice_date,
amount,
paid,
CASE
WHEN paid = 1 THEN '已付款'
WHEN julianday('now') - julianday(invoice_date) <= 30 THEN '30天内'
WHEN julianday('now') - julianday(invoice_date) <= 60 THEN '31-60天'
WHEN julianday('now') - julianday(invoice_date) <= 90 THEN '61-90天'
ELSE '90天以上'
END as aging_category,
CASE
WHEN paid = 0 AND julianday('now') - julianday(invoice_date) > 90 THEN '高风险'
WHEN paid = 0 AND julianday('now') - julianday(invoice_date) > 60 THEN '中风险'
WHEN paid = 0 THEN '低风险'
ELSE '无风险'
END as risk_level
FROM receivables
ORDER BY invoice_date
"""
result = pd.read_sql_query(query, conn)
print("CASE 语句示例：应收账款账龄与风险分析：")
print(result)
# 嵌套CASE：交易金额分级
query = """
SELECT
entry_id,
account_code,
debit,
credit,
CASE
WHEN debit > 0 THEN
CASE
WHEN debit >= 50000 THEN '大额借方'
WHEN debit >= 20000 THEN '中额借方'
ELSE '小额借方'
END
WHEN credit > 0 THEN
CASE
WHEN credit >= 50000 THEN '大额贷方'
WHEN credit >= 20000 THEN '中额贷方'
ELSE '小额贷方'
END
ELSE '无金额'
END as transaction_class
FROM journal_entries
ORDER BY (debit + credit) DESC
"""
result = pd.read_sql_query(query, conn)
print("\n嵌套CASE：交易金额分类：")
print(result)CASE 语句示例：应收账款账龄与风险分析：
invoice_id customer_id invoice_date amount paid aging_category risk_level
0 INV001 C001 2024-01-15 30000 1 已付款 无风险
1 INV003 C002 2024-01-25 15000 1 已付款 无风险
2 INV002 C001 2024-02-10 25000 0 90天以上 高风险
3 INV004 C003 2024-02-20 50000 0 90天以上 高风险
4 INV005 C999 2024-03-01 10000 0 90天以上 高风险
嵌套CASE：交易金额分类：
entry_id account_code debit credit transaction_class
0 JE001 1002 50000 0 大额借方
1 JE001 6001 0 50000 大额贷方
2 JE003 6001 0 40000 中额贷方
3 JE002 1122 30000 0 中额借方
4 JE002 6001 0 30000 中额贷方
5 JE004 2202 0 25000 中额贷方
6 JE003 1001 20000 0 中额借方
7 JE003 1002 0 20000 中额贷方
8 JE004 6401 15000 0 小额借方会计应用: CASE语句的典型应用场景： - 账龄分析：根据日期计算账龄区间 - 科目分类：将明细科目归类到报表项目 - 异常标识：标记异常交易或超限额交易 - KPI评级：根据财务指标进行等级评定
UNION用于合并多个查询结果,常用于编制合并报表或组合不同来源的数据。
# UNION 示例：合并收入和支出科目
query = """
SELECT
'收入' as category,
c.account_code,
c.account_name,
SUM(j.credit) as amount
FROM chart_of_accounts c
JOIN journal_entries j ON c.account_code = j.account_code
WHERE c.account_type = '收入'
GROUP BY c.account_code, c.account_name
UNION ALL
SELECT
'成本费用' as category,
c.account_code,
c.account_name,
SUM(j.debit) as amount
FROM chart_of_accounts c
JOIN journal_entries j ON c.account_code = j.account_code
WHERE c.account_type IN ('成本', '费用')
GROUP BY c.account_code, c.account_name
ORDER BY category, amount DESC
"""
result = pd.read_sql_query(query, conn)
print("UNION 示例：收入与成本费用汇总：")
print(result)
# UNION vs UNION ALL：资产负债表简化示例
query = """
SELECT 1 as sort_order, '资产' as section, SUM(debit - credit) as balance
FROM chart_of_accounts c
JOIN journal_entries j ON c.account_code = j.account_code
WHERE c.account_type = '资产'
UNION ALL
SELECT 2, '负债', SUM(credit - debit)
FROM chart_of_accounts c
JOIN journal_entries j ON c.account_code = j.account_code
WHERE c.account_type = '负债'
UNION ALL
SELECT 3, '所有者权益', SUM(credit - debit)
FROM chart_of_accounts c
JOIN journal_entries j ON c.account_code = j.account_code
WHERE c.account_type = '所有者权益'
ORDER BY sort_order
"""
result = pd.read_sql_query(query, conn)
print("\nUNION ALL：资产负债表简化结构：")
print(result)UNION 示例：收入与成本费用汇总：
category account_code account_name amount
0 成本费用 6401 主营业务成本 15000
1 收入 6001 主营业务收入 120000
UNION ALL：资产负债表简化结构：
sort_order section balance
0 1 资产 80000.0
1 2 负债 25000.0
2 3 所有者权益 NaNUNION vs UNION ALL: - UNION：自动去除重复行,性能较慢 - UNION ALL：保留所有行包括重复,性能较快 - 在财务报表中通常使用UNION ALL,因为各部分数据不会重复
当数据量很大时（如数百万条交易记录），可以使用 chunksize 参数分块读取数据,避免内存溢出。
# 分块读取大型凭证数据
query = "SELECT * FROM journal_entries"
chunks = pd.read_sql_query(query, conn, chunksize=3)
print("分块读取凭证数据：")
total_rows = 0
total_debit = 0
total_credit = 0
for i, chunk in enumerate(chunks):
chunk_debit = chunk['debit'].sum()
chunk_credit = chunk['credit'].sum()
total_debit += chunk_debit
total_credit += chunk_credit
total_rows += len(chunk)
print(f"块 {i+1}: {len(chunk)} 行, 借方合计: {chunk_debit}, 贷方合计: {chunk_credit}")
print(f"\n总行数: {total_rows}")
print(f"总借方: {total_debit}, 总贷方: {total_credit}")
print(f"借贷平衡检查: {'平衡' if abs(total_debit - total_credit) < 0.01 else '不平衡'}")分块读取凭证数据：
块 1: 3 行, 借方合计: 80000, 贷方合计: 50000
块 2: 3 行, 借方合计: 20000, 贷方合计: 50000
块 3: 3 行, 借方合计: 15000, 贷方合计: 65000
总行数: 9
总借方: 115000, 总贷方: 165000
借贷平衡检查: 不平衡大数据处理建议: - 对于超过100万条记录的数据,建议使用chunksize分块处理 - 每块大小可设置为10000-50000行,根据内存情况调整 - 可在每块处理后进行聚合,避免一次性加载全部数据
本案例综合运用前面学习的各种SQL技术,构建一个完整的财务分析仪表盘。
案例背景: 某制造企业需要每月生成财务分析报告,包括： 1. 收入成本分析 2. 客户价值排名 3. 应收账款健康度 4. 月度趋势分析 5. 财务健康度评分
# 创建更完整的测试数据集
print("=== 综合案例：财务分析仪表盘 ===\n")
# 添加更多月度数据（如果尚未添加）
additional_months = pd.DataFrame({
'month': ['2024-01', '2024-02', '2024-03'],
'revenue': [850000, 920000, 880000],
'cost': [600000, 650000, 620000]
})
# 检查月度收入表是否存在足够数据
existing_count = pd.read_sql_query("SELECT COUNT(*) as cnt FROM monthly_revenue", conn).iloc[0]['cnt']
if existing_count < 3:
additional_months.to_sql('monthly_revenue', conn, if_exists='replace', index=False)
print("财务数据准备完成")=== 综合案例：财务分析仪表盘 ===
财务数据准备完成# 分析收入成本结构和趋势
query = """
WITH monthly_analysis AS (
SELECT
month,
revenue,
cost,
revenue - cost as gross_profit,
ROUND(100.0 * (revenue - cost) / revenue, 2) as gross_margin,
LAG(revenue) OVER (ORDER BY month) as prev_revenue,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) /
LAG(revenue) OVER (ORDER BY month), 2) as revenue_growth_rate
FROM monthly_revenue
)
SELECT
month,
revenue,
cost,
gross_profit,
gross_margin,
COALESCE(revenue_growth_rate, 0) as revenue_growth_rate,
CASE
WHEN gross_margin >= 35 THEN '优秀'
WHEN gross_margin >= 25 THEN '良好'
WHEN gross_margin >= 15 THEN '一般'
ELSE '需改进'
END as profitability_rating
FROM monthly_analysis
ORDER BY month
"""
revenue_analysis = pd.read_sql_query(query, conn)
print("1. 收入成本分析：")
print(revenue_analysis)1. 收入成本分析：
month revenue cost gross_profit gross_margin revenue_growth_rate \
0 2024-01 850000 600000 250000 29.41 0.00
1 2024-02 920000 650000 270000 29.35 8.24
2 2024-03 880000 620000 260000 29.55 -4.35
3 2024-04 950000 670000 280000 29.47 7.95
4 2024-05 990000 700000 290000 29.29 4.21
5 2024-06 1050000 740000 310000 29.52 6.06
6 2024-07 980000 690000 290000 29.59 -6.67
7 2024-08 1020000 720000 300000 29.41 4.08
8 2024-09 1100000 770000 330000 30.00 7.84
9 2024-10 1080000 760000 320000 29.63 -1.82
10 2024-11 1150000 810000 340000 29.57 6.48
11 2024-12 1200000 850000 350000 29.17 4.35
profitability_rating
0 良好
1 良好
2 良好
3 良好
4 良好
5 良好
6 良好
7 良好
8 良好
9 良好
10 良好
11 良好 # 客户分级和价值分析
query = """
WITH customer_metrics AS (
SELECT
c.customer_id,
c.customer_name,
c.customer_type,
c.credit_limit,
COUNT(r.invoice_id) as total_invoices,
SUM(r.amount) as total_sales,
SUM(CASE WHEN r.paid = 1 THEN r.amount ELSE 0 END) as paid_amount,
SUM(CASE WHEN r.paid = 0 THEN r.amount ELSE 0 END) as outstanding_amount,
ROUND(100.0 * SUM(CASE WHEN r.paid = 1 THEN 1 ELSE 0 END) / COUNT(*), 2) as payment_rate,
AVG(r.amount) as avg_invoice_amount
FROM customers c
LEFT JOIN receivables r ON c.customer_id = r.customer_id
GROUP BY c.customer_id, c.customer_name, c.customer_type, c.credit_limit
),
customer_ranking AS (
SELECT
*,
RANK() OVER (ORDER BY total_sales DESC) as sales_rank,
CASE
WHEN total_sales >= 60000 THEN 'A类-大客户'
WHEN total_sales >= 30000 THEN 'B类-中客户'
ELSE 'C类-小客户'
END as customer_class,
CASE
WHEN payment_rate >= 80 THEN '优质'
WHEN payment_rate >= 50 THEN '良好'
WHEN payment_rate >= 20 THEN '需关注'
ELSE '高风险'
END as credit_rating
FROM customer_metrics
)
SELECT * FROM customer_ranking
ORDER BY sales_rank
"""
customer_analysis = pd.read_sql_query(query, conn)
print("\n2. 客户价值与信用分析：")
print(customer_analysis)
2. 客户价值与信用分析：
customer_id customer_name customer_type credit_limit total_invoices \
0 C001 甲公司 VIP 100000.0 2
1 C003 丙公司 VIP 150000.0 1
2 C002 乙公司 普通 50000.0 1
3 C004 丁公司 普通 30000.0 0
total_sales paid_amount outstanding_amount payment_rate \
0 55000.0 30000 25000 50.0
1 50000.0 0 50000 0.0
2 15000.0 15000 0 100.0
3 NaN 0 0 0.0
avg_invoice_amount sales_rank customer_class credit_rating
0 27500.0 1 B类-中客户 良好
1 50000.0 2 B类-中客户 高风险
2 15000.0 3 C类-小客户 优质
3 NaN 4 C类-小客户 高风险 # 应收账款账龄和风险分析
query = """
SELECT
COUNT(*) as total_invoices,
SUM(amount) as total_receivables,
SUM(CASE WHEN paid = 1 THEN amount ELSE 0 END) as collected,
SUM(CASE WHEN paid = 0 THEN amount ELSE 0 END) as outstanding,
ROUND(100.0 * SUM(CASE WHEN paid = 1 THEN amount ELSE 0 END) / SUM(amount), 2) as collection_rate,
-- 账龄分析
SUM(CASE WHEN paid = 0 AND julianday('now') - julianday(invoice_date) <= 30 THEN amount ELSE 0 END) as current,
SUM(CASE WHEN paid = 0 AND julianday('now') - julianday(invoice_date) BETWEEN 31 AND 60 THEN amount ELSE 0 END) as aged_31_60,
SUM(CASE WHEN paid = 0 AND julianday('now') - julianday(invoice_date) BETWEEN 61 AND 90 THEN amount ELSE 0 END) as aged_61_90,
SUM(CASE WHEN paid = 0 AND julianday('now') - julianday(invoice_date) > 90 THEN amount ELSE 0 END) as aged_over_90,
-- 风险评估
CASE
WHEN 100.0 * SUM(CASE WHEN paid = 0 AND julianday('now') - julianday(invoice_date) > 90 THEN amount ELSE 0 END) /
NULLIF(SUM(CASE WHEN paid = 0 THEN amount ELSE 0 END), 0) < 10 THEN '低风险'
WHEN 100.0 * SUM(CASE WHEN paid = 0 AND julianday('now') - julianday(invoice_date) > 90 THEN amount ELSE 0 END) /
NULLIF(SUM(CASE WHEN paid = 0 THEN amount ELSE 0 END), 0) < 25 THEN '中风险'
ELSE '高风险'
END as ar_risk_level
FROM receivables
"""
ar_health = pd.read_sql_query(query, conn)
print("\n3. 应收账款健康度分析：")
print(ar_health)
3. 应收账款健康度分析：
total_invoices total_receivables collected outstanding collection_rate \
0 5 130000 45000 85000 34.62
current aged_31_60 aged_61_90 aged_over_90 ar_risk_level
0 0 0 0 85000 高风险 # 计算企业财务健康度综合评分
query = """
WITH monthly_growth AS (
SELECT
month,
revenue,
cost,
100.0 * (revenue - cost) / revenue as gross_margin,
100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) /
NULLIF(LAG(revenue) OVER (ORDER BY month), 0) as growth_rate
FROM monthly_revenue
),
metrics AS (
SELECT
-- 盈利能力指标
(SELECT AVG(gross_margin) FROM monthly_growth) as avg_gross_margin,
-- 收款能力指标
(SELECT 100.0 * SUM(CASE WHEN paid = 1 THEN amount ELSE 0 END) / SUM(amount) FROM receivables) as collection_rate,
-- 增长能力指标
(SELECT AVG(growth_rate) FROM monthly_growth WHERE growth_rate IS NOT NULL) as avg_growth_rate
),
scores AS (
SELECT
avg_gross_margin,
collection_rate,
COALESCE(avg_growth_rate, 0) as avg_growth_rate,
-- 各维度评分（满分100）
CASE
WHEN avg_gross_margin >= 35 THEN 100
WHEN avg_gross_margin >= 25 THEN 80
WHEN avg_gross_margin >= 15 THEN 60
ELSE 40
END as profitability_score,
CASE
WHEN collection_rate >= 80 THEN 100
WHEN collection_rate >= 60 THEN 80
WHEN collection_rate >= 40 THEN 60
ELSE 40
END as collection_score,
CASE
WHEN COALESCE(avg_growth_rate, 0) >= 10 THEN 100
WHEN COALESCE(avg_growth_rate, 0) >= 5 THEN 80
WHEN COALESCE(avg_growth_rate, 0) >= 0 THEN 60
ELSE 40
END as growth_score
FROM metrics
)
SELECT
ROUND(avg_gross_margin, 2) as gross_margin_pct,
ROUND(collection_rate, 2) as collection_rate_pct,
ROUND(avg_growth_rate, 2) as avg_growth_rate_pct,
profitability_score,
collection_score,
growth_score,
ROUND((profitability_score * 0.4 + collection_score * 0.3 + growth_score * 0.3), 2) as overall_score,
CASE
WHEN (profitability_score * 0.4 + collection_score * 0.3 + growth_score * 0.3) >= 90 THEN 'A-优秀'
WHEN (profitability_score * 0.4 + collection_score * 0.3 + growth_score * 0.3) >= 80 THEN 'B-良好'
WHEN (profitability_score * 0.4 + collection_score * 0.3 + growth_score * 0.3) >= 70 THEN 'C-中等'
WHEN (profitability_score * 0.4 + collection_score * 0.3 + growth_score * 0.3) >= 60 THEN 'D-及格'
ELSE 'F-需改进'
END as health_grade
FROM scores
"""
health_score = pd.read_sql_query(query, conn)
print("\n4. 财务健康度综合评分：")
print(health_score)
4. 财务健康度综合评分：
gross_margin_pct collection_rate_pct avg_growth_rate_pct \
0 29.5 34.62 3.31
profitability_score collection_score growth_score overall_score \
0 80 40 60 62.0
health_grade
0 D-及格 print("\n" + "="*60)
print("财务分析仪表盘总结")
print("="*60)
# 输出关键发现
print("\n【关键发现】")
print(f"1. 平均毛利率: {health_score['gross_margin_pct'].iloc[0]:.2f}%")
print(f"2. 应收账款回款率: {health_score['collection_rate_pct'].iloc[0]:.2f}%")
print(f"3. 平均增长率: {health_score['avg_growth_rate_pct'].iloc[0]:.2f}%")
print(f"4. 财务健康度评分: {health_score['overall_score'].iloc[0]:.2f}分")
print(f"5. 综合评级: {health_score['health_grade'].iloc[0]}")
print("\n【管理建议】")
margin_pct = health_score['gross_margin_pct'].iloc[0]
if margin_pct < 20:
print("• 毛利率偏低,建议优化成本结构或提升产品定价")
elif margin_pct < 30:
print("• 毛利率处于合理区间,可继续保持")
else:
print("• 毛利率表现优秀,盈利能力强")
collection_pct = health_score['collection_rate_pct'].iloc[0]
if collection_pct < 60:
print("• 应收账款回款率较低,需加强催收管理")
elif collection_pct < 80:
print("• 应收账款回款情况一般,建议优化信用政策")
else:
print("• 应收账款回款情况良好,现金流健康")
print("\n" + "="*60)
============================================================
财务分析仪表盘总结
============================================================
【关键发现】
1. 平均毛利率: 29.50%
2. 应收账款回款率: 34.62%
3. 平均增长率: 3.31%
4. 财务健康度评分: 62.00分
5. 综合评级: D-及格
【管理建议】
• 毛利率处于合理区间,可继续保持
• 应收账款回款率较低,需加强催收管理
============================================================案例要点总结:
本案例展示了SQL在财务分析中的强大能力：
在实际工作中,这些SQL查询可以： - 保存为视图(VIEW)供反复使用 - 定时执行生成定期报告 - 导出为Excel/CSV供进一步分析 - 集成到BI工具(如Tableau、Power BI)中可视化展示
# 关闭连接
conn.close()
print("数据库连接已关闭")数据库连接已关闭通过本章的学习,你已经掌握了SQL在会计领域的核心应用技能：
sqlite3模块操作数据库pandas.read_sql_query()读取查询结果DataFrame.to_sql()将数据写入数据库chunksize处理大数据集为什么会计人员需要掌握SQL？
记住：SQL是一门”做中学”的技能,只有通过大量实践才能真正掌握。从今天开始,在你的会计工作中寻找SQL的应用场景,将学习转化为生产力！