import sqlite3
import pandas as pd
# 创建内存中的SQLite数据库
conn = sqlite3.connect(':memory:')
# 创建Cursor对象
cur = conn.cursor()
# 创建transactions表
cur.execute('''
CREATE TABLE transactions (
transaction_id INT PRIMARY KEY,
date DATE,
amount DECIMAL(10, 2),
description VARCHAR(255),
account_id INT
)
''')
# 插入数据到transactions表
cur.executemany('''
INSERT INTO transactions (transaction_id, date, amount, description, account_id) VALUES (?, ?, ?, ?, ?)
''', [
(1, '2023-01-01', 50.00, 'Office Supplies', 101),
(2, '2023-01-05', 300.00, 'Software Purchase', 102),
(3, '2023-01-10', 1500.00, 'Equipment Maintenance', 103),
(4, '2023-01-15', 75.00, 'Travel Expenses', 104),
(5, '2023-01-20', 450.00, 'Marketing Campaign', 101),
(6, '2023-01-25', 1200.00, 'Conference Fees', 105),
(7, '2023-02-01', 200.00, 'Subscription Service', 106),
(8, '2023-02-05', 95.00, 'Utility Bill', 107),
(9, '2023-02-10', 400.00, 'Equipment Purchase', 101),
(10, '2023-02-15', 700.00, 'Office Renovation', 108),
(11, '2023-02-20', 30.00, 'Stationery', 102),
(12, '2023-02-25', 500.00, 'Legal Fees', 103)
])
# 创建accounts表
cur.execute('''
CREATE TABLE accounts (
account_id INT PRIMARY KEY,
account_name VARCHAR(255),
account_type VARCHAR(50)
)
''')
# 插入数据到accounts表
cur.executemany('''
INSERT INTO accounts (account_id, account_name, account_type) VALUES (?, ?, ?)
''', [
(101, 'General Expenses', 'Expense'), # 一般费用账户/费用账户，记录各种运营支出
(102, 'IT Department', 'Department'), # IT部门账户/部门账户，记录特定部门（如IT部门）的费用和支出
(103, 'Maintenance', 'Service'), # 维护账户/服务账户，记录与外部服务相关的费用，如维护费、订阅服务费等
(104, 'Travel', 'Expense'), # 差旅费用账户/费用账户，记录各种运营支出
(105, 'Conferences', 'Event'), # 会议费用账户/事件账户，记录特定事件或活动的费用，如会议费用。
(106, 'Subscriptions', 'Service'), # 订阅服务账户/服务账户，记录与外部服务相关的费用，如维护费、订阅服务费等
(107, 'Utilities', 'Expense'), # 公用事业费用账户/费用账户，记录水电气等公共设施的费用
(108, 'Renovations', 'Project') # 装修项目账户/项目账户，记录特定项目的费用，如装修项目
])
# 查询数据
cur.execute('SELECT * FROM transactions')
rows = cur.fetchall()
# 使用pandas展示结果
df = pd.DataFrame(rows, columns=['transaction_id', 'date', 'amount', 'description', 'account_id'])
print(df)
# 关闭连接
# conn.close()
sql = '''
SELECT * FROM transactions
WHERE date BETWEEN '2023-01-01' AND '2023-01-31'
ORDER BY amount DESC;
'''
# 查询数据
cur.execute(sql)
rows = cur.fetchall()
# 使用pandas展示结果
df = pd.DataFrame(rows, columns=['transaction_id', 'date', 'amount', 'description', 'account_id'])
print(df)
sql = '''
SELECT account_id, SUM(amount) as total_amount
FROM transactions
GROUP BY account_id;
'''
# 查询数据
cur.execute(sql)
rows = cur.fetchall()
# 使用pandas展示结果
df = pd.DataFrame(rows, columns=['account_id', 'total_amount'])
print(df)
sql = '''
SELECT strftime('%Y-%m', date) as month, COUNT(*) as transaction_count
FROM transactions
GROUP BY month;
'''
# 查询数据
cur.execute(sql)
rows = cur.fetchall()
# 使用pandas展示结果
df = pd.DataFrame(rows, columns=['month', 'transaction_count'])
print(df)
sql = '''
SELECT transaction_id, amount,
CASE
WHEN amount < 100 THEN 'Small'
WHEN amount BETWEEN 100 AND 500 THEN 'Medium'
ELSE 'Large'
END as amount_category
FROM transactions;
'''
# 查询数据
cur.execute(sql)
rows = cur.fetchall()
# 使用pandas展示结果
df = pd.DataFrame(rows, columns=['transaction_id', 'amount', 'amount_category'])
print(df)
sql = '''
SELECT
CASE
WHEN amount < 100 THEN 'Small'
WHEN amount BETWEEN 100 AND 500 THEN 'Medium'
ELSE 'Large'
END as amount_category,
SUM(amount) as total_amount
FROM transactions
GROUP BY amount_category;
'''
# 查询数据
cur.execute(sql)
rows = cur.fetchall()
# 使用pandas展示结果
df = pd.DataFrame(rows, columns=['amount_category', 'amount_category'])
print(df)
# 表间连接方式（等价于内连接）
def fun_1():
sql = '''
SELECT t.transaction_id, t.date, t.amount, a.account_name
FROM transactions t, accounts a
WHERE t.account_id = a.account_id;
'''
# 查询数据
cur.execute(sql)
rows = cur.fetchall()
# 使用pandas展示结果
df = pd.DataFrame(rows, columns=['transaction_id', 'date', 'amount', 'account_name'])
print(df)
fun_1()
# 左外连接方式
def fun_2():
sql = '''
SELECT t.transaction_id, t.date, t.amount, a.account_name
FROM transactions t
LEFT OUTER JOIN accounts a ON t.account_id = a.account_id;
'''
# 查询数据
cur.execute(sql)
rows = cur.fetchall()
# 使用pandas展示结果
df = pd.DataFrame(rows, columns=['transaction_id', 'date', 'amount', 'account_name'])
print(df)
fun_2()
# 插入新数据到transactions表，该数据的account_id在accounts表中尚未维护
cur.executemany('''
INSERT INTO transactions (transaction_id, date, amount, description, account_id) VALUES (?, ?, ?, ?, ?)
''', [
(13, '2023-03-01', 760.00, 'Development Fees', 109)
])
fun_1()
print('-------------')
fun_2()
sql = '''
SELECT a.account_type, SUM(t.amount) as total_amount
FROM transactions t
LEFT JOIN accounts a ON t.account_id = a.account_id
GROUP BY a.account_type;
'''
# 查询数据
cur.execute(sql)
rows = cur.fetchall()
# 使用pandas展示结果
df = pd.DataFrame(rows, columns=['account_type', 'total_amount'])
print(df)
背景：你是一名数据分析师，负责分析公司财务交易数据。你的任务是从transactions和accounts表中提取和聚合数据，以生成有用的财务报告。
任务：
编写SQL查询，提取所有交易记录，显示交易ID、日期、金额、描述和账户名称。
编写SQL查询，按账户类型计算每个月的总交易金额。# 提取所有交易记录
sql = '''
SELECT
t.transaction_id,
t.date,
t.amount,
t.description,
a.account_name
FROM
transactions t
LEFT JOIN
accounts a
ON
t.account_id = a.account_id;
'''
# 查询数据
cur.execute(sql)
rows = cur.fetchall()
# 使用pandas展示结果
df = pd.DataFrame(rows, columns=['transaction_id', 'date', 'amount', 'description', 'account_name'])
print(df)
# 按账户类型计算每个月的总交易金额
sql = '''
SELECT
strftime('%Y-%m', date) AS month,
a.account_type,
SUM(t.amount) AS total_amount
FROM
transactions t
LEFT JOIN
accounts a
ON
t.account_id = a.account_id
GROUP BY
month,
a.account_type
ORDER BY
month,
a.account_type;
'''
# 查询数据
cur.execute(sql)
rows = cur.fetchall()
# 使用pandas展示结果
df = pd.DataFrame(rows, columns=['month', 'account_type', 'total_amount'])
print(df)
背景：你需要对公司不同类型的财务交易进行分类，并生成详细的财务报告。
任务：
编写SQL查询，将每笔交易根据金额大小分类，并显示交易ID、日期、金额、描述、账户名称和分类结果。
编写SQL查询，计算各账户类型在不同交易分类下的总金额。# 根据金额大小分类交易
sql = '''
SELECT
t.transaction_id,
t.date,
t.amount,
t.description,
a.account_name,
CASE
WHEN t.amount < 100 THEN 'Small'
WHEN t.amount BETWEEN 100 AND 500 THEN 'Medium'
ELSE 'Large'
END AS amount_category
FROM
transactions t
LEFT JOIN
accounts a
ON
t.account_id = a.account_id;
'''
# 查询数据
cur.execute(sql)
rows = cur.fetchall()
# 使用pandas展示结果
df = pd.DataFrame(rows, columns=['transaction_id', 'date', 'amount', 'description', 'account_name', 'amount_category'])
print(df)
# 计算各账户类型在不同交易分类下的总金额
sql = '''
SELECT
a.account_type,
CASE
WHEN t.amount < 100 THEN 'Small'
WHEN t.amount BETWEEN 100 AND 500 THEN 'Medium'
ELSE 'Large'
END AS amount_category,
SUM(t.amount) AS total_amount
FROM
transactions t
LEFT JOIN
accounts a
ON
t.account_id = a.account_id
GROUP BY
a.account_type,
amount_category
ORDER BY
a.account_type,
amount_category;
'''
# 查询数据
cur.execute(sql)
rows = cur.fetchall()
# 使用pandas展示结果
df = pd.DataFrame(rows, columns=['account_type', 'amount_category', 'total_amount'])
print(df)
# 关闭连接
conn.close()