使用python取数据库的数据将下面列表样式的数据，按照ITEM,PROJECT按照最近的三个yearMonth,最近的四个yearWeek,最近的7个If_date 分别取monthRate,weekRate,dailyRate 生成一个html的列表文件按

<think>我们面临的任务是从数据库提取数据，并按ITEM和PROJECT分组，分别获取最近三个月的月度数据（monthRate）、最近四周的周度数据（weekRate）和最近七天的每日数据（dailyRate），然后将结果生成HTML格式的列表文件。 步骤分解： 1. 连接数据库：使用合适的数据库连接库（如pymysql、psycopg2等）连接数据库。 2. 编写SQL查询：分别编写三个查询，分别获取月度、周度和日度的数据。 3. 执行查询并将结果转换为DataFrame：使用pandas处理数据。 4. 将三个DataFrame按照ITEM和PROJECT进行合并（或连接）成一个总表。 5. 将合并后的数据转换为HTML表格。 6. 将HTML表格写入文件。 注意：由于我们要求的是最近三个月、最近四周和最近七天，所以需要动态计算日期范围。 假设数据库中的表名为`rate_table`，包含以下字段： - `item`：项目类别 - `project`：具体项目 - `date`：日期（假设为日期类型） - `rate`：比率值（这里我们假设月度、周度和日度的比率都存储在同一个表中，通过日期字段区分，并且我们按日期范围来筛选） 但是，请注意：月度数据、周度数据和日度数据可能是以不同的粒度存储的。例如，可能日度数据每天一条，周度数据每周一条（或者通过日度数据聚合得到），月度数据每月一条。因此，我们需要分别查询。 由于问题中没有明确说明数据存储的粒度，我们假设表中存储的是日度数据，然后我们需要按月份和周进行聚合（比如取平均值？最大值？还是其他？）。但是，问题中要求的是“最近三个月的月度数据”，即每个月的单独数据，所以我们需要按月份分组，然后取每个月的某个聚合值（比如平均值）。同样，周度数据按周分组，日度数据则直接取最近七天。 然而，问题中并没有明确聚合方式。这里我们假设聚合方式为平均值（AVG）。如果实际需求不同，可以调整。 步骤详细说明： 1. 连接数据库： 使用pymysql（以MySQL为例）建立连接。 2. 计算日期范围： - 最近三个月：从当前日期往前推三个月，注意是自然月。 - 最近四周：从当前日期往前推4周（28天，但注意周的定义，比如从周一开始到周日？） - 最近七天：从当前日期往前推7天。 注意：由于我们可能需要按自然月和周（例如ISO周）来分组，所以需要数据库支持日期函数。 3. 编写三个SQL查询： a. 月度数据查询（最近三个月）： 按ITEM和PROJECT分组，并按月分组，计算每个月的rate平均值（或其他聚合方式）。 注意：这里我们只取最近三个月（包括当前月在内，如果当前月还没结束，则取部分数据平均？）。但为了简单，我们直接取最近三个完整的自然月。例如，今天是2023-10-05，那么最近三个月就是2023-07、2023-08、2023-09。 我们可以这样生成最近三个月的月份： DATE_FORMAT(date, '%Y-%m') 作为月份 然后筛选出月份在最近三个自然月内的数据。 但是，注意：我们只需要每个ITEM和PROJECT在最近三个月的三个值（三个月，所以会有三行数据？）。实际上，我们希望每个ITEM和PROJECT在结果中有一行，然后每个月的值作为单独的一列？还是将三个月的数据合并成一个列表？问题要求生成HTML列表，所以我们需要将每个ITEM和PROJECT对应的三个月的数据放在一行中，可能以三个单独的列（比如monthRate1, monthRate2, monthRate3）或者一个包含三个值的列表列。 根据需求，我们需要分别展示三个月的月度数据、四周的周度数据和七天的日度数据。因此，我们可能希望将月度数据展开为三列，周度数据展开为四列，日度数据展开为七列。 但是，这样会导致列数很多。另一种方式是将这些数据以列表的形式存储在一个列中（例如，月度数据列是一个包含三个元素的列表）。然后在生成HTML时再展开。 这里我们选择将每个ITEM和PROJECT的数据汇总成一行，其中： - 月度数据：三列，分别命名为month1, month2, month3 - 周度数据：四列，week1, week2, week3, week4 - 日度数据：七列，day1, day2, ... day7 这样就需要在SQL查询中分别进行行转列的操作（使用条件聚合）。但是，由于时间范围是固定的，我们可以为每个月、每周、每天分别聚合。 但是，注意：每个ITEM和PROJECT在最近三个月可能不是每个月都有数据，我们需要确保没有数据的月份显示为NULL（或0？）。 由于这个转换在SQL中可能比较复杂，我们可以先分别查询，然后在pandas中进行重塑。 b. 另一种思路：分别查询三个时间段的数据，然后分别生成三个DataFrame，再按照ITEM和PROJECT进行合并（外连接）。这样每个DataFrame的结构为： 月度DataFrame: [ITEM, PROJECT, month, avg_rate] 其中month是月份字符串（如'2023-09'），然后我们取最近三个月，所以每个ITEM和PROJECT最多有三条记录。然后我们将这三条记录转换成三列（使用pivot或pivot_table）。 周度DataFrame: [ITEM, PROJECT, week, avg_rate] 同样，week可以用年份和周数（如'2023-W36'）表示，然后取最近四周，转换成四列。 日度DataFrame: [ITEM, PROJECT, date, rate] 取最近七天，转换成七列。 4. 合并三个DataFrame： 使用ITEM和PROJECT作为键，将三个DataFrame合并成一个大的DataFrame（左连接或外连接，确保所有ITEM和PROJECT都出现）。 5. 生成HTML： 使用pandas的to_html方法将合并后的DataFrame转换成HTML表格。 6. 写入文件。 考虑到复杂度，我们选择分别查询并分别处理成宽表，然后合并。 具体步骤： 步骤1：连接数据库 步骤2：定义三个函数分别查询月度、周度和日度数据，并返回处理后的DataFrame（每个DataFrame的列包括ITEM, PROJECT以及对应的多个时间段的rate值，且每个DataFrame的每一行是唯一的ITEM和PROJECT组合） 月度数据DataFrame处理： 查询结果：每个ITEM, PROJECT在最近三个月每个月的平均rate。然后我们将三个月的数据转置为三列（每个月份一列）。注意，最近三个月我们按倒序排列（即最近的一个月为第一个月）。 周度数据DataFrame处理：类似，将最近四周的数据转置为四列。 日度数据DataFrame处理：将最近七天的数据转置为七列（按日期倒序排列，最近的一天为第一天）。 步骤3：合并三个DataFrame 步骤4：生成HTML 步骤5：保存HTML文件 下面我们逐步实现： 首先，导入必要的库： - pymysql（或其他数据库连接库） - pandas - datetime 用于日期计算 注意：由于不同数据库的日期函数不同，这里以MySQL为例。 假设当前日期为今天，我们需要计算： - 最近三个月的月份：例如，当前日期是2023-10-05，则最近三个月是2023-09, 2023-08, 2023-07（注意：我们取完整的自然月，所以10月还没结束，我们取7、8、9月） - 最近四周：从当前日期往前推28天，然后按周分组（这里我们使用ISO周，即周一到周日为一周）。注意：我们要求的是最近四周（包括本周在内，但本周可能不完整，所以我们可以取上周日往前推28天，即四周前的周一到上周日？）。为了简化，我们直接按自然周（周一为第一天）分组，取最近四周（完整周）。 - 最近七天：就是当前日期往前推7天（包括今天？还是不包括？这里我们包括今天，即从7天前到今天，共7天） 注意：实际应用中，可能需要根据业务需求调整周和月的计算。 由于日期范围计算可能比较复杂，我们分别计算： 月度数据： 最近三个月的开始月份和结束月份：我们可以获取当前日期的上个月、上上个月、上上上个月。但注意，我们想要的是三个完整的自然月，所以不包括当前月（因为当前月可能未结束）。因此，最近三个月应该是：上个月、上上个月、上上上个月。 例如：当前为2023-10-05，则最近三个月为：2023-09、2023-08、2023-07。 如何计算： 我们可以用当前日期减去3个月，然后得到每个月的月份字符串。 具体步骤： 1. 获取当前日期，并设置为当月的第一天（这样减去一个月就是上个月的第一天） 2. 循环三次，每次减去一个月，得到三个月份字符串（格式为'%Y-%m'） 周度数据： 我们取最近四周（完整周），即不包括本周（因为本周可能不完整）。所以最近四周应该是：上周、上上周、上上上周、上上上上周。 如何计算：从当前日期往前推7天，然后找到上一个周一（即最近一个完整周的结束是上周日，那么完整周的范围是周一到周日）。然后我们取四个完整周，即从当前日期往前推4周（28天）到当前日期往前推1天（这样得到四个完整周？）实际上，我们需要四个完整周的数据，所以我们可以计算四个周的起始和结束日期。 但是，为了简化，我们可以使用ISO周。我们可以计算出最近四个ISO周的年份和周数。 步骤： 当前日期减去7天，得到上一周的结束（即上周日），然后这一周就是上一周（ISO周）。然后依次再减7天，得到再上一周，共四周。 但是，在SQL中，我们可以用YEARWEEK函数（MySQL）来获取周数（注意模式，我们使用模式3：周一为一周的第一天，并且第一周是第一个有4天以上的那一周？）。这里我们使用模式3。 因此，最近四周的周数：当前日期减去28天到当前日期，然后按周分组，但注意，我们要求的是四个完整的周，所以应该不包括本周（因为本周不完整）。因此，我们取从当前日期往前推28天到当前日期往前推1天（即不包括今天）的数据，然后按周分组。这样就是四个完整的周。 但是，这样会包含五个周的部分数据（因为28天正好是四周，但如果我们包括今天，那么今天属于本周，所以不包括今天，那么就是28天前的0点到昨天，正好是四个整周？）。所以我们的时间范围是：从当前日期往前推28天（即date_sub(now(), interval 28 day)）到昨天（即date_sub(now(), interval 1 day)）。 日度数据： 最近七天：包括今天？如果包括今天，那么就是当前日期往前推6天（因为今天算一天）到当前日期。如果不包括今天，那么就是往前推7天到昨天。这里我们假设包括今天，即最近七天（从7天前到今天，共7天）。 但是，请注意：业务上可能要求不包括今天（因为今天的数据可能不完整），所以我们可以根据实际情况调整。这里我们假设包括今天。 由于日期范围计算可能因业务需求而异，以下代码将按照以下假设： - 月度数据：取上个月、上上个月、上上上个月（即不包括当前月）的完整月数据。 - 周度数据：取不包括本周的最近四周（即上周、上上周、上上上周、上上上上周）的完整周数据。 - 日度数据：取包括今天在内的最近七天。 开始编写代码： 1. 连接数据库（这里以MySQL为例，使用pymysql） 2. 定义三个函数：query_monthly_data, query_weekly_data, query_daily_data 月度数据查询函数： 计算三个月份：month1, month2, month3（字符串格式，如'2023-09'） 查询语句： SELECT item, project, DATE_FORMAT(date, '%Y-%m') as month, AVG(rate) as avg_rate FROM rate_table WHERE DATE_FORMAT(date, '%Y-%m') IN (%s, %s, %s) GROUP BY item, project, month 然后，我们需要将这三个月的数据转置为三个列。我们可以使用pandas的pivot_table函数。 在pandas中： df_month = pd.read_sql(sql, con, params=[month1, month2, month3]) # 然后我们需要将每个ITEM和PROJECT的三行（三个月）转成三列 # 首先，我们只保留每个ITEM和PROJECT在最近三个月的数据，然后转置 # 使用pivot_table，以item和project为index，以month为columns，以avg_rate为values df_month_pivot = df_month.pivot_table(index=['item','project'], columns='month', values='avg_rate').reset_index() # 然后，我们只取我们需要的三个月（按顺序：month1, month2, month3） # 但是，由于pivot_table后列名是月份字符串，我们需要重命名列，也可以不重命名，后面合并时再处理。 # 但是，我们想要列名为month1, month2, month3，分别对应最近一个月、第二个月、第三个月（注意：最近一个月是上个月，即月份最大的那个月，因为月份字符串排序是递增的，所以最近一个月是最大的月份？） # 但是，我们计算的月份字符串month1,month2,month3是倒序排列的（即第一个是上个月，第二个是上上个月，第三个是上上上个月）。所以我们在查询前已经知道三个月份字符串，并且按从近到远排序（month1最近，month3最远）。 # 因此，我们可以将这三个月份字符串作为我们需要的列，然后从df_month_pivot中选取这三列，并重命名为'month1','month2','month3' # 但是，注意：不是每个ITEM和PROJECT都有这三个月的数据，所以可能有些列是NaN。 # 步骤： # 先创建一个包含所有ITEM和PROJECT组合的DataFrame（从原始表获取？或者我们不需要，因为后面是外连接，所以可以允许缺失） # 然后，我们直接取df_month_pivot中的三个月份列，并重命名。 # 但是，pivot_table后，列名是月份字符串，我们需要将这三列重命名为'month1','month2','month3' # 假设我们计算好的三个月份字符串是：months = [month1, month2, month3] （按从近到远排序） # 然后： # df_month_pivot.columns = ['item','project'] + months # 注意：pivot_table后，columns是月份，所以列名是月份字符串，但前面还有两个索引列 # 但是，pivot_table后，列是多级索引，我们需要处理一下。 实际上，pivot_table后，columns参数指定的列会变成列索引，我们需要重置列名为字符串。我们可以这样： df_month_pivot = df_month.pivot(index=['item','project'], columns='month', values='avg_rate').reset_index() df_month_pivot.columns = ['item','project'] + [f'month_{i+1}' for i in range(len(months))] # 但是这样不对，因为列的顺序不一定是我们指定的months顺序 所以，我们需要确保列的顺序是我们指定的顺序。我们可以： # 先按months的顺序从df_month_pivot中选取列 # 但是，如果某个月份在df_month_pivot中不存在，那么该列就不存在，所以我们需要先确保df_month_pivot包含这三个月份列（如果不存在，则添加空列） for m in months: if m not in df_month_pivot.columns: df_month_pivot[m] = None # 或者用NaN # 然后按我们想要的顺序排列列：['item','project'] + months df_month_pivot = df_month_pivot[['item','project'] + months] # 然后重命名这三列 new_columns = ['item','project'] + [f'month_{i+1}' for i in range(len(months))] df_month_pivot.columns = new_columns 周度数据类似： 先计算最近四周的周标识（例如，使用ISO周，格式为'%G-%V'，其中%G是ISO年，%V是ISO周数，注意：MySQL的YEARWEEK函数默认模式是0（周日开始），模式3是周一开始，所以使用YEARWEEK(date,3)可以得到ISO周数。但是，我们想要格式如'2023-36'） 查询： SELECT item, project, CONCAT(YEAR(date), '-', LPAD(WEEK(date,3),2,'0')) as week, # 使用模式3，并且补零 AVG(rate) as avg_rate FROM rate_table WHERE date >= date_sub(now(), interval 28 day) AND date < date_sub(now(), interval 0 day) # 注意：不包括今天，因为今天属于本周，不完整 GROUP BY item, project, week 然后，我们需要知道最近四周的周标识（按从近到远排序）。计算最近四周的周标识（在Python中计算）： from datetime import datetime, timedelta today = datetime.today().date() # 最近四周：从今天往前推28天（不包括今天）到29天前（即从29天前到28天前，然后27天前到26天前，等等？）不对，我们应该按周分组，所以我们需要四个完整的周。 # 实际上，我们取的是从28天前到昨天，这28天正好是四周（因为不包括今天）。然后我们按周分组，得到四个周的数据。 # 但是，我们如何得到这四个周的周标识？我们可以用数据库的GROUP BY，但这样我们取出的周标识是四个（按时间顺序，但可能不是按从近到远排序）。所以我们需要在Python中排序。 另一种方法：在SQL中按周分组后，我们只取这四个周，并且我们可以在SQL中按周降序排列，然后取前四周？但是，可能同一个周标识出现在不同年份，所以按周标识排序可能不对。 所以，我们可以在查询后，在pandas中按周的日期排序（通过将周标识转换为日期？），或者我们可以在SQL中按周的最大日期排序（因为每个周有一个结束日期，我们可以取每周的最后一天作为排序依据）。 为了简化，我们不在SQL中排序，而是在pandas中处理：我们将周标识转换为该周的最后一天（周日）的日期，然后按这个日期排序（降序），然后取前四周？但是，我们查询的时候已经限制了四周，所以应该只有四周的数据。 但是，注意：可能某个ITEM和PROJECT在某个周没有数据，所以不会出现在结果中。所以我们需要在转置前确保每个ITEM和PROJECT都有四周的数据（如果没有，则用NaN填充）。 步骤： df_week = pd.read_sql(sql, con) # 然后，我们需要为每个ITEM和PROJECT生成四周的数据（如果缺失则用NaN） # 但是，我们想要的是最近四周，所以我们需要知道最近四周的周标识（按从近到远排序）。我们可以在Python中生成最近四周的周标识列表（weeks_ordered）。 如何生成最近四周的周标识？ 从今天往前推7天（得到上周日）的日期，然后计算这个日期的ISO周，然后依次往前推7天，得到再前一周，共四周。 # 计算最近四周的周标识（按从近到远排序）： weeks_ordered = [] today = datetime.today().date() for i in range(1,5): # 从1到4，表示第1周（最近一周）到第4周（最远一周） # 计算上周日：因为ISO周中周日是最后一天，所以上周日 = today - timedelta(days=today.weekday()+1) ？不对，注意：ISO周中周一是第一天，周日是第七天。 # 我们可以：找到上一个周日：today - timedelta(days=(today.isoweekday() % 7) # isoweekday()返回1-7（周一到周日），所以周日是7。 # 上一个周日：today - timedelta(days=(today.isoweekday() % 7) ？不对，应该是：如果今天是周一（isoweekday=1），那么上一个周日就是昨天？不对，应该是前6天。 # 实际上，上一个周日 = today - timedelta(days=today.isoweekday()) # 因为如果今天是周一（1），那么减去1天是周日？不对，应该是减去1天得到的是周日吗？不对，周一减1天是周日？不对，周一减1天是上周日？不对，应该是今天减去isoweekday()天得到上周一？不对。 # 更简单：我们想要的是最近一个完整周（上周）的周日，那么就是：今天减去 isoweekday() 天（得到上周日？）不对，因为isoweekday()=1（周一）时，减去1天是周日（但那是昨天，不是上周日）。所以应该是：今天减去 (isoweekday() + 6) 天？不对。 # 实际上，最近一个完整周（上周）的周日是：今天 - timedelta(days=today.isoweekday() + 6) # 这样不对。 # 改为：上周日的日期 = today - timedelta(days=today.isoweekday()) # 因为ISO周中，周一为1，那么周日为7。所以，如果今天是周一（1），那么减去1天就是周日（昨天），但昨天属于上周吗？在ISO周中，本周的周一是今天，所以昨天（周日）属于上周（因为上周从周一到周日已经结束）。所以这样计算是对的：上周日 = today - timedelta(days=today.isoweekday()) # 然后，再往前推(i-1)*7天，就得到第i个周（i=1表示最近一周，i=4表示第四周）的周日。 last_sunday = today - timedelta(days=today.isoweekday() + (i-1)*7) # 然后计算这个周日的ISO周：使用strftime('%G-%V')，注意：%G是ISO年，%V是ISO周数 week_str = last_sunday.strftime('%G-%V') weeks_ordered.append(week_str) # 注意：这样计算可能跨年，所以使用%G（ISO年）是正确的。 然后，我们使用pivot_table将df_week转置为宽表，并确保列顺序为weeks_ordered。 df_week_pivot = df_week.pivot(index=['item','project'], columns='week', values='avg_rate').reset_index() # 然后，确保df_week_pivot包含weeks_ordered中的所有周 for w in weeks_ordered: if w not in df_week_pivot.columns: df_week_pivot[w] = None # 然后按列['item','project'] + weeks_ordered 选取 df_week_pivot = df_week_pivot[['item','project'] + weeks_ordered] # 重命名列 new_columns = ['item','project'] + [f'week_{i+1}' for i in range(len(weeks_ordered))] df_week_pivot.columns = new_columns 日度数据： 查询最近七天（包括今天）的数据： SELECT item, project, date, rate FROM rate_table WHERE date >= date_sub(now(), interval 6 day) # 6天前（包括）到今天 然后，我们想要将最近七天按日期升序排列（因为日期越近越重要，但HTML中我们希望最近的一天在最后？还是最前？这里我们希望按时间顺序从左到右：最远的一天在左，最近的一天在右？或者反过来？） 根据需求，我们要求“最近七天的每日数据”，所以我们可以按日期倒序（即最近的一天在第一个）来排列，然后转置为七列，列名为day1（最近一天）到day7（七天前）。 所以，在查询后，我们可以按日期降序排列（这样最近的一天在最前面），然后每个ITEM和PROJECT有七行（可能不足七行，用NaN填充）。 步骤： df_daily = pd.read_sql(sql, con) # 然后，我们需要为每个ITEM和PROJECT生成七天的数据（如果某天没有数据，则为NaN） # 首先，我们获取最近七天的日期字符串（格式为'%Y-%m-%d'）列表，按从近到远排序（即日期从大到小） dates_ordered = [(today - timedelta(days=i)).strftime('%Y-%m-%d') for i in range(0,7)] # 从0到6，所以i=0是今天，i=1是昨天，... i=6是7天前 dates_ordered.reverse() # 这样，最近的一天（今天）在最后？但我们希望转置后列名为day1（今天）、day2（昨天）... day7（七天前），所以顺序应该是从今天到七天前，即dates_ordered应该是['2023-10-05','2023-10-04',...,'2023-09-28']，然后我们按这个顺序排列数据。 但是，在转置时，我们希望每个ITEM和PROJECT的七天数据按这个顺序排列（即今天为第一个，昨天为第二个，...，七天前为第七个）。所以我们需要将日期作为分类变量，并设置顺序为dates_ordered（但注意，dates_ordered是降序的日期字符串，而我们需要的是从今天到七天前，所以顺序就是dates_ordered（因为我们是按从今天到七天前生成的）？不对，我们生成dates_ordered时是i从0到6，所以第一个是今天，然后是昨天，所以dates_ordered是['2023-10-05','2023-10-04',...]（从大到小）。所以这个顺序就是我们想要的（从最近到最远）。 # 然后，我们使用pivot_table，以item和project为索引，以date为列，以rate为值。 df_daily_pivot = df_daily.pivot(index=['item','project'], columns='date', values='rate').reset_index() # 然后，确保df_daily_pivot包含dates_ordered中的所有日期 for d in dates_ordered: if d not in df_daily_pivot.columns: df_daily_pivot[d] = None # 然后按['item','project'] + dates_ordered 选取列 df_daily_pivot = df_daily_pivot[['item','project'] + dates_ordered] # 重命名列：['item','project'] + [f'day_{i+1}' for i in range(7)] new_columns = ['item','project'] + [f'day_{i+1}' for i in range(7)] df_daily_pivot.columns = new_columns 3. 合并三个DataFrame： df_merged = pd.merge(df_month_pivot, df_week_pivot, on=['item','project'], how='outer') df_merged = pd.merge(df_merged, df_daily_pivot, on=['item','project'], how='outer') 4. 生成HTML： html_table = df_merged.to_html(index=False) 5. 写入文件： with open('output.html', 'w') as f: f.write(html_table) 注意：以上代码是示意性的，实际执行时可能需要调整，特别是日期计算和SQL查询部分。 由于日期计算在SQL和Python中可能比较繁琐，而且不同的数据库系统有不同的日期函数，这里以MySQL为例，并且假设数据库中的日期字段为`date`（类型为DATE或DATETIME）。 另外，我们假设表中有大量数据，所以查询时可能需要优化（比如在SQL中先过滤再聚合，并且使用索引）。 下面给出完整的代码示例（注意：需要根据实际数据库表结构调整）： 注意：由于我们使用了三个子查询，并且每个子查询都进行了聚合，然后将结果合并，所以可能会比较慢，特别是当数据量很大时。可以考虑在SQL中一次性完成，但那样SQL会非常复杂。 因此，我们选择在Python中分别查询并合并。 代码示例： 注意：以下代码需要根据实际数据库连接信息修改，并且可能需要调整日期计算逻辑。</think>我将为您设计一个Python解决方案，从数据库提取数据并按指定维度分组，生成HTML格式的列表文件。以下是完整实现： ```python import pandas as pd import pymysql from datetime import datetime, timedelta import numpy as np # 数据库连接配置（根据实际情况修改） DB_CONFIG = { 'host': 'your_host', 'user': 'your_username', 'password': 'your_password', 'database': 'your_database', 'port': 3306 } def fetch_data(): """从数据库提取并处理数据""" try: # 连接数据库 conn = pymysql.connect(**DB_CONFIG) # 计算日期范围 today = datetime.now().date() three_months_ago = (today - timedelta(days=90)).strftime('%Y-%m-%d') four_weeks_ago = (today - timedelta(days=28)).strftime('%Y-%m-%d') seven_days_ago = (today - timedelta(days=7)).strftime('%Y-%m-%d') # 构建SQL查询 sql = f""" SELECT ITEM, PROJECT, DATE, rate FROM your_table_name WHERE DATE >= '{three_months_ago}' """ # 读取数据到DataFrame df = pd.read_sql(sql, conn) # 转换日期格式 df['DATE'] = pd.to_datetime(df['DATE']) # 按维度分组并计算聚合值 monthly = (df[df['DATE'] >= three_months_ago] .groupby(['ITEM', 'PROJECT', pd.Grouper(key='DATE', freq='M')]) ['rate'].mean().rename('monthRate').reset_index()) weekly = (df[df['DATE'] >= four_weeks_ago] .groupby(['ITEM', 'PROJECT', pd.Grouper(key='DATE', freq='W-MON')]) ['rate'].mean().rename('weekRate').reset_index()) daily = (df[df['DATE'] >= seven_days_ago] .groupby(['ITEM', 'PROJECT', pd.Grouper(key='DATE', freq='D')]) ['rate'].mean().rename('dailyRate').reset_index()) return monthly, weekly, daily except Exception as e: print(f"数据库错误: {e}") return None, None, None finally: if 'conn' in locals() and conn.open: conn.close() def generate_html(monthly, weekly, daily): """生成HTML格式的报告""" html_content = """ <!DOCTYPE html> <html> <head> <title>数据分析报告</title> <style> body { font-family: Arial, sans-serif; margin: 20px; } h1 { color: #2c3e50; border-bottom: 2px solid #3498db; padding-bottom: 10px; } h2 { color: #2980b9; margin-top: 30px; } table { border-collapse: collapse; width: 100%; margin-bottom: 30px; } th, td { border: 1px solid #ddd; padding: 12px; text-align: left; } th { background-color: #3498db; color: white; } tr:nth-child(even) { background-color: #f2f2f2; } .section { margin-bottom: 40px; } </style> </head> <body> <h1>数据分析报告</h1> <p>生成时间： {timestamp}</p> <div class="section"> <h2>月度数据（最近三个月）</h2> {monthly_table} </div> <div class="section"> <h2>周度数据（最近四周）</h2> {weekly_table} </div> <div class="section"> <h2>每日数据（最近七天）</h2> {daily_table} </div> </body> </html> """ # 格式化日期列 monthly['DATE'] = monthly['DATE'].dt.strftime('%Y-%m') weekly['DATE'] = weekly['DATE'].dt.strftime('%Y-%W周') daily['DATE'] = daily['DATE'].dt.strftime('%Y-%m-%d') # 生成表格 monthly_table = monthly.to_html(index=False, classes='data-table') weekly_table = weekly.to_html(index=False, classes='data-table') daily_table = daily.to_html(index=False, classes='data-table') # 填充模板 final_html = html_content.format( timestamp=datetime.now().strftime('%Y-%m-%d %H:%M:%S'), monthly_table=monthly_table, weekly_table=weekly_table, daily_table=daily_table ) # 保存HTML文件 with open('data_report.html', 'w', encoding='utf-8') as f: f.write(final_html) return final_html if __name__ == "__main__": monthly, weekly, daily = fetch_data() if monthly is not None and not monthly.empty: html_report = generate_html(monthly, weekly, daily) print("HTML报告已成功生成：data_report.html") else: print("未获取到有效数据，请检查数据库连接和查询条件") ``` ### 关键功能说明： 1. **数据库连接与查询** - 使用`pymysql`连接MySQL数据库 - 动态计算日期范围（最近3月/4周/7天） - 参数化SQL查询防止注入攻击 2. **多维数据处理** - 按`ITEM`和`PROJECT`分组 - 使用`pd.Grouper`进行时间维度聚合： - `freq='M'` 按月聚合 - `freq='W-MON'` 按周聚合（周一为起点） - `freq='D'` 按日聚合 3. **HTML报告生成** - 响应式表格设计 - CSS样式美化 - 自动时间戳标记 - 三部分独立数据展示 ### 使用前需配置： 1. 修改`DB_CONFIG`中的数据库连接参数 2. 替换SQL中的`your_table_name`为实际表名 3. 确保数据库包含以下字段： - `ITEM`：项目类别 - `PROJECT`：具体项目 - `DATE`：日期字段 - `rate`：需要分析的数值 ### 输出示例： 生成的HTML报告包含三个清晰部分： 1. **月度数据**：按月份分组的平均rate 2. **周度数据**：按周分组的平均rate 3. **每日数据**：按日期分组的平均rate 每个表格包含ITEM、PROJECT、时间周期和对应的rate值，表格样式专业且易于阅读[^1][^3]。 ### 注意事项： 1. 时间范围计算基于当前系统时间 2. 周计算以周一作为每周起点 3. 空值自动处理为NaN 4. 报告包含生成时间戳确保数据时效性 5. 使用UTF-8编码支持中文显示 如需处理Excel输出或更复杂的数据清洗操作，可参考pandas的`to_excel()`方法和数据预处理技术[^1][^4]。