LR模型快速生成SQL脚本

LR模型快速生成SQL脚本

由于经常需要部署新LR模型，需要以SQL的方式在数仓环境中运行。
固化LR模型的形式是excel文件，所以写了一个脚本解析这个excel文件，完成模型的快速SQL化

LR模型部署模板

第一行是名称列可以不用管，第二行是线性模型基础得分
第三行开始：第一列是表中的列，第二列是表中的列的所在的区间，第三列是区间对应的分数
区间可以适配四种格式：1.{'missing'}空类型 2.[-inf,) 以最小值开始的区间 3.[,) 正常的数字区间 4.[, inf) 以最大值结束的区间

var_name	interval	score
base_score	999	999
column_name	{'missing'}	3
column_name	[-inf, 2.0)	4
column_name	[2.0, 3.0)	5
column_name	[3.0, 4.0)	6
column_name	[4.0, inf)	7

解析excel代码

依赖
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.0</version>
        </dependency>

##正式代码

import java.io.FileInputStream

import org.apache.poi.ss.usermodel.DataFormatter
import org.apache.poi.xssf.usermodel.XSSFWorkbook

object parse_excel {
  def main(args: Array[String]): Unit = {
    val inputStream = new FileInputStream("bs_small.xlsx")
    val model_name = "model_name"
    val date = "20230419"
    val workbook = new XSSFWorkbook(inputStream)
    val column_case_string = new StringBuilder

    val row_length = workbook.getSheetAt(0).getLastRowNum
    // 选择要读取的工作表（这里选择第一个工作表）
    val sheet = workbook.getSheetAt(0)
    var base_score = 0
    val total_score_stringBuilder = new StringBuilder
    val column_string = new StringBuilder

    // 遍历每一行，并读取每一列的数据
    for (row <- 1 to row_length) {
      val row_data = sheet.getRow(row)
      val formatter = new DataFormatter()
      val col_name = formatter.formatCellValue(row_data.getCell(0))
      val interval = formatter.formatCellValue(row_data.getCell(1))
      val score = formatter.formatCellValue(row_data.getCell(2))
      val pre_col = if (row - 1 > row_length) "aaa" else formatter.formatCellValue(sheet.getRow(row - 1).getCell(0))
      val next_col = if (row + 1 > row_length) "bbb" else formatter.formatCellValue(sheet.getRow(row + 1).getCell(0))
      if (row == 1) {
        base_score = score.toInt //拿到基础分
        total_score_stringBuilder.append(base_score)
      }
      else {
        if (interval.contains("missing")) { //判断为空的默认分数
          column_string.append(col_name + ",")
          column_case_string.append(" case when " + col_name + " is null then " + score + " ")
        }
        else if (interval.contains("[-inf")) { //小于第二个参数
          if (!pre_col.eq(col_name)) { //判断当前没有miss的时候  直接一个最小值
            column_string.append(col_name + ",")
            val value = interval.split(",").apply(1).trim.replaceAll("\\)", "").toDouble.toInt
            column_case_string.append(" case when " + col_name + " < " + value + " then " + score + " ")
          } else {
            val value = interval.split(",").apply(1).trim.replaceAll("\\)", "").toDouble.toInt
            column_case_string.append("when " + col_name + " < " + value + " then " + score + " ")
          }
        }
        else if (interval.contains("inf)")) { //大于等于第一个参数
          val value = interval.split(",").apply(0).trim.replaceAll("\\[", "").toDouble.toInt
          column_case_string.append("when " + col_name + " >= " + value + " then " + score + " ")
        }
        else {
          val value = interval.split(",").apply(1).trim.replaceAll("\\)", "").toDouble.toInt
          column_case_string.append("when " + col_name + " < " + value + " then " + score + " ")
        }
      }
      if (row != 1 && !next_col.eq(col_name)) { //生成最终的as语句和 select 外层的base_score+列
        total_score_stringBuilder.append("+")
        total_score_stringBuilder.append(model_name)
        total_score_stringBuilder.append("_")
        total_score_stringBuilder.append(col_name)

        column_case_string.append(" else 0 end as " + model_name + "_" + col_name + "," + "\r\n")
      }
    }
    total_score_stringBuilder.append(" as ")
    total_score_stringBuilder.append(model_name)


    println(
      s"""
          select
          ${column_string}
          ${total_score_stringBuilder.toString()}
          from (
            select
            ${column_case_string.toString()}
            ${column_string}
            '' as mm
            from table where dt='${date}'
          )t
      """.stripMargin)
  }
}