基于R语言制作数据库表统计信息老化直方图的技术求助

需求 #

统计数据库各表上次生成统计信息的天数，按30天、60天、90天、120+天的会计账龄区间分组，制作以NUM_TABLES为频次的老化直方图。

现有数据 #

数据包含两列：

• STATS_DATE：统计信息上次更新日期（数值型，格式YYYYMMDD）
• NUM_TABLES：对应日期更新统计信息的表数量

数据结构示例：

> dput(df)
structure(list(STATS_DATE = c(20210908L, 20240814L, 20240815L
), NUM_TABLES = c(5L, 193L, 746L)), class = "data.frame", row.names = c(NA, 3L))

> sapply(df, mode)
STATS_DATE NUM_TABLES 
 "numeric"  "numeric" 

已完成步骤 #

已实现目标日期与统计更新日期的天数差计算：

# 目标日期（带时区）
DATE <- parse_date_time("2024-08-16", "ymd", tz = "US/Central")

# 计算天数差
df$DAYS <- as.numeric(DATE - df$STATS_DATE)

遇到的问题 #

• STATS_DATE列转换错误：直接用parse_date_time处理数值型日期导致后续逻辑异常
• 使用stats_dates <- stats_dates[stats_dates <= ref_date]会清空数据
• 移除过滤行后报错：Error in hist.default(...) : some 'x' not counted; maybe 'breaks' do not span range of 'x'

解决方案 #

步骤1：修复日期转换 #

数值型的STATS_DATE需先转为字符型，再解析为日期格式：

library(lubridate)

# 转换STATS_DATE为日期型（先转字符避免解析错误）
df$STATS_DATE <- ymd(as.character(df$STATS_DATE), tz = "US/Central")

# 定义参考日期（与目标日期一致）
ref_date <- ymd("2024-08-16", tz = "US/Central")

# 重新计算天数差
df$DAYS <- as.numeric(ref_date - df$STATS_DATE)

步骤2：按账龄区间分组统计 #

无需展开重复行，直接基于现有频次统计各区间表数量：

# 定义账龄区间
age_bins <- c(0, 30, 60, 90, Inf)
age_labels <- c("0-30天", "31-60天", "61-90天", "120+天")

# 分组并统计频次
df$AGE_GROUP <- cut(df$DAYS, breaks = age_bins, labels = age_labels, right = FALSE)
age_summary <- aggregate(NUM_TABLES ~ AGE_GROUP, data = df, sum)

步骤3：制作直方图 #

方案1：基础包hist函数

# 生成用于直方图的加权数据（仅当需要用hist原生函数时）
weighted_days <- rep(df$DAYS, df$NUM_TABLES)

# 绘制直方图
hist(weighted_days, 
     breaks = age_bins,
     freq = TRUE,
     xlab = "统计信息老化天数",
     ylab = "表数量",
     main = "数据库表统计信息老化分布",
     col = hcl.colors(4, "heat", rev = TRUE),
     xaxt = "n")

# 自定义x轴标签
axis(1, at = c(15, 45, 75, 150), labels = age_labels)

方案2：ggplot2（更直观，推荐）

library(ggplot2)

ggplot(age_summary, aes(x = AGE_GROUP, y = NUM_TABLES, fill = AGE_GROUP)) +
  geom_bar(stat = "identity") +
  scale_fill_hue(l = 40) +
  labs(title = "数据库表统计信息老化分布",
       x = "老化天数区间",
       y = "表数量") +
  theme_minimal() +
  theme(legend.position = "none")

关键说明 #

• 日期转换必须先转字符：数值型的20210908直接解析会被识别为时间戳而非日期，转为字符后ymd才能正确解析。
• 避免展开大数量行：当NUM_TABLES数值很大时，rep会生成海量数据，直接用aggregate分组统计更高效。
• 区间设置：right = FALSE确保区间左闭右开，符合会计账龄的统计逻辑（如0-30天包含第30天）。

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