如何按特定条件合并MySQL表或Pandas DataFrame并生成指定格式报表

一、MySQL端直接关联并输出目标格式 #

适合数据量较大的场景，减少本地数据传输压力，直接在数据库完成所有计算。

步骤说明 #

1. 对季度数据按ticker分组，按报告日期倒序排序，标记每个ticker的最近8个季度（q_rank为1是最新季度，对应q1，以此类推）
2. 对年度数据做类似处理，标记最近5年（y_rank为1是最新年度，对应y1）
3. 将标记后的季度、年度数据转成宽表（q1-q8、y1-y5列），再与company_info关联

完整SQL代码 #

WITH ranked_q AS (
    SELECT 
        ticker,
        report_date,
        revenue, -- 替换成你需要的季度指标列
        net_income,
        ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY report_date DESC) AS q_rank
    FROM income_statement_q
),
pivoted_q AS (
    SELECT
        ticker,
        MAX(CASE WHEN q_rank = 1 THEN revenue END) AS q1_revenue,
        MAX(CASE WHEN q_rank = 1 THEN net_income END) AS q1_net_income,
        MAX(CASE WHEN q_rank = 2 THEN revenue END) AS q2_revenue,
        MAX(CASE WHEN q_rank = 2 THEN net_income END) AS q2_net_income,
        MAX(CASE WHEN q_rank = 3 THEN revenue END) AS q3_revenue,
        MAX(CASE WHEN q_rank = 3 THEN net_income END) AS q3_net_income,
        MAX(CASE WHEN q_rank = 4 THEN revenue END) AS q4_revenue,
        MAX(CASE WHEN q_rank = 4 THEN net_income END) AS q4_net_income,
        MAX(CASE WHEN q_rank = 5 THEN revenue END) AS q5_revenue,
        MAX(CASE WHEN q_rank = 5 THEN net_income END) AS q5_net_income,
        MAX(CASE WHEN q_rank = 6 THEN revenue END) AS q6_revenue,
        MAX(CASE WHEN q_rank = 6 THEN net_income END) AS q6_net_income,
        MAX(CASE WHEN q_rank = 7 THEN revenue END) AS q7_revenue,
        MAX(CASE WHEN q_rank = 7 THEN net_income END) AS q7_net_income,
        MAX(CASE WHEN q_rank = 8 THEN revenue END) AS q8_revenue,
        MAX(CASE WHEN q_rank = 8 THEN net_income END) AS q8_net_income
    FROM ranked_q
    WHERE q_rank <= 8
    GROUP BY ticker
),
ranked_y AS (
    SELECT 
        ticker,
        report_date,
        revenue AS y_revenue, -- 替换成你需要的年度指标列
        net_income AS y_net_income,
        ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY report_date DESC) AS y_rank
    FROM income_statement_y
),
pivoted_y AS (
    SELECT
        ticker,
        MAX(CASE WHEN y_rank = 1 THEN y_revenue END) AS y1_revenue,
        MAX(CASE WHEN y_rank = 1 THEN y_net_income END) AS y1_net_income,
        MAX(CASE WHEN y_rank = 2 THEN y_revenue END) AS y2_revenue,
        MAX(CASE WHEN y_rank = 2 THEN y_net_income END) AS y2_net_income,
        MAX(CASE WHEN y_rank = 3 THEN y_revenue END) AS y3_revenue,
        MAX(CASE WHEN y_rank = 3 THEN y_net_income END) AS y3_net_income,
        MAX(CASE WHEN y_rank = 4 THEN y_revenue END) AS y4_revenue,
        MAX(CASE WHEN y_rank = 4 THEN y_net_income END) AS y4_net_income,
        MAX(CASE WHEN y_rank = 5 THEN y_revenue END) AS y5_revenue,
        MAX(CASE WHEN y_rank = 5 THEN y_net_income END) AS y5_net_income
    FROM ranked_y
    WHERE y_rank <=5
    GROUP BY ticker
)
SELECT 
    ci.*,
    pq.*,
    py.*
FROM company_info ci
LEFT JOIN pivoted_q pq ON ci.ticker = pq.ticker
LEFT JOIN pivoted_y py ON ci.ticker = py.ticker;

注意：替换代码中的revenue、net_income为你实际需要的财务指标列，若有更多指标，对应扩展CASE语句即可。

二、导出到Pandas后合并格式化 #

适合需要灵活调整格式、数据量较小的场景，Python的Pandas处理宽表转换更直观。

步骤说明 #

1. 从MySQL读取三张表的数据到DataFrame
2. 处理季度数据：按ticker分组，取最近8个季度，排序后标记q1-q8，转成宽表
3. 处理年度数据：按ticker分组，取最近5年，标记y1-y5，转成宽表
4. 合并公司信息、季度宽表、年度宽表，每个ticker对应一行

完整Python代码 #

import pandas as pd
import pymysql

# 1. 连接MySQL并读取数据
conn = pymysql.connect(
    host='你的主机地址',
    user='用户名',
    password='密码',
    database='stocks'
)

company_info = pd.read_sql('SELECT * FROM company_info', conn)
income_q = pd.read_sql('SELECT * FROM income_statement_q', conn)
income_y = pd.read_sql('SELECT * FROM income_statement_y', conn)

conn.close()

# 2. 处理季度数据：取最近8个季度，转宽表
income_q['report_date'] = pd.to_datetime(income_q['report_date'])
# 按ticker分组，按日期倒序排序，取前8个
ranked_q = income_q.groupby('ticker').apply(
    lambda x: x.sort_values('report_date', ascending=False).head(8)
).reset_index(drop=True)
# 添加q_rank标记（1=最新，对应q1）
ranked_q['q_rank'] = ranked_q.groupby('ticker').cumcount() + 1
# 转宽表：将每个指标按q_rank拆分为q1-q8列
pivoted_q = ranked_q.pivot(
    index='ticker',
    columns='q_rank',
    values=['revenue', 'net_income']  # 替换为你的实际指标列
)
# 重命名列，比如revenue_1 → q1_revenue
pivoted_q.columns = [f'q{rank}_{col}' for col, rank in pivoted_q.columns]
pivoted_q = pivoted_q.reset_index()

# 3. 处理年度数据：取最近5年，转宽表
income_y['report_date'] = pd.to_datetime(income_y['report_date'])
ranked_y = income_y.groupby('ticker').apply(
    lambda x: x.sort_values('report_date', ascending=False).head(5)
).reset_index(drop=True)
ranked_y['y_rank'] = ranked_y.groupby('ticker').cumcount() + 1
pivoted_y = ranked_y.pivot(
    index='ticker',
    columns='y_rank',
    values=['revenue', 'net_income']  # 替换为你的实际指标列
)
pivoted_y.columns = [f'y{rank}_{col}' for col, rank in pivoted_y.columns]
pivoted_y = pivoted_y.reset_index()

# 4. 合并所有表
final_report = company_info.merge(pivoted_q, on='ticker', how='left')\
                          .merge(pivoted_y, on='ticker', how='left')

# 缺失值留空（转为字符串空值，或保持NaN，根据需求调整）
final_report = final_report.fillna('')

# 输出到CSV或查看结果
final_report.to_csv('stock_report.csv', index=False)
print(final_report.head())

方案选择建议 #

• 数据量>100万行：优先选MySQL方案，数据库计算效率更高，减少内存占用
• 需要频繁调整格式/指标：优先选Pandas方案，代码修改更灵活，可视化调试方便

内容的提问来源于stack exchange，提问作者saga